How to fill entity's field with separate queue in Hibernate?

Question

Hi there!

I have an entity, say

@Entity
public class Category {

  private String name;

  private int usersInCategory;
}

I want to fill usersInCategory field with some aggregating SQL query (select count(*) ... group by ...).

How can I do this in Hibernate?

Answer 1

Use a Formula (this is an Hibernate specific annotation). From the documentation:

2.4.3.1. Formula

Sometimes, you want the Database to do some computation for you rather than in the JVM, you might also create some kind of virtual column. You can use a SQL fragment (aka formula) instead of mapping a property into a column. This kind of property is read only (its value is calculated by your formula fragment).

@Formula("obj_length * obj_height * obj_width")
public long getObjectVolume()

The SQL fragment can be as complex as you want and even include subselects.

As the documentation writes, the SQL fragment can be pretty complex and can reference the owning entity like in the example below (the non aliased id column in the o.customer_id=id part of the where clause references the owning entity column):

@Formula("(select coalesce(extract ('day' from age(max(o.creation_date))), 9999) from Orders o where o.customer_id = id)")
private int daysSinceLastOrder;

See also

• Hibernate Derived Properties - Performance and Portability

Answer 2

Another way which has been figured out to do that is following query for retreiving:

select c, (select count(i) from Item i where c = i.category) from Category c

This will produce list of tuples (Categroy, items_count). The initial task to init entity's field won't be accomplished that way, but that will be more efficient when you don't need to have that field always.