Why JPA hints on NamedQuery won't work?

Well, we faced this problem recently. We were using JPA 2.0 with Oracle 11g. 

We are working on huge data, millions of data being processed at a very faster rate. With lot of data in question and RDBMS like oracle, sometimes queries take lot of time to execute.

We picked query timeout feature of JPA for solving this problem. 

1. Applied  @QueryHint(name = QueryHints.TIMEOUT_HIBERNATE, value = 10) }, on @NamedQuery

waited for query to get timed out endlessly!

2. Tried something with standalone plain old jdbc class with same driver. This seemed to work perfectly!

      Statement st = conn.createStatement();

       String sql = "select....from ... ";  

        st.setQueryTimeout(2);

Then it all boiled down to JPA 2.0 which was certainly not working as expected!

After drilling down through JPA and Spring API, we found pretty interesting thing.

1. @NamedQuery are compiled and loaded at Spring application startup only. Therefore any hints applied will be applied at the time of app startup. 

     private void initQuery(Query query, NamedQueryDefinition nqd) {

query.setCacheable( nqd.isCacheable() );

query.setCacheRegion( nqd.getCacheRegion() );

if ( nqd.getTimeout()!=null ) query.setTimeout( nqd.getTimeout().intValue() );

if ( nqd.getFetchSize()!=null ) query.setFetchSize( nqd.getFetchSize().intValue() );

if ( nqd.getCacheMode() != null ) query.setCacheMode( nqd.getCacheMode() );

query.setReadOnly( nqd.isReadOnly() );

if ( nqd.getComment() != null ) query.setComment( nqd.getComment() );

}

2. While if you call any query call inside @transactional function with certain timeout, then this parameter will be overridden again by Transaction timeout! 

    public static void applyTransactionTimeout(Criteria criteria, SessionFactory sessionFactory) {

Assert.notNull(criteria, "No Criteria object specified");

SessionHolder sessionHolder =

(SessionHolder) TransactionSynchronizationManager.getResource(sessionFactory);

if (sessionHolder != null && sessionHolder.hasTimeout()) {

criteria.setTimeout(sessionHolder.getTimeToLiveInSeconds());

}

}

So whatever be your Query hint value, it will be overridden by transaction timeout!

Now lets come to solution.

Instead of using JPARepository just create a DAO function, and fetch namedQuery and then apply timeout.

           Query query = entityManager.createNamedQuery("abc");

            query.setHint("org.hibernate.timeout", "5");

            query.getSingleResult();

This way if this function is called inside @transactional function, firstly transaction timeout will be applied and then query one will override timeout.

Cheers!