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 non-parameterized query:
dbQuery.getTranslatedSQLString(session, dbrecord);
This will give you an sql with all the parameter values inside - so you can easily modify it - but precautions should be considered regarding SQL injection.
Using this approach Eclipselink seem to not have added the LIMIT clause in the prepared statement. The LIMIT clause is added for mysql if you use setFirstResult/setMaxRows. In this case you'll have to append the LIMIT clause manually - and make sure that EclipseLink ignores firstResult/maxRows.
dbQuery.setSQLString(dbQuery.getSQLString()+" force index (myindex) LIMIT "+firstResult+","+maxRows);
dbQuery.getCall().setIgnoreFirstRowSetting(true);
dbQuery.getCall().setIgnoreMaxResultsSetting(true);
Ideally the query should have been modified using the parameterized version, so that also EclipseLink could add the LIMIT clause and bind the parameters - but this is how far I got in this round. If someone knows how to do this better - please leave a comment.
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 non-parameterized query:
dbQuery.getTranslatedSQLString(session, dbrecord);
This will give you an sql with all the parameter values inside - so you can easily modify it - but precautions should be considered regarding SQL injection.
Using this approach Eclipselink seem to not have added the LIMIT clause in the prepared statement. The LIMIT clause is added for mysql if you use setFirstResult/setMaxRows. In this case you'll have to append the LIMIT clause manually - and make sure that EclipseLink ignores firstResult/maxRows.
dbQuery.setSQLString(dbQuery.getSQLString()+" force index (myindex) LIMIT "+firstResult+","+maxRows);
dbQuery.getCall().setIgnoreFirstRowSetting(true);
dbQuery.getCall().setIgnoreMaxResultsSetting(true);
Ideally the query should have been modified using the parameterized version, so that also EclipseLink could add the LIMIT clause and bind the parameters - but this is how far I got in this round. If someone knows how to do this better - please leave a comment.
Comments