I have created a nice table tag that pulls data from a database and allows the data to be sorted by clicking it's header. All works fine.

Now I want to paginate this output. For example, I want to be able to specify that 10 rows be displayed per page, and then provide First/Last, Next/Previous links (assuming of course there is a next/previous).

The problem I'm having is the ability to retrieve data from the database 10 records at a time...

Here's an example of what I'm doing:

<code>
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
st.setFetchSize(10);
int pages = 0;
if (st.execute("SELECT * FROM users")) {
   while (st.getMoreResults()) {
     ResultSet results = st.getResultSet();
     pages++;
   }
}
</code>

I realize that this does nothing but count the number of pages, but that's the point. I get 1. It's always one, whether my SQL should return 1 or 1,000,000 rows. I get them all in the first ResultSet.

I tried using setMaxRows(10), which does return only 10, but that's it. Successive calls to geMoreResults return false.

Am I missing something here? Thanks a bunch.