mybatis批量添加,更新,删除(mapper.xml)

1,批量添加

批量插入数据使用的sql语句是:

insert into table (字段一,字段二,字段三) values(xx,xx,xx),(oo,oo,oo)

 

mapper文件:

int insertBatch(List<TmApplyTemplateDetail> applyTemplateDetailList);

 

mapper.xml文件:

  <insert id="insertBatch" parameterType="list">
        insert into
        tm_apply_template_detail(
            template_id,model_id,total_count,remark
            )
        values
        <foreach collection="list" item="item" separator=",">
            (
            #{templateId},#{modelId},#{totalCount},#{remark}
            )
        </foreach>

    </insert>

 

注意:当然你也可以在Java中写个循环,然后一条条的去添加,不过这样频繁insert执行效率比较低,在项目规模比较小和一次性插入数据不多时可以用。还是建议批量添加。

 

2,批量更新

批量更新SQL:

UPDATE table
    SET aa = CASE id
        WHEN 1 THEN 'oo'
        WHEN 2 THEN 'pp'
        WHEN 3 THEN 'qq'
    END
  ,SET bb = CASE id
        WHEN 1 THEN 'xx'
        WHEN 2 THEN 'yy'
        WHEN 3 THEN 'zz'
    END
WHERE id IN (1,2,3)

 

mapper.xml文件:

   <update id="updateBatch">
        update wd_solr set
        name =
        <foreach collection="list" item="item" separator=" " open="case id" close="end">
            when #{item.id} then
            #{item.name}
        </foreach>
        ,logo =
        <foreach collection="list" item="item"separator=" " open="case id" close="end">
            when #{item.id} then
            #{item.logo}
        </foreach>        
        ,timestamp =
        <foreach collection="list" item="item"separator=" " open="case id" close="end">
            when #{item.id} 
        then #{item.timestamp}
</foreach> where id in <foreach collection="list" item="item" separator="," open="(" close=")"> #{item.id} </foreach> </update>

 

3,批量删除

SQL:

delete from table where XX IN(XX,XX,XX)

 

mapper.xml:

  <delete id="deleteByBatch">
        delete from t_enterprise_output_value
        where output_id IN
        <foreach collection="array" item="outputId" open="(" separator="," close=")">
            #{outputId}
        </foreach>
    </delete>

 

posted @ 2020-08-04 11:14  风子磊  阅读(5192)  评论(4编辑  收藏  举报