This content has been marked as final. Show 2 replies
Unfortunately, prepared statements do not seem to allow binding parameters in the hint string. The hint string used doesn't have a value intead of a FIRST_ROWS(n) type hint because the statements need to add the parameter to the SQL string, preventing statement caching and reuse. If you want to use a value, it will mean that it needs to be a hard coded constant, or that you will have a much harder time reusing prepared statements. Also hints added using the setHints() api will go within the inner select, and so might not help the performance of the pagination query.
EclipseLink's pagination use can be turned off/on using the DatabasePlatform's setShouldUseRownumFiltering(boolean) method. This might not be helpful though, as it means that EclipseLink will then not use pagination at all, and instead use JDBC to set the max rows, and the resultste to skip to the first result.
The actual string used and code for the feature exists in the OraclePlatform class, within the printSQLSelectStatement method. The best option might be to extend the OraclePlatform and override this method on a custom platform, so that you can print your own values and hints in the pagination query.
The other option is to not use setFirstResult and setMaxResults, and instead intercept the EclipseLink generated SQL and wrap it in your own pagination SQL (much like the printSQLSelectStatement method does). Ie
String sqlString = query.getSQLString();
query.setString( "SELECT * FROM (SELECT /*+ FIRST_ROWS(n) / a.*, ROWNUM rnum FROM ("+sqlString + ") a WHERE ROWNUM <="+maxvalue+" ) WHERE rnum > "+firstValue);
Old thread, but very helpful. Thanks.