Showing posts from September, 2010

Intercepting and adjusting SQL generated by Eclipselink JPA

In some cases it might be useful to intercept and adjust the SQL generated by EclipseLink JPA. E.g. if you want to force an index in mysql you need to append force index (myindex) to the table name.

If you create a query on the entitymanager:

Query q = em.createQuery("select e from MyEntity");

you can cast it to the EclipseLink JpaQuery:

JpaQuery jq = (JpaQuery)q;

and the JpaQuery gives you access to the EclipseLink DataBaseQuery where you can prepare it before executing it:

DatabaseQuery dbQuery = q.getDatabaseQuery();
dbQuery.prepareCall(((org.eclipse.persistence.jpa.JpaEntityManager)em).getActiveSession(),new DatabaseRecord());

You can now get the sql string and add the forced index:

dbQuery.setSQLString(dbQuery.getSQLString()+" force index (myindex)");

and finally you can get the resultset using q.getResultList().

If you have parameters in the sql it's a bit more to it. Currently I've only found one option - probably not optimal since the query is translated to no…