Tuesday, 16 July 2013


The Formula annotation is used on a property of a Hibernate entity. It is currently not a part of the Java Persistence Standard, and thusly is a Hibernate extension.

I thought I'd write some of my notes and bookmarks to other sites here, as the information in the Hibernate book seems very brief.


  • the SQL in the formula is inserted automatically whenever you retrieve the Entity, without you having to think about it.
  • the advantage compared to calculating the fields in the Getter or Setter, is that it is possible to use the property in HQL queries. For example, adding "ORDER BY existsFreeby" in HQL without problems.


  • HQL cannot be used in the Formula.
  • you have plain SQL in your Entity. I know this is normal according to the JPA spec, but at work they tend to frown on that. Don't ask me why.
  • whenever a keyword is unknown, it will prefix it with the name of the Entity table. This is a good feature, normally. In the example give, for example, the "id" will be expanded to "ORDERS0_.id". However, in the case where we used COALESCE, if we had a space between COALESCE and the opening bracket, like so "COALESCE (", Hibernate would expand this to "ORDERS0_.COALESCE (" causing an error, obviously.
  • a problem can occur when you are using ansi sql queries and using query.addEntity(Orders.class); It will break on that now.


Always use opening and closing brackets at the beginning and end of the actual SQL part. As it is inserted into the Hibernate created SQL, it might break the syntax if you forget.

References Formula
Hibernate derived properties
Calculated property with JPA / Hibernate
@Formula adds a this_. prefix to an SQL function - Hibernate HH-5265
Hibernate with JPA ignoring @Formula

No comments:

Post a Comment