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

弱龄寄事外，委怀在琴书。这篇文章主要讲述mybatis批量添加，更新，删除(mapper.xml)相关的知识，希望能为你提供帮助。
1，批量添加批量插入数据使用的sql语句是：

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

【mybatis批量添加，更新，删除(mapper.xml)】
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>