526互联

MyBatis手写SQL批量操作

发布时间 2023-12-21 08:49:30作者: 梅丹隆

一、查询

List<ArticleAccumulatedIncomeDTO> batchAccumulatedIncome(List<Long> ids);

<select id="batchAccumulatedIncome"  resultMap="ArticleSumIncome">
    select article_id,sum(income) as accumulated_income
    from wallet
    <where>
        article_id in (
        <foreach collection="list"  item="id" index="index" separator=",">
            #{id}
        </foreach>
        )
    </where>
    group by article_id
</select>

二、插入

1、基本插入

int addResource(List<Resource> ResourceList);

<insert id="addResource" parameterType="java.util.List">
  insert into resource (object_id, res_id, res_detail_value, res_detail_name)
  values
  <foreach collection="list" item=" ResourceList " index="index" separator=",">
    (  #{ResourceList.objectId,jdbcType=VARCHAR},
    #{ResourceList.resId,jdbcType=VARCHAR},
    #{ResourceList.resDetailValue,jdbcType=VARCHAR},
    #{ResourceList.resDetailName,jdbcType=VARCHAR}
    )
  </foreach>
</insert>

2、ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE

三、更新

1、方法一

int updateRoles(List<String> roleList);

<update id="updateRoles" parameterType="java.util.List">
  update role
  set enabled = '0'
  where role_id in
   <foreach collection="list" item="roleIds" index="index" open="(" separator="," close=")"> 
     #{roleIds} 
  </foreach>
</update>

2、方法二

 void articleBatchUpdate(@Param("list") List<ArticleEntity> list);

<update id="articleBatchUpdate" parameterType="list">
    update article
    set  
    base_income =
    <foreach collection="list" item="item" index="index"
             separator=" " open="case ID" close="end">
        when #{item.id} then  #{item.baseIncome}
    </foreach>,
    total_income =
    <foreach collection="list" item="item" index="index"
             separator=" " open="case ID" close="end">
        when #{item.id} then  #{item.totalIncome}
    </foreach>,
    total_views =
    <foreach collection="list" item="item" index="index"
             separator=" " open="case ID" close="end">
        when #{item.id} then  #{item.totalViews}
    </foreach>
    real_views =
    <foreach collection="list" item="item" index="index"
             separator=" " open="case ID" close="end">
        when #{item.id} then  #{item.realViews}
    </foreach>
    where id in
    <foreach collection="list" index="index" item="item"
             separator="," open="(" close=")">
        #{item.id}
    </foreach>
</update>

//批量循环操作方法： objList要操作的数据，maxValue每次批量处理的条数
public void BatchSql(List<Object> objList,int maxValue) {

    List<Object> list = new ArrayList<>();
    int size = objList.size();
    int total = size / maxValue;
    if (size % maxValue != 0) {
        total += 1;
    }

    for (int i = 0; i < total; i++) {
        if (i == total - 1) {
            maxValue = size - (i * maxValue);
        }
        for (int j = 0; j < maxValue; j++) {
            list.add(objList.get(j));
        }
        // 批量处理的方法
        xxxDao.xxxBatchxxx(list);
        log.info("日志：批量xxx。。。");
        objList.removeAll(list);
        list.clear();
    }
}

参考：
mybatis批量操作（批量查询，批量插入，批量更新）_mybatis 批量查询_Java牛马的博客-CSDN博客