Hibernate Criteria API Bug

I’ve talked about it before, but we’ve got a chunk of code in our primary product that allows permissioned users to build extremely complex queries using a GUI. The GUI inputs are converted to an intermediate format AST that is used for persistence and this gets converted into the Hibernate Criteria API equivalent by the DAO when the built up query is executed. The resulting feature is pretty dang powerful and I actually think it would be a cool thing to wrap up into a clean package and provide for others whenever I have time to do so.

In any case, this thing is complex enough that I’ve encountered many bugs or edge cases that aren’t handled cleanly in the Criteria API itself and we found a new one (new for us, the JIRA issue has been around for awhile) the other day that is worth throwing up a post about for others to find the workaround for more easily.

Sometimes databases require some denormalization for performance reasons (joins between 8+ digit row count tables don’t care how much RAM you have). If the column count gets too intense though and if you only really need a few values of each record the IO required to pull out all of the data can be fairly expensive and projections can be needed. With the Criteria API you can project specific columns with something like this:

public List<Object[]> getNamesOfPeople(){
    Criteria query = getSession().createCriteria(Person.class);

    ProjectionList projections = Projections.projectionList();
    projections.add(Projections.property("firstName"));
    projections.add(Projections.property("lastName"));

    query.setProjection(projections);

    return query.list();
}

One downside to this approach is you have to deal with Object arrays as the result from your queries. If you have manager or controller code its a bummer to have to write chunks of code for marshaling the values in the Object arrays into the actual objects (Person in this case) before doing things with it. Queries built with the Criteria API can have result transformers applied to them. Without going into specifics i’ll just say there is an AliasToBeanResultTransformer that can be used to automatically dump projected values into a instance of the specified class. Now the code would look like this:

public List<Person> getNamesOfPeople(){
    Criteria query = getSession().
        createCriteria(Person.class);

    ProjectionList projections = Projections.projectionList();
    projections.add(Projections.property("firstName"), "firstName");
    projections.add(Projections.property("lastName"), "firstName");

    query.setProjection(projections);
    query.setResultTransformer(
        new AliasToBeanResultTransformer(Person.class));

    return query.list();
}

The way the AliasToBeanResultTransformer works is to check the names of the properties of the object you want and write into them any values from the result with the same name. In the above example the second parameter to ProjectionList.add is the alias or the label for the column when the result hits the AliasToBeanResultTransformer. The above code will yield a list of Person objects with their firstName and lastName properties set. If the alias parameter to ProjectionList.add is omitted you will get a list of Person objects with nothing set (because Hibernate will use its own aliases and they won’t match up).

Finally the bug. When Hibernate generates the actual SQL that hits the database it aliases each projected column with its own internal scheme for the purposes of the SQL query. It will generate SQL that looks like this:

select this_.first_name as y0_, this_.last_name as y1_
from people this_;

If you add restrictions to your Criteria API query that involve the same properties and aliases you used in your projection list you can end up with generated SQL that looks like this:

select this_.first_name as y0_, this_.last_name as y1_
from people this_
where this_.y0_ = 'John' and this_.y1_ = 'Smith';

The key thing to note is that Hibernate is trying to be smart and reuse the alias specified in the select clause. The problem is that this isn’t valid SQL to any rdbms I know of. If you try to use the alias like that in the where clause you will get a “column ‘y0_’ does not exist” in PostgreSQL and similar messages in other databases.

This is a long known issue and the corresponding JIRA issue can be found here. Basically someone reports it, he gets cut off and told its Oracle’s problem because the SQL looks fine, and people pitch in about pretty much every other rdbms also not supporting it. Its still marked Minor and is Unassigned after 2 years so I doubt it will be fixed anytime soon. Thankfully, if you are having this problem you can look deeper in the comments and get a CustomPropertyAliasProjection class that when combined with a modification a little further down can be used to make it work with the AliasToBeanResultTransformer. The fix is essentially to ignore the alias in the select clause when generating the where clause. I won’t repost the code here as I didn’t write it and the JIRA page works just fine.

So if you run into this problem that is the fix. There are actually lots of bugs in the Criteria API but until someone is able to build an ORM tool better than Hibernate (and no such thing exists to my knowledge) I’m not going to switch and the fact that it is open source means you can usually find a workaround online.