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:
In Java, given any sql statement as string named 'sql', we can execute a statement as:
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.
Driver Hints given to the Statement object:
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: