SpringBoot 实战 (六) | 用 JdbcTemplates 访问 Mysql-白红宇

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

前言

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

准备工作

SpringBoot 2.x

jdk 1.8

maven 3.0

idea

mysql

构建 SpringBoot 项目，不会的朋友参考旧文章：

项目目录结构

pom 文件引入依赖


            
             
                 
      
       org.springframework.boot
                  
      
       spring-boot-starter-jdbc
              
             
             
                 
      
       org.springframework.boot
                  
      
       spring-boot-starter-web
              
             
             
                 
      
       mysql
                  
      
       mysql-connector-java
                  
      
       runtime
              
             
             
             
                 
      
       com.alibaba
                  
      
       druid
                  
      
       1.1.13
              
          
                
      
       org.springframework.boot
                 
      
       spring-boot-starter-test
                 
      
       test
          
     
    复制代码

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 nasus
 * 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 nasus
 */public interface IStudentDao {    int add(Student student);    int update(Student student);    int delete(int id);    Student findStudentById(int id);    List
    
      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 nasus
 */@Repositorypublic 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
    
      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
     
       findStudentList() {        // 使用Spring的JdbcTemplate查询数据库，获取List结果列表，数据库表字段和实体类自动对应，可以使用BeanPropertyRowMapper        List
      
        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 nasus
 */public interface IStudentService {    int add(Student student);    int update(Student student);    int delete(int id);    Student findStudentById(int id);    List
    
      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 nasus
 * Copyright Notice ========================================================= * This file contains proprietary information of Eastcom Technologies Co. Ltd. * Copying or reproduction without prior written approval is prohibited. * Copyright (c) 2019 ======================================================= */@Repositorypublic 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
    
      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 nasus
 */@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
    
      findStudentList(){        return iStudentService.findStudentList();    }    @DeleteMapping("/{id}")    public int deleteStudentById(@PathVariable Integer id){        return iStudentService.delete(id);    }}复制代码

演示结果

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

源码下载：

后语

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

另外，关注之后在发送 1024 可领取免费学习资料。资料内容详情请看这篇旧文：