mybatis+mysql批量插入和批量更新
一、批量插入
批量插入数据使用的sql语句是:
insert into table (字段一,字段二,字段三) values(xx,xx,xx),(oo,oo,oo)
mybatis中mapper.xml的代码如下:
<!-- 批量插入数据 --> <insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="true"> <selectKey resultType="long" keyProperty="id" order="AFTER"> SELECT LAST_INSERT_ID() </selectKey> insert into wd_solr (fayu_id, tablename, name,logo,description,section_no,look_count,favorite_count,create_uid,create_time,update_time,timestamp) values <foreach collection="list" item="wdSolr" index="index" separator=","> ( #{wdSolr.fayuId},#{wdSolr.tablename},#{wdSolr.name},#{wdSolr.logo}, #{wdSolr.description},#{wdSolr.sectionNo},#{wdSolr.lookCount},#{wdSolr.favoriteCount}, #{wdSolr.createUid},#{wdSolr.createTime},#{wdSolr.updateTime},#{wdSolr.timestamp} ) </foreach> </insert>
二、批量更新
批量更新数据使用的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)
上面这一条mysql语句可以更新多条记录,mybatis中mapper.xml的代码如下:
<!-- 批量更新数据 --> <update id="updateBatch"> update wd_solr set name = <foreach collection="list" item="wdSolr" index="index" separator=" " open="case id" close="end"> when #{wdSolr.id} then #{wdSolr.name} </foreach> ,logo = <foreach collection="list" item="wdSolr" index="index" separator=" " open="case id" close="end"> when #{wdSolr.id} then #{wdSolr.logo} </foreach> ,timestamp = <foreach collection="list" item="wdSolr" index="index" separator=" " open="case id" close="end"> when #{wdSolr.id} then #{wdSolr.timestamp} </foreach> where id in <foreach collection="list" item="wdSolr" index="index" separator="," open="(" close=")"> #{wdSolr.id} </foreach> </update>
三、SELECT LAST_INSERT_ID() 的使用和注意事项
转载自:https://blog.csdn.net/czd3355/article/details/71302441
总体解释:将插入数据的主键返回到 user 对象中。
具体解释:
SELECT LAST_INSERT_ID():得到刚 insert 进去记录的主键值,只适用与自增主键
keyProperty:将查询到主键值设置到 parameterType 指定的对象的那个属性
order:SELECT LAST_INSERT_ID() 执行顺序,相对于 insert 语句来说它的执行顺序
resultType:指定 SELECTLAST_INSERT_ID() 的结果类型