首先批量更新需要增加
数据库的链接必须加上但是数据库连接必须加上 allowMultiQueries=true 属性,不然会报错
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
批量插入数据使用的sql语句是:
insert into table (aa,bb,cc) 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>