How to get hibernate to print out whats wrong with a named query?

Question

In my Spring / Hibernate / JPA application I use a lot of named queries and when I have a typo in one of those queries the see errors in my application start-up log file similar to the one below.

Caused by: org.hibernate.HibernateException: Errors in named queries: FindAllCompanyFileTypes
    at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:426)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1872)
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:906)
    ... 70 more

How do configure hibernate to print out what is wrong with the named query and not just the named query has an error?

UPDATE for example the JPA query SELECT f FROM Foo WHERE f.v := true will fail with hibernate complaining that the query is invalid. Hibernate has not even tried to generate SQL from it, the query is not correct JPQL. What I want to know is how to get hibernate to say the query is wrong because := was used instead of = ? not sure if this is a setting that can be turned on in hibernate or not.

Answer 1

Hibernate custom query loader stuff is located into org.hibernate.loader.custom.sql (for Hibernate 3 and seems that Hibernate 4 too). If using log4j, it's only a matter of setting this package its own category in order to get the logs printed (I recommend you to use the file appender because the afterwards error logs can overlap what you're interested in if using console appender).

<category name="org.hibernate.loader.custom.sql" additivity="false">
    <priority value="trace" />
    <appender-ref ref="fileAppender" />
</category>

Supposing your root logger displays every error in file, that's the output you get for an error while query loading:

17:27:18,348 TRACE SQLCustomQuery:85 -     starting processing of sql query [SELECT equipment.*, det.*
        FROM tdetectable_equipment equipment JOIN
        tdetectable det
        ON det.id = equipment.id_detectable
        WHERE
        equipment.id_detectable=det.id and det.active=1 and
        equipment.id_warehouse_container = :_Id]
17:27:18,358 TRACE SQLCustomQuery:85 -     starting processing of sql query [select line.*  from tpacking_slip_line line  join tpacking_slip slip  on line.id_packing_slip = slip.id where line.id_detectable = :detectableId and line.id_related_packing_slip_line is null and slip.`type`= :slipType order by slip.date desc;]
17:27:18,359 TRACE SQLQueryReturnProcessor:387 -     mapping alias [line] to entity-suffix [0_]
17:27:18,364 TRACE SQLCustomQuery:85 -     starting processing of sql query [select res.* from tdetectable det  join tpacking_slip_line line on det.id=line.id_detectable  and line.id_related_packing_slip_line is null join tpacking_slip slip on line.id_packing_slip = slip.id  and slip.`type`='OUT' join vreservation res on slip.id_reservation=res.id where det.id in ( :detIds ) group by(res.id) order by count(res.id) desc;]
17:27:18,365 TRACE SQLQueryReturnProcessor:387 -     mapping alias [res] to entity-suffix [0_]
17:27:18,368 ERROR SessionFactoryImpl:424 -     Error in named query: equipmentWarehouseQuery
org.hibernate.MappingException: Unknown collection role: com.mycompany.model.container.Container.detectables
    at org.hibernate.impl.SessionFactoryImpl.getCollectionPersister(SessionFactoryImpl.java:701)
    at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.addCollection(SQLQueryReturnProcessor.java:393)
    at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.processCollectionReturn(SQLQueryReturnProcessor.java:428)
    at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.processReturn(SQLQueryReturnProcessor.java:358)
    at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.process(SQLQueryReturnProcessor.java:171)
    at org.hibernate.loader.custom.sql.SQLCustomQuery.<init>(SQLCustomQuery.java:87)
    at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:67)
    at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:166)
    at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:589)
    at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:413)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1872)
    at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:863)
    at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:782)
    at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:188)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1541)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1479)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:521)

That exception is being raised at loading time, but you can also have query errors while executing your code. In that case an HibernateException or similar is thrown, which you can inspect later. For the case of missing colon it is actually an IllegalArgumentException thrown by AbstractQueryImpl class:

java.lang.IllegalArgumentException: No positional parameters in query: SELECT equipment.*, det.*
        FROM tdetectable_equipment equipment JOIN
        tdetectable det
        ON det.id = equipment.id_detectable
        WHERE
        equipment.id_detectable=det.id and det.active=1 and
        equipment.id_container = _Id