Pagination in Java JDBC

Question

I have a table with millions of records in it. In order to make the system faster, I need to implement the pagination concept in my Java code. I need to fetch just 1000 records at a time and process them, then pick another 1000 records and do my processing and so on. I have already tried a few things and none of them is working. Some of the things I tried are listed below -

1) String query = "select * from TABLENAME" +  " WHERE ROWNUM BETWEEN %d AND       %d";
sql = String.format(query, firstrow, firstrow + rowcount);

In the above example, when the query is SELECT * from TABLENAME Where ROWNUM BETWEEN 0 and 10 it gives me a result but when the query is SELECT * from TABLENAME Where ROWNUM BETWEEN 10 and 20, it returns an empty result set. I even tried to run it in the DB, it return Empty result set (not sure why!!)

2) preparedStatement.setFetchSize(100); I have that in my Java code, but it still fetches all the records from the table. Adding this statement didnt affect my code in anyway.

Please help!

Answer 1

It sounds like you are not actually needed to paginate the results but to just process the results in batches. If this is the case then all you need to do is set the fetch size to 1000 using setFetchSize and iterate over the resultset as usual (using resultset.next()) and process the results as you iterate. There are many resources describing setFetchSize and what it does. Do some research:

• What does Statement.setFetchSize(nSize) method really do in SQL Server JDBC driver?
• How JDBC Statement.SetFetchsize exaclty works
• What and when should I specify setFetchSize()?

For oracle pagination there are a lot of resources describing how to do this. Just do a web search. Here are a couple of resources that describe how to do it:

• http://www.databasestar.com/limit-the-number-of-rows-in-oracle/
• http://ocptechnology.com/how-to-use-row-limiting-clause/

Pagination is not very useful if you do not define a consistent ordering (ORDER BY clause) since you cannot rely on the order they are returned.
This answer explains why your BETWEEN statement is not working: https://stackoverflow.com/a/10318244/908961

From the answer if using oracle older than 12c you need to do a sub select to get your results. Something like:

SELECT c.*
FROM (SELECT c.*, ROWNUM as rnum
      FROM (SELECT * FROM TABLENAME ORDER BY id) c) c
WHERE c.rnum BETWEEN %d AND %d

If you are using Oracle 12c or greater I would recommend using the newer OFFSET FETCH syntax instead of fiddling with rownum. See the first link above or http://www.toadworld.com/platforms/oracle/b/weblog/archive/2016/01/23/oracle-12c-enhanced-syntax-for-row-limiting-a-k-a-top-n-queries

So your query would be something like

String query = "select * from TABLENAME OFFSET %d ROWS FETCH NEXT 1000 ONLY";
String.format(query, firstrow);

or using prepared statements

PreparedStatement statement = con.prepareStatement("select * from TABLENAME OFFSET ? ROWS FETCH NEXT 1000 ONLY");
statement.setInt(1, firstrow);
ResultSet rs = statement.executeQuery();

Alternately you can also use the limit keyword as described here http://docs.oracle.com/javadb/10.10.1.2/ref/rrefjdbclimit.html and your query would be something like

String query = "select * from TABLENAME { LIMIT 1000 OFFSET %d }";
String.format(query, firstrow);

Answer 2

The normal way to implement pagination in Oracle is to use an analytic windowing function, e.g. row_number together with an ORDER BY clause that defines the row ordering. The query with the analytic function is then wrapped into an inline view (or a "window"), from which you can query the row numbers you need. Here's an example that queries the first 1000 rows from my_table (ordering by column_to_sort_by):

select rs.* from
  (select t.*,
    row_number() over (order by column_to_sort_by) as row_num
  from my_table t
  ) rs
where rs.row_num >= 1 and rs.row_num  < 1001
order by rs.row_num

A JDBC implementation could then look like the following:

public void queryWithPagination() throws SQLException {
    String query = "select rs.* from"
        + " (select t.*,"
        + " row_number() over (order by column_to_sort_by) as row_num"
        + " from my_table t"
        + " ) rs"
        + " where rs.row_num >= ? and rs.row_num  < ?"
        + " order by rs.row_num";

    final int pageSize = 1000;
    int rowIndex = 1;

    try (PreparedStatement ps = myConnection.prepareStatement(query)) {

      do {
         ps.setInt(1, rowIndex);
         ps.setInt(2, rowIndex + pageSize);
         rowIndex += pageSize;
      } while (handleResultSet(ps, pageSize));
    }
}

private boolean handleResultSet(PreparedStatement ps, final int pageSize)
        throws SQLException {
    int rows = 0;
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            /*
             * handle rows here
             */
            rows++;
        }
    }
    return rows == pageSize;
}

Note that the table should remain unchanged while you're reading it so that the pagination works correctly across different query executions.

If there are so many rows in the table that you're running out of memory, you probably need to purge/serialize your list after some pages have been read.

EDIT:

If the ordering of rows doesn't matter to you at all, then -- as @bdrx mentions in his answer -- you don't probably need pagination, and the fastest solution would be to query the table without a WHERE condition in the SELECT. As suggested, you can adjust the fetch size of the statement to a larger value to improve throughput.