title: Inserting a list of values in mybatis
body: <p>I have a Java application running under Weblogic 12c using an Oracle DB and Apache mybatis.</p>
  
  <p>For a single insert I am using this mapper:</p>
  
  <pre><code> &lt;resultMap id="DataQualityCRUDPlainResultMap" type="de.vkd.niswrapper.model.persistence.DataQualityCRUD"&gt;                                                          
      &lt;id     column="ID"                  property="id"              jdbcType="DECIMAL"   /&gt;
      &lt;result column="REGION"              property="region"          jdbcType="DECIMAL"   /&gt;
      &lt;result column="ONB_NUM"             property="onbNum"          jdbcType="DECIMAL"   /&gt;
      &lt;result column="ASB_NUM"             property="asbNum"          jdbcType="DECIMAL"   /&gt;
      &lt;result column="FIBERNODES_TOTAL"    property="fiberNodesTotal" jdbcType="DECIMAL"   /&gt;
      &lt;result column="FIBERNODES_COUNT"    property="fiberNodesCount" jdbcType="DECIMAL"   /&gt;     
      &lt;result column="FIBERROUTE_TOTAL_COUNT"       property="fiberrouteTotalCount"     jdbcType="DECIMAL"   /&gt;
      &lt;result column="FIBERROUTE_LONGITUDE"         property="fiberrouteLongitude"      jdbcType="DECIMAL"   /&gt;
      &lt;result column="FIBERROUTE_TOTAL_COUNT_USED"  property="fiberrouteTotalCountUsed" jdbcType="DECIMAL"   /&gt;
      &lt;result column="FIBERROUTE_LONGITUDE_USED"    property="fiberrouteLongitudeUsed"  jdbcType="DECIMAL"   /&gt;       
      &lt;result column="DT_CREATION"         property="dtCreation"      jdbcType="TIMESTAMP" /&gt;
  &lt;/resultMap&gt;
  
  &lt;sql id="cols"&gt;
      ${alias}ID,     
      ${alias}REGION,
      ${alias}ONB_NUM, 
      ${alias}ASB_NUM, 
      ${alias}FIBERNODES_COUNT, 
      ${alias}FIBERNODES_TOTAL,       
      ${alias}FIBERROUTE_TOTAL_COUNT, 
      ${alias}FIBERROUTE_LONGITUDE, 
      ${alias}FIBERROUTE_TOTAL_COUNT_USED, 
      ${alias}FIBERROUTE_LONGITUDE_USED,                  
      ${alias}DT_CREATION 
  &lt;/sql&gt;
  
  &lt;insert  id="insert"  parameterType="de.vkd.niswrapper.model.persistence.DataQualityCRUD"&gt;
  INSERT INTO NW_NIS_DATA_QUALITY (
  &lt;include refid="cols"&gt;
      &lt;property name="alias" value="" /&gt;
  &lt;/include&gt;
  ) 
  VALUES ( 
      #{id},
      #{region},
      #{onbNum},
      #{asbNum},
      #{fiberNodesTotal},
      #{fiberNodesCount},         
      #{fiberrouteTotalCount},
      #{fiberrouteLongitude},
      #{fiberrouteTotalCountUsed},
      #{fiberrouteLongitudeUsed},                             
      #{dtCreation, jdbcType=TIMESTAMP}
  )
  &lt;/insert&gt;
  </code></pre>
  
  <p>From java I am doing the following:</p>
  
  <pre><code>for (final DataQuality aDQ : aDQList) {
      DataQualityCRUD myDDQCrud = new DataQualityCRUD();          
      fillFields(aRegion, now, aDQ, myDDQCrud);                                                                           dataQualityCRUDMapper.insert(myDDQCrud);                            
  }
  </code></pre>
  
  <p>This is working but it is not optimal.</p>
  
  <p>I wanted to eliminate the <code>insert</code> in the loop and to perform an <code>insert</code> of a list of values.</p>
  
  <p>For achieving this I did the following:</p>
  
  <pre><code>Map&lt;String, Object&gt; map = new HashMap&lt;String, Object&gt;();                
      for (final DataQuality aDQ : aDQList) {
          DataQualityCRUD myDDQCrud = new DataQualityCRUD();          
          fillFields(aRegion, now, aDQ, myDDQCrud);                                           
          resultList.add(myDDQCrud);              
      }
      map.put("list", resultList);
      dataQualityCRUDMapper.insertAll(map);
  </code></pre>
  
  <p>I've added to my <code>Mapper</code> the <code>insertAll</code> specification as follows:</p>
  
  <pre><code>    &lt;insert  id="insertAll"  parameterType="java.util.Map"&gt;
      INSERT INTO NW_NIS_DATA_QUALITY (
          &lt;include refid="cols"&gt;
          &lt;property name="alias" value="" /&gt;
      &lt;/include&gt;
      ) VALUES
      &lt;foreach collection="list" item ="element" open = "(" separator="),(" close=")"&gt;            
          #{element.id},
          #{element.region},
          #{element.onbNum},
          #{element.asbNum},
          #{element.fiberNodesTotal},
          #{element.fiberNodesCount},         
          #{element.fiberrouteTotalCount},
          #{element.fiberrouteLongitude},
          #{element.fiberrouteTotalCountUsed},
          #{element.fiberrouteLongitudeUsed},                             
          #{element.dtCreation, jdbcType=TIMESTAMP}       
      &lt;/foreach&gt;
  &lt;/insert&gt;
  </code></pre>
  
  <p>Unfortunately this is generating an exception as follows (and I am not understanding):</p>
  
  <pre><code>### Error updating database. Cause: java.sql.SQLSyntaxErrorException: 
  ORA-00933: SQL command not properly ended ### The error may involve 
  de.vkd.niswrapper.adapter.gis.mapping.DataQualityCRUDMapper.insertAll-
  Inline ### The error occurred while setting parameters ### SQL: INSERT 
  INTO NW_NIS_DATA_QUALITY ( ID, REGION, ONB_NUM, ASB_NUM, FIBERNODES_COUNT, 
  FIBERNODES_TOTAL, FIBERROUTE_TOTAL_COUNT, 
  FIBERROUTE_LONGITUDE, FIBERROUTE_TOTAL_COUNT_USED,
  FIBERROUTE_LONGITUDE_USED, DT_CREATION ) 
  VALUES 
  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ),
  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ),
  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ),
  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
  
  ### Cause: java.sql.SQLSyntaxErrorException: 
  ORA-00933: SQL command not properly ended ; bad SQL grammar []; 
  nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: 
  SQL command not properly ended 
  </code></pre>
  
  <p>Any hint?</p>