使用union all 来串连每个values,其中jdbcType的设置可以使null值也输入进去

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<insert id="saveList" parameterType="java.util.List">
INSERT INTO DDZHPT.CMS_SCHEDUAL_DETIAL
(
DEPT_ID,
SCHEDUAL_DATE,
CMS_SCHEDUAL_TYPE_ID,
CMS_SCHEDUAL_TEAM_ID,
CMS_SCHEDUAL_TYPE_PERIOD_ID,
CMS_SCHEDUAL_TIME_ID,
SYS_POST_ID,
POINT_ID,
PERSON_ID
)
<foreach collection="list" item="item" index="index" separator="union all">
SELECT
#{item.deptId,jdbcType=DECIMAL},
#{item.schedualDate,jdbcType=TIMESTAMP},
#{item.cmsSchedualTypeId,jdbcType=VARCHAR},
#{item.cmsSchedualTeamId,jdbcType=VARCHAR},
#{item.cmsSchedualTypePeriodId,jdbcType=VARCHAR},
#{item.cmsSchedualTimeId,jdbcType=VARCHAR},
#{item.sysPostId,jdbcType=VARCHAR},
#{item.pointId,jdbcType=VARCHAR},
#{item.personId,jdbcType=VARCHAR}
FROM DUAL
</foreach>
</insert>

纯粹使用foreach

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<insert id="saveList" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" separator="union all">
INSERT INTO DDZHPT.CMS_SCHEDUAL_DETIAL
(
DEPT_ID,
SCHEDUAL_DATE,
CMS_SCHEDUAL_TYPE_ID,
CMS_SCHEDUAL_TEAM_ID,
CMS_SCHEDUAL_TYPE_PERIOD_ID,
CMS_SCHEDUAL_TIME_ID,
SYS_POST_ID,
POINT_ID,
PERSON_ID
)VALUES(
#{item.deptId,jdbcType=DECIMAL},
#{item.schedualDate,jdbcType=TIMESTAMP},
#{item.cmsSchedualTypeId,jdbcType=VARCHAR},
#{item.cmsSchedualTeamId,jdbcType=VARCHAR},
#{item.cmsSchedualTypePeriodId,jdbcType=VARCHAR},
#{item.cmsSchedualTimeId,jdbcType=VARCHAR},
#{item.sysPostId,jdbcType=VARCHAR},
#{item.pointId,jdbcType=VARCHAR},
#{item.personId,jdbcType=VARCHAR}
)
</foreach>
</insert>

纯粹使用foreach

1
2
3
4
5
6
7
8
<insert id="insertBatch">
INSERT INTO t_user
(id, name, del_flag)
VALUES
<foreach collection ="list" item="user" separator =",">
(#{user.id,jdbcType=DECIMAL}, #{user.name,jdbcType=VARCHAR}, #{user.delFlag,jdbcType=VARCHAR})
</foreach >
</insert>

特别注意:mysql默认接受sql的大小是1048576(1M),即第三种方式若数据量超过1M会报如下异常:(可通过调整MySQL安装目录下的my.ini文件中[mysqld]段的"max_allowed_packet = 1M")

nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576).

You can change this value on the server by setting the max_allowed_packet’ variable.

个人一般使用第三种方法.

文章参考:坏坏的蛋,小勇DW3