SpringBoot实现批量插入当数据存在时并更新 springboot jdbctemplate 批量更新

转载

网络锐评 2024-06-05 14:26:25

文章标签 bc sql SQL 文章分类 架构后端开发

文章目录

第一部分：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:

通过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();
　　　　　　　　}
　　　　});}

批量导入注意点：

sql不要分号"；" 结尾，否则执行报错。它的原理是要字符串连接你这个sql。
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:

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

增加批量写的速度：
useServerPrepStmts=false
rewriteBatchedStatements=true
useCompression=true
增加读的速度：
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

第二部分： 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:

使用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:
SpringMVC jdbcTemplate中queryForObject以及queryForList返回映射实体使用
参考URL:

注意使用，使用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方法返回结果为空会报错及解决方法

问题描述：

问题demo1：

SpringBoot实现批量插入当数据存在时并更新 springboot jdbctemplate 批量更新_bc

SpringBoot实现批量插入当数据存在时并更新 springboot jdbctemplate 批量更新_sql_02

返回的结果是空给我抛了一个异常。

问题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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。