mybatis批量操作之oracle
1、批量insert2、批量update
1、批量insert
Mapper.java
int insertBatch(@Param("list")List
<?> list
);
Mapper.xml
<insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="false">
insert all
<foreach collection="list" separator="" item="item" index="index">
into table
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="item.id != null">
id,
</if>
<if test="item.name != null">
name,
</if>
<if test="item.createTime != null">
create_time,
</if>
<if test="item.updateTime != null">
update_time,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="item.id != null">
#{item.id,jdbcType=VARCHAR},
</if>
<if test="item.name != null">
#{item.name,jdbcType=VARCHAR},
</if>
<if test="item.createTime != null">
#{item.createTime,jdbcType=TIMESTAMP},
</if>
<if test="item.updateTime != null">
#{item.updateTime,jdbcType=TIMESTAMP},
</if>
</trim>
</foreach>
select 1 from dual
</insert>
注：此写法useGeneratedKeys=“false” 必须要有，select 1 from dual 语法需要
2、批量update
Mapper.java
int updateBatch(@Param("list")List
<?> list
);
Mapper.xml
<update id="updateBatch" parameterType="java.util.List">
update table
SET
name = CASE id
<foreach collection="list" item="item">
WHEN cast(#{item.id} as
nvarchar2(36)) THEN #{item.name}
</foreach>
END,
create_time = CASE id
<foreach collection="list" item="item">
WHEN cast(#{item.id} as nvarchar2(36)) THEN #{item.createTime}
</foreach>
END,
update_time = CASE id
<foreach collection="list" item="item">
WHEN cast(#{item.id} as nvarchar2(36)) THEN #{item.updateTime}
</foreach>
END
WHERE
id IN
<foreach collection="list" item="item" separator="," open="(" close=")">
#{item.id}
</foreach>
</update>
出现字符集不匹配使用—cast(#{item.id} as nvarchar2(36))
下一篇:mybatis批量insert和update（mysql/postgres版）
