SpringBoot学习笔记六:使用JdbcTemplate访问数据库

SpringJDBC框架承担了资源管理和异常处理的工作,将数据访问的样板代码抽象到模板类之中,从而简化了JDBC代码,使我们只需编写从数据库读写数据所必需的代码。
SpringJDBC提供了三个模板类供选择:

  • JdbcTemplate
    最基本的Spring JDBC模板,支持简单的JDBC数据访问功能以及基于索引参数的查询,上层接口为JdbcOperations
    This is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic, more informative exception hierarchy defined in the org.springframework.dao package.
  • NamedParameterJdbcTemplate
    使用该模板类进行查询时可以将值以命名参数的形式绑定到sql中,而不是使用简单的索引参数,上层接口为NamedParameterJdbcOperations
    Template class with a basic set of JDBC operations, allowing the useof named parameters rather than traditional '?' placeholders.
  • SimpleJdbcTemplate
    已废弃

导入依赖

pom.xml 中添加对 JdbcTemplate 的依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

连接数据库

application.yml中添加如下配置。值得注意的是,Spring Boot默认会自动配置DataSource,它将优先采用HikariCP连接池,如果没有该依赖的情况则选取 Tomcat pooling DataSource ,如果前两者都不可用最后选取 Commons DBCP2
。通过spring.datasource.type属性可以指定其它种类的连接池

application.yml

spring:
  application:
    name: spring-boot-jdbc
  datasource:
    url: jdbc:mysql://localhost:3306/test_db?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
    username: root
    password: mysql123
    driver-class-name: com.mysql.jdbc.Driver

启动项目,通过日志,可以看到默认情况下注入的是HikariDataSource

2018-07-07 15:28:37.925  INFO 2316 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Bean with name 'dataSource' has been autodetected for JMX exposure
2018-07-07 15:28:37.932  INFO 2316 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Located MBean 'dataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=dataSource,type=HikariDataSource]
2018-07-07 15:28:37.978  INFO 2316 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''

具体编码

表结构

创建tb_user

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(64) NOT NULL COMMENT '用户名',
  `password` varchar(64) NOT NULL COMMENT '用户密码',
  `birthday` date DEFAULT NULL COMMENT '用户生日',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
BEGIN;
INSERT INTO `tb_user` VALUES (1, 'aaa', '123456', '1994-08-10');
INSERT INTO `tb_user` VALUES (2, 'bbb', '123456', '1996-07-25');
INSERT INTO `tb_user` VALUES (3, 'ccc', '123456', '2000-05-01');
INSERT INTO `tb_user` VALUES (4, 'ddd', '123456', '1997-05-10');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

实体类

package com.example.springbootjdbc.pojo;

import java.util.Date;

public class User {

    /** 用户id */
    private Integer id;

    /** 用户名 */
    private String username;

    /** 用户密码 */
    private String password;

    /** 用户生日 */
    private Date birthday;
    
    // setters and getters ...
}

Dao层

com.example.springbootjdbc.dao.IUserDao

package com.example.springbootjdbc.dao;

import com.example.springbootjdbc.pojo.User;

import java.util.List;

public interface IUserDao {

    int save(User user);

    int delete(Integer id);

    int update(User user);

    User findById(Integer id);

    List<User> findAll();

    List<User> findList(Integer page, Integer pageSize);

}

com.example.springbootjdbc.dao.impl.UserDaoImpl

package com.example.springbootjdbc.dao.impl;

import com.example.springbootjdbc.dao.IUserDao;
import com.example.springbootjdbc.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class UserDaoImpl implements IUserDao {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public UserDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int save(User user) {
        String sql = "INSERT INTO tb_user(username, password, birthday) VALUES(?, ?, ?)";
        return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getBirthday());
    }

    @Override
    public int delete(Integer id) {
        String sql = "DELETE FROM tb_user WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public int update(User user) {
        String sql = "UPDATE tb_user u SET u.username = ?, u.password = ?, u.birthday = ? WHERE u.id = ?";
        return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getBirthday(), user.getId());
    }

    @Override
    public User findById(Integer id) {
        String sql = "SELECT * FROM tb_user WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
    }

    @Override
    public List<User> findAll() {
        String sql = "SELECT * FROM tb_user";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
    }

    @Override
    public List<User> findList(Integer page, Integer pageSize) {
        String sql = "SELECT * FROM tb_user LIMIT ?, ?";
        Integer offset = (page - 1) * pageSize;
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), offset, pageSize);
    }

}

