Consider a scenario, where you want to display a set of records from database in the User interface page. If the number of records is more, we would be using pagination to display the records. In this case we need to reform the SQL query to retrieve results for a particular page number with the limited records per page. For example, consider the following SQL query.
Assume that the above query returns 1000 records and that you need to display the resulting records in an UI page with page limit of 20 records per page. This will lead to 50 pages each containing 20 records. We need to change the query to retrieve records for each page with different ranges. For example, to retrieve records for the page number 25, the query to be used is as follows
Consider the following class, PaginationQueryUtil. It consist of method such as getPagedQueryString to generate such queries, when the page number is passed. The class's constructor takes number of records per page and query as parameters. The getRecordCountQueryString method is used to generate queries to find the number of records in as a result of a query. This can be helpful to compute the number of pages required for pagination.
PaginationQueryUtil.java
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE IS DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*/
/**
* This is a simple utility class to generate SQL query with Pagination support
*/
public class PaginationQueryUtil
{
private String query;
private int numberOfRecordsPerPage;
public String getQuery()
{
return query;
}
public void setQuery(String query)
{
this.query = query;
}
public int getNumberOfRecordsPerPage()
{
return numberOfRecordsPerPage;
}
public void setNumberOfRecordsPerPage(int numberOfRecordsPerPage)
{
this.numberOfRecordsPerPage = numberOfRecordsPerPage;
}
public PaginationQueryUtil(String query, int numberOfRecords)
{
this.query = query;
this.numberOfRecordsPerPage = numberOfRecords;
}
/**
* This method returns a paged query string for the given page number.
*
* @param pageNum
* @return
*/
public String getPagedQueryString(int pageNum)
{
StringBuffer sbBuffer = new StringBuffer(200);
int iEndPage = pageNum * numberOfRecordsPerPage;
int iStartPage = iEndPage - numberOfRecordsPerPage + 1;
String[] splitQry = getQuery().toUpperCase().split("ORDER BY");
if (splitQry.length > 1)
{
sbBuffer.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (");
sbBuffer.append(splitQry[0]).append(" ) Q1 ");
}
else
{
sbBuffer.append("SELECT * FROM ( SELECT Q1.*, rownum AS RN FROM (");
sbBuffer.append(getQuery()).append(" ) Q1 ");
}
sbBuffer.append(" )");
sbBuffer.append(" WHERE RN BETWEEN ");
sbBuffer.append(iStartPage).append(" AND ");
sbBuffer.append(iEndPage);
if (splitQry.length > 1)
{
sbBuffer.append(" ORDER BY " + splitQry[1]);
}
return sbBuffer.toString();
}
/**
* This method returns a query string to find the record count for the given query. Using the record
* count, the number pages can be found.
*
* @return Max Row number
*/
public String getRecordCountQueryString()
{
StringBuffer sbBuffer = new StringBuffer(200);
sbBuffer.append("SELECT NVL(MAX(ROWNUM),0) RECORD_COUNT FROM (");
sbBuffer.append(getQuery()).append(")");
return sbBuffer.toString();
}
public static void main(String[] args)
{
String sql = "SELECT * FROM EMPLOYEES order by DEPT_ID,DOJ ";
PaginationQueryUtil pqU = new PaginationQueryUtil(sql, 20);
System.out.println("Paged Query String -> " + pqU.getPagedQueryString(25));
System.out.println("Query to Find Record Count -> " + pqU.getRecordCountQueryString());
}
}
Sample Output:
Query to Find Record Count -> SELECT NVL(MAX(ROWNUM),0) RECORD_COUNT FROM (SELECT * FROM EMPLOYEES order by DEPT_ID,DOJ )