iBatis – Mapping Database NULL

iBatis is a framework used to map a SQL database to POJOs (Plain Old Java Objects).  iBatis is one framework of many out there, others include Hibernate.  Recently I’ve been tasked with a new webapp which will connect to a very old legacy database.  One of the first things I decided to do was look through the database and figure out what data I needed, and then design and create java objects to hold said data.  I chose iBatis for this particular project because I was dealing with a legacy database which I had little control over, and iBatis can give me the flexibility I need in case I need to write complex and specific SQL to construct my objects. 

The issue:
One thing I noticed about the database was that there are NULLs littered everywhere, and using a default value for columns was barely used at all.  So there are plenty of columns with a data type of int, float and bit (I’m using a MS SQL Server database) that have NULLs in there.  Now, if I was recoding other applications that updated and inserted into these places, I would have checked data and inserted a default value, as well as maybe altering the column to allow no NULLs and set a default value, but I can’t do that.  These tables I’m using will get mapped to a POJO, and of course I want the ints and booleans in the database mapped to ints and booleans in my POJO.  But what happens if one of those database ints or booleans has a NULL?  Well, java will try to set the int or boolean to a null (which can’t be done) and it throws an exception instead…an IllegalArgumentException to be exact.

The quick fix:
Going through the iBatis docs can sometimes be tedious.  Fortunately, the solution is simple.  If you open your xml file dealing with the object in question, and go to your resultMap element for the class, you’ll typically have a result element for each column you’re dealing with.  Add an attribute called nullValue with a value to give the object in question in case a NULL is present.  It’s that simple. 

Here’s an example…
You have a table called Employee with several columns and create a POJO like below to hold that data:

public class Employee {
    private int id;
    private String login;
    private String password;
    private int authorityType;
    private boolean passwordExpired;
 
    // ... constructors and setter/getter methods here...
}

Let’s say that authorityType and passwordExpired were NULL values in the database.  Adding the nullValue attribute to the xml file like so would solve your issue:

<resultMap id="EmployeeResult" class="Employee">
  <result property="id"              column="id"                               />
  <result property="login"           column="login"                            />
  <result property="password"        column="password"                         />
  <result property="authorityType"   column="authorityType"   nullValue="1"    />
  <result property="passwordExpired" column="passwordExpired" nullValue="false"/>
</resultMap>

4 Comments

  1. Mauli says:

    Why don’t you use Integer Objects instead of primitive types? That way your columns can have null without a problem. That is my definite aproach for enterprise apps, because there are always such problems. A different approach could be that you create setters which would set a default value in case of null.

  2. Developer Dude says:

    Like Mauli, I would choose objects over primitives too because often a null value does have some meaning in an app (*gasp* – yeah, yeah, I know, it is heresy, but this is the real world after all, and even Date said null means the value is unknown – IIRC).

    However, it is good to know that you can set a default value in the mapping (regardless of whether you are mapping to an object or primitive) if that is what you need to do. You often don’t have control over a database/schema/data, and sometimes you don’t have control over a POJO either, so it is always nice to have the options and know how to use them.

    Thanks for the tip, I don’t remember whether I knew this before or not because while I use iBatis as my preferred method of mapping POJOs to SQL and vice versa, I spend most of my time in the biz logic and not the persistence/query logic.

  3. Nick says:

    Thanks for the comments.
    Yes, using objects is another option although I’m not convinced the best. Of course it really depends on your database and design. Having an old legacy database where anything goes as far as type checking and so on, then I agree that objects may be a slightly more useful (not better) option.

  4. Kambiz says:

    you could also modify the sql statement to get default values instead of null.

    SELECT CASE
    WHEN passwordExpired IS NULL THEN false
    ELSE passwordExpired
    END
    FROM Employee;

Leave a Reply