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.

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)

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;

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