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

User · Answer

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

User · Answer

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    quot price quot   private Double price    Column name    quot tax percentage quot   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  quot  quot  quot      round          interestRate  numeric   100           cents          date part  month   age now    createdOn              12        100  numeric      quot  quot  quot   private double interestDollars

User · Answer

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

[java] How to map calculated properties with JPA and Hibernate

Examples related to java

Examples related to hibernate

Examples related to jpa

Examples related to orm

Examples related to hibernate-mapping