Contents
1 Scope
This document specifies,
1. How the PaginationHelper works to fetch
paginated result set
2. How it can be used in an application to avail
pagination functionality.
2 Why Pagination
In a Web Application, if
the search returns a huge result set (thousands..), then it can cause:
UI/Client:
1.
Need to display all the results at once.
a.
It is difficult for the user to go through all the records by
scrolling through the results.
2. It will take more time to get
all the results rendered.
Server/DB:
1.
DB Query will take a lot of time to fetch all the results at
once – Affects the performance
2. Sometimes Java will throw Memory
Exception, if the number of records to handle is very huge.
This is not acceptable in
today’s high responsive web applications, where performance is the critical aspect.
To resolve this we can
have pagination implementation for the search pages/huge result sets.
3 Pagination Helper Functionality
Pagination Helper is implemented using formal
type parameter using <E>. This type will be resolved during runtime based
on the actual implementation of the type.
It accepts the following arguments to fetch paginated
result set.
Ø Data fetch query & its arguments array
Ø Count fetch query & its arguments array
Ø PageFilter
Ø RowMapper instance for mapping the page result
This helper class fetches the data for
pagination using properties of PageFilter.
Ø PageIndex
Ø PageSize
Ø Order By Column
Ø Order By
4 Sorting the Result
If the PageFilter’s orderByColumn and orderBy
properties are populated with some values, the helper class adds a wrapper
query as below for fetching the results in a sorted manner.
sqlFetch
="SELECT * FROM (" +
sqlFetchRows + ") ORDER BY "
+
orderByCol + " " + orderBy
5 FetchingPage Result
Based on PageFilter’s pageSize & pageIndex
properties, start row & end row values are calculated by PaginationHelper
and use it for fetching specific set of records.
The helper class adds a wrapper query as below for
fetching specific set of result.
fetchQuery = "SELECT
* FROM (SELECT ROW_NUMBER() OVER() RNUM, T.*
FROM
("+ sqlFetch + ")
T)
WHERE
RNUM <=? AND RNUM >=?"
6 Database Access
The helper class extends BaseDAO for availing access on
datasource and jdbctemplate objects. The Pagination Helper uses Spring DAO’s
JdbcTemplate to execute the query and map result to a given row mapper.
The fetched data is stored on PageDTO and returned to
the caller.
7 Implementation
7.1 Client
Below is the sample code that uses
Pagination Helper for fetching paginated result set.
|
// Set
the Page Filter Details using the values from the HTTPRequest
PageDTO<PendingComponentDTO>
pageFilter = new PageFilterDTO();
pageFilter. setPageIndex
(pageIndex);
pageFilter. setPageSize(pageSize)
//
Pagination Helper Instance
IPaginationHelperDAO<UserDTO> iPaginationHelperDAO = null;
iPaginationHelperDAO
= new PaginationHelperDAO<UserDTO>();
//
Pagination Arguments
Object[]
countArgs = new Object[] { roleId };
Object[]
queryArgs = new Object[] { roleId };
//
Pagination queries
String
sqlFetchUserDetails = "SELECT U.USER_NAME,
R.ROLE_NAME, U.ADDRESS FROM USER U, ROLE R, USER_ROLE_MAP URM WHERE URM.USER_ID=URM.USER_ID
AND URM.ROLE_ID = R.ROLE_ID AND R.ROLE_ID=?";
String
sqlCountUserRows = "SELECT COUNT(*)
FROM("+ sqlFetchUserDetails +);"
//Paginated
Result
PageDTO<PendingComponentDTO>
pageDTO = null;
//
Invoke the pagination helper for the paginated result
pageDTO
= iPaginationHelperDAO.fetchPage(sqlCountUserRows,
countArgs, sqlFetchUserDetails, queryArgs, pageFilter, new UserRowMapper());
|
7.2 UserRowMapper
|
/**
* The Class UserRowMapper
maps the result set of the query used to
* fetch user
details
*/
public class UserRowMapper implements
ParameterizedRowMapper<UserDTO>
{
// fetching and mapping User Details from USER and ROLE
table to
// UserDTO
public UserDTO
mapRow(ResultSet rs, int row)
throws
SQLException {
UserDTO
userDTO = new
UserDTO();
user.setUserName(rs.getString("USER_NAME"));
user.setAddress(rs.gtString("ADDRESS"));
user.setRoleName(rs.getString("ROLE_NAME"));
return userDTO;
}
}
|
7.3 PaginationHelperDAO
|
public class PaginationHelperDAO<E>
extends BaseDAO implements
IPaginationHelperDAO<E>
{
public
PageDTO<E> fetchPage(final String sqlCountRows,
final
Object countArgs[], final String sqlFetchRows,
final
Object queryArgs[], final PageFilterDTO pageFilter,
final ParameterizedRowMapper<E>
rowMapper) {
// create the page object.
final
PageDTO<E> pageDTO = new PageDTO<E>();
final
List<E> pageItems = new ArrayList<E>();
int
startRow = PaginationUtil.getStartRow(pageFilter.getPageIndex(), pageFilter
.getPageSize());
int
endRow = PaginationUtil.getEndRow(pageFilter.getPageIndex(), pageFilter
.getPageSize());
String
orderByCol = pageFilter.getOrderByColumn();
String
orderBy = pageFilter.getOrderBy();
String
sqlFetch = sqlFetchRows;
// Append Order BY clause if required
if
(!orderByCol.isEmpty() && !orderBy.isEmpty()) {
sqlFetch
= "SELECT * FROM (" + sqlFetchRows + ") ORDER BY "
+
orderByCol + " " + orderBy;
}
int
length = 0;
if (null !=
queryArgs) {
length
= queryArgs.length;
}
// Append Start Row & End Row Arguments to fetch
the results
Object
fetchQueryArgs[] = Arrays.copyOf(queryArgs, length + 2);
fetchQueryArgs[length]
= endRow;
fetchQueryArgs[length
+ 1] = startRow;
String fetchQuery = "SELECT * FROM (SELECT ROW_NUMBER() OVER()
RNUM, T.* FROM (" +
sqlFetch + ") T) WHERE RNUM <=? AND RNUM
>=?";
// Execute the pagination query and get the page
items
getJdbcTemplate().query(fetchQuery,
fetchQueryArgs,
new ResultSetExtractor()
{
public
Object extractData(ResultSet rs)
throws
SQLException, DataAccessException {
int
currentRow = 0;
while
(rs.next()) {
pageItems.add(rowMapper.mapRow(rs,
currentRow));
currentRow++;
}
// set the page items in the page object
pageDTO.setPageItems(pageItems);
return
pageDTO;
}
});
int
totalRows = pageItems.size();
// determine how many more rows are available
if (null !=
sqlCountRows) {
int
rowsRequested = (endRow - startRow) + 1;
// We need not query for the count if,
// the resulted no. of records is less than the
requested.
if
((totalRows == rowsRequested)
||
((totalRows < rowsRequested) && startRow != 1)) {
totalRows
= getJdbcTemplate().queryForInt(sqlCountRows,
countArgs);
}
}
// Set page counters
pageDTO.setTotalRows(totalRows);
pageDTO.setPageIndex(pageFilter.getPageIndex());
pageDTO.setPageCount(PaginationUtil.getPageCount(totalRows,
pageFilter.getPageSize()));
return
pageDTO;
}
}
|
7.4 PaginationUtil
|
public class PaginationUtil
{
public static int
getPageCount(int recordsCount, int pageSize) {
int pageCount
= (recordsCount / pageSize)
+
(recordsCount % pageSize == 0 ? 0 : 1);
return
pageCount;
}
public static int
getStartRow(int pageIndex, int pageSize) {
return (pageIndex
- 1) * pageSize + 1;
}
public static int
getEndRow(int pageIndex, int pageSize) {
return
pageIndex * pageSize;
}
}
|
7.5 PageFilterDTO
|
public class
PageFilterDTO implements Serializable {
/**
* The Serial Version Id
*/
private static final long serialVersionUID
= -7709063021259627638L;
/** The Page Index */
private int pageIndex;
/** The Page Size */
private int pageSize;
/** The Order By Column */
private
String orderByColumn = "";
/** The Order (asc/desc) */
private
String orderBy
= "";
//Have the getter/setter
methods for the properties
|
7.6 PageDTO
|
public class
PageDTO<E> implements Serializable {
/**
* The Serial Version Id
*/
private static final long serialVersionUID
= -5027820698650544274L;
// The Page Index to store the current page number
private
Integer pageIndex;
// The page count for the grid
private
Integer pageCount;
// Total rows from the query
private
Integer totalRows;
// List of page items
private
List<E> pageItems = new ArrayList<E>();
//Have
the getter/setter methods for the properties
|
