SpringBoot 实战 (六) | 用 JdbcTemplates 访问 Mysql

今天药忘吃喽~ 2022-03-19 14:46 193阅读 0赞

> 微信公众号：一个优秀的废人  
> 如有问题或建议，请后台留言，我会尽力解决你的问题。

#### 前言 ####

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

#### 准备工作 ####

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

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

#### 项目目录结构 ####

![项目目录][1240]

#### pom 文件引入依赖 ####

<dependencies>
 
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-jdbc</artifactId>
 </dependency>
 
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-web</artifactId>
 </dependency>
 
 
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <scope>runtime</scope>
 </dependency>
 
 
 <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 * Package Name:com.nasus.domain * Date:2019/2/3 10:55 * Description: TODO: 描述该类的作用 * * @author <a href="turodog@foxmail.com">nasus</a> * 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 * Package Name:com.nasus.dao * Date:2019/2/3 10:59 * Description: TODO: 描述该类的作用 * @author <a href="turodog@foxmail.com">nasus</a> */
 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 * Package Name:com.nasus.dao.impl * Date:2019/2/3 11:00 * Description: TODO: 描述该类的作用 * * @author <a href="turodog@foxmail.com">nasus</a> */
 @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 * Package Name:com.nasus.service * Date:2019/2/3 11:17 * Description: TODO: 描述该类的作用 * * @author <a href="turodog@foxmail.com">nasus</a> */
 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 * Package Name:com.nasus.service.impl * Date:2019/2/3 11:18 * Description: TODO: 描述该类的作用 * * @author <a href="turodog@foxmail.com">nasus</a> * 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 * Package Name:com.nasus.controller * Date:2019/2/3 11:21 * Description: TODO: 描述该类的作用 * * @author <a href="turodog@foxmail.com">nasus</a> */
 @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);
 }
 }

#### 演示结果 ####

![查询全部学生结果][1240 1]

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

源码下载：[https://github.com/turoDog/Demo/tree/master/jdbctemplate\_demo][https_github.com_turoDog_Demo_tree_master_jdbctemplate_demo]

#### 后语 ####

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

另外，关注之后在发送 **1024** 可领取免费学习资料。资料内容详情请看这篇旧文：[Python、C++、Java、Linux、Go、前端、算法资料分享][Python_C_Java_Linux_Go]

![1240 2][]

[IDEA _ Spring Boot]: https://www.jianshu.com/p/83c0541165cf
[1240]: /images/20220319/e1d3301e637144e48eb2d95141bc86eb.png
[1240 1]: /images/20220319/89aa328c248c462f8f8aa10edd617ded.png
[https_github.com_turoDog_Demo_tree_master_jdbctemplate_demo]: https://github.com/turoDog/Demo/tree/master/jdbctemplate_demo
[Python_C_Java_Linux_Go]: https://www.jianshu.com/p/ed43dc8aa11e
[1240 2]: /images/20220319/7ce09a15b9574326ad8b38027007c113.png