Spring Boot2 系列教程 (六) | 使用 JdbcTemplates 访问 Mysql

2023-08-04 14:22:22

Spring Boot2 系列教程 (六) | 使用 JdbcTemplates 访问 Mysql

前言

如题，今天介绍 springboot 通过jdbc访问关系型mysql,通过 spring 的 JdbcTemplate 去访问。

准备工作

  • SpringBoot 2.x
  • jdk 1.8
  • maven 3.0
  • idea
  • mysql

构建 SpringBoot 项目，不会的朋友参考旧文章：如何使用 IDEA 构建 Spring Boot 工程

项目目录结构

Spring Boot2 系列教程 (六) | 使用 JdbcTemplates 访问 Mysql

pom 文件引入依赖

<dependencies>

        <!-- jdbcTemplate 依赖 -->

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-jdbc</artifactId>

        </dependency>

        <!-- 开启web: -->

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-web</artifactId>

        </dependency>

        <!-- mysql连接类 -->

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <scope>runtime</scope>

        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->

        <!-- druid 连接池-->

        <dependency>

            <groupId>com.alibaba</groupId>

            <artifactId>druid</artifactId>

            <version>1.1.13</version>

        </dependency>

     <dependency>

           <groupId>org.springframework.boot</groupId>

           <artifactId>spring-boot-starter-test</artifactId>

           <scope>test</scope>

    </dependency>

</dependencies>

application.yaml 配置数据库信息

spring:

  datasource:

    driver-class-name: com.mysql.jdbc.Driver

    url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=true

    username: 数据库用户名

    password: 数据库密码

实体类

package com.nasus.domain;

/**

 * Project Name:jdbctemplate_demo <br/>

 * Package Name:com.nasus.domain <br/>

 * Date:2019/2/3 10:55 <br/>

 * <b>Description:</b> TODO: 描述该类的作用 <br/>

 *

 * @author <a href="turodog@foxmail.com">nasus</a><br/>

 * Copyright Notice =========================================================

 * This file contains proprietary information of Eastcom Technologies Co. Ltd.

 * Copying or reproduction without prior written approval is prohibited.

 * Copyright (c) 2019 =======================================================

 */

public class Student {

    private Integer id;

    private String name;

    private Integer age;

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public Integer getAge() {

        return age;

    }

    public void setAge(Integer age) {

        this.age = age;

    }

    @Override

    public String toString() {

        return "Student{" +

                "id=" + id +

                ", name='" + name + '\'' +

                ", age=" + age +

                '}';

    }

}

dao 层

package com.nasus.dao;

import com.nasus.domain.Student;

import java.util.List;

/**

 * Project Name:jdbctemplate_demo <br/>

 * Package Name:com.nasus.dao <br/>

 * Date:2019/2/3 10:59 <br/>

 * <b>Description:</b> TODO: 描述该类的作用 <br/>

 * @author <a href="turodog@foxmail.com">nasus</a><br/>

 */

public interface IStudentDao {

    int add(Student student);

    int update(Student student);

    int delete(int id);

    Student findStudentById(int id);

    List<Student> findStudentList();

}

具体实现类：

package com.nasus.dao.impl;

import com.nasus.dao.IStudentDao;

import com.nasus.domain.Student;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.BeanPropertyRowMapper;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Repository;

/**

 * Project Name:jdbctemplate_demo <br/>

 * Package Name:com.nasus.dao.impl <br/>

 * Date:2019/2/3 11:00 <br/>

 * <b>Description:</b> TODO: 描述该类的作用 <br/>

 *

 * @author <a href="turodog@foxmail.com">nasus</a><br/>

 */

@Repository

public class IStudentDaoImpl implements IStudentDao{

    @Autowired

    private JdbcTemplate jdbcTemplate;

    @Override

    public int add(Student student) {

        return jdbcTemplate.update("insert into student(name, age) values(?, ?)",

                student.getName(),student.getAge());

    }

    @Override

    public int update(Student student) {

        return jdbcTemplate.update("UPDATE  student SET NAME=? ,age=? WHERE id=?",

                student.getName(),student.getAge(),student.getId());

    }

    @Override

    public int delete(int id) {

        return jdbcTemplate.update("DELETE from TABLE student where id=?",id);

    }

    @Override

    public Student findStudentById(int id) {

        // BeanPropertyRowMapper 使获取的 List 结果列表的数据库字段和实体类自动对应

        List<Student> list = jdbcTemplate.query("select * from student where id = ?", new Object[]{id}, new BeanPropertyRowMapper(Student.class));

        if(list!=null && list.size()>0){

            Student student = list.get(0);

            return student;

        }else{

            return null;

        }

    }

    @Override

