目录
批量更新描述
foreach写法：
case when then写法： when后面的条件必须能确定一条数据，即id
case when then 的mapper接口方式实现动态sql批量插入
批量更新描述
tk.mybatis没有带批量更新的功能，批量更新却是经常使用
实现批量更新主要有两种方式：
case when then方式：一条sql实现多条数据多个字段数据更新。
foreach方式：生成多条数据更新数据库。
foreach写法：
这种方式效率非常低下，比较普通的写法，是通过循环，依次执行update语句。
xml配置写法如下：
update course
name=${item.name}
where id = ${item.id}
结果：一条记录update一次，性能比较差，容易造成阻塞。
case when then写法： when后面的条件必须能确定一条数据，即id
表结构：
sql写法1 ： case 后接主键
UPDATE stream_box SET
capability = CASE stream_box_id
WHEN 'sboxbatchInsertTKMybatis1' THEN 1001
WHEN 'sboxbatchInsertTKMybatis2' THEN 1001
WHEN 'sboxbatchInsertTKMybatis3' THEN 1001
WHEN 'sboxbatchInsertTKMybatis4' THEN 1001
WHEN 'sboxbatchInsertTKMybatis5' THEN 1001
END,
use_state = CASE stream_box_id
WHEN 'sboxbatchInsertTKMybatis1' THEN 0
WHEN 'sboxbatchInsertTKMybatis2' THEN 0
WHEN 'sboxbatchInsertTKMybatis3' THEN 0
WHEN 'sboxbatchInsertTKMybatis4' THEN 0
WHEN 'sboxbatchInsertTKMybatis5' THEN 0
END
WHERE op_state=1;
sql写法2 ： when后面接主键
UPDATE stream_box SET
capability = CASE
WHEN stream_box_id ='sboxbatchInsertTKMybatis1' AND stream_line_id ='slineid1' THEN 3333
WHEN stream_box_id ='sboxbatchInsertTKMybatis2' AND stream_line_id ='slineid2' THEN 3333
WHEN stream_box_id ='sboxbatchInsertTKMybatis3' AND stream_line_id ='slineid3' THEN 3333
WHEN stream_box_id ='sboxbatchInsertTKMybatis4' AND stream_line_id ='slineid4' THEN 3333
WHEN stream_box_id ='sboxbatchInsertTKMybatis5' AND stream_line_id ='slineid5' THEN 3333
END,
use_state = CASE
WHEN stream_box_id ='sboxbatchInsertTKMybatis1' AND stream_line_id ='slineid1' THEN 0
WHEN stream_box_id ='sboxbatchInsertTKMybatis2' AND stream_line_id ='slineid2' THEN 0
WHEN stream_box_id ='sboxbatchInsertTKMybatis3' AND stream_line_id ='slineid3' THEN 0
WHEN stream_box_id ='sboxbatchInsertTKMybatis4' AND stream_line_id ='slineid4' THEN 0
WHEN stream_box_id ='sboxbatchInsertTKMybatis5' AND stream_line_id ='slineid5' THEN 0
END
WHERE op_state=1;
dao层接口：
int updateSboxByPrimaryKeySelective(@Param("list") Listlist);
xml配置文件写法：
UPDATE stream_box
when stream_box_id = #{item.streamBoxId} then #{item.capability}
when stream_box_id = #{item.streamBoxId} then #{item.useState}
where
stream_box_id =#{item.streamBoxId}
测试代码
@Test
public void caseWhenThenTest(){
ListsBoxes = getSboxes("batchInsertTKMybatis");
sBoxes.forEach(sBox -> {
sBox.setUseState((byte)0);
sBox.setCapability(10000d);
});
int i = streamBoxDao.updateSboxByPrimaryKeySelective(sBoxes);
System.out.println("成功更改：\t" + i + "条数据");
}
控制台日志：
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@386d41cb] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@5da41d02] will not be managed by Spring
==> Preparing: UPDATE stream_box set capability = CASE when stream_box_id = ? then ? when stream_box_id = ? then ? when stream_box_id = ? then ? when stream_box_id = ? then ? when stream_box_id = ? then ? END, use_state = CASE when stream_box_id = ? then ? when stream_box_id = ? then ? when stream_box_id = ? then ? when stream_box_id = ? then ? when stream_box_id = ? then ? END where stream_box_id =? or stream_box_id =? or stream_box_id =? or stream_box_id =? or stream_box_id =?
==> Parameters: sboxbatchInsertTKMybatis1(String), 10000.0(Double), sboxbatchInsertTKMybatis2(String), 10000.0(Double), sboxbatchInsertTKMybatis3(String), 10000.0(Double), sboxbatchInsertTKMybatis4(String), 10000.0(Double), sboxbatchInsertTKMybatis5(String), 10000.0(Double), sboxbatchInsertTKMybatis1(String), 0(Byte), sboxbatchInsertTKMybatis2(String), 0(Byte), sboxbatchInsertTKMybatis3(String), 0(Byte), sboxbatchInsertTKMybatis4(String), 0(Byte), sboxbatchInsertTKMybatis5(String), 0(Byte), sboxbatchInsertTKMybatis1(String), sboxbatchInsertTKMybatis2(String), sboxbatchInsertTKMybatis3(String), sboxbatchInsertTKMybatis4(String), sboxbatchInsertTKMybatis5(String)
<== Updates: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@386d41cb]
成功更改：5条数据
以上参考：https://www.cnblogs.com/exmyth/p/5757137.html
case when then 的mapper接口方式实现动态sql批量插入
可参考：https://blog.csdn.net/sunct/article/details/90146681