Web pagination with JdbcTemplate and Oracle 11g / H2 without scrollable resultset

This must have been done a thousand times but I can't find the Java code online.

I'm coding a web page that shows a list from Oracle 11g, retrieved by a Dao using JdbcTemplate.

It's essentially an audit log, with a search field for date to show log entries from that date and previous.

My audit log service class will call the Dao and limit the number of rows returned to 100, and has to work out the latest date to specify on the query. Then it will pass back a list of audit log javabeans to the MVC layer.

This is my best effort for the SQL, which has to be H2 (Oracle mode) compatible as well for testing purposes. (That rules out the row_number() function). latestDate and maxRows are the params.

    SELECT * FROM (
                SELECT * FROM T_ETL_AUDIT_LOG 
                WHERE LOG_TIMESTAMP  < ?latestDate 
            ) WHERE rownum < ?maxRows;

So I need to work out what the parameters on the web page are going to be (e.g. lastDate, previous, next), and the audit log service algorithm has to handle the first call (with date=Now), and previous page, next page without getting confused.

I haven't searched for a pagination tag for the JSP yet so any suggestions would be cool but that's icing on the cake.

NB I am not using Oracle 12 so I can't utilise the new Oracle pagination functionality

2015-01-20 - just changed the title to exclude scrollable resultset solutions.

Solution

I'm using page numbers on the client-side, so the 'next' and 'previous' buttons transmit the next or previous page number. My service translates the page number into the previousLastRow and the lastRow parameters for the DAO. The original search date remains the same on every page until the user changes it.

This is the SQL for Oracle, with the parameters substituted in for page 2 (i.e. rows 101 to 200):

select * from (
    select ROWNUM as row_num, b.* from (
        select ROWNUM, MY_TIMESTAMP 
        from T_MY_TABLE 
        where MY_TIMESTAMP < TIMESTAMP'2015-01-20 12:12:34'
        and ROWNUM <= 200
        order by MY_TIMESTAMP desc
    ) b 
) where row_num > 100;