Many Web-based applications include the requirement to display some part of the user search results in chunks of pages. For example, think of the typical search engine, say Google, which displays search results in chunks of 10 or more items per page.<\/p>\n
In this blog, I will describe how to implement this design requirement using features provided by DB2 \u00c2\u00ae Universal Database TM (UDB) and how to cache the results using a stateful bean or session. Though this example is specific to DB2 and JSP, the same approach can be used with any relational database and dynamic pages technique.<\/p>\n
We will consider two approaches:<\/p>\n
* Querying the database to get records in chunks using the rownext() feature of DB2
\n* Caching results in stateful session beans or HttpSession<\/p>\n
The scenario<\/strong><\/p>\n We shall consider these approaches by looking at a scenario for a product-based Web site which allows the user to search for products offered based on category, price, brand, and so on. The results will be displayed in chunks of user-defined page sizes, say 10 or 20 items.<\/p>\n Here are our sample table definitions:<\/p>\n Using DB2 features to achieve pagination<\/strong><\/p>\n This approach is good for applications where you have a chance to do some design work before the database and application go into production.<\/p>\n Here we assume that the user wants the products to be displayed in chunk of 20 results per page. As we know, the rows in a relational table have no specific order, so the SQL query needs to be designed to allow the data to be retrieved in a particular order using an ORDER BY clause on the primary key, which in our case is our product id.<\/p>\n DB2 allows you to order a result set on the fly and fetch an arbitrary number of rows from the beginning or end of this result set.<\/p>\n Here is a query to select first 20 records from our product table based on the category “Books.”<\/p>\n The ORDER BY will force a sort of the entire result set in memory, so we’re not doing this to improve DB2 server performance (although sending only 10 rows to the client may improve network performance).<\/p>\n If the order is not a factor and we just want to get the first 20 rows based on the user criteria, then we can eliminate the ORDER BY to save the sort on the DB2 server:<\/p>\n Since the application needs to display say 20 results per page, we can make use of the row_next() feature provided by DB2.<\/p>\n Below is an example of how to select results from rows numbered 21 to 40. We assume that the user has selected category as “Books” and product description as “Application Servers”.<\/p>\n In the case of a Web-based application, the parameters “21” and “40” can be specified at run time. Here is the modified query:<\/p>\n The rownumber() function allows the developers to assign row numbers to results sets on the fly.<\/p>\n If the row_next clause is removed ( ROW_NEXT BETWEEN ? and ? ), all the rows that matched the criteria will be returned.<\/p>\n The SELECT * FROM clause in case above can be thought of as a temporary table holding the entire result set that matches the criteria and than returning that set of results specified in the row range.<\/p>\n Using the rownumber() feature will cause an additional performance impact on the system as the database first fetches all rows that matches the criteria and returns only those rows specified in the range.<\/p>\n Next we look at the trade-offs and advantages of this approach and when it’s an appropriate choice.<\/p>\n Trade-offs for this approach<\/p>\n In this approach, the transactions need to be handled programmatically by the developer. In our scenario, if we assume that products are updated only once daily, say at midnight, we can use the lowest transaction isolation level of READ_UNCOMITTED.<\/p>\n Using the row_next feature definitely will have some performance impact on the database application as compared to a query without the row_next feature. Also this approach requires us to hit the database each time to get next sequence of result sets based on the range of rows.<\/p>\n Fetching an arbitrary number of rows from the beginning or end of this result set is provided by almost every RDBMS system, though the SQL syntax varies. Hence if we move from one database vendor to other, we might have to modify the query accordingly.<\/p>\n If our design requires browser back button functionality and the user is interested in viewing the latest information from the database each time, then this design can provide a possible solution. The drawback is that this may require a hit on the database for each results page, which might affect the performance drastically.<\/p>\n Basically the URL query will have the search parameters embedded which can be fed to our SQL Query. Here is a possible URL query: When the user clicks on browser back button, the previous URL to be displayed in browser will be:<\/p>\n http:\/\/localhost:8050\/ProductSearch? We’ll assume that the latest page id is available in the session. So the browser back button can be tracked as follows:<\/p>\n Using this approach the user will be able to view the latest information from the database.<\/p>\n Next we move on to our second approach, which is widely used in many applications.<\/p>\n Caching result sets in stateful beans or HttpSession<\/strong><\/p>\n This is one of the widely used approaches to pagination in which results are cached in some stateful object like session beans or HttpSession. The choice between HttpSession or stateful bean depends upon the size of the data to be retrieved. If there is large amount of data one should definitely go for the stateful session bean method.<\/p>\n In this approach we would get all the results from the database in one go and store the results in our stateful session bean.<\/p>\n Here is a possible query to retrieve all the books that are available in our product table:<\/p>\n Here is our design of our remote interface stateful bean:<\/p>\n Here are the steps required to achieve pagination:<\/p>\n 1. Get the result set based on the search criteria. Here is our typical implementation of getPerPagSearchResults(String page_count):<\/p>\n The product list can be iterated and all the product information can be displayed at that point. Whenever the next button is clicked the next page_count can be passed to our servlet, which does the same process listed above.<\/p>\n Trade-offs for this approach<\/p>\n In this approach, fetching the result set based on the search criteria needs to be handled by the developer, while maintaining the state of our category object to implement caching is handled by the EJB Container. This provides an additional layer for managing our client state.<\/p>\n This approach will definitely provide better performance than our earlier one, as we will avoid subsequent database calls and will have caching of result sets.<\/p>\n Since we are caching the results, it is possible that the user might be viewing stale data. Because of this issue, this approach is well-suited for an application where the data is not constantly updated. For example if you are updating your products list every night at midnight, then the viewer will likely not be viewing stale data when using the application throughout the day.<\/p>\n This approach provides a vendor-neutral approach as our caching logic can be moved from one EJB container to another without changing our code. However we might have to modify our EJB vendor-specfic deployment descriptor if the code needs to be run on different EJB containers.<\/p>\n Consider an example where the browser cache is disabled and the user clicks on the browser back button. That would result in the browser requesting the JSP page again. Now suppose we navigate across different search criteria pages. As per this design only the latest search results will be displayed, as we are keeping our latest search results in the Stateful bean. So in order to track which search criteria the user is working on, we can store the search criteria in the session and compare it with request parameters present in the URL when user clicks on the browser back button.<\/p>\n Here is sample code that is present in the JSP which displays Search Results page:<\/p>\n SearchCriteria searchObjSession = (SearchCriteria) Next we would get search parameters from the URL .Here we assume that URL is:<\/p>\n http:\/\/localhost:8050\/ProductSearch?minprize=50&maxprice=100 Here is the sample code:<\/p>\n Which approach is best for me?<\/strong><\/p>\n Based on your requirements you can apply a combination of both approaches. The most widely used approach is that which provides caching of result sets.<\/p>\n Consider a scenario where we want to display only the latest 20 products that have been added. The first approach definitely can be used since in this case sorting of the result sets and providing only 20 records would be a job for the database system. This scenario is very useful if your database is located remotely as this will ensure only 20 records flow over the network layer.<\/p>\n Conclusion<\/strong><\/p>\n We’ve talked about how to achieve pagination using two possible approaches. Depending upon your application and business needs, one of these approaches can be used to solve the pagination problem for your Web applications.<\/p>\n This article of mine was first published by IBM DeveloperWorks at -http:\/\/www.ibm.com\/developerworks\/data\/library\/techarticle\/0307balani\/0307balani.html.All rights retained by IBM and the author.\r\nCREATE TABLE Product_Category(\r\ncategory_id integer not null,\r\ncategory_name varchar(200), not null\r\ncategory_decription varchar2(500)\r\n)\r\n\r\nCREATE TABLE Product (\r\nproduct_id Integer not null,\r\nproduct_name varchar2(50) not null,\r\nprod_category_id integer not null,\r\nproduct_decription varchar2(100) not null,\r\nproduct_price decimal(15,2),\r\nproduct_status char(1) default 'Y',\r\nproduct_width decimal(5,2) not null,\r\nproduct_length decimal(5,2) not null,\r\nproduct_created_date timestamp,\r\nproduct_netweight decimal(10,3),\r\nconstraint PK_PRODUCT_ID PRIMARY KEY\r\n(product_id),\r\nconstraint product_category_fk foreign key\r\n(prod_category_id)\r\nreferences Product_Category (category_id))\r\n<\/pre>\n
\r\nSELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION,\r\nPRODUCT_PRICE, PROD_CATEGORY_ID\r\nFROM PRODUCT,PRODUCT_CATEGORY\r\nWHERE\r\nPRODUCT.PROD_CATEGORY_ID\r\n= PRODUCT_CATEGORY.CATEGORY_ID\r\nAND\r\nPRODUCT_CATEGORY.CATEGORY_ID = 'Books'\r\nORDER BY PRODUCT.PRODUCT_ID\r\nFETCH FIRST 20 ROWS ONLY\r\n<\/pre>\n
\r\nSELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION,\r\nPRODUCT_PRICE, PROD_CATEGORY_ID\r\nFROM PRODUCT,PRODUCT_CATEGORY\r\nWHERE\r\nPRODUCT. PROD_CATEGORY_ID\r\n= PRODUCT_CATEGORY.CATEGORY_ID\r\nAND\r\nPRODUCT_CATEGORY.CATEGORY_ID = 'Books'\r\nFETCH FIRST 20 ROWS ONLY\r\n<\/pre>\n
SELECT * FROM (\r\nSELECT PRODUCT_ID, PRODUCT_NAME,\r\nPRODUCT_DESCRIPTION, PRODUCT_PRICE,\r\nrownumber() OVER\r\n(ORDER BY PRODUCT_ID)\r\nAS ROW_NEXT\r\nFROM PRODUCT,PRODUCT_CATEGORY WHERE\r\nPRODUCT.PROD_CATEGORY_ID\r\n= PRODUCT_CATEGORY.CATEGORY_ID\r\nAND\r\nPRODUCT_CATEGORY.CATEGORY_ID = 'Books'\r\nAND\r\nPRODUCT.PRODUCT_DESCRIPTION LIKE\r\n'Application Servers'\r\n)\r\nAS PRODUCT_TEMP WHERE\r\nROW_NEXT BETWEEN 21 and 40\r\n<\/pre>\n
SELECT * FROM (\r\nSELECT PRODUCT_ID, PRODUCT_NAME,\r\nPRODUCT_DESCRIPTION, PRODUCT_PRICE,\r\nrownumber() OVER\r\n(ORDER BY PRODUCT_ID) AS ROW_NEXT\r\nFROM PRODUCT,PRODUCT_CATEGORY\r\nWHERE\r\nPRODUCT. PROD_CATEGORY_ID\r\n= PRODUCT_CATEGORY.CATEGORY_ID\r\nAND\r\nPRODUCT_CATEGORY.CATEGORY_ID = 'Books'\r\nAND\r\nPRODUCT. PRODUCT_DESCRIPTION LIKE\r\n'Application Servers'\r\n)\r\nAS PRODUCT_TEMP WHERE\r\nROW_NEXT BETWEEN ? and ?\r\n<\/pre>\n
\n
\n
\n
\nhttp:\/\/localhost:8050\/ProductSearch?
\nminprize=50& maxprice=100& pagecount=20& resultsPerpage=10<\/p>\n
\nminprize=50&maxprice=100&pagecount=10&resultsPerpage=10<\/p>\nif(!session.getAttribute(\"pageID).equalsIgnoreCase\r\n(request.getAttribute(\"pageCount\")){\r\n\/\/Then user has clicked the back button\r\n\/\/Get possible search parameters, pagecount\r\n\/\/and resultsPerPage from URL and formulate\r\n\/\/a dynamic URL and post the information back\r\n\/\/to the servlet.The URL would be\r\nhttp:\/\/localhost:8050\/ProductSearch?minprize=50&\r\nmaxprice=100&amp;pagecount=10&amp;resultsPerpage=10<\/p>\r\nreturn;\r\n\/\/Done display the results screen page\r\n}<\/pre>\n[\r\n\r\nSELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION,\r\nPRODUCT_PRICE, PROD_CATEGORY_ID\r\nFROM PRODUCT\r\nWHERE\r\nPRODUCT. PROD_CATEGORY_ID\r\n= PRODUCT_CATEGORY.CATEGORY_ID\r\nAND\r\nPRODUCT_CATEGORY.CATEGORY_ID = 'Books'\r\n<\/pre>\n
\r\npublic void setSearchResults(Category category)\r\nthrows RemoteException\r\n\r\npublic Category getPerPagSearchResults(String page_count)\r\nthrows RemoteException\r\n\r\nHere is what typical Category and Product objects would look like:\r\n\r\npublic class Category extends java.io.Serializable {\r\nprivate String category_id,\r\nprivate String category_name,\r\n\/\/Holds an Array List of products under that category.\r\nprivate ArrayList products;\r\n}\r\n\r\npublic class Product extends java.io.Serializable {\r\nprivate String product_id;\r\nprivate String product_name;\r\nprivate String product_desc;\r\nprivate String product_price;\r\n}\r\n<\/pre>\n
\n2. Loop though the result set and store each product object in our product ArrayList.
\n3. Store the array list of project objects in the category object.
\n4. Call our remote method setSearchResults and store the category object.
\n5. In order to get results to be displayed per page, call the remote method getPerPagSearchResults() passing the page_count (such as 10 or 20) to retrieve the corresponding records.<\/p>\n\r\n\/\/Check if Product List contains at least page_count products\r\nint len = category.getProduct_list().length&gt;page_count\r\n? page_count : category.getProduct_list().length\r\n\r\nArrayList product_tempList = new ArrayList();\r\n\r\nfor( int i = page_count+1 ; i &lt; len ; i++){\r\n\/\/Loop though the product list array and get\r\n\/\/corresponding records\r\nproduct_tempList.add(category.getProduct_list().get(I);\r\n}\r\n\r\n\/\/return category object containing an arraylist of\r\n\/\/products containg records from page_count\r\n\/\/to page_count+10 or product_list.length if\r\n\/\/product_list.length is less than page_count+10\r\n\r\nreturn product_tempList\r\n<\/pre>\n\n
\n
\n
\nsession.getAttribute(“SearchCriteria”)<\/p>\n
\n&pagecount=10&resultsPerpage=10<\/p>\n\r\nSearchCriteria previousSearch = new SearchCriteria();\r\nIf(request.getAttribute(\"minPrice\") != null){\r\npreviousSearch.setMinPrice(request.getAttribute(\"minPrice\"))<\/p>\r\n\/\/and so on for remaining parameters\r\n\/\/Next we would compare our SearchCriteria object from session with our PreviousSearch object:\r\nif(!searchObjSession.equals(previousSearch)){\r\n\/\/User is trying to view previous search results\r\n\/\/Show an error to user that he is allowed to work on latest\r\n\/\/search criteria only or else\r\n\/\/depending on your business logic you can give a call to\r\n\/\/database with previous search\r\n\/\/criteria and store it in our Stateful bean and display the\r\n\/\/results back to the user.\r\n\/\/Redirect him to Search Criteria Page or Search Results Page\r\n\/\/ return;\r\n}<\/pre>\n
\n