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

来源：互联网发布：网络摄像头忘记密码编辑：程序博客网时间：2024/05/18 13:10

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)     SelectSEQ_Guide_Video.NEXTVAL ,#{guideId}, video.*From(      <foreach collection="arrGuideVideo" item="videoId" index="index" separator="union all">    Select#{videoId} videoIdFrom 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语句。

1 0