Mybatis中实现oracle的批量插入、更新

oracle 实现在Mybatis中批量插入，下面测试可以使用，在批量插入中不能使用insert 标签，只能使用select标签进行批量插入，否则会提示错误

 

### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

 

• 批量插入方式一
  

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper 
	PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.attence.attence.dao.AttenceDao">
	<resultMap id="Attence" type="Attence">
		<id column="ATTENCEKEY" 	property="attenceKey"/>
		<result column="EPC" 		property="epc"/>
		<result column="ANT" 		property="ant"/>
		<result column="RSSI" 		property="rssi"/>
		<result column="DEVICE" 	property="device"/>
		<result column="CRC" 		property="crc"/>
		<result column="BCC" 		property="bcc"/>
		<result column="DATETIME" 	property="dateTime" />
	</resultMap>		
	
	<select id="insertAttence" parameterType="List">
		INSERT ALL	
        <foreach collection="list" item="attence" index="index" separator="">  
		into Attence(
			attenceKey,
			epc,
			ant,
			rssi,
			device,
			crc,
			bcc,
			dateTime
		)
		values
        (
        	#{attence.id},
        	#{attence.epc},
        	#{attence.ant},
        	#{attence.rssi},
        	#{attence.device},
        	#{attence.crc},
        	#{attence.bcc},
        	#{attence.dateTime}        	
        ) 
        </foreach>  		
        SELECT * 
        FROM dual
	</select>	
</mapper>

 

<select id="insertWorksheet" parameterType="java.util.List">
        insert into TDB_WORKSHEET(WORKSHEET_ID,SIM_WORKSHEET_ID)  (
        <foreach item="item" index="index" collection="list"  separator="union all">
        (select
          #{item.worksheetId,jdbcType=VARCHAR},#{item.simWorksheetId,jdbcType=VARCHAR}
          from dual)
        </foreach>
        )
    </select>

      以上方式不支持oracle的主键序列方式。

•  批量插入方式二
  

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper 
	PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.attence.attence.dao.AttenceDao">
	<resultMap id="Attence" type="Attence">
		<id column="ATTENCEKEY" 	property="attenceKey"/>
		<result column="EPC" 		property="epc"/>
		<result column="ANT" 		property="ant"/>
		<result column="RSSI" 		property="rssi"/>
		<result column="DEVICE" 	property="device"/>
		<result column="CRC" 		property="crc"/>
		<result column="BCC" 		property="bcc"/>
		<result column="DATETIME" 	property="dateTime" />
	</resultMap>		
	
	<insert id="insertAttence" parameterType="java.util.List">
		insert into Attence(
			attenceKey,
			epc,
			ant,
			rssi,
			device,
			crc,
			bcc,
			dateTime
		)
                Select  
                     SEQ_ATTENCE.NEXTVAL,a.*
                From (
                     <foreach collection="list" item="attence" index="index" separator="union all"> 
                     ( 
                          Select 
                                #{attence.epc}, 
                                    #{attence.ant}, 
                                    #{attence.rssi}, 
                                    #{attence.device}, 
                                    #{attence.crc}, 
                                    #{attence.bcc}, 
                                    #{attence.dateTime} 
                                    From dual 
                            ) 

                      </foreach>
                )
       </insert>
       </mapper>

         同时由于使用oracle的自增加序列，无法联合union all使用，会提示错误，必须加上select语句进行进一步封装。

         1、传入的参数只有一个list时，则Mybatis映射collection的键名list，若传入含有其他参数，需要使用HashMap，同时键名为HashMap对应的键名。

         2、传入的参数只有一个array时，则Mybatis映射collection的键名array，若传入含有其他参数，需要使用HashMap，同时键名为HashMap对应的键名。

• 传递包含有数组参数的多参数HashMap实现批量插入

 

	<select id="insertGuideVideoList" parameterType="HashMap">
 	    insert into Guide_Video(	
				id,
				guideId,
				videoId
		) 	    
		Select
			SEQ_Guide_Video.NEXTVAL ,#{guideId}, video.*
		From( 	
 	    <foreach collection="arrGuideVideo" item="videoId" index="index" separator="union all">    
			Select
				#{videoId} videoId
			From dual 
		</foreach>
		)video
	</select>	

        arrGuideVideo是一个字符串数组，这里必须使用select标签，而不能使用insert标签。如果使用insert标签执行会提示语句未结束。

• 实现批量更新

 

	<update id="updateTest" parameterType="Test">
	    Begin
	         Update TST_TEST
			<set> 
	            <if test="test.testName != null and test.testName!= ''">
	                TESTNAME 	= #{test.testName}, 
	            </if>
	            
	            <if test="test.gatherKey != null and test.gatherKey!= ''">
	                GATHERKEY 	= #{test.gatherKey}, 
	            </if>	   
	            
	            <if test="test.subjectKey != null and test.subjectKey!= ''">
	                SUBJECTKEY 	= #{test.subjectKey}, 
	            </if>	 	  
	            
	            <if test="test.gradeKey != null and test.gradeKey!= ''">
	                GRADEKEY 	= #{test.gradeKey}, 
	            </if>	 
	            
	            <if test="test.answerCount != null and test.answerCount!= ''">
	                answerCount 	= #{test.answerCount}, 
	            </if>	   
	            
	            <if test="test.answerEndTime != null and test.answerEndTime!= ''">
	                answerEndTime 	= #{test.answerEndTime}, 
	            </if>	
	            
	            <if test="test.answerTimeLength != null and test.answerTimeLength!= ''">
	                answerTimeLength 	= #{test.answerTimeLength}, 
	            </if>				
			</set>
			Where testKey = #{testKey}
		End;
	</update>	

       实现批量更新实际上就是 生成oracle语句的代码块，然后jdbc执行sql语句。