tags:

views:

107

answers:

1

My Java bean has a childCount property. This property is not mapped to a database column. Instead, it should be calculated by the database with a COUNT() function operating on the join of my Java bean and its children. It would be even better if this property could be calculated on demand / "lazily", but this is not mandatory.

In the worst case scenario, I can set this bean's property with HQL or the Criteria API, but I would prefer not to.

The Hibernate @Formula annotation may help, but I could barely find any documentation.

Any help greatly appreciated. Thanks.

+1  A: 

JPA doesn't offer any support for derived property so you'll have to use a provider specific extension. As you mentioned, @Formula is perfect for this when using Hibernate. You can use an SQL fragment:

@Formula("PRICE*1.155")
private float finalPrice;

Or even complex queries on other tables:

@Formula("(select min(o.creation_date) from Orders o where o.customer_id = id)")
private Date firstOrderDate;

Where id is the id of the current entity.

The following blog post is worth the read: Hibernate Derived Properties - Performance and Portability.

Without more details, I can't give a more precise answer but the above link should be helpful.

See also

Pascal Thivent
Thanks Pascal.Do you have any idea why the following is not working? @Formula(value = "(select count(*) from ic inner join c where ic.category_id = c.id and c.id = id)") public Integer getCountInternal() { return countInternal; }The query is OK and I see it being run in the logs. The mapping seems OK:main org.hibernate.cfg.Ejb3Column - binding formula (select count(*) blah blah blahBut countInternal remains set to its initial value (-1) :(I have tried using field annotations instead of getter annotations, but it still does not work.
Francois