Paginating Queries in Oracle SQL

Here’s what I’ve used a couple of times to paginate my queries. It adds a limit and offset to your query results so you don’t have to do it Java-side:

SELECT * FROM (SELECT ROWNUM rnum, paginateInner.* FROM (%s) paginateInner WHERE ROWNUM <= ? ) WHERE rnum >= ?

This is a Java string. I inject the actual query in it like this:

final String sql = String.format(PAGINATION_WRAPPER, actual_query_sql);

Then add the final two parameters to your prepared statement:

try (final PreparedStatement statement = connection.getPreparedStatement(sql)) {
	int counter = 1;

	// ...set all the other parameters...

	statement.setInt(count++, limit);  //limits go first
	statement.setInt(count++, offset);
}

If your limit isn’t known in advance, you can use just omit the inner query:

SELECT * FROM (%s) WHERE rnum >= ?

And that’s it.