Pagination using sql

If you have a complex query that returns lot of rows from the database and you are displaying the results in a table,  you may want to add some pagination logic to your application.  However, most people add pagination to the application layer, but retrieve all the rows back from the database.  Java programming frameworks/API such as IBatis, JPA etc provides a mechanism to limit the number of rows retrieved or skip a number of rows.   However, the implementation of those framework is left behind the jdbc driver and often results in the data still retrieved to the application server and then discarded.   You could avoid this resource wastage by changing your query to limit the rows.  The following is an example of how you can do just that.

    select * from ( select rownum rnum, a.* from (
         select columns from your_complex_table order by someField )  a 
            where rownum <  #UPPER_LIMIT )     where rnum >= #LOWER_LIMIT

In this example, you can change the values of #UPPER_LIMIT and #LOWER_LIMIT as parameters to your query limit the results to just the rows you want to deal with.  If you are using a  iBATIS you can make your actual query (the one does fetches all the records) in to an sql fragment and reuse it for the paginated query and get row counts.   It also helps to get all the records in case of exporting all the data to some external format.  The following example shows how you can reuse the sql fragment within your sqlMapping.

<sql id="selectItems">
select columns from your_complex_table order by someField
</sql>
<select id="selectItemCount" resultClass="int">
SELECT COUNT(*) AS total
<include refid="selectItems"/>
</select>
<select id="selectPaginated" resultClass="Item" parameterClass="map">
 select * from ( select rownum rnum, a.* from (
<include refid="selectItems"/>
    ) a  where rownum <  #UPPER_LIMIT )
   where rnum >= #LOWER_LIMIT
</select>