JDBC Paging

This tutorial will show you how to boost performance by selecting results from the datasource, page by page.

Implementation on MySQL:

The SQL required to fetch only a specific portion of the results is: "Select Statement" LIMIT [PageNumber ,] PageSize
PageNumber - is optional and starts with Zero (0). For example, we want to fetch Page number 3 from "some table" where page size is 25 rows:

SELECT * FROM SOME_TABLE LIMIT 2, 25;


-- Or, Just select the first page (same as select the first rows)

SELECT * FROM SOME TABLE LIMIT 25;

In Java, given any sql statement as string named 'sql', we can execute a statement as:

st.executeQuery(sql + " Limit " + (pageNum-1) * pageSize + ", " + pageSize);

For even better performance I recommend setting the following "hints" to the JDBC Driver:
First, when using paging you should create the JDBC statement as "Scroll Sensitive" this means that the driver will not "miss" any new records that have been changed (i.e. deleted, added, updated) since the fetch of the previous page.

Statement st = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

Driver Hints given to the Statement object:

st.setFetchSize(int rows);
Gives the JDBC driver a hint as to the number of rows that should be fetched from the database. Here, for the best performance, especially when dealing with heavy duty applications you should use the same number you want for the page size
st.setMaxRows(int maxRows);
Sets the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number. If the limit is exceeded, the excess rows are silently dropped. Again, here, put the same number as your page size. There is also
st.setFetchDirection(int direction)
, BUT, I recommend you should leave this to its default (ResultSet.FETCH_FORWARD) and simply work with "Order By ASC/DESC" at the sql level instead.

Implementation on Oracle:

Oracle has different approach and uses the ROWNUM pseudo column. The ROWNUM is a column generated and added automatically for any query.
It is important to note that this column is generated AFTER the result is given.
This means that: "SELECT * FROM SOME_TABLE WHERE ROWNUM > 5" Will produce NOTHING ! because when processing the first row the condition is false as we do not have more than five rows in our results and so we never pass the first row and so the result is nothing.
However, "SELECT * FROM SOME_TABLE WHERE ROWNUM < 5" will show the first four records as processing the first row we get a true value for the condition and so on until the forth.
Therefore, selecting a range of rows requires a subselect first to produce some results, and then referring to that subselect with ROWNUM conditions.
Example including Order By clause:

SELECT * FROM SOME_TABLE ORDER BY SOME_COL DESC
This will first get the full results we want. now we will envelope this statement with ROWNUM conditions on top of this query:
SELECT ROWNUM ROW_NUM, SUBQ.*
FROM
     (SELECT * FROM SOME_TABLE ORDER BY ID DESC) SUBQ
WHERE ROW_NUM <= 75
Enveloping this result again, will generate all the relevant ROW_NUMs and we will be able to refer to the numbers above some value:
SELECT * FROM (
  SELECT ROWNUM ROW_NUM, SUBQ.*
  FROM
    (SELECT * FROM SOME_TABLE ORDER BY ID DESC) SUBQ
     WHERE ROW_NUM <= 75)
WHERE ROW_NUM > 50;


Home