20.Spring Boot JdbcTemplate 入门

1. 概述

虽然说，我们现在项目的 DAL 数据访问层，大多使用 MyBatis 或者 JPA ，但是可能极少部分情况下也会使用 JDBC 。而使用的 JDBC 一般来说，一共有 3 种方式：

原生 JDBC ，或者自己项目封装的 JDBC 工具类。
Apache Common ，提供了 DbUtils 工具类。
Spring JDBC ，提供了 JdbcTemplate 工具类。

因为项目中大多采用了 Spring ，此时艿艿比较推荐使用 Spring JDBC 提供的 JdbcTemplate 。如果没有，推荐使用 Apache Common 提供的 DbUtils 。

2. 快速入门

示例代码对应仓库：lab-14-jdbctemplate 。

本小节，我们会使用 spring-boot-starter-jdbc 自动化配置 JDBC 主要配置。同时，编写相应的 SQL 操作。

2.1 引入依赖

在 pom.xml 文件中，引入相关依赖。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>lab-14-jdbctemplate</artifactId>

    <dependencies>
        <!-- 实现对数据库连接池的自动化配置 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency> <!-- 本示例，我们使用 MySQL -->
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>

        <!-- 方便等会写单元测试 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

2.2 Application

创建 Application.java 类，配置 @SpringBootApplication 注解即可。代码如下：

// Application.java

@SpringBootApplication
public class Application {
}

2.3 配置文件

在 application.yml 中，添加 DataSource 配置，如下：

spring:
  # datasource 数据源配置内容
  datasource:
    url: jdbc:mysql://47.112.193.81:3306/testb5f4?useSSL=false&useUnicode=true&characterEncoding=UTF-8
    driver-class-name: com.mysql.jdbc.Driver
    username: testb5f4
    password: F4df4db0ed86@11

2.4 UserDO

在 cn.iocoder.springboot.lab14.jdbctemplate.dataobject 包路径下，创建 UserDO.java 类，用户 DO 。代码如下：

// UserDO.java

public class UserDO {

    /**
     * 用户编号
     */
    private Integer id;
    /**
     * 账号
     */
    private String username;
    /**
     * 密码（明文）
     *
     * ps：生产环境下，千万不要明文噢
     */
    private String password;
    /**
     * 创建时间
     */
    private Date createTime;

    // ... 省略 setting/getting 方法

}

对应的创建表的 SQL 如下：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `username` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
  `password` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '密码',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2.5 UserDao

在 cn.iocoder.springboot.lab14.mybatis.dao 包路径下，创建 UserDao 接口。代码如下：

// UserDao.java

@Repository
public class UserDao {

    /**
     * 声明 INSERT 操作的 PreparedStatementCreatorFactory 对象
     */
    private static final PreparedStatementCreatorFactory INSERT_PREPARED_STATEMENT_CREATOR_FACTORY
            = new PreparedStatementCreatorFactory("INSERT INTO users(username, password, create_time) VALUES(?, ?, ?)");

    static {
        // 设置返回主键
        INSERT_PREPARED_STATEMENT_CREATOR_FACTORY.setReturnGeneratedKeys(true);
        INSERT_PREPARED_STATEMENT_CREATOR_FACTORY.setGeneratedKeysColumnNames("id");
        // 设置每个占位符的类型
        INSERT_PREPARED_STATEMENT_CREATOR_FACTORY.addParameter(new SqlParameter(Types.VARCHAR));
        INSERT_PREPARED_STATEMENT_CREATOR_FACTORY.addParameter(new SqlParameter(Types.VARCHAR));
        INSERT_PREPARED_STATEMENT_CREATOR_FACTORY.addParameter(new SqlParameter(Types.TIMESTAMP));
    }

    @Autowired
    private JdbcTemplate template;

    /**
     * 使用 PreparedStatementCreator 实现插入数据
     *
     * @param entity 实体
     * @return 影响行数
     */
    public int insert(UserDO entity) {
        // 创建 KeyHolder 对象，设置返回的主键 ID
        KeyHolder keyHolder = new GeneratedKeyHolder();
        // 执行插入操作
        int updateCounts = template.update(INSERT_PREPARED_STATEMENT_CREATOR_FACTORY.newPreparedStatementCreator(
                Arrays.asList(entity.getUsername(), entity.getPassword(), entity.getCreateTime())
        ), keyHolder);
        // 设置 ID 主键到 entity 实体中
        if (keyHolder.getKey() != null) {
            entity.setId(keyHolder.getKey().intValue());
        }
        // 返回影响行数
        return updateCounts;
    }

