Implementing dynamic paged queries - POJOs in Action: Developing Enterprise Applications with Lightweight Frameworks - page 421

List result = getSqlMapClientTemplate().queryForList(
"findOrders", map, startingIndex, pageSize);
boolean more = result.size() > pageSize;
if (more) {
result.remove(pageSize);
}
return new PagedQueryResult(result, more);
}
This method creates a map containing the OrderSearchCriteria and the number
of desired rows. The findOrders() method then invokes queryForList() , passing
findOrders , the map, and the desired range of rows to be returned. The find-
Orders() method retrieves one more row than is actually required in order to
determine whether there are more rows. It removes the extra row from the list
before returning it.
Listing 11.1 shows the findOrders dynamic mapped statement that is executed
by findOrders() and its result map. The mapped statement has conditional XML
elements that use the contents of the map, such as the properties of the Order-
SearchCriteria object, to dynamically construct the WHERE clause of the SELECT
statement. The result map constructs an OrderSummaryDTO from each row of the
result set.

Listing 11.1 The findOrders mapped statement and its result set

<sqlMap>
…
<select id="findOrders"
resultMap="OrderResultMap"
resultSetType="SCROLL_INSENSITIVE">
SELECT /*+ FIRST_ROWS($pageSize$) */
O.ORDER_ID, R.NAME AS RESTAURANT_NAME
FROM FTGO_ORDER O,
FTGO_RESTAURANT R
WHERE O.RESTAURANT_ID = R.RESTAURANT_ID

Specify the number of rows

<isNotEmpty property="criteria.restaurantName">
AND r.name = #criteria.restaurantName#
</isNotEmpty>

Conditionally includes
restaurantName

<isNotEmpty property="criteria.deliveryCity">
AND o.delivery_city
= #criteria.deliveryCity#
</isNotEmpty>

Conditionally includes
deliveryCity

<isNotEmpty property="criteria.state">
AND o.status = #criteria.state#
</isNotEmpty>

Conditionally
includes state