Previous
Working with Very Large Collections of Data
Another approach to paging is the query-based paging. In its strictest definition, query-based means retrieving only the data that is to be displayed in a page. No caching is involved. However, this would mean more hits to the database, which translates into slower user response. In addition, this puts extra burdens on the database server. A better approach is to combine cache-based and the query-based solution. You define the number of records to be retrieved with each hit to the database and cache the collection. If the user wants to view more, you make another hit to the database to retrieve the second batch of data. Now, which collection needs to be cached? In the simplest solution, you replace the first collection in the cache with the second collection, hoping the user is not interested in viewing the previous pages that display data from the first collection. Alternatively, you can cache a limited number of collections. This chapter deals with having one collection in the cache at a time.
The app18b application, which is a modification of the app18a application, provides an example of working with very large collections of data. Any search operation always attempts to retrieve and cache a maximum of n records, in which n records are enough to fill at least several presentation pages. If the user browses further than the available cached data, another hit to the database is performed and a second batch of data is cached. For every search, the corresponding Struts action first tries to search its cache. If the data is not found, it will retrieve fresh data from the database.
There are four things that we look at closely in the app18b application, the searchCustomers method in the CustomerDAO class, the customer search criteria action form, the action class, and the JSP that displays the search results.
The Search Method
The searchCustomers method in the CustomerDAO implementation class must also know how many records to return at most and the offset starting record. In app18b, the searchCustomer method has the following signature.
public ArrayList searchCustomer(CustomerSearchCriteriaTO searchCriteria, int offset, int maxRecordsToRetrieve) throws DAOException
Different database servers provide different ways of specifying the number of records returned and the first start record. For example, in Oracle, you can use the rownum keyword to select your data. If you want a query to return 5 records starting from the eleventh record, you would have the following WHERE clause in your SQL statement:
WHERE rownum > 10 and rownum < 16
In MySQL, you would use the keyword LIMIT in your WHERE clause. The LIMIT keyword has the following format.
LIMIT offset, maxRecordsToRetrieve
If you want the eleventh to fifteenth records, you would have the following WHERE clause.
WHERE LIMIT 10, 5
The app18b application provides the MySQL implementation of the CustomerDAO class. The searchCustomers method is given in Listing 18.3.
Listing 18.3: The searchCustomers method of the CustomerDAOMySQLImpl class
public ArrayList searchCustomers(CustomerSearchCriteriaTO
searchCriteria, int offset, int maxRecordsToRetrieve) throws
DAOException {
//Build the search criterias
StringBuffer criteriaSql = new StringBuffer(512);
criteriaSql.append(SEARCH_CUSTOMER_SQL);
if (searchCriteria.getName() != null) {
criteriaSql.append("name LIKE '%" +
DBUtil.fixSqlFieldValue(
searchCriteria.getName()) + "%' AND ");
}
if (searchCriteria.getAddress() != null) {
criteriaSql.append("address LIKE '%" +
DBUtil.fixSqlFieldValue(searchCriteria.getAddress()) +
"%' AND ");
}
if (searchCriteria.getPhone() != null) {
criteriaSql.append("phone LIKE '%" +
DBUtil.fixSqlFieldValue(searchCriteria.getPhone()) + "%'");
}
// Remove the dangling 'AND' or 'WHERE'
if (criteriaSql.substring(criteriaSql.length()-5).equals(" AND "))
criteriaSql.delete(criteriaSql.length()-5,
criteriaSql.length()-1);
if (criteriaSql.substring(criteriaSql.length()-7).equals(
" WHERE "))
criteriaSql.delete(criteriaSql.length()-7,
criteriaSql.length()-1);
criteriaSql.append(" LIMIT " + offset + "," +
maxRecordsToRetrieve);
ArrayList arrayList = new ArrayList();
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
try {
connection = getConnection();
statement = connection.createStatement();
rs = statement.executeQuery(criteriaSql.toString());
while (rs.next()) {
CustomerTO customer = new CustomerTO();
customer.setIndex(++offset);
customer.setId(rs.getInt("id"));
customer.setName(rs.getString("name"));
customer.setAddress(rs.getString("address"));
customer.setPhone(rs.getString("phone"));
arrayList.add(customer);
}
rs.close();
statement.close();
}
catch (SQLException e) {
throw new DAOException();
}
catch (Exception ex) {
throw new DAOException();
}
finally {
try {
connection.close();
}
catch (SQLException ex) {
throw new DAOException();
}
}
return arrayList;
}
The Action Form
To enable the user to navigate through the pages, the action object needs to remember the following values, which you encapsulate in the CustomerSearchCriteriaForm action form. Here are the properties of this form.
- name, address, phone. These properties provide holders of a customer’s details, they have nothing to do with user navigation.
- currentPage. The page number of the current search result. A new search always sets an initial value of 1 to the currentPage property.
- nextPage. currentPage + 1. This property is only needed because the HTML Tag library’s link tag used to render a Next link does not know how to resolve currentPage + 1.
- previousPage. currentPage - 1. This property is only here because the HTML Tag Library’s link tag used to render a Previous link does not know how to resolve currentPage - 1.
- startRecord. A search result is cached in an ArrayList that has elements enough for several pages. The JSP will have access to this ArrayList and must be told the starting point in the ArrayList from which the elements should be displayed.
- endRecord. The last element in the cached ArrayList that the current page must display.
- highestPageNumber. The highest page number that a search result returns. For example, if a search result returns 100 records, and each page displays 5 records, then highestPageNumber will be 20. If the current page is less than the highestPageNumber property, the Next link will be displayed. Otherwise, no Next link is shown. Because we’re working with a very large set of data, sometimes the highestPageNumber property is not known, until the user browses towards the end of the last batch of search result. In this case, the highestPageNumber property will be set to -1.
- cachedStartPage. At a time, the HttpSession object holds a batch of search result. For example, if the maximum number of records retrieved in each database hit is 100 and each page displays 5 records, then each batch covers 20 pages of data. For the first batch, it is Page 1 to Page 20. If the user clicks the Next link on Page 20, the action object must hit the database and take the second batch of search result, for the second 20 pages. This time, it is Page 21 to Page 40. The cachedStartPage property specifies the first page number of the search result batch. For each invocation, the action object checks the current page the user is requesting and the cachedStartPage. If the user wants to see Page 6 and the value of cachedStartPage is 1 and a batch contains data for 20 pages, then we know that Page 6 can be retrieved from the cache.
The SearchCustomerAction Class
The SearchCustomerAction is invoked every time the user fill in the search form and submits it and when the user clicks the Previous or Next links. A new search, that is when the user submits the form, is indicated by the POST method of the request. Requests for subsequent or previous pages of the same search will have a GET method. A new search must always retrieve data from the database, whether or not the HttpSession object has cached data.
The SearchCustomerAction class is listed in Listing 18.4.
Listing 18.4: The SearchCustomerAction class
package app18b.action;
import app18b.dao.CustomerDAO;
import app18b.dao.DAOFactory;
import app18b.form.CustomerSearchCriteriaForm;
import app18b.util.Config;
import app18b.to.CustomerSearchCriteriaTO;
import java.util.ArrayList;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
public class SearchCustomerAction extends Action {
private static int maxRecordsPerPage;
private static int maxPagesPerRetrieve;
private static int maxRecordsPerRetrieve;
private static String CUSTOMER_LIST = "customerList";
static {
maxRecordsPerPage =
Integer.parseInt(Config.getInstance().
getStringValue("maxRecordsPerPage"));
maxPagesPerRetrieve =
Integer.parseInt(Config.getInstance().
getStringValue("maxPagesPerRetrieve"));
maxRecordsPerRetrieve = maxRecordsPerPage * maxPagesPerRetrieve;
}
public ActionForward execute(ActionMapping mapping,
ActionForm form, HttpServletRequest request,
HttpServletResponse response) {
CustomerSearchCriteriaForm criteria =
(CustomerSearchCriteriaForm) form;
int currentPage = 1; // the page number requested
try {
currentPage = Integer.parseInt(request.getParameter("page"));
}
catch (NumberFormatException e) {
}
criteria.setCurrentPage(currentPage);
criteria.setNextPage(currentPage + 1);
criteria.setPreviousPage(currentPage - 1);
HttpSession session = request.getSession();
if (request.getMethod().equalsIgnoreCase("POST") ||
session.getAttribute(CUSTOMER_LIST)==null) {
ArrayList customerList =
retrieveDataFromDB(criteria, 0, maxRecordsPerRetrieve);
session.setAttribute(CUSTOMER_LIST, customerList);
criteria.setCachedStartPage(1);
}
else {
// retrieve the cached result
if (currentPage<criteria.getCachedStartPage() ||
currentPage >= criteria.getCachedStartPage() +
maxPagesPerRetrieve) {
ArrayList customerList = retrieveDataFromDB(criteria,
(currentPage - 1) * maxRecordsPerPage, maxRecordsPerRetrieve);
session.setAttribute(CUSTOMER_LIST, customerList);
criteria.setCachedStartPage(currentPage);
}
}
criteria.setStartRecord((currentPage –
criteria.getCachedStartPage()) * maxRecordsPerPage);
criteria.setEndRecord(criteria.getStartRecord() +
maxRecordsPerPage - 1);
return mapping.getInputForward();
}
private ArrayList retrieveDataFromDB (CustomerSearchCriteriaForm
criteria, int offset, int maxRecordsPerRetrieve) {
CustomerSearchCriteriaTO customerSearch =
new CustomerSearchCriteriaTO();
ArrayList customerList = new ArrayList();
customerSearch.setName(criteria.getName());
customerSearch.setAddress(criteria.getAddress());
customerSearch.setPhone(criteria.getPhone());
CustomerDAO customerDAO =
DAOFactory.getInstance().getCustomerDAO();
try {
customerList = customerDAO.searchCustomers(customerSearch,
offset, maxRecordsPerRetrieve +1);
int size = customerList.size();
if (size < maxRecordsPerRetrieve + 1)
criteria.setHighestPageNumber(criteria.getCurrentPage() +
(int) Math.ceil(size/maxRecordsPerPage));
else
criteria.setHighestPageNumber(-1);
}
catch (Exception ex) {
}
return customerList;
}
}
First and foremost, the CustomerSearchAction class has three static integers whose values come from the web.xml file through the Config object (See Chapter 14).
private static int maxRecordsPerPage; private static int maxPagesPerRetrieve; private static int maxRecordsPerRetrieve;
Secondly, the retrieveDataFromDB method retrieves the data from the database by calling the searchCustomers method of the CustomerDAO implementation class. The retrieveDataFromDB method’s first argument is a CustomerSearchCriteriaForm from which it can get the values of the name, address, and phone properties to populate a CustomerSearchCriteriaTO object required by the searchCustomer method. More importantly, the retrieveDataFromDB method also sets the value of the lastPageNumber of the CustomerSearchCriteriaForm object it received by using the following code.
customerList = customerDAO.searchCustomers(customerSearch,
offset, maxRecordsPerRetrieve +1);
int size = customerList.size();
if (size < maxRecordsPerRetrieve + 1)
criteria.setHighestPageNumber(criteria.getCurrentPage() +
(int) Math.ceil(size/maxRecordsPerPage));
else
criteria.setHighestPageNumber(-1);
The CustomerDAO interface’s searchCustomer method returns an ArrayList containing CustomerTO objects that matched the search criteria. However, it can also tell whether or not the current database hit returns the last batch of data. We do this by passing maxRecordsPerRetrieve + 1 as the third argument of the searchCustomers method, instead of maxRecordsPerRetrieve. If the size of the returned ArrayList is maxRecordsPerRetrieve + 1, we know that this database hit is not the last batch because there are more records than the maxRecordsPerRetrieve. However, if the size of the returned ArrayList is less than what we are requesting, then we know that the returned result is the last batch for this search.
If the last batch has been retrieved, we can calculate the highest page number from the current page and the division of the size and the maximum records per page, and we set the highestPageNumber property of the CustomerSearchCriteriaForm object received by the retrieveDataFromDB method. Otherwise, we assign -1 to the highestPageNumber property to indicate we do not know yet the highest page number for the current search.
The execute method starts by getting the current page number from the request parameter and sets the previousPage, currentPage, and nextPage properties of the SearchCustomerSearchCriteriaForm object. The rest is simple. If the request method is POST or there is no cached search result (because the previous HttpSession object has expired), then the execute method calls the retrieveDataFromDB method. Otherwise, it checks if the current page is available from the cache. If not, hit the database.
The JSP
The search action object forwards to the displaySearchCustomerForm.jsp page to display the search result. This page uses the tags from the HTML Tag Library and JSTL for the search form and search result. This JSP is given in Listing 18.5.
Listing 18.5: The displaySearchCustomerForm.jsp page
<%@ taglib uri="/tags/struts-html" prefix="html" %>
<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %>
<html>
<body>
<b>Search Customer</b>
<html:form action="/searchCustomer">
<table>
<tr>
<td width="100">Name:</td>
<td colspan="2"><html:text property="name"/></td>
</tr>
<tr>
<td>Address:</td>
<td colspan="2"><html:text property="address"/></td>
</tr>
<tr>
<td>Phone:</td>
<td colspan="2"><html:text property="phone"/></td></tr>
</tr>
<tr>
<td><html:submit>Search</html:submit></td>
<td> </td>
<td><html:link action="/displayCreateCustomerForm">Create New
Customer</html:link></td>
</tr>
</table>
<c:if test="${customerList!=null}">
<hr/>
<b>Search Results</b><br/><br/>
<table border="1">
<tr>
<td colspan="6" align="right">
<c:if test="${customerSearchCriteriaForm.currentPage>1}">
<html:link action="/searchCustomer" paramId="page"
paramName="customerSearchCriteriaForm"
paramProperty="previousPage">Previous</html:link>
</c:if>
<c:if test="${customerSearchCriteriaForm.highestPageNumber!=
customerSearchCriteriaForm.currentPage}">
<html:link action="/searchCustomer" paramId="page"
paramName="customerSearchCriteriaForm"
paramProperty="nextPage">Next</html:link>
</c:if>
</td>
</tr>
<tr>
<td width="100"><b>No.</b></td>
<td width="100"><b>Name</b></td>
<td width="100"><b>Address</b></td>
<td width="100"><b>Phone</b></td>
<td width="100"> </td>
<td width="100"> </td>
</tr>
<c:forEach var="customer" items="${customerList}"
begin="${customerSearchCriteriaForm.startRecord}"
end="${customerSearchCriteriaForm.endRecord}">
<tr>
<td><c:out value="${customer.index}"/></td>
<td><c:out value="${customer.name}"/></td>
<td><c:out value="${customer.address}"/></td>
<td><c:out value="${customer.phone}"/></td>
<td>
<html:link action="/getCustomer" paramName="customer"
paramId="customerId" paramProperty="id">update</html:link>
</td>
<td>
<html:link action="/deleteCustomer" paramName="customer"
paramId="customerId" paramProperty="id">delete</html:link>
</td>
</tr>
</c:forEach>
</table>
</c:if>
</html:form>
</body>
</html>
The first part of the page is the Search form. The second page is a table for presenting the paged search result. The forEach tag of the JSTL is used to iterate the ArrayList containing the result. The forEach tag uses the values of the CustomerSearchCriteriaForm object to determine which elements in the ArrayList should be displayed. For example, the begin attribute is given the value of the startRecord property and the end attribute the value of the endRecord property.
<c:forEach var="customer" items="${customerList}"
begin="${customerSearchCriteriaForm.startRecord}"
end="${customerSearchCriteriaForm.endRecord}">
Running the Application
To run the application, you must have a MySQL test database running and you must have created the Customers table in Chapter 15. Then, you must create enough records for testing. To run the application, direct your browser to the following URL:
http://localhost:8080/app18b/displaySearchCustomerForm.do
Figure 18.2 shows what a paged search result looks like.
Figure 18.2: Working with a very large set of data