How to implement this Query in Hibernate?

Question

I'm converting a legacy iBatis implementation to Hibernate, and for backwards compatibility purposes need to present counts of an object's collections rather than the collections themselves. The original query was:

select A.*, ( select count(*) from B where B.A_id = A.id ) as B_count from A;

and b_count would be presented in the response. I'd like to be able to do the same without lazy-loading A's collection of B's for each query result.

Any ideas or suggestions?

Answer 1

You can use a projection.

The syntax for the row count is below:

    Criteria crit = session.createCriteria(B.class);
    crit.setProjection(Projections.rowCount());
    List results = crit.list();

Edit: After re-reading, I think this may not be what you're asking for....

Answer 2

Hibernate filters are used to apply additional restrictions to query results (e.g. think of them as part of "where" clause), so they won't do what you want. You have two options here:

A) You can eagerly get collection of Bs for your A:

from A a left join fetch a.Bs b

If you do so, keep in mind that for queries that would return multiple As you may get duplicates in the result list (e.g. if you have 2 As and each of them has 3 Bs you'll get 6 results back). Wrap them in a set to ensure uniqueness.

B) Assuming you have an appropriate constructor for A, you can do the following:

 select new A(a.field1, a.field2, ... a.fieldN, count(*) as B_count)
   from A a left join a.Bs b
  group by a.field1, a.field2, ... a.fieldN

Answer 3

The best method seems to be using a Hibernate formula, mapped to the getter and setter of my BCount attribute in the class A. My code:

public class A {
   // ...
   private long bCount;

   // ...

   @Formula( "(select count(*) from B where B.A_id = id" )
   public long getBCount() {
      return this.bCount;
   } 

   public void setBCount( long bCount ) {
      this.bCount = bCount;
   }
}

Great thing about this method is that the count is returned in the same fetch to hydrate the initial object, and does not result in 1+N queries for collection query results!