Hibernate – Populating an unmapped Entity field with COUNT(*) using @Formula

This post demonstrates how to populate an Entity Bean property that is not mapped to a DB table column. It uses the @Formula annotation to insert native SQL into the hibernate generated query.

MySQL SQL

(DB name is “test”)

 

Parent.java

@Formula explained:

  • In order to have the Hibernate SQL generator produce valid SQL, the opening ( and closing )’s are required.
  • I.e. @Formula(“(select count(*) from t_child c where c.parent_id = parent_id)“)
  • The SQL in the @Formula is not HQL. That may be obvious from looking at it but anyway….. HQL is not supported, just SQL because this SQL is inserted into the larger query automatically by Hibernate.
  • parent_is is the ID of the current entity.

 

 Child.java

 

Here is the SQL generated.

 (Formatted by: http://www.dpriver.com/pp/sqlformat.htm)

Get Parent with Children

 

Print the result

 

The Result Printed

 

Comments always welcome…………

 

3 Comments