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

"findOrders", map);
boolean more = result.size() > pageSize;
if (more) {
result.remove(pageSize);
}
return new PagedQueryResult(result, more);
}
The dynamic mapped statement that is executed by this version of findOrders()
is similar to the one we saw earlier and uses the same result map. As well as using
the conditional XML tags to construct the WHERE clause, this mapped statement
uses them to nest the query inside a SELECT statement that uses ROWNUM to skip over
the number of rows specified by the start property.

<sqlMap>

<select id="findOrders" resultMap="OrderResultMap"
resultSetType="SCROLL_INSENSITIVE">
<isGreaterThan property=" startingIndex" compareValue="0">
SELECT * FROM (SELECT XX.*,
ROWNUM RNXX FROM (
</isGreaterThan>

Skips over
startingIndex rows

Returns no more
than maxRows

SELECT * FROM (

SELECT O.ORDER_ID, R.NAME AS RESTAURANT_NAME FROM FTGO_ORDER O,
FTGO_RESTAURANT R
WHERE O.RESTAURANT_ID = R.RESTAURANT_ID
<isNotEmpty property="criteria.restaurantName">
AND r.name = #criteria.restaurantName#
</isNotEmpty>

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

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

ORDER BY o.ORDER_ID ASC

Returns no more
than maxRows

) WHERE ROWNUM &lt; #maxRows#

<isGreaterThan property="start" compareValue="0">
) XX ) WHERE RNXX &gt; #startingIndex#
</isGreaterThan>
</select>
…
</sqlMap>

Skips over
startingIndex rows