How to map calculated properties with JPA and Hibernate

Question

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.

Dimercaprol

Answer 1

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:

• Section 5.1.22. Column and formula elements (Hibernate Core documentation)
• Section 2.4.3.1. Formula (Hibernate Annotations documentation)

Banjo

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 blah But countInternal remains set to its initial value (-1) :( I have tried using field annotations instead of getter annotations, but it still does not work. – Dimercaprol

U r the man Pascal. Is the SQL in the formula real SQL or HQL? Thx – Haughty

@NeilMcGuigan: The @Formula annotation uses SQL, not HQL. – Flipper

Just learned how important it is to have the parentheses around the query. Always ended up with an SQL Exception, since this query of course becomes a subquery when the host object is loaded. MySQL did not like the inline select without parentheses. – Nim

The new link to the article is: blog.eyallupu.com/2009/07/hibernate-derived-properties.html – Lowkey

So is the @Formula another query in addition to the primary query? How would you get the same value in multiple chain joined tables? I think JOIN are peferable to additional separate queries. – Manolo

Answer 2

You have three options:

• either you are calculating the attribute using a @Transient method
• you can also use @PostLoad entity listener
• or you can use the Hibernate specific @Formula annotation

While Hibernate allows you to use @Formula, with JPA, you can use the @PostLoad callback to populate a transient property with the result of some calculation:

@Column(name = "price")
private Double price;

@Column(name = "tax_percentage")
private Double taxes;

@Transient
private Double priceWithTaxes;

@PostLoad
private void onLoad() {
    this.priceWithTaxes = price * taxes;
}

So, you can use the Hibernate @Formula like this:

@Formula("""
    round(
       (interestRate::numeric / 100) *
       cents *
       date_part('month', age(now(), createdOn)
    )
    / 12)
    / 100::numeric
    """)
private double interestDollars;

Kraska

This however doesn't allow for more complex queries – Watthour

I updated the answer so now you have a solution for more complex queries too. – Kraska

Can I use order by with transient fields which are set by @PostLoad? – Carrizales

Answer 3

Take a look at Blaze-Persistence Entity Views which works on top of JPA and provides first class DTO support. You can project anything to attributes within Entity Views and it will even reuse existing join nodes for associations if possible.

Here is an example mapping

@EntityView(Order.class)
interface OrderSummary {
  Integer getId();
  @Mapping("SUM(orderPositions.price * orderPositions.amount * orderPositions.tax)")
  BigDecimal getOrderAmount();
  @Mapping("COUNT(orderPositions)")
  Long getItemCount();
}

Fetching this will generate a JPQL/HQL query similar to this

SELECT
  o.id,
  SUM(p.price * p.amount * p.tax),
  COUNT(p.id)
FROM
  Order o
LEFT JOIN
  o.orderPositions p
GROUP BY
  o.id

Here is a blog post about custom subquery providers which might be interesting to you as well: https://blazebit.com/blog/2017/entity-view-mapping-subqueries.html

Broil