This week's book giveaway is in the Spring forum. We're giving away four copies of Pro Spring MVC with WebFlux: Web Development in Spring Framework 5 and Spring Boot 2 and have Marten Deinum & Iuliana Cosmina on-line! See this thread for details.
Most of the J2EE applications have search, sorting and records per page functionality. Sun also provides a design pattern called �Value List Handler� to address this issue. However, this pattern does provide a robust solution in case of very large records which is explained below
�The search query is executed only once on the database and all the records are stored in application server which will have serious impact on the allocated memory. In most of the large real-time application, the records fetched could be in thousands. In this case, fetching all the records into application server will make the server to go out of memory. �Since most of the applications also has sorting on various columns, the records in the application server needs to be rearranged again which is CPU and memory intensive job. �User can view only one page at a time but the system would have fetched all the records which are unwanted.
FORCES �The component should have an effective pagination feature which does not overload the application server memory �The component should fetch the up to date data that is present in the database. �The component should be easily plugged into the application that requires the pagination feature. �The component should have pagination, sorting and number of records per page features.
SOLUTION Use this component to control the search, provide the results to the client and keep the application server memory under control.
FEATURES The pagination component allows the user to view the search results in pages so that the user is not overwhelmed by the number of records displayed on the search, especially in case of thousands of records.
The pagination component provides the 3 key features, �Pages: This feature allows user to move to next or previous page by click on the �Prev� or �Next� link (as shown in the figure below). The user can also click on any specific page number to directly view the specific page. If there are more pages than shown initially, it will be available when the user clicks on �Next� link. �No of Records: This feature provides flexibility to the user to choose number of records to be displayed on the screen. �Sorting: This feature allows the user to sort the results based on any column.
The key strategies of the pagination component are 1.Query the database only for those records that are required for the client: This strategy enables the application to use the memory only for those records and not for the entire search results. 2.Query the database each time the user request for the data: This strategy enables the client to fetch the up to date data from the database. Though there is an overhead on querying the database each time, it addresses the memory issue that might occur if ValueListPattern is implemented for pagination (i.e. caching the entire result set in the application server memory).
The client creates the pagination data which contains the requested page number, no of records per page and sort order. This pagination object is passed to the business Process object (PO) or EJB which will first get the total count of the result set. Depending on the business requirement, there are two ways to handle this situation �Fetch row count every time: In this strategy, the Business process object queries the database every time to fetch the count. The advantage of this strategy is that the client will always get the up to date record count. The disadvantage of this strategy is the overhead of accessing the database every time. �Fetch row count only for the first time: In this strategy, the business object accesses the database only once and the record count is cached. The advantage is that the database will not be accessed for every request. The disadvantage is that the count might be incorrect if the records in the database changes. The strategy should be carefully selected based on the business needs.
Once the record count is fetched, the process object invokes the Business DAO and passes the Pagination Data along with the business specific search criteria parameters. The DAO prepares the base query and passes the pagination Object and the Base query string to the �Pagination Utility� object.
The Pagination Utility will be called by the DAO to massage the base SQL by applying the order by clause and outer select statement to the base query and returns the massaged query string back to DAO.
The DAO will then execute the query which will fetch only those records that are specific to the requested page. The result is then passed back to the client and the Pagination Data object is updated such as current page, no of records etc
CONFIGURATION 1.In the search page, include application specific form bean (e.g. com.app.BusinessForm) 2.In the BusinessForm, include PaginationData object 3.Add getter setter methods for this pagination Data attribute 4.In application specific action class, retrieve the form bean 5.Call the specific Business process object method and pass PaginationData object as one of its parameters 6.In Application specific DAO, have a method like getQuery(), which returns the base query(i.e. which contains the SQL for the actual search criteria) �From the AppXXXPO, call the CountRecordDAO or any Application specific DAO to get the record count for the search criteria. �Set the RecordCount in PaginationData.setTotalRecords(count). �Set the default sort order column name in Pagination Data by calling setSortByCol(colName) method �Call the PaginationUtility.formatSQLQuery(PaginationData,query) method which will format the query to include the sort by column and row range in where clause �Then invoke the AppXXDAO�s application specific getQuery() method and pass the formatted query to fetch the request page records and set it into List of business object and pass it back to the Action. �In the Action call the requestAtribute (PAGINATION_DATA, paginationData) object to set the Pagination Object in the JSP which will be used for subsequent requests.