Oracles ROW_NUMBER () OVER (ODER BY ...) in HSQLDB
If you are using Oracle 12 you can use the ANSI SQL fetch first x rows only which works with HSQLDB as well:
SELECT acme_dept.LOG_TABLE.LOG_TIME, acme_dept.LOG_TABLE.LOG_LEVEL, acme_dept.LOG_TABLE.MESSAGE, acme_dept.LOG_TABLE.CATEGORY,FROM acme_dept.LOG_TABLEORDER BY acme_dept.LOG_TABLE.LOG_TIME ASCOFFSET #{first_row}FETCH FIRST #{num_rows} ROWS ONLY There is a difference to your existing code: you need to specify the number of rows to be fetch per page, not the absolute row number for the last row (that's why I changed #{last_row} to #{num_rows} in my example)
But in general I think it's a bad idea to use a different DBMS for testing then in production.There are too many subtle differences that make the tests futile in my opinion