Spring Boot2 系列教程 (六) | 使用 JdbcTemplates 访问 Mysql
前言
如题，今天介绍 springboot 通过jdbc访问关系型mysql,通过 spring 的 JdbcTemplate 去访问。
准备工作
- SpringBoot 2.x
- jdk 1.8
- maven 3.0
- idea
- mysql
构建 SpringBoot 项目，不会的朋友参考旧文章：如何使用 IDEA 构建 Spring Boot 工程
项目目录结构
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);
}
}
演示结果
其他的 api 测试可以通过 postman 测试。我这里已经全部测试通过，请放心使用。
源码下载：https://github.com/turoDog/Demo/tree/master/jdbctemplate_demo
后语
以上SpringBoot 用 JdbcTemplates 访问Mysql 的教程。最后，对 Python 、Java 感兴趣请长按二维码关注一波，我会努力带给你们价值，如果觉得本文对你哪怕有一丁点帮助，请帮忙点好看，让更多人知道。
如果看到这里，说明你喜欢这篇文章，请转发、点赞。微信搜索「一个优秀的废人」，关注后回复「1024」送你一套完整的 java 教程。