    /**
     * 使用 SimpleJdbcInsert 实现插入数据
     *
     * @param entity 实体
     * @return 影响行数
     */
    public int insert0(UserDO entity) {
        // 创建 SimpleJdbcInsert 对象
        SimpleJdbcInsert insertOp = new SimpleJdbcInsert(template);
        insertOp.setTableName("users");
        insertOp.setColumnNames(Arrays.asList("username", "password", "create_time"));
        insertOp.setGeneratedKeyName("id");
        // 拼接参数
        Map<String, Object> params = new HashMap<>();
        params.put("username", entity.getUsername());
        params.put("password", entity.getPassword());
        params.put("create_time", entity.getCreateTime());
        // 执行插入操作
        Number id = insertOp.executeAndReturnKey(params);
        // 设置 ID 主键到 entity 实体中
        entity.setId(id.intValue());
        // 返回影响行数
        return 1;
    }

    public int updateById(UserDO entity) {
        // JdbcTemplate 生成更新的动态 SQL 不是很方便，需要自己二次封装。类似 SimpleJdbcInsert 对象
        return template.update("UPDATE users SET password = ? WHERE id = ?", entity.getPassword(),
                entity.getId());
    }

    public int deleteById(Integer id) {
        return template.update("DELETE FROM users WHERE id = ?", id);
    }

    public UserDO selectById(Integer id) {
        return template.queryForObject("SELECT id, username, password, create_time FROM users WHERE id = ?",
                new BeanPropertyRowMapper<>(UserDO.class), // 结果转换成对应的对象
                id);
    }

    public UserDO selectByUsername(String username) {
        return template.queryForObject("SELECT id, username, password, create_time FROM users WHERE username = ? LIMIT 1",
                new BeanPropertyRowMapper<>(UserDO.class), // 结果转换成对应的对象
                username);
    }

    public List<UserDO> selectByIds(List<Integer> ids) {
        // 创建 NamedParameterJdbcTemplate 对象
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(template);
        // 拼接参数
        Map<String, Object> params = new HashMap<>();
        params.put("ids", ids);
        // 执行查询
        return namedParameterJdbcTemplate.query(
                "SELECT id, username, password, create_time FROM users WHERE id IN (:ids)", // 使用 :ids 作为占位服务
                params,
                new BeanPropertyRowMapper<>(UserDO.class) // 结果转换成对应的对象
        );
    }

}

虽然说，我们可以在 Service 中使用 JdbcTemplate 进行数据库的操作，但是艿艿还是建议将每个表的操作，分装到对应的 Dao 中。一方面，代码可以更加简洁，另一方面，未来如果替换为 MyBatis 或 JPA 等 ORM 框架时，业务层无需做调整。

2.6 简单测试

创建 UserDaoTest 测试类，我们来测试一下简单的 UserDaoTest 的每个操作。代码如下：

// UserDaoTest.java

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class UserDaoTest {

    @Autowired
    private UserDao userDao;

    @Test
    public void testInsert() {
        UserDO user = new UserDO().setUsername(UUID.randomUUID().toString())
                .setPassword("nicai").setCreateTime(new Date());
        userDao.insert(user);
        System.out.println(user);
    }

    @Test
    public void testInsert0() {
        UserDO user = new UserDO().setUsername(UUID.randomUUID().toString())
                .setPassword("nicai").setCreateTime(new Date());
        userDao.insert0(user);
        System.out.println(user);
    }

    @Test
    public void testUpdateById() {
        UserDO updateUser = new UserDO().setId(1)
                .setPassword("wobucai");
        userDao.updateById(updateUser);
    }

    @Test
    public void testDeleteById() {
        userDao.deleteById(2);
    }

    @Test
    public void testSelectById() {
        UserDO user = userDao.selectById(1);
        System.out.println(user);
    }

    @Test
    public void testSelectByUsername() {
        UserDO user = userDao.selectByUsername("yunai");
        System.out.println(user);
    }

    @Test
    public void testSelectByIds() {
        List<UserDO> users = userDao.selectByIds(Arrays.asList(1, 5));
        System.out.println("users：" + users.size());
    }

}

虽然说，我们在日常开发中，基本很少在直接接触到 JDBC ，但是 JDBC 在问们的开发中，无处不在：

HikariCP、Druid 在其上，提供了数据库连接池的功能。
Sharding Sphere 在其上，提供了分库分表、读写分离等功能。
Seata 在其上，提供了分布式事务的功能。
Hibernate、MyBatis 在其上，提供了 ORM 的功能。
Oracle、MySQL 在其上，提供了不同的 Driver 实现。
Elasticsearch SQL JDBC 在其上，提供了访问 ES 的 Driver 实现。