jpa save()，saveAll()替代方案，mysql的insert并发问题（on DUPLICATE KEY UPDATE）

日期：2020-2-20 栏目： SpringBoot... 浏览：2268次评论：0条侧边栏

一、jpa save()

使用springdata jpa的save()方法在存储数据时，都会先执行select语句，再执行insert语句，相当于每存储一条数据，就会执行两次sql，效率非常慢；

saveAll()也一样，查看源码可以看出调用的还是save()，这样就在数据量上来时候出现了瓶颈；

网上搜索到的一些解决方法如下（实际并不一定生效，或者不可行）：
在properties配置文件中加入
spring.jpa.properties.hibernate.jdbc.batch_size=5000
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates =true
这个batch_size的值可以是数据库每秒最大写入数

自己在repository里面定义两个接口 batchSave，batchUpdate并实现：

@Repository
public interface Repository {
    // 批量存储的方法
    <S extends T> Iterable<S> batchSave(Iterable<S> var1);
    // 批量更新的方法
    <S extends T> Iterable<S> batchUpdate(Iterable<S> var1);
}

以上方式并不能满足需求，同样效率提升不明显，切不一定能配置成功；

参照如下配置hibernate使其打印sql执行相关日志，可查看是否批处理生效等：

Hibernate-SpringBoot

二、JdbcTemplate batchUpdate()

效率有所提高

public Integer batchInsertIk(List<IK> ikList) {
	String sql = "insert SWJ_IK1(name,count) values(?,?)";
	jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
		public void setValues(PreparedStatement ps, int i) throws SQLException {
			String name = ikList.get(i).getName();
			int count = ikList.get(i).getCount();
			ps.setString(1, name);
			ps.setInt(2, count);
		}
		public int getBatchSize() {
			return ikList.size();
		}
	});
	return 0;
}

public Integer batchUpdateIk(List<IK> ikList) {
	String sql = "update SWJ_IK1 set count=? where name=?";
	jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
		public void setValues(PreparedStatement ps, int i) throws SQLException {
			int count = ikList.get(i).getCount();
			String name = ikList.get(i).getName();
			ps.setInt(1, count);
			ps.setString(2, name);
		}
		public int getBatchSize() {
			return ikList.size();
		}
	});
	return 0;
}

// 也可以自定义字段对应，但是要注意Object[]中元素的位置
public Integer batchInsertUsers(List<IK> list) {
    String sql = "insert SWJ_IK1(name,count) values(?,?)";
    jdbcTemplate.batchUpdate(sql,setParameters(list));
    return 0;
}
private List<Object[]> setParameters(List<IK> list){
    List<Object[]> parameters = new ArrayList<Object[]>();
    for (IK ik : list) {
        parameters.add(new Object[] { ik.getName(),ik.getCount()});
    }
    return parameters;
}

三、insert …… on DUPLICATE KEY UPDATE

下边这个场景可以替代以上jpa操作，insert …… on DUPLICATE KEY UPDATE：

更适合动态拼接，且使用entityManager.createNativeQuery()执行效率更高

根据查询数据库的结果：存在，则进行更新；不存在，则进行新增；
还有一个场景需求：若已存在，则仅查询；不存在，则进行新增；
mysql已经为我们提供了相应的sql语句去实现这些场景。

一、insert，存在则更新，不存在则新增
语法：on DUPLICATE KEY UPDATE

1、表结构如下:
CREATE TABLE `testMfc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_01` (`age`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4

注意：想要使用该语法，必须利用“唯一索引”或者“主键索引”，只有产生“索引冲突”，该语法才能正常生效。

此处建立唯一索引：age列和name列

插入初始数据：

insert into testMfc values (null, 1,'1',30),(null,2,'2',40)

2、sql语句
已存在，则更新：（影响行数为：2，如果使用mybatis，返回值 int =2，同时该语句可返回当前主键）

insert into testMfc values (null, 1,'1',30) on duplicate key update num = 1;

不存在，则新增：（影响行数为：1，如果使用mybatis，返回值 int =1，同时该语句可返回当前主键）

insert into testMfc values (null, 3,'3',50) on duplicate key update num = 1;

3、批量插入，某一条记录存在，则更新；其余进行新增
insert into testMfc values
(null, 3,'3',300),
(null, 4,'4',400),
(null, 5,'5',500),
(null, 2,'2',200)
on duplicate key update num = values(num);

更新：age = 2 和 age =3 的num属性，新增age = 4 和 5 的数据

注意： num = values(num) ，此处的格式，必须是 values(列名)，才能更新到对应的行。

mybatis语法：（与该表无关，则用作语法参考）

@Insert({
"<script>",
"insert into weekly_item (weekly_id, weekly_task_name," +
"task_id, plan, project_id,duty_user_key, status, copy_status," +
"create_at, update_at, comment_id,complete)" +
"<foreach collection='insertList' item='item' open='values ' separator=','> " +
"(#{item.weeklyId,jdbcType=INTEGER}," +
"#{item.weeklyTaskName,jdbcType=VARCHAR}," +
"#{item.taskId,jdbcType=INTEGER}," +
"#{item.plan,jdbcType=VARCHAR}," +
"#{item.projectId,jdbcType=INTEGER}," +
"#{item.dutyUserKey,jdbcType=VARCHAR}," +
"#{item.status,jdbcType=TINYINT}," +
"#{item.copyStatus,jdbcType=TINYINT}," +
"now()," +
"now()," +
"#{item.commentId,jdbcType=INTEGER}," +
"#{item.complete,jdbcType=VARCHAR})" +
"</foreach>"+
"ON DUPLICATE KEY UPDATE " +
"complete = VALUES(complete)",
"</script>"
})
void insertBatchOrUpdate(@Param("insertList") List<WeeklyItem> copyItemList);

二、insert，存在则不进行任何操作；不存在则新增
语法：insert ignore into 表名

1、sql语句
注意：

已存在：（影响行数为：0，如果使用mybatis，返回值 int =0，该语句不能返回当前主键）
不存在则新增：（影响行数为：1，如果使用mybatis，返回值 int =1，该语句可以返回当前主键）
insert ignore into testMfc values (null,1,'1',123);

三、总结
mysql还提供了其他原子性操作，比如说：insert，若已存在，则先删除再新增，个人觉得可以使用存在则更新操作代替，所以此处就不做介绍，希望对你们有用。

备注：网上有人提问，on DUPLICATE KEY UPDATE 在mysql客户端可以正常使用，但是在mybatis就没有效果，既不更新也不新增，同时语句还不报错。小编刚也遇到了“类似情况”，后来发现是参数传错了，粗心了呀。还有一个是sql语句insert的时候，将id也赋值了，这个时候就是主键冲突了。可能不是我们想要的自定义“唯一索引”

第三部分原文链接：https://blog.csdn.net/qq_36095679/article/details/100775338