    public List<Student> findStudentList() {

        // 使用Spring的JdbcTemplate查询数据库，获取List结果列表，数据库表字段和实体类自动对应，可以使用BeanPropertyRowMapper

        List<Student> list = jdbcTemplate.query("select * from student", new Object[]{}, new BeanPropertyRowMapper(Student.class));

        if(list!=null && list.size()>0){

            return list;

        }else{

            return null;

        }

    }

}

service 层

package com.nasus.service;

import com.nasus.domain.Student;

import java.util.List;

/**

 * Project Name:jdbctemplate_demo <br/>

 * Package Name:com.nasus.service <br/>

 * Date:2019/2/3 11:17 <br/>

 * <b>Description:</b> TODO: 描述该类的作用 <br/>

 *

 * @author <a href="turodog@foxmail.com">nasus</a><br/>

 */

public interface IStudentService {

    int add(Student student);

    int update(Student student);

    int delete(int id);

    Student findStudentById(int id);

    List<Student> findStudentList();

}

实现类：

package com.nasus.service.impl;

import com.nasus.dao.IStudentDao;

import com.nasus.domain.Student;

import com.nasus.service.IStudentService;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Repository;

/**

 * Project Name:jdbctemplate_demo <br/>

 * Package Name:com.nasus.service.impl <br/>

 * Date:2019/2/3 11:18 <br/>

 * <b>Description:</b> TODO: 描述该类的作用 <br/>

 *

 * @author <a href="turodog@foxmail.com">nasus</a><br/>

 * Copyright Notice =========================================================

 * This file contains proprietary information of Eastcom Technologies Co. Ltd.

 * Copying or reproduction without prior written approval is prohibited.

 * Copyright (c) 2019 =======================================================

 */

@Repository

public class IStudentServiceImpl implements IStudentService {

    @Autowired

    private IStudentDao iStudentDao;

    @Override

    public int add(Student student) {

        return iStudentDao.add(student);

    }

    @Override

    public int update(Student student) {

        return iStudentDao.update(student);

    }

    @Override

    public int delete(int id) {

        return iStudentDao.delete(id);

    }

    @Override

    public Student findStudentById(int id) {

        return iStudentDao.findStudentById(id);

    }

    @Override

    public List<Student> findStudentList() {

        return iStudentDao.findStudentList();

    }

}

controller 构建 restful api

package com.nasus.controller;

import com.nasus.domain.Student;

import com.nasus.service.IStudentService;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.DeleteMapping;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.PathVariable;

import org.springframework.web.bind.annotation.PostMapping;

import org.springframework.web.bind.annotation.PutMapping;

import org.springframework.web.bind.annotation.RequestBody;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

/**

 * Project Name:jdbctemplate_demo <br/>

 * Package Name:com.nasus.controller <br/>

 * Date:2019/2/3 11:21 <br/>

 * <b>Description:</b> TODO: 描述该类的作用 <br/>

 *

 * @author <a href="turodog@foxmail.com">nasus</a><br/>

 */

@RestController

@RequestMapping("/student")

public class StudentController {

    @Autowired

    private IStudentService iStudentService;

    @PostMapping("")

    public int addStudent(@RequestBody Student student){

        return iStudentService.add(student);

    }

    @PutMapping("/{id}")

    public String updateStudent(@PathVariable Integer id, @RequestBody Student student){

        Student oldStudent = new Student();

        oldStudent.setId(id);

        oldStudent.setName(student.getName());

        oldStudent.setAge(student.getAge());

        int t = iStudentService.update(oldStudent);

        if (t == 1){

            return student.toString();

        }else {

            return "更新学生信息错误";

        }

    }

    @GetMapping("/{id}")

    public Student findStudentById(@PathVariable Integer id){

        return iStudentService.findStudentById(id);

    }

    @GetMapping("/list")

    public List<Student> findStudentList(){

        return iStudentService.findStudentList();

    }

    @DeleteMapping("/{id}")

    public int deleteStudentById(@PathVariable Integer id){

        return iStudentService.delete(id);

    }

}

演示结果

Spring Boot2 系列教程 (六) | 使用 JdbcTemplates 访问 Mysql

其他的 api 测试可以通过 postman 测试。我这里已经全部测试通过，请放心使用。

源码下载：https://github.com/turoDog/Demo/tree/master/jdbctemplate_demo

后语

以上SpringBoot 用 JdbcTemplates 访问Mysql 的教程。最后，对 Python 、Java 感兴趣请长按二维码关注一波，我会努力带给你们价值，如果觉得本文对你哪怕有一丁点帮助，请帮忙点好看，让更多人知道。

如果看到这里，说明你喜欢这篇文章，请转发、点赞。微信搜索「一个优秀的废人」，关注后回复「1024」送你一套完整的 java 教程。

Spring Boot2 系列教程 (六) | 使用 JdbcTemplates 访问 Mysql

Spring Boot2 系列教程 (六) | 使用 JdbcTemplates 访问 Mysql

上一篇：java中 hashCode() 和 equals()


下一篇：MySQL系列教程（三）