注解配置:
@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 prefix=\"PROVINCE_NAME =case\" suffix=\"end,\"> " +
" <foreach collection=\"updateHYs\" item=\"i\" index=\"index\"> " +
" <if test=\"i.SHENG_NAME != null\"> " +
" when COUNTY_CODE = #{i.XIAN_CODE} " +
" and INDUSTRY_ID = #{i.HY_CODE}" +
" then #{i.SHENG_NAME} " +
" </if> " +
" </foreach> " +
" </trim> " +
" <trim prefix=\"CITY_CODE =case\" suffix=\"end,\"> " +
" <foreach collection=\"updateHYs\" item=\"i\" index=\"index\"> " +
" <if test=\"i.SHI_CODE != null\"> " +
" when COUNTY_CODE = #{i.XIAN_CODE} " +
" and INDUSTRY_ID = #{i.HY_CODE}" +
" then #{i.SHI_CODE} " +
" </if> " +
" </foreach> " +
" </trim> " +
" <trim prefix=\"CITY_NAME =case\" suffix=\"end,\"> " +
" <foreach collection=\"updateHYs\" item=\"i\" index=\"index\"> " +
" <if test=\"i.SHI_NAME != null\"> " +
" when COUNTY_CODE = #{i.XIAN_CODE} " +
" and INDUSTRY_ID = #{i.HY_CODE}" +
" then #{i.SHI_NAME} " +
" </if> " +
" </foreach> " +
" </trim> " +
" <trim prefix=\"COUNTY_NAME =case\" suffix=\"end,\"> " +
" <foreach collection=\"updateHYs\" item=\"i\" index=\"index\"> " +
" <if test=\"i.XIAN_NAME != null\"> " +
" when COUNTY_CODE = #{i.XIAN_CODE} " +
" and INDUSTRY_ID = #{i.HY_CODE}" +
" then #{i.XIAN_NAME} " +
" </if> " +
" </foreach> " +
" </trim> " +
" <trim prefix=\"INDUSTRY_NAME =case\" suffix=\"end,\"> " +
" <foreach collection=\"updateHYs\" item=\"i\" index=\"index\"> " +
" <if test=\"i.HY_NAME != null\"> " +
" when COUNTY_CODE = #{i.XIAN_CODE} " +
" and INDUSTRY_ID = #{i.HY_CODE}" +
" then #{i.HY_NAME} " +
" </if> " +
" </foreach> " +
" </trim> " +
" <trim prefix=\"RISK_INDEX =case\" suffix=\"end,\"> " +
" <foreach collection=\"updateHYs\" item=\"i\" index=\"index\"> " +
" <if test=\"i.RISK_INDEX != null\"> " +
" when COUNTY_CODE = #{i.XIAN_CODE} " +
" and INDUSTRY_ID = #{i.HY_CODE}" +
" then #{i.RISK_INDEX} " +
" </if> " +
" </foreach> " +
" </trim> " +
" <trim> " +
" AREA_LEVEL = '3', " +
" STATISTICAL_YEAR = to_char (SYSDATE, 'yyyy')," +
" STATISTICAL_QUARTER = to_char (SYSDATE, 'q'), " +
" STATISTICAL_TIME = to_date(to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss'))" +
" </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,
PROVINCE_NAME =
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,
CITY_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,
CITY_NAME =
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,
COUNTY_NAME =
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,
INDUSTRY_NAME =
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,
RISK_INDEX =
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,
AREA_LEVEL = '3',
STATISTICAL_YEAR = to_char (SYSDATE, 'yyyy'),
STATISTICAL_QUARTER = to_char (SYSDATE, 'q'),
STATISTICAL_TIME = to_date(to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss'))
where
COUNTY_CODE = ? and INDUSTRY_ID = ?
or
COUNTY_CODE = ? and INDUSTRY_ID = ?
or
COUNTY_CODE = ? and INDUSTRY_ID = ?
or
COUNTY_CODE = ? and INDUSTRY_ID = ?
打印的数据参数:
130726(String), 6(String),
130000(String),
130727(String), 6(String),
130000(String),
130728(String), 3(String),
130000(String),
130728(String), 6(String),
130000(String),
130726(String), 6(String),
S1****(String),
130727(String), 6(String),
S1****(String),
130728(String), 3(String),
S1****(String),
130728(String), 6(String),
S1****(String),
130726(String), 6(String),
130700(String),
130727(String), 6(String),
130700(String),
130728(String), 3(String),
130700(String),
130728(String), 6(String),
130700(String),
130726(String), 6(String),
SS1****(String),
130727(String), 6(String),
SS1****(String),
130728(String), 3(String),
SS1****(String),
130728(String), 6(String),
SS1****(String),
130726(String), 6(String),
X1****(String),
130727(String), 6(String),
X2****(String),
130728(String), 3(String),
X3****(String),
130728(String), 6(String),
X3****(String),
130726(String), 6(String),
hy6****(String),
130727(String), 6(String),
hy6****(String),
130728(String), 3(String),
hy3****(String),
130728(String), 6(String),
hy6****(String),
130726(String), 6(String),
100(String),
130727(String), 6(String),
100(String),
130728(String), 3(String),
0(String),
130728(String), 6(String),
100(String),
130726(String), 6(String),
130727(String), 6(String),
130728(String), 3(String),
130728(String), 6(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,
PROVINCE_NAME =
case
when COUNTY_CODE = '130726' and INDUSTRY_ID = '6'
then 'S1****'
when COUNTY_CODE = '130727' and INDUSTRY_ID = '6'
then 'S1****'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '3'
then 'S1****'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '6'
then 'S1****'
end,
CITY_CODE =
case
when COUNTY_CODE = '130726' and INDUSTRY_ID = '6'
then '130700'
when COUNTY_CODE = '130727' and INDUSTRY_ID = '6'
then '130700'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '3'
then '130700'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '6'
then '130700'
end,
CITY_NAME =
case
when COUNTY_CODE = '130726' and INDUSTRY_ID = '6'
then 'SS1****'
when COUNTY_CODE = '130727' and INDUSTRY_ID = '6'
then 'SS1****'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '3'
then 'SS1****'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '6'
then 'SS1****'
end,
COUNTY_NAME =
case
when COUNTY_CODE = '130726' and INDUSTRY_ID = '6'
then 'X1****'
when COUNTY_CODE = '130727' and INDUSTRY_ID = '6'
then 'X2****'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '3'
then 'X3****'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '6'
then 'X3****'
end,
INDUSTRY_NAME =
case
when COUNTY_CODE = '130726' and INDUSTRY_ID = '6'
then 'hy6****'
when COUNTY_CODE = '130727' and INDUSTRY_ID = '6'
then 'hy6****'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '3'
then 'hy3****'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '6'
then 'hy6****'
end,
RISK_INDEX =
case
when COUNTY_CODE = '130726' and INDUSTRY_ID = '6'
then '100'
when COUNTY_CODE = '130727' and INDUSTRY_ID = '6'
then '100'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '3'
then '1'
when COUNTY_CODE = '130728' and INDUSTRY_ID = '6'
then '100'
end,
AREA_LEVEL = '3',
STATISTICAL_YEAR = to_char (SYSDATE, 'yyyy'),
STATISTICAL_QUARTER = to_char (SYSDATE, 'q'),
STATISTICAL_TIME = to_date(to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss'))
where
COUNTY_CODE = '130726' and INDUSTRY_ID = '6'
or
COUNTY_CODE = '130727' and INDUSTRY_ID = '6'
or
COUNTY_CODE = '130728' and INDUSTRY_ID = '3'
or
COUNTY_CODE = '130728' and INDUSTRY_ID = '6'
注意:
之前想着字段写成动态的,即增加条件判断字段已有值是否和更新值相同,如果相同则不更新,代码如下:
" <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,则执行会报错),去掉这个判断之后执行成功,去掉这个判断后的问题是不管值是否相同都会被修改,如果需要动态判断后再决定是否修改该字段,可以优化上面的配置语句,外层加循环,循环内先判断值是否一致,如果一致再写 "字段名=值," 语句