mybatis解决批量更新阻塞这是mybatis批量更新的两种写法，第⼆种采⽤的是mysql的语法
UPDATE test set username=#{item.username},
password=#{item.password},
u_t=#{item.u_t},
c_t=#{item.c_t}
where id=#{item.id}
update test
when id=#{i.id} then #{i.username}
</foreach>
</trim>
<trim prefix=" password =case" suffix="end,">
<foreach collection="list" item="i" index="index">
when id=#{i.id} then #{i.password}
</foreach>
</trim>
<trim prefix="c_t =case" suffix="end," >
<foreach collection="list" item="i" index="index">
when id=#{i.id} then #{i.c_t}
</foreach>
</trim>
<trim prefix="u_t =case" suffix="end," >
<foreach collection="list" item="i" index="index">
when id=#{i.id} then #{i.u_t}
</foreach>
</trim>
</trim>
where
<foreach collection="list" separator="or" item="i" index="index" >
id=#{i.id}
</foreach>
</update>
再来看看，这两种写法的执⾏sql
[com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@a8e6492]
18:01:06.213 [main] icai.dao.mapper.TestEntityMapper.update - ==> Preparing: UPDATE test set
username=?, password=?, u_t=?, c_t=? where id=? ; UPDATE test set username=?, password=?, u_t=?, c_t=? where id=? ; UPDATE test set username=?, password=?, u_t=?, c_t=? where id=? ; UPDATE test set username=?, password=?, u_t=?,
c_t=? where id=? ; UPDATE test set username=?, password=?, u_t=?, c_t=? where id=? ; UPDATE test set username=?, password=?, u_t=?, c_t=? where id=? ; UPDATE test set username=?, password=?, u_t=?, c_t=? where id=? ; UPDATE test set username=?, password=?, u_t=?, c_t=? where id=? ; UPDATE test set username=?, password=?, u_t=?, c_t=? where id=? 18:01:06.261 [main] icai.dao.mapper.TestEntityMapper.update - ==> Parameters: 张三(String),
1234561(String), 12:17(String), 12:17(String), 1(Integer), 张三(String), 1234562(String), 12:17(String), 12:17(String),
2(Integer), 张三(String), 1234563(String), 12:17(String), 12:17(String), 3(Integer), 张三(String), 1234564(String),
12:17(String), 12:17(String), 4(Integer), 张三(String), 1234565(String), 12:17(String), 12:17(String), 5(Integer), 张三(String), 1234566(String), 12:17(String), 12:17(String), 6(Integer), 张三(String), 1234567(String), 12:17(String),
12:17(String), 7(Integer), 张三(String), 1234568(String), 12:17(String), 12:17(String), 8(Integer), 张三(String),
1234569(String), 12:17(String), 12:17(String), 9(Integer)
18:01:06.281 [main] DEBUG com.alibaba.druid.pool.PreparedStatementPool - {conn-10005, pstmt-20000} enter cache批量更新sql语句
[com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@a8e6492]
17:56:54.548 [main] icai.dao.mapper.TestEntityMapper.batchUpdate1 - ==> Preparing: update test set username =case when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? end, password =case when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=?
then ? when id=? then ? end, c_t =case when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? end, u_t =case when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? when id=? then ? end where id=? or id=? or id=? or id=? or id=? or id=? or id=? or id=? or id=? or id=?
17:56:54.621 [main] icai.dao.mapper.TestEntityMapper.batchUpdate1 - ==> Parameters: 0(Integer), 张三(String), 1(Integer), 张三(String), 2(Integer), 张三(String), 3(Integer), 张三(String), 4(Integer), 张三(String), 5(Integer), 张三(String), 6(Integer), 张三(String), 7(Integer), 张三(String), 8(Integer), 张三(String), 9(Integer), 张三(String), 0(Integer),
1234560(String), 1(Integer), 1234561(String), 2(Integer), 1234562(String), 3(Integer), 1234563(String), 4(Integer), 1234564(String), 5(Integer), 1234565(String), 6(Integer), 1234566(String), 7(Integer), 1234567(String), 8(Integer), 1234568(String), 9(Integer), 1234569(String), 0(Integer), 12:16(String), 1(Integer), 12:16(String), 2(Integer),
12:16(String), 3(Integer), 12:16(String), 4(Integer), 12:16(String), 5(Integer), 12:16(String), 6(Integer), 12:16(String),
7(Integer), 12:16(String), 8(Integer), 12:16(String), 9(Integer), 12:16(String), 0(Integer), 12:16(String), 1(Integer),
12:16(String), 2(Integer), 12:16(String), 3(Integer), 12:16(String), 4(Integer), 12:16(String), 5(Integer), 12:16(String),
6(Integer), 12:16(String), 7(Integer), 12:16(String), 8(Integer), 12:16(String), 9(Integer), 12:16(String), 0(Integer),
1(Integer), 2(Integer), 3(Integer), 4(Integer), 5(Integer), 6(Integer), 7(Integer), 8(Integer), 9(Integer)
17:56:54.636 [main] DEBUG com.alibaba.druid.pool.PreparedStatementPool - {conn-10005, pstmt-20000} enter cache

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。