mybatis batch insert

    when we mention batch operation in mybatis , many people first think of <foreach> tag, let us first look at it.

  

   <insert id="insert" parameterType="map" >

      insert into label_resources (questionid, labelid)
      values
          <foreach collection="labelid" item="item" index="index" separator=",">
              (#{questionid,jdbcType=BIGINT},#{item})
          </foreach>

  </insert>

      we notice that, it only put sentience after values into foreach, if you want put the whole sentience into foreach, we must do one thing first, 

     jdbc:mysql://127.0.0.1:3306/mybank?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true

     or it will throws exception.

     But strictly to say, foreach tag is not batch operation. it is only a long sql.

     if we want use batch operation, we must open batch operation first.

     we can add this in our mybatis configuration file as below:

     

1. <settings>    
2.     <setting name="defaultExecutorType" value="BATCH" />  
3. </settings>  

     but usually we use spring to integrate with mybatis , so we can make it like this:

     <bean name="batchSqlSession" class="org.mybatis.spring.SqlSessionTemplate">

           <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"/>

           <constructor-arg name="executorType" ref="BATCH"/>

     </bean>

   

     and then it can make sql as batch insertion.

     Other things we should notice are:  

     1)if we put our operation in a spring transaction, and then we must use one same sqlSession, if we use one simple sqlSession to get data, another batchSqlSession to insert data, it will throws exception like
    Can't change the ExecutorType when there is an existing transaction.

     2) if we make the executorType as BATCH, then we can't get the automatic id after insertion. Although many articles says it can , but mybatis don't support, maybe you can add plug-in to change it , but all I failed.