Derby, iBATIS and nulls

Today I stumbled upon a funny null issue with Derby and iBATIS:

 java.sql.SQLDataException: An attempt was made to get a data value of type '<UNKNOWN>' from a data value of type  CHAR

It happened when I tried to perform an iBATIS update and a string value of my POJO was null. The problem appeared to be trivial: I used parameterClass in update and iBATIS, naturally, did not know any good way of telling Derby what type of null to use :) (and Derby was dumb enough not to set a value to null anyway)

The solution is simple: use parameterMap for updates and in the map clearly specify the jdbcType for all attributes which can become nulls. Like this, for example:

 <parameterMap class="user" id="userMap">
    <parameter property="username" jdbcType="VARCHAR"/>
    <parameter property="password" jdbcType="CHAR"/>
    <parameter property="email" jdbcType="VARCHAR"/>
    <parameter property="activationCode" jdbcType="CHAR"/>
    <parameter property="lastLogin" jdbcType="TIMESTAMP"/>
    <parameter property="id" jdbcType="INTEGER"/>
 </parameterMap>

 <update id="update" parameterMap="userMap">
    <![CDATA[
    update "user" set username = ?, password = ?, email = ?,
    activationCode = ?, lastLogin = ?, lastModified = CURRENT_TIMESTAMP    
    where id = ?
    ]]>
 </update>

First published on the old anydoby.com blog on 2009-06-06.