How to match calculated properties with JPA and Hibernate

Question

My Java bean has a childCount property. This property does not appear in the database column . Instead, it should be calculated using the database using the COUNT() function, which acts when combining my Java bean and its children. It would be even better if this property could be calculated on demand / "lazy", but this is not necessary.

In the worst case scenario, I can set this bean property using the HQL or API criteria, but I would prefer not to.

Hibernate @Formula may help, but I could hardly find any documentation.

Any help is greatly appreciated. Thank.

Answer 1

JPA does not offer any support for a derived property, so you will have to use the extension for a specific provider. As you mentioned, @Formula perfect for this when using Hibernate. You can use the SQL fragment:

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

Or even complex queries to other tables:

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

Where id is the id current object.

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

Without more details, I cannot give a more accurate answer, but the link above should be useful.

See also:

• Section 5.1.22. Column and Formula Elements (Hibernate Core Documentation)
• Section 2.4.3.1. Formula (Hibernate Annotations documentation)

Answer 2

As explained in this article , you have three options:

• either you calculate the attribute using the @Transient method
• You can also use the @PostLoad entity @PostLoad
• or you can use @Formula specific @Formula annotation

While Hibernate allows you to use @Formula , with JPA you can use the @PostLoad callback to populate the transition 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; } 

For more complex queries, you can use Hibernate @Formula , as described in this article :

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

Answer 3

Take a look at the Blaze-Persistence Entity Views, which runs on top of JPA and provides first-class DTO support. You can project anything onto attributes in Entity Views, and it will even use existing connection nodes for associations, if possible.

Here is a comparison example

 @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's a blog post about custom subquery providers that may also be of interest to you: https://blazebit.com/blog/2017/entity-view-mapping-subqueries.html