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 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,则执行会报错),去掉这个判断之后执行成功,去掉这个判断后的问题是不管值是否相同都会被修改,如果需要动态判断后再决定是否修改该字段,可以优化上面的配置语句,外层加循环,循环内先判断值是否一致,如果一致再写 "字段名=值," 语句

 

 

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MyBatis中,可以使用批量插入语句多条记录同时插入数据库。具体来说,可以使用foreach标签来遍历一个列表,并在循环中执行插入操作。下面是一个示例代码片段,展示了如何在MyBatis中使用foreach实现批量插入: <<引用:注解插入 @Insert({ "<script>", "insert into task_info(id,info) values ", "<foreach collection='recordList' item='item' index='index' separator=','>", "(#{item.id}, #{item.info})", "</foreach>", "</script>" }) int insertList(@Param("recordList") List<TaskInfo> recordList); Oracle 批量 。 引用:总感觉自己做过批量插入,又好像没有,刻意看一下,我一次插入了1W条数据,结合自己的MYSQL版本最大插入来做,记得做好字段阈值 MYSQL 批量 。 引用:XML插入 <insert id="insertAll" parameterType="java.util.List"> insert into big_temp_data (`time`, region, `type`, current_period_data, cumulative_data, indicator_type, time_attribute, `indicator`,period_last_year,period_last,year_on_year,code ) values <foreach collection="list" item="item" index="index" separator=","> ( #{item.time,jdbcType=VARCHAR}, #{item.region,jdbcType=VARCHAR}, #{item.type,jdbcType=VARCHAR}, #{item.currentPeriodData,jdbcType=VARCHAR}, #{item.cumulativeData,jdbcType=VARCHAR}, #{item.indicatorType,jdbcType=VARCHAR}, #{item.timeAttribute,jdbcType=VARCHAR}, #{item.indicator,jdbcType=VARCHAR}, #{item.periodLastYear,jdbcType=VARCHAR},#{item.periodLast,jdbcType=VARCHAR},#{item.yearOnYear,jdbcType=VARCHAR}, #{item.code ,jdbcType=VARCHAR} ) </foreach> </insert> >> 在注解方式中,可以使用@Insert注解,结合<foreach>标签来实现。示例代码中的insertList方法接收一个名为recordList的参数,该参数是一个List<TaskInfo>类型的对象。在SQL语句中,使用<foreach>标签将recordList中的每个元素插入到数据库中。 在XML配置文件中,可以使用<insert>标签,并在其中使用<foreach>标签来实现批量插入。示例代码中的insertAll语句接收一个名为list的参数,该参数是一个java.util.List类型的对象。在<foreach>标签中,通过#{item.xxx}来引用list中的属性,并使用separator属性指定每条记录之间的分隔符。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值