spring boot- JdbcTemplate、NamedParameterJdbcTemplate基本使用（批量更新插入）

冷不防 2022-12-20 08:35 1623阅读 0赞

### 文章目录 ###

*  第一部分：JdbcTemplate
 *   *  一、了解JdbcTemplate
     *  二、JdbcTemplate使用步骤
     *   *  1. sql直接插入一条数据
         *  2. 通过Statement方式批量插入【推荐】
         *   *  批量导入 batchUpdate(String sql, BatchPreparedStatementSetter pss)
         *  3. 通过Statement方式批量更新
     *  三、JDBC连接URL中的配置参数（注意配置）
     *  四、jdbctemplate 打印sql配置
     *  五、参考
 *  第二部分： NamedParameterJdbcTemplate
 *   *  一、查询返回结果映射到自定义类型
     *   *  使用demo举例
     *  二、queryForList使用时注意事项
 *  第三部分：工作中常见问题整理
 *   *  queryForObject方法返回结果为空会报错
 *  相关参考

# 第一部分：JdbcTemplate #

我们开发DAO层时用的最多的就是ORM框架(Mybatis,hibernate)了。在有些特殊的情况下,ORM框架的搭建略显笨重,这时最好的选择就是Spring中的jdbcTemplate了。

## 一、了解JdbcTemplate ##

JDBC已经能够满足大部分用户最基本的需求，但是在使用JDBC时，**必须自己来管理数据库资源如：获取PreparedStatement，设置SQL语句参数，关闭连接**等步骤。

JdbcTemplate是Spring对JDBC的封装，目的是使JDBC更加易于使用。JdbcTemplate是Spring的一部分。JdbcTemplate处理了资源的建立和释放。**他帮助我们避免一些常见的错误，比如忘了总要关闭连接。他运行核心的JDBC工作流，如Statement的建立和执行，而我们只需要提供SQL语句和提取结果。**

JdbcTemplate是原生JDBC的更高一级别的工具性质的操作类，对原生JDBC进行了很多细节上的封装，只提供了入口API，使用者只需要选对API然后传对应的参数即可；将原有的比较繁琐的细节实现屏蔽了，对于使用的角度来说，表现得更加的友好；

jdbcTemplate提供的主要方法:

*  execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
 *  update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；
 *  query方法及queryForXXX方法：用于执行查询相关语句；
 *  call方法：用于执行存储过程、函数相关语句。

## 二、JdbcTemplate使用步骤 ##

*  项目工程是Maven整合SpringBoot2.x进行开发的，所以默认整合了JDBCTemplate, 直接注入导包即可：

### 1. sql直接插入一条数据 ###

组装sql，直接调用jdbcTemPlate的API即可

private boolean insertBySql() { 
        // 简单的sql执行
        String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES ('一灰灰blog', 100, 0);";
        return jdbcTemplate.update(sql) > 0;
    }

> 不推荐这种使用方式，不安全！

### 2. 通过Statement方式批量插入【推荐】 ###

jdbctemplate的batchUpdate使用方法  
参考URL: https://www.cnblogs.com/rookie-ray/p/11790612.html

通过Statement可以指定参数类型，这种插入方式更加安全，有两种常见的方式，注意设置参数时，起始值为1，而不是通常说的0

#### 批量导入 batchUpdate(String sql, BatchPreparedStatementSetter pss) ####

public int[] batchInsert(List<Book> books) { 
    
        return this.jdbcTemplate.batchUpdate("insert into books (name, price) values(?,?)",
    　　　　　new BatchPreparedStatementSetter() { 
    　　　　　	 @Override
    　　　　　　　　public void setValues(PreparedStatement ps, int i) throws SQLException { 　　　　　　　　　　ps.setString(1, books.get(i).getName());
    　　　　　　　　　　ps.setBigDecimal(2, books.get(i).getPrice());
    　　　　　　　　}
                 @Override
    　　　　　　　　public int getBatchSize() { 
    　　　　　　　　　　return books.size();
    　　　　　　　　}
    　　　　});}

批量导入注意点：

1.  sql不要 分号"；" 结尾，否则执行报错。它的原理是要字符串连接你这个sql。
2.  getBatchSize 传进来list大小，setValues里面，根据列表.get(i)获取列表当前遍历的实体类。

返回的int\[\] it .但是里面的值都是-2 ，-2表示成功。没有具体的行数。

但是从目前测试来看，一条成功，就返回-2， 那么可以用it.length() 记录插入成功多少条记录。  
例如，返回结果如： \[-2,-2,-2\] 代码3条成功！

### 3. 通过Statement方式批量更新 ###

使用 JdbcTemplate BATCHUPDATE()方法来执行批量插入操作。用这种方法，该语句只被编译一次，执行多次。