Service层

com.example.springbootjdbc.service.IUserService

package com.example.springbootjdbc.service;

import com.example.springbootjdbc.pojo.User;

import java.util.List;

public interface IUserService {

    int save(User user);

    int delete(Integer id);

    int update(User user);

    User findById(Integer id);

    List<User> findAll();

    List<User> findList(Integer page, Integer pageSize);

}

com.example.springbootjdbc.service.impl.UserServiceImpl

package com.example.springbootjdbc.service.impl;

import com.example.springbootjdbc.dao.IUserDao;
import com.example.springbootjdbc.pojo.User;
import com.example.springbootjdbc.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements IUserService {

    private final IUserDao userDao;

    @Autowired
    public UserServiceImpl(IUserDao userDao) {
        this.userDao = userDao;
    }

    @Override
    public int save(User user) {
        return userDao.save(user);
    }

    @Override
    public int delete(Integer id) {
        return userDao.delete(id);
    }

    @Override
    public int update(User user) {
        return userDao.update(user);
    }

    @Override
    public User findById(Integer id) {
        return userDao.findById(id);
    }

    @Override
    public List<User> findAll() {
        return userDao.findAll();
    }

    @Override
    public List<User> findList(Integer page, Integer pageSize) {
        return userDao.findList(page, pageSize);
    }
}

Rest 接口

package com.example.springbootjdbc.controller;

import com.example.springbootjdbc.pojo.User;
import com.example.springbootjdbc.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

@RestController
@RequestMapping("/users")
public class UserController {

    private final IUserService userService;

    @Autowired
    public UserController(IUserService userService) {
        this.userService = userService;
    }

    @PostMapping("")
    public String saveUser(@RequestBody User user) {
        int count = userService.save(user);
        return count > 0 ? "success" : "fail";
    }

    @DeleteMapping("/{id}")
    public String deleteUser(@PathVariable Integer id) {
        int count = userService.delete(id);
        return count > 0 ? "success" : "fail";
    }

    @PutMapping("/{id}")
    public String updateUser(@PathVariable Integer id, @RequestParam(value = "username", required = true) String username,
                          @RequestParam(value = "password", required = true) String password,
                          @RequestParam(value = "birthday", required = true) String birthday) throws ParseException {
        User updateUser = new User();
        updateUser.setId(id);
        updateUser.setUsername(username);
        updateUser.setPassword(password);
        updateUser.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(birthday));
        int count = userService.update(updateUser);
        return count > 0 ? "success" : "fail";
    }

    @GetMapping("")
    public List<User> findList(@RequestParam(value = "page", defaultValue = "1") Integer page,
                               @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize) {
        return userService.findList(page, pageSize);
    }

    @GetMapping("{id}")
    public User findById(@PathVariable Integer id) {
        return userService.findById(id);
    }

}


JdbcTemplate API文档

推荐阅读更多精彩内容

  • Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具(例如配置管理,服务发现,断路器,智...
    卡卡罗2017阅读 104,970评论 12 126
  • Spring Boot 参考指南 介绍 转载自:https://www.gitbook.com/book/qbgb...
    毛宇鹏阅读 37,537评论 5 339
  • 2018年3月28日 周三 天气晴p68 今天这天气穿衬衣,明天又得换上毛衣了。下午接...
    童心_依旧阅读 51评论 0 0
  • 一路走过 有阵回声缭绕 不知道是什么存在勾起了记忆 或许是风吧 毕竟是飘过的曲调 路过之后 总感觉有一种滋味 吹拂...
    醉酒的灵魂阅读 23评论 0 0
  • 那是1985年的春节,村里出去务工的人们都纷纷回来过节了。那时候正是邓小平在实行改革开放的第二个五年,正是从那年开...
    Kang强阅读 591评论 0 5