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…………

 

  • costa costa

    log4j:WARN No appenders could be found for logger (org.hibernate.type.BasicTypeRegistry).
    log4j:WARN Please initialize the log4j system properly.
    log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
    java.lang.NullPointerException
    at br.locadora.TesteParentes.main(TesteParentes.java:11)

  • sushil

    really helped… thanks

  • Marlin Glasgow

    Wonderful! So simple. That helped me a lot. I would like to share with you a great service to fill a form online. I just filled out a form with an online software. It looked much better typed than hand-written. I used “http://goo.gl/XR7TuI” and it’s very easy to use.