Springboot注解形式的开发批量更新数据（mybatis批量(多条语句多字段)更新SQL配置）

 

注解配置：

@Update(" <script> " +
" update CS_********_table_name " +
" <trim prefix=\"set\" suffixOverrides=\",\"> " +
" <trim prefix=\"PROVINCE_CODE =case\" suffix=\"end,\"> " +
" <foreach collection=\"updateHYs\" item=\"i\" index=\"index\"> " +
" <if test=\"i.SHENG_CODE != null\"> " +
" when COUNTY_CODE = #{i.XIAN_CODE} " +
" and INDUSTRY_ID = #{i.HY_CODE}" +
" then #{i.SHENG_CODE} " +
" </if> " +
" </foreach> " +
" </trim> " +
" </trim> " +
" where " +
" <foreach collection=\"updateHYs\" separator=\"or\" item=\"i\" index=\"index\" > " +
" COUNTY_CODE = #{i.XIAN_CODE} " +
" and INDUSTRY_ID = #{i.HY_CODE} " +
" </foreach> " +
"</script>")
Integer update************(@Param("updateHYs")List<Map<String,Object>> updateHYs);

 

打印的sql:

update CS_********_table_name
set
PROVINCE_CODE =
case
when COUNTY_CODE = ? and INDUSTRY_ID = ?
then ?
when COUNTY_CODE = ? and INDUSTRY_ID = ?
then ?
when COUNTY_CODE = ? and INDUSTRY_ID = ?
then ?
when COUNTY_CODE = ? and INDUSTRY_ID = ?
then ?
end,
where
COUNTY_CODE = ? and INDUSTRY_ID = ?

 

打印的数据参数：

130726(String), 6(String),
130000(String),

130727(String), 6(String),
130000(String),

130728(String), 3(String),
130000(String),

 

拼接后的sql:

update CS_********_table_name
set
PROVINCE_CODE =
case
when COUNTY_CODE = '130726' and INDUSTRY_ID = '6'
then '130000'
when COUNTY_CODE = '130727' and INDUSTRY_ID = '6'
then '130000'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '3'
then '130000'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '6'
then '130000'
end,

注意：

之前想着字段写成动态的，即增加条件判断字段已有值是否和更新值相同，如果相同则不更新，代码如下：

" <trim prefix=\"PROVINCE_CODE =case\" suffix=\"end,\"> " +
" <foreach collection=\"updateHYs\" item=\"i\" index=\"index\"> " +
" <if test=\"i.SHENG_CODE != null\"> " +
" when COUNTY_CODE = #{i.XIAN_CODE} " +
" and INDUSTRY_ID = #{i.HY_CODE}" +
" and PROVINCE_CODE = #{i.SHENG_CODE}" +
" then #{i.SHENG_CODE} " +
" </if> " +
" </foreach> " +
" </trim> " +
其中 " and PROVINCE_CODE = #{i.SHENG_CODE} " 是判断值是否相同的条件，最后执行时表中字段值全部被改为NULL（如果字段约束不能为null，则执行会报错），去掉这个判断之后执行成功，去掉这个判断后的问题是不管值是否相同都会被修改，如果需要动态判断后再决定是否修改该字段，可以优化上面的配置语句，外层加循环，循环内先判断值是否一致，如果一致再写 "字段名=值，" 语句