title: MyBatis Batch Update Exception:Column cannot be null
body: <p>Blow is my table</p>
  
  <pre><code>CREATE TABLE `user` 
  (   `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `username` VARCHAR}(255) NOT NULL,
      `priority` bigint(20) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;  
  </code></pre>
  
  <p>Below is my SQL file</p>
  
  <pre><code>&lt;update id="batchUpdate"&gt; 
  UPDATE user  &lt;trim prefix="SET" suffixOverrides=","&gt;  
  &lt;trim prefix="username=CASE" suffix="END,"&gt;
  &lt;foreach collection="list" item="item" index="index" separator=" "&gt;
   &lt;if test="item.username!=null"&gt;WHEN id=#{item.id,jdbcType=BIGINT} THEN #{item.username,jdbcType=VARCHAR}&lt;/if&gt;
   &lt;/foreach&gt;
  &lt;/trim&gt;
  &lt;trim prefix="priority=CASE" suffix="END,"&gt;
  &lt;foreach collection="list" item="item" index="index" separator=" "&gt;
    &lt;if test="item.priority!=null"&gt;WHEN id=#{item.id,jdbcType=BIGINT} THEN #{item.priority,jdbcType=BIGINT}&lt;/if&gt;
  &lt;/foreach&gt;
  &lt;/trim&gt;  
  &lt;/trim&gt;
  WHERE id IN  
  &lt;foreach collection="list" item="item" index="index" open="(" close=")" separator=","&gt;#{item.id,jdbcType=BIGINT}
   &lt;/foreach&gt;
  &lt;/update&gt; 
  </code></pre>
  
  <p>Below is my update data:  </p>
  
  <pre><code>user1{ id:1, username:"test1", priority:1 } 
  user2{ id:1, priority:3 } 
  </code></pre>
  
  <p>When I update user1 and user2 together using batchUpdate, I got exception below:</p>
  
  <blockquote>
    <p>org.springframework.dao.DataIntegrityViolationException: ### Error
    updating database.  Cause:
    com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
    Column 'priority' cannot be null### The error may involve
    defaultParameterMap### The error occurred while setting parameters###
    SQL: SET username=CASE WHEN id=? THEN ? END,    priority=CASE WHEN
    id=? THEN ?  WHEN id=? THEN ? END, WHERE id IN( ?,?)### Cause:
    com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
    Column 'username' cannot be null; SQL []; Column 'cspuid' cannot be
    null; nested exception is
    com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
    Column 'cspuid' cannot be null    at
    org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:85)   at
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at
    org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)    at
    org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:365)   at
    $Proxy8.update(Unknown Source)    at
    org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:251) at
    org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)  at
    org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:40) at
    $Proxy69.batchUpdateByKey(Unknown Source)</p>
  </blockquote>
  
  <p>While the SQL Logger print the SQL below which could be executed success in mysql:SQL:</p>
  
  <pre><code>UPDATE user SET username=CASE WHEN id=2 THEN 'test1' END, priority=CASE WHEN id=2 THEN 3 WHEN id=1 THEN 1 END WHERE id IN ( 2 , 1 )
  </code></pre>
  
  <p>While I update user1 and user2 dividually, it update them success. And if I change user1 and user2 as blew, it don't throw exception too. It seems that the update colummns should be same in update data List. </p>
  
  <pre><code>user1{ id:1, priority:1 } user2{ id:1, priority:3 }
  </code></pre>
  
  <p>The issue is very weird, because the SQL print could be executed success in mysql. It's problem about MyBatis?</p>
  
  <p>Another batch update method like below. It's simple but it always return 1/0, don't return row(s)affected. Have any good methods to get count of rows affected?</p>
  
  <pre><code>&lt;update id="batchUpdate"&gt; 
  &lt;foreach collection="list" item="item" index="index" separator=";"&gt;
  UPDATE user  &lt;trim prefix="SET" suffixOverrides=","&gt;  
   &lt;if test="item.username!=null"&gt;username=#{item.username,jdbcType=VARCHAR}&lt;/if&gt;
   &lt;if test="item.priority!=null"&gt;priority=#{item.priority,jdbcType=BIGINT}&lt;/if&gt;
  WHERE id=#{item.id,jdbcType=BIGINT}
   &lt;/foreach&gt;
  &lt;/update&gt; 
  </code></pre>