如果业务中有几百条数据的插入,用for循环一个一个插入效率太慢,而且事务处理复杂,下面是MySQL和Oracle两个版本的批量插入方法:
MySQL:
<insert id="insertList" parameterType="java.util.List">
INSERT INTO
`t_user`
( `id`,`name`, `password`, `phone`, `email`, `create_time` )
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
(SELECT REPLACE(UUID(), '-', '') AS id),
#{item.name,jdbcType=VARCHAR},
#{item.password,jdbcType=VARCHAR},
#{item.phone,jdbcType=VARCHAR},
#{item.email,jdbcType=VARCHAR},
NOW()
)
</foreach>
</insert>
Oracle:
<insert id="insertList" parameterType="java.util.List">
INSERT INTO
`t_user`
( `id`,`name`, `password`, `phone`, `email`, `create_time` )
<foreach collection="list" item="item" index="index" separator="union all">
select
sys_guid(),
#{item.name,jdbcType=VARCHAR},
#{item.password,jdbcType=VARCHAR},
#{item.phone,jdbcType=VARCHAR},
#{item.email,jdbcType=VARCHAR},
SYSDATE
from dual
</foreach>
</insert>