This filtering capability goes a long way towards assisting users in finding what they want, but there still is a gap in the functionality. We should expect that our web application to be wildly successful and have millions of users. Doesn't everyone's? And with that volume of users we should also anticipate the query filter to return hundreds if not thousands of entities. To manage this we need provide the ability to paginate the results of our filter.
The table tag that Roo generated for the application has pagination capability built in. But to use that functionality we will have to provide a count of records that match the filter and expose that value to the tag. Also the pagination tag that Roo provides doesn't handle requests with parameters so this will need to be fixed too. To do this we will need to;
- Create a custom JPQL query to return the count of AppUsers that match the finders results.
- Modify the custom find controller method we built in the last post to handle pagination.
- Modify the pagination tag to handle request parameters correctly.
Create a Custom JPQL Query
The first step is to create the JPQL query. Since we need this query to return a count of records that match the finder that was created in the first post (findAppUsersByNameLikeOrEmailLike) we will clone an modify that query.
Recall that Roo considers any file matching *_Roo_*.aj as being managed by the shell so the new query needs to be placed in another file or the entity definition file itself. I tend to take advantage of ITD's and the compartmentalization that they provide, so I created and AppUser_Finder.aj aspect file in the domain package.
Here is the filter count code;
package com.repik.multitenant.security.domain;
import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
privileged aspect AppUser_Finder {
public static long AppUser.countAppUsersByNameLikeOrEmailLike(String name, String email) {
if (name == null || name.length() == 0)
throw new IllegalArgumentException("The name argument is required");
name = name.replace('*', '%');
if (name.charAt(0) != '%') {
name = "%" + name;
}
if (name.charAt(name.length() - 1) != '%') {
name = name + "%";
}
if (email == null || email.length() == 0)
throw new IllegalArgumentException("The email argument is required");
email = email.replace('*', '%');
if (email.charAt(0) != '%') {
email = "%" + email;
}
if (email.charAt(email.length() - 1) != '%') {
email = email + "%";
}
EntityManager em = AppUser.entityManager();
TypedQuery<long> q = em.createQuery("SELECT count(o) FROM AppUser AS o WHERE LOWER(o.name) LIKE LOWER(:name) OR LOWER(o.email) LIKE LOWER(:email)", Long.class);
q.setParameter("name", name);
q.setParameter("email", email);
return q.getSingleResult();
}
}
Modify the Controller find Method
Now we need to modify the controller find method to handle the pagination. There are two pagination parameters that can optionally be passed into the controller method; page and size. The page parameter represents the page number being requested. The size parameter represents the number of entities displayed on a page. Our controller method will use these parameters along the finder method to fetch the AppUsers for display on the page requested. Additionally, we will need to determine the maximum number of pages that the filter matches so the pagination logic found in the JSP can render correctly.
Here is the modified controller;
package com.repik.multitenant.security.web;
import com.repik.multitenant.security.domain.AppUser;
import org.springframework.roo.addon.web.mvc.controller.scaffold.RooWebScaffold;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
@RequestMapping("/appusers")
@Controller
@RooWebScaffold(path = "appusers", formBackingObject = AppUser.class)
public class AppUserController {
@RequestMapping(value="/find", produces = "text/html")
public String find(
@RequestParam(value = "find", required = true) String finder,
@RequestParam(value = "filter", required = true) String filter,
@RequestParam(value = "page", required = false) Integer page,
@RequestParam(value = "size", required = false) Integer size,
Model uiModel) {
if ( "findAppUsersByNameLikeOrEmailLike".equals( finder ) && ! filter.isEmpty() ) {
int sizeNo = size == null ? 10 : size.intValue();
uiModel.addAttribute( "size", sizeNo ) ;
final int firstResult = page == null ? 0 : (page.intValue() - 1) * sizeNo;
uiModel.addAttribute("appusers", AppUser.findAppUsersByNameLikeOrEmailLike(filter, filter).setFirstResult( firstResult ).setMaxResults( sizeNo ).getResultList());
float nrOfPages = (float) AppUser.countAppUsersByNameLikeOrEmailLike( filter, filter ) / sizeNo;
uiModel.addAttribute("maxPages", (int) ((nrOfPages > (int) nrOfPages || nrOfPages == 0.0) ? nrOfPages + 1 : nrOfPages));
uiModel.addAttribute("filter", filter) ;
return "appusers/list";
}
else
return "redirect:/appusers" ;
}
}
The following changes were made to the finder method from the earlier post;- line 19-20 additional optional request parameters were added to handle the page number and count of elements (size) on the page.
- lines 23-26 for both the page and size attributes assign default values if missing and then add those attributes to the uiModel.
- line 27 calculate the first record offset for the page being displayed.
- line 28 find and add to the uiModel the AppUser records using the filter, offset and size attributes.
- line 29 use the JPQL query from above to get a count of AppUser's that match the filter, use that to calculate the max page number.
- line 30 add the max page number to the uiModel.
Modify the Pagination Tag to Handle Request Parameters
Within the functionality of the table tag (tags/forms/table.tagx) is the logic to embed the pagination tag (tags/util/pagination.tagx) if maxPages has been set. In the controller code we set this attribute in the uiModel so pagination is enabled.
The problem now is that the URL's being generated are incorrect. Specifically we have a couple of request parameters, find and filter, that are not being propagated into the URL's. So we need to modify the pagination tag to handle this.
Adding any request parameters to URL's generated by the pagination tag requires that when building a URL we iterate though the request parameters adding those to the URL. The exception being that if the parameter is either 'page' or 'size' we don't want to propagate those since those are the ones that are particular to the URL. The following snippet of JSP will do this;
<c:forEach var="paramName" items="${pageContext.request.parameterNames}">
<c:if test="${paramName ne 'page' and paramName ne 'size' }">
<spring:param name="${paramName}" value="${param[ paramName ]}"/>
</c:if>
</c:forEach>
Here is the complete modified pagination.tagx code;
<jsp:root xmlns:c="http://java.sun.com/jsp/jstl/core" xmlns:fn="http://java.sun.com/jsp/jstl/functions" xmlns:spring="http://www.springframework.org/tags" xmlns:jsp="http://java.sun.com/JSP/Page" version="2.0">
<jsp:output omit-xml-declaration="yes" />
<jsp:directive.attribute name="maxPages" type="java.lang.Integer" required="true" rtexprvalue="true" description="The maximum number of pages available (ie tableRecordCount / size)" />
<jsp:directive.attribute name="page" type="java.lang.Integer" required="false" rtexprvalue="true" description="The current page (not required, defaults to 1)" />
<jsp:directive.attribute name="size" type="java.lang.Integer" required="false" rtexprvalue="true" description="The number of records per page (not required, defaults to 10)" />
<jsp:directive.attribute name="render" type="java.lang.Boolean" required="false" rtexprvalue="true" description="Indicate if the contents of this tag and all enclosed tags should be rendered (default 'true')" />
<c:if test="${empty render or render}">
<c:if test="${empty page || page lt 1}">
<c:set var="page" value="1" />
</c:if>
<c:if test="${empty size || size lt 1}">
<c:set var="size" value="10" />
</c:if>
<spring:message code="list_size" var="list_size" htmlEscape="false" />
<c:out value="${list_size} " />
<c:forEach var="i" begin="5" end="25" step="5">
<c:choose>
<c:when test="${size == i}">
<c:out value="${i}" />
</c:when>
<c:otherwise>
<spring:url value="" var="sizeUrl">
<c:forEach var="paramName" items="${pageContext.request.parameterNames}">
<c:if test="${paramName ne 'page' and paramName ne 'size' }">
<spring:param name="${paramName}" value="${param[ paramName ]}"/>
</c:if>
</c:forEach>
<spring:param name="page" value="1" />
<spring:param name="size" value="${i}" />
</spring:url>
<a href="${sizeUrl}">${i}</a>
</c:otherwise>
</c:choose>
<c:out value=" " />
</c:forEach>
<c:out value="| " />
<c:if test="${page ne 1}">
<spring:url value="" var="first">
<c:forEach var="paramName" items="${pageContext.request.parameterNames}">
<c:if test="${paramName ne 'page' and paramName ne 'size' }">
<spring:param name="${paramName}" value="${param[ paramName ]}"/>
</c:if>
</c:forEach>
<spring:param name="page" value="1" />
<spring:param name="size" value="${size}" />
</spring:url>
<spring:url value="/resources/images/resultset_first.png" var="first_image_url" />
<spring:message code="list_first" var="first_label" htmlEscape="false" />
<a class="image" href="${first}" title="${fn:escapeXml(first_label)}">
<img alt="${fn:escapeXml(first_label)}" src="${first_image_url}" />
</a>
</c:if>
<c:if test="${page gt 1}">
<spring:url value="" var="previous">
<c:forEach var="paramName" items="${pageContext.request.parameterNames}">
<c:if test="${paramName ne 'page' and paramName ne 'size' }">
<spring:param name="${paramName}" value="${param[ paramName ]}"/>
</c:if>
</c:forEach>
<spring:param name="page" value="${page - 1}" />
<spring:param name="size" value="${size}" />
</spring:url>
<spring:url value="/resources/images/resultset_previous.png" var="previous_image_url" />
<spring:message code="list_previous" var="previous_label" htmlEscape="false" />
<a class="image" href="${previous}" title="${fn:escapeXml(previous_label)}">
<img alt="${fn:escapeXml(previous_label)}" src="${previous_image_url}" />
</a>
</c:if>
<c:out value=" " />
<spring:message code="list_page" arguments="${page},${maxPages}" argumentSeparator="," />
<c:out value=" " />
<c:if test="${page lt maxPages}">
<spring:url value="" var="next">
<c:forEach var="paramName" items="${pageContext.request.parameterNames}">
<c:if test="${paramName ne 'page' and paramName ne 'size' }">
<spring:param name="${paramName}" value="${param[ paramName ]}"/>
</c:if>
</c:forEach>
<spring:param name="page" value="${page + 1}" />
<spring:param name="size" value="${size}" />
</spring:url>
<spring:url value="/resources/images/resultset_next.png" var="next_image_url" />
<spring:message code="list_next" var="next_label" htmlEscape="false" />
<a class="image" href="${next}" title="${fn:escapeXml(next_label)}">
<img alt="${fn:escapeXml(next_label)}" src="${next_image_url}" />
</a>
</c:if>
<c:if test="${page ne maxPages}">
<spring:url value="" var="last">
<c:forEach var="paramName" items="${pageContext.request.parameterNames}">
<c:if test="${paramName ne 'page' and paramName ne 'size' }">
<spring:param name="${paramName}" value="${param[ paramName ]}"/>
</c:if>
</c:forEach>
<spring:param name="page" value="${maxPages}" />
<spring:param name="size" value="${size}" />
</spring:url>
<spring:url value="/resources/images/resultset_last.png" var="last_image_url" />
<spring:message code="list_last" var="last_label" htmlEscape="false" />
<a class="image" href="${last}" title="${fn:escapeXml(last_label)}">
<img alt="${fn:escapeXml(last_label)}" src="${last_image_url}" />
</a>
</c:if>
</c:if>
</jsp:root>
Where the modifications are that we injected the request parameter handling snippet in at lines; 29, 46, 62, 81, and 97.
All the implementation pieces are in place now and we can support pagination of a filter.
