Friday, June 22, 2012

Fixing Hibernate, DB2, H2, and Boolean Issues With a User Type

Edit: It was pointed out that this was a problem with H2, NOT Derby... not sure how I missed that, I've updated (Thanks Nick)

Our team recently started using H2 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 h2 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.

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 H2 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.

2 comments:

Anonymous said...

Why didn't you subclass the H2-Dialect as to let it have the same behaviour as DB2? Basically you spill annotations all over production code for testing purposes.

Mike Mainguy said...

That's not a bad idea, but then it makes the behavior implicit (also not necessarily bad). By doing it this way we can switch to ANY dbms and not have to change anything assuming we want the behavior to be the same. I guess depends on which question get's the strongest "yes" answer. Is it: 'do we want booleans to behave the same way in every dbms?' or 'do we want booleans in one specific dialect to work like booleans in another specific dialect?"