public void batchUpdateByStatement(List<UserPO> updateList) { 
            logger.info("batchUpdateByStatement start! ");
    
    
            String sql = "UPDATE user_check set name=?, code=? where mobile=?";
            int[] ans = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { 
                @Override
                public void setValues(PreparedStatement preparedStatement, int i) throws SQLException { 
                    //注意设置参数时，起始值为1，而不是通常说的0
                    preparedStatement.setString(1, updateList.get(i).getName());
                    preparedStatement.setString(2, updateList.get(i).getCode());
                    preparedStatement.setString(3, updateList.get(i).getMobile());
                }
    
                @Override
                public int getBatchSize() { 
                    return updateList.size();
                }
            });
    
            logger.info("batchUpdateByStatement success update db size: {}", ans.length);
        }

如果需要大量更新数据，并且使用的jdbc driver支持batchUpdate的情况下，使用batchUpdate会比直接执行逐行执行update效率快一个数量级。但如果jdbc不支持batchUpdate，那么jdbcTemplate的batchUpdate在其内部仍然是逐行执行。

我使用postgreSQL进行试验：

当需要插入1000个People时，逐行插入需要4秒，使用batchUpdate需要时间不到1秒

当需要插入10000个People时，逐行插入需要17秒，使用batchUpdate仅仅需要1秒

## 三、JDBC连接URL中的配置参数（注意配置） ##

db mysql / mysql cluster 5.7.19 / useServerPrepStmts / cachePrepStmts  
参考URL: https://www.cnblogs.com/lindows/p/13373347.html

JDBC连接URL中的这些参数可以对批处理语句的速度产生很大影响 – 根据我的经验,它们可以加快速度：

1.  增加批量写的速度：  
    useServerPrepStmts=false  
    rewriteBatchedStatements=true  
    useCompression=true
2.  增加读的速度：  
    useServerPrepStmts=true  
    cachePrepStmts=true

1）useCompression=true，压缩数据传输，优化客户端和MySQL服务器之间的通信性能。  
2）rewriteBatchedStatements=true ，开启批量写功能

将会使大批量单条插入语句：

INSERT INTO t (c1,c2) VALUES (‘One’,1);  
INSERT INTO t (c1,c2) VALUES (‘Two’,2);  
INSERT INTO t (c1,c2) VALUES (‘Three’,3);

改写成真正的批量插入语句：

INSERT INTO t (c1,c2) VALUES (‘One’,1),(‘Two’,2),(‘Three’,3);

3）useServerPrepStmts=false 关闭服务器端编译，sql语句在客户端编译好再发送给服务器端，发送语句如上。

如果为true,sql会采用占位符方式发送到服务器端，在服务器端再组装sql语句。  
占位符方式：INSERT INTO t (c1,c2) VALUES (？,？),(？,？),(？,？);

当然我们的目的是为了提高数据库写速度，所以当rewriteBatchedStatements =true时useServerPrepStmts=false必须配合使用。

## 四、jdbctemplate 打印sql配置 ##

之前用mybaties打印语句很好配置，后来用了JdbcTemplate就不知道怎么打印了，其实JdbcTemplate执行sql语句的过程会做打印sql语句的操作，只不过我们的日志级别没有设置为debug，将自己的日志级别设置为debug就可以了

logging:
      level:
        org.springframework.jdbc.core.JdbcTemplate: DEBUG

或

logging.level.org.springframework.jdbc.core.JdbcTemplate=DEBUG

## 五、参考 ##

JdbcTemplate基本使用  
参考URL: https://blog.csdn.net/weixin\_40001125/article/details/88538576  
SpringBoot高级篇JdbcTemplate之数据插入使用姿势详解  
参考URL: https://blog.csdn.net/liuyueyi25/article/details/89470146  
使用Spring jdbcTemplate的insertOrUpdate操作的最佳实践是什么？  
参考URL: https://www.thinbug.com/q/22941655

# 第二部分： NamedParameterJdbcTemplate #

在经典的 JDBC 用法中, SQL 参数是用占位符 ? 表示,并且受到位置的限制. 定位参数的问题在于, 一旦参数的顺序发生变化, 就必须改变参数绑定.

在 Spring JDBC 框架中, 绑定 SQL 参数的另一种选择是使用具名参数(named parameter).

那么什么是具名参数？  
**具名参数: SQL 按名称(以冒号开头)而不是按位置进行指定. 具名参数更易于维护, 也提升了可读性**. 具名参数由框架类在运行时用占位符取代  
具名参数只在 NamedParameterJdbcTemplate 中得到支持。NamedParameterJdbcTemplate可以使用全部jdbcTemplate方法

> NamedParameterJdbcTemplate类拓展了JdbcTemplate类，对JdbcTemplate类进行了封装从而支持命名参数特性。

