批量插入数据使用的sql语句是:
insert into table (aa,bb,cc) values(xx,xx,xx),(oo,oo,oo)
<!-- 批量插入数据 -->
<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>
trim代替where/set标签
trim 是更灵活用来去处多余关键字的标签,它可以用来实现 where 和 set 的效果。原文出自【易百教程】,商业转载请联系作者获得授权,非商业转载请保留原文链接:https://www.yiibai.com/mybatis/mybaits_trim.html
<!-- 使用 if/trim 代替 where(判断参数) - 将 User 类不为空的属性作为 where 条件 -->
<select id="getUsertList_if_trim" resultMap="resultMap_User">
SELECT *
FROM user u
<trim prefix="WHERE" prefixOverrides="AND|OR">
<if test="username !=null ">
u.username LIKE CONCAT(CONCAT('%', #{username, jdbcType=VARCHAR}),'%')
</if>
<if test="sex != null and sex != '' ">
AND u.sex = #{sex, jdbcType=INTEGER}
</if>
<if test="birthday != null ">
AND u.birthday = #{birthday, jdbcType=DATE}
</if>
</trim>
</select>
trim 代替 set
<!-- if/trim代替set(判断参数) - 将 User 类不为空的属性更新 -->
<update id="updateUser_if_trim" parameterType="com.yiibai.pojo.User">
UPDATE user
<trim prefix="SET" suffixOverrides=",">
<if test="username != null and username != '' ">
username = #{username},
</if>
<if test="sex != null and sex != '' ">
sex = #{sex},
</if>
<if test="birthday != null ">
birthday = #{birthday},
</if>
</trim>
WHERE user_id = #{user_id}
</update>
trim (对包含的内容加上 prefix,或者 suffix 等,前缀,后缀)
<select id="dynamicTrimTest" parameterType="Blog" resultType="Blog">
select * from t_blog
<trim prefix="where" prefixOverrides="and |or">
<if test="title != null">
title = #{title}
</if>
<if test="content != null">
and content = #{content}
</if>
<if test="owner != null">
or owner = #{owner}
</if>
</trim>
</select>
trim 元素的主要功能是可以在自己包含的内容前加上某些前缀,也可以在其后加上某些后缀,与之对应的属性是 prefix 和 suffix;可以把包含内容的首部某些内容覆盖,即忽略,也可以把尾部的某些内容覆盖,对应的属性是 prefixOverrides 和 suffixOverrides;正因为 trim 有这样的功能,所以我们也可以非常简单的利用 trim 来代替 where 元素的功能。