The most efficient way to do this is, if you only want one page of data (e.g. 20 records) then only select one page of data from the database.
In order for this to work you need to:
limit the number of rows, returned by your DB query, to the page size (*)retrieve the results in a consistent order, ideally by unique keynote the unique keys of the first and last records in the page If you want the next page you select rows where your unique key is greater than that of the last record on current page. Guess what you do if you want the previous page!
(*) This may not be possible on all RDBMS but MS SQL Server has the "TOP n" option; Sybase has SET ROWCOUNT n; and Oracle has the rownum pseudo-column (e.g. WHERE rownum <= n). These are not without their issues, however. Strangely, Mickeysoft has the best implementation here.
The advantage of this approach becomes particularly apparent when you have, say, 50,000 rows on the database and your page size is 100. You really don't want to pull 50,000 matching rows out of the database and into
Java (memory) just to take the top 100 to display in your web page or app GUI and then discard the lot.
I've seen this kind of implementation (the inefficient one) numerous times on Enterprise systems. It's quite often a major contributing factor to poor user perception of system performance (e.g. their searches seem to take forever).
Unfortunately I don't have an example implementation I can show you.
Jules