mybatis解决批量更新阻塞

向右看齐 2022-05-27 03:13 236阅读 0赞

这是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\] DEBUG com.meicai.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\] DEBUG com.meicai.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

\[com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@a8e6492\]  
17:56:54.548 \[main\] DEBUG com.meicai.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\] DEBUG com.meicai.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