NamedParameterJdbcTemplate主要提供以下三类方法：execute方法、query及queryForXXX方法、update及batchUpdate方法。

总结：  
开发中尽量使用NamedParameterJdbcTemplate代替JdbcTemplate，如果想使用JdbcTemplate，也可以通过NamedParameterJdbcTemplate\#getJdbcOperations()获取。  
不建议使用查询结构为Map的API。

## 一、查询返回结果映射到自定义类型 ##

SpringMVC jdbcTemplate中queryForObject以及queryForList返回映射实体使用  
参考URL: https://blog.csdn.net/fengshizty/article/details/43309055

使用BeanPropertyRowMapper + query方法返回自定义对象集合：

List<User> list = new ArrayList();
    // 条件查询
    String sql = "SELECT * FROM USER WHERE AGE = ?";
    // 获取结果
    list = jdbcTemplate.query(sql, new Object[]{ age}, new BeanPropertyRowMapper<User>(User.class));

其中new Object\[\]\{age\}为条件查询，如果查询全量，该字段可以去掉。

### 使用demo举例 ###

StringBuffer sqlBuffer = new StringBuffer();
            sqlBuffer.append("select * from user_info where id = :accountId ");
            MapSqlParameterSource sqlParam = new MapSqlParameterSource();
            sqlParam.addValue("accountId", accountId);
            
    
            BeanPropertyRowMapper<UserVO> rowMapper = new BeanPropertyRowMapper<>(UserVO.class);
    
            return jdbcTemplate.query(sqlBuffer.toString(), sqlParam,  rowMapper);

## 二、queryForList使用时注意事项 ##

SpringBoot中使用JdbcTemplate中queryForList遇到的坑  
参考URL: https://blog.csdn.net/HD243608836/article/details/99841903  
SpringMVC jdbcTemplate中queryForObject以及queryForList返回映射实体使用  
参考URL: https://blog.csdn.net/fengshizty/article/details/43309055

> 注意使用，使用queryForList只能返回基本类型的集合。

不仅是jdbcTemplate.queryForList不能这么使用，queryForObject同样也不能这么使用，而是应该添加new RowMapper接口才能返回自定义的实体类对象。

使用queryForList返回基本类型的集合<以String为例>：

List<String> names = new ArrayList();
    // sql语句,SUBSTR是SQL截取字段的方法，
    String sql = "SELECT DISTINCT SUBSTR(NAME, 2) FROM USER ";
    // 查询,使用queryForList方法,返回基本类型
    names = jdbcTemplate.queryForList(sql, String.class);

注意：映射到自定义类型,我们可以用这个query，不是queryForList

//可以用这个（注意是query，不是queryForList）
    List<T> result = jdbcTemplate.query(sql.toString(), new Object[] { queryPara}, 
        new BeanPropertyRowMapper<T>(T.class))

# 第三部分：工作中常见问题整理 #

## queryForObject方法返回结果为空会报错 ##

关于queryqueryForObject方法返回结果为空会报错及解决方法  
参考URL: https://www.pianshen.com/article/65121091946/

问题描述：  
问题demo1：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ludGhhdA_size_16_color_FFFFFF_t_70]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ludGhhdA_size_16_color_FFFFFF_t_70 1]返回的结果是空给我抛了一个异常。

问题demo2：

select  count(b.product_name)  from t_product_code_mapping a, t_asset_info b
                    where a.account_id=6
                    and a.service_code = b.service_code
                    group by b.product_name

namedJdbcTemplate.queryForObject(sql, sqlParam, Long.class);

当count 搭配group by时，group by 没有查到一个分组时，count的结果就是null。 此时我们queryForObject(sql, sqlParam, Long.class)方法时就会报错。

问题原因：  
返回的结果是空给我抛了一个异常。

解决方法：  
针对问题demo2，修改sql。 理解其sql，其实它想统计两表联合查询后不同 product\_name 的个数。因此 我们 distinct b.product\_name 之后，对其进行count 就行。

select  count(distinct b.product_name) as total from t_product_code_mapping a, t_asset_info b
                    where a.account_id=8
                    and a.service_code = b.service_code

# 相关参考 #

详解jdbcTemplate和namedParameterJdbcTemplate  
参考URL: https://www.jianshu.com/p/1bdc0e26a7e4  
NamedParameterJdbcTemplate常用方法总结  
参考URL: https://blog.csdn.net/u011179993/article/details/74791304  
Spring的NamedParameterJdbcTemplate使用方法小结以及项目实战实例  
参考URL: https://blog.csdn.net/Jaiaxn/article/details/87889550

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ludGhhdA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2021032410040883.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ludGhhdA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ludGhhdA_size_16_color_FFFFFF_t_70 1]: /images/20221120/ebe4d51de38a4157af2ed7bb5324621b.png