MyBatis批量插入数据SQL—Oracle与MySql的写法

在开发的过程会遇到往数据库（Oracle与MySql）表中批量插入数据的业务场景。但用MyBatis在执行批量插入的过程中总是报sql未正确结束的问题，经过搜索发现MySql与Oracle的批量插入的sql语句写法不一致。

示例为xxmapper.xml中的部分代码片断
1、Oracle写法一：

<!--2、批量插入 投诉与表扬信息-->
<insert id="insertBatchComplaintsPraise" parameterType="java.util.List" >
   INSERT INTO TSYBY (JGMC, TDMC, TDZXM, YISHENG, YAOJISHI, HUSHI, FANGBAO, TSYBY, STATIC_DATE, UPLOAD_DATE)
   VALUES 
    <foreach collection="list" item="item" index="index" open="(" separator="union all" close=")" >
        select 	#{item.jgmc,jdbcType=VARCHAR}, #{item.tdmc,jdbcType=VARCHAR}, #{item.tdzxm,jdbcType=VARCHAR}, 
        		#{item.yisheng,jdbcType=VARCHAR}, #{item.yaojishi,jdbcType=VARCHAR}, #{item.hushi,jdbcType=VARCHAR}, 
        		#{item.fangbao,jdbcType=VARCHAR}, #{item.tsyby,jdbcType=DECIMAL}, #{item.staticDate,jdbcType=DATE}, 
        		#{item.uploadDate,jdbcType=TIMESTAMP}
        from dual
    </foreach>
  </insert>

2、Oralce写法二：

<!--2、批量插入 投诉与表扬信息-->
<insert id="insertBatchComplaintsPraise" parameterType="java.util.List" >
    insert all
   	<foreach collection="list" item="item" index="index">
   		into TSYBY (STATION_CODE, JGMC, TEAM_ID, TDMC, TDZXM, YISHENG, YAOJISHI, HUSHI, FANGBAO, TSYBY, STATIC_DATE, UPLOAD_DATE)
   		values (#{item.stationCode,jdbcType=VARCHAR}, #{item.jgmc,jdbcType=VARCHAR}, #{item.teamId,jdbcType=VARCHAR}, 
   				#{item.tdmc,jdbcType=VARCHAR}, #{item.tdzxm,jdbcType=VARCHAR}, #{item.yisheng,jdbcType=VARCHAR}, 
        		#{item.yaojishi,jdbcType=VARCHAR}, #{item.hushi,jdbcType=VARCHAR}, #{item.fangbao,jdbcType=VARCHAR}, 
        		#{item.tsyby,jdbcType=DECIMAL}, #{item.staticDate,jdbcType=DATE}, #{item.uploadDate,jdbcType=TIMESTAMP})
   	</foreach>
    select 1 from dual
</insert>

以上为Oracle中批量插入的两种方式，但方式一有个缺点，就是所查询的字段不能有空值，即 item.xxx 不能为空，否则插入会失败。

3、MySql写法：示例如下

<!-- 2、批量插入 团队评价与满意度数据  -->
<insert id="insertBatchSatisfaction" parameterType="java.util.List">
  	insert into pe_team_evaluation_satisfaction (
  		id, 
  		org_code, org_name, 
  		team_id, team_name, team_leader_name, 
      	praise_complaint_score, service_gain_ratio, 
      	signpatient_satisfaction, sign_service_awareness, 
      	team_satisfaction, fill_year, 
      	add_org_id, add_org_name, add_user_id, 
      	add_user_name, add_time
      )
    values 
    <foreach collection="record" item="item" index="index" separator="," >
    	(#{item.id,jdbcType=VARCHAR}, 
    	#{item.orgCode,jdbcType=VARCHAR}, #{item.orgName,jdbcType=VARCHAR}, 
    	#{item.teamId,jdbcType=VARCHAR}, #{item.teamName,jdbcType=VARCHAR}, #{item.teamLeaderName,jdbcType=VARCHAR}, 
      	#{item.praiseComplaintScore,jdbcType=DECIMAL}, #{item.serviceGainRatio,jdbcType=DECIMAL}, 
      	#{item.signpatientSatisfaction,jdbcType=DECIMAL}, #{item.signServiceAwareness,jdbcType=DECIMAL}, 
      	#{item.teamSatisfaction,jdbcType=DECIMAL}, #{item.fillYear,jdbcType=DATE}, 
      	#{item.addOrgId,jdbcType=VARCHAR}, #{item.addOrgName,jdbcType=VARCHAR}, #{item.addUserId,jdbcType=VARCHAR}, 
      	#{item.addUserName,jdbcType=VARCHAR}, #{item.addTime,jdbcType=TIMESTAMP})
    </foreach>
</insert>