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.

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.
June 26, 2012 at 7:48 AM
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?"
July 13, 2012 at 6:03 PM
Post a Comment

Popular posts from this blog

Please use ANSI-92 SQL Join Syntax

-
Image
Way back in 1992, a standard was published for SQL. In it, the syntax for specifying joins in SQL Database was finally standardized. This was a good thing because (for folks who've been doing this a while), trying to decode the vagaries of how various Databases handled outer joins could drive you bonkers yielding different results depending on the parse order of things in the from and where clauses. Unfortunately, some DBMS vendors took a while to adopt this standard (Oracle didn't support it until 2001), but at this point if you're using a major RDBMS and NOT using it, you're probably mired in a tradition or habit that bears changing. As an example of what I'm describing, here are some examples of what I mean. Old School (no standard syntax + implementation and evaluation of predicates varied by vendor [and query]): select * from customer, order where customer.customer = order.customerid ANSI way (agreed to standard, predicate evaluation has some rules ...
Read more

The difference between Scalability, Performance, Efficiency, and Concurrency explained

-
I thought I'd give some explanation of the difference between scalability, performance, Efficiency, and concurrency. To make things more accessible (and because I'm watching a cooking show on TV) I'm going to use a baking metaphor to help explain the differences. In our case, the system in question is baking cookies for consumers. Scalability, is how well something responds to adding more resources. If we can double our capacity by doubling the number of resources, we've got linear scalability. In our baking example, if we add more ingredients, we can scale to a larger number of cookies. To contrast with our other metrics, this doesn't actually increase the performance of creating a single cookie (they still take 10 minutes to bake), and the efficiency is the same (we just have more capacity). We also cannot bake more cookies in a fixed time period, but over a larger time period we can produce more cookies. Performance is how fast something can get done, u...
Read more

the myth of asynchronous JDBC

-
Image
I keep seeing people (especially in the scala/typesafe world) posting about async jdbc libraries. STOP IT! Under the current APIs, async JDBC belongs in a realm with Unicorns, Tiger Squirrels, and 8' spiders. While you might be able to move the blocking operations and queue requests and keep your "main" worker threads from blocking, jdbc is synchronous. At some point, somewhere, there's going to be a thread blocking waiting for a response. It's frustrating to see so many folks hyping this and muddying the waters. Unless you write your own client for a dbms and have a dbms that can multiplex calls over a single connection (or using some other strategy to enable this capability) db access is going to block. It's not impossible to make the calls completely async, but nobody's built it yet. Yes, I know ajdbc is taking a stab at this capability, but even IT uses a thread pool for the blocking calls (be default). Someday we'll have async databa...
Read more