Tuesday, May 19, 2009

Hibernate IN statement

Problem with hibernate selections of ranged values (sets)

update (2009-05-19):
Thanks to Denis Tsyplakov, found HQL in expression :D. Problem comlpetely closed. So, one should better user HQL, not criteries.

Disclaimer: there's nothing really new in this article. You might know everything which is written here. This posting is mostly for myself and people facing the same problem (with memory :) I just forgot how this should be done. Thanks to colleagues returning me to the right way. Here is the problem described:
  1. We have a mapped Hibernate entity.
  2. This entity has some field. :) For example. Let it be some enumeration (status?).
  3. We need to select the entities with several statuses.
Here's example of such entity:

public class Entity {

private long id;

private Status entityStatus;

// Getters and setters.


and example of the enumeration:
public enum Status {

We need, for example, select all STARTED and COMPLETED entities, not touching NOT_STARTING.

There are 3 valid ways I see:
  1. We make a query for STARTED entities, and then other query - for NOT_STARTED. After the results are gathered the result objects are mixed.
    Cons: you'll need to do N queries for N statuses. Quite bad, isn't it?
    Pros: don't really know. Maybe, it's simple? ;)

  2. We construct a query, with OR statement in the middle. That how it should look like:

    select e from Entity e where e.status=1 or e.status=2
    I'm using the 1, 2 numbers and not the enum names especially to make statement less complicated.
    Cons: single query, but static. That means that if you need to form the list of statuses to load at runtime (set of checkboxes?), you'll need to glue HQL. And any query gluing smells very bad.
    Pros: simple, and if you are sure the list of statuses to load would never change... if you are SURE.

  3. And the least one I see. We may perform selection using the Criterion Hibernate API, not the query language. The 'query' itself would look like the following code:

    List statusList = new ArrayList(); statusList.add(Status.STARTED); stausList.add(Status.COMPLETED);
    Criteria c = session.createCriteria(Entity.class). add(Restriction.in("status", statusList)); c.list(); // get the list of entities with corresponding status.
    Cons:uses Criteria API, which really is a code. So, if you plan to store HQL queries in a (properties) file, this way is not for you.
    Pros: fast, simple and dynamic. That's what I left with.

No comments: