Step1. SQL Conversion

The original SQL (which does not consider pagination) should be converted into a pagination featured SQL so that it only retrieves one particular page of data from database. Different databases have different syntax to achieve this goal. For example, if you want to search products from database and the original SQL is:

select productId, prodNo, price, madeIn, description from product p where madeIn = ? and description like ?

In oracle the converted pagination SQL would be:

select * from (select a.*,rownum myrownum from ( select productId,prodNo,price,madeIn,description from product p where madeIn = ? and description like ? ) a where rownum <= :endRecord ) b where b.myrownum > :startRecord

However, in MySQL database, it would be:

select productId, prodNo, price, madeIn, description from product p where madeIn = ? and description like ? limit :startRecord, :pageSize

As you can see, each database has different way to support pagination feature for query (some database does not support at all), the developer needs to be aware of this.

Step2. Count total records

After executing the SQL generated in step1, the number of total records for the current search should be counted. Based on above original SQL, the count SQL would be:

select count(*) from product p where madeIn = ? and description like ?

Step3. Save result data and pagination meta data

The result data after running the SQL against database in step1 is stored in request so that it can be displayed in the page. The pagination meta data is stored in session and it generally includes the following information:
(a) Total records of the search
(b) Current page number
(c) SQL context, including SQL statement as well as binding parameters. It is saved in session so that the subsequent request for other pages can restore it to execute the same SQL statement but with different page number.
(d) Sort information, including the column by which the data is sorted, ascending or descending.

Step4. Display in page

Display the result data in a table using jsp code, jstl or other tags.
Display page navigation links (such as "Previous", "Next" page) to allow user to access one particular page of data.
Display other helpful information to allow user to easily identify the location (such as current page number, total pages, records scope of current page).
Apply a clickable link on column title so that user can sort data result by that column.

As you can see, it's not a simple and straight forward job to implement a single pagination search function. So much tedious work has to be done behind the screen. As a matter of fact, majority of the code are boilerplate code, such as translation of pagination SQL, getting data source connection, counting total records, saving search result, generating and storing pagination meta data and displaying page links in pages.

HDPagination framework is designed to handle this boilerplate, allowing you to focus on writing queries and binding parameters with received input values based on the business logic.