Using a User Type to Fix Hibernate, DB2, Derby, and Boolean

Our team recently started using Derby for local development, with our production database being DB2. One nice thing is that the sql dialects for these are nearly identical and you don't need to change the dialect to get things to mostly work. We did, however, hit a snag with our boolean type fields. By default, the values for a boolean object default to "1" and "0" (or 1 and 0) when using the db2 driver and definition the column as a char(1), but when using the derby jdbc driver (with the DB2 hibernate dialect), the values were getting translated to "true" and "false" which was breaking because obviouly you cannot store 4 characters in a 1 character field. There is an existing http://mail-archives.apache.org/mod_mbox/db-derby-dev/201101.mbox/%3C13025320.210331296032083487.JavaMail.jira@thor%3E"> JIRA explaining that this behavior is actually desired (for some reason) and that Derby behaves like the DB2 type 2 driver instead of like the DB2 type 4 universal driver.

After googling the problem, it turns out a lot of people have run into this and there aren't any obvious solutions floating around. The best bet we could find using hibernate 3.5 was create a custom user Type (based on the hibernate delivered YesNoType) and annotated all our booleans to tell hibernate to use this type to map these fields. The user type we ended up with looks like the following (based on YesNoType).

package org.shamalamading.dong;

public class OneZeroType extends org.hibernate.type.CharBooleanType {


    protected final java.lang.String getTrueString() {
        return "1";
    }

    protected final java.lang.String getFalseString() {
        return "0";
    }

    public java.lang.String getName() {
        return "OneZeroType";
    }
}

We then modified our Hibenate entities to use this type as follows:

package org.shamalamading.dong;
@Entity
@Table(name = "REPORT_TBL")
public class Report implements Serializable {
    @Column(name = "ACTIVE_FLAG", columnDefinition="char(1)")
    @Type(type="org.shamalamading.dong.OneZeroType")
    private Boolean active;
    public Boolean getActive() {
        return this.active;
    }
    public void setActive(Boolean newValue) {
        this.active = newValue;
    }

}

Now when persisting to database and setting values, hibernate will set "1" as true and "0" as false. This helps when using derby and db2 together because of the differences in how the drivers natively handle booleans. On an additional note, I think it's interesting that in DB2 (or database) land, it seems pretty common for "0" or 0 to represent "false", but in programming land, it's much more common for 0 to represent "true" and "everything else" to represent false.