Spring JdbcTemplate

什么是 JdbcTemplate

大家来回顾一下，java中操作db最原始的方式就是纯jdbc了，是不是每次操作db都需要加载数据库驱动、获取连接、获取PreparedStatement、执行sql、关闭PreparedStatement、关闭连接等等，操作还是比较繁琐的。

spring中提供了一个模块，对jdbc操作进行了封装，使其更简单，就是本文要讲的JdbcTemplate，JdbcTemplate是Spring对JDBC的封装，目的是使JDBC更加易于使用。

JdbcTemplate 使用步骤

1. 创建数据源 DataSource
2. 创建JdbcTemplate，new JdbcTemplate(dataSource)
3. 调用JdbcTemplate的方法操作db，如增删改查

添加 Maven 依赖项

<dependency>
  <groupId>org.apache.tomcat</groupId>
  <artifactId>tomcat-jdbc</artifactId>
  <version>7.0.35</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.18</version>
</dependency>

创建数据库表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `pwd` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tel` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `grade` int(0) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

案例

public class JdbcTest {
    private DataSource dataSource;

    @Before
    public void before() {
        //创建数据源DataSource
        dataSource = new org.apache.tomcat.jdbc.pool.DataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/binge?characterEncoding=UTF-8&serverTimezone=GMT");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        dataSource.setInitialSize(5);
        System.out.println("初始化测试");
    }

    @After
    public void after() {
        System.out.println("结束测试");
    }

    //查询
    @Test
    public void test1() {
        //1.创建JdbcTemplate
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        //2.调用JdbcTemplate的方法操作db，返回值是一个Map集合，Map表示一行记录，key为列名，value为列对应的值
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from t_user");
        System.out.println(maps);
    }

    //查询（条件）
    @Test
    public void test2() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "select id,name from t_user where id>?";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, 1);
        System.out.println(maps);
    }

    //查询(单行记录结果转换为对象)
    @Test
    public void test3() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "select id,name from t_user where id = ?";
        //查询id为34的用户信息
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
        User user = jdbcTemplate.queryForObject(sql, rowMapper, 1);
        System.out.println(user);
    }

    //查询（多行记录转换为对象集合）
    @Test
    public void test4() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "select id,name from t_user where id>?";
        List<User> maps = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class), 1);
        System.out.println(maps);
    }

    //插入（无参）
    @Test
    public void test5() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        int updateRows = jdbcTemplate.update("INSERT INTO t_user (name,pwd,email,tel) VALUE ('spring系列','111111','[email protected]','1354922010')");
        System.out.println("影响行数：" + updateRows);
    }

    //插入（有参）
    @Test
    public void test6() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        int updateRows = jdbcTemplate.update("INSERT INTO t_user (name,pwd,email,tel) VALUE (?,?,?,?)", "spring系列","111111","[email protected]","1354922010");
        System.out.println("影响行数：" + updateRows);
    }

    //批量插入
    @Test
    public void test7() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<Object[]> list = Arrays.asList(
            new Object[]{"刘德华","11111","[email protected]","13452211233"},
            new Object[]{"郭富城","22222","[email protected]","13752244533"},
            new Object[]{"张学友","33333","[email protected]","13042811233"},
            new Object[]{"黎明","44444","[email protected]","13918211233"});
        int[] updateRows = jdbcTemplate.batchUpdate("INSERT INTO t_user (name,pwd,email,tel) VALUE (?,?,?,?)", list);
        for (int updateRow : updateRows) {
            System.out.println(updateRow);
        }
    }

    //删除
    @Test
    public void test8() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        int updateRows = jdbcTemplate.update("DELETE FROM t_user WHERE id = ?",1);
        System.out.println("影响行数：" + updateRows);
    }

    //修改
    @Test
    public void test9() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        int updateRows = jdbcTemplate.update("UPDATE t_user SET pwd = ? WHERE id = ?",123,2);
        System.out.println("影响行数：" + updateRows);
    }
}

@Data
public class User {
    private Integer id;
    private String name;
    private String pwd;
    private String tel;
    private int grade;
    private String email;
}

总结

1. BeanPropertyRowMapper 可以将行记录映射为 Java 对象
2. JdbcTemplate 采用模板的方式操作 jdbc 变的特别的容易，代码特别的简洁，不过其内部没有动态 sql 的功能，即通过参数，动态生成指定的 sql，mybatis 在动态 sql 方面做的比较好，大家用的时候可以根据需求进行选择