Oracle Hints in HQL function, oracle

amy

Is there any possibility to write oracle Hints in HQL

As per this Hibernate enhancement request the use of Oracle SQL hints is not directly supported:

proj...rowse/HHH-2736

I am using a work arround by sub-classing org.hibernate.EmptyInterceptor and over-riding the function quot;String onPrepareStatement(final String sql)quot;.  Inside the over-ridden method I am using regular expressions to insert hints into the SQL generated from the HQL (in my case JPQL) by Hibernate.

You can deploy the custom intercepter by setting the name of the sub-class as the value of the Hibernate property:

hibernate.ejb.interceptor.session_scoped

See the following Hibernate doco:

hibernate/stab...ml/events.html

Note that hints rely on table aliases to work correctly, as these alias are generated by Hibernate, changing the query structure can result in a different alias!

Chris

Thanks for EmptyInterceptor suggestion.

As a variation you can use ThreadLocal to store hint (in DAO) and access it in Interceptor. Sometimes hint should be used only for some parameter values (e.g. date range) - this information is not available in interceptor - only the query is. Also, regular expressions are not necessary - use the hint if found in ThreadLocal, otherwise leave original sql intact. Thread pools or tasks could be an issue in this case, but I still haven't seen this problem.