Folks,

I am trying to write a servlet that returns a "page" of a ResultSet. That is, the caller should be able to provide a simple SQL select such as "select * from addresses", a page size such as 100 rows, and an offset into the ResultSet such as 500. I assumed that the JDBC method rs.absolute(n) would enable me to do what I want, allowing me to jump to an absolute offset into a ResultSet.

Unfortunately, the JDBC drivers for MySQL that I have found do not support the rs.absolute method. At first I just thought that perhaps the org.gjt.mm.mysql open source JDBC driver was incomplete. But the only other JDBC driver for MySql I found did not support that method, either, which leads me to think I may be approaching the problem wrong--or what I want to do is impossible in MySQL (which seems unlikely to me).

My servlet will not have knowledge of the primary key (though I could get that from metadata), but even if it does, how does that help me navigate to a given absolute position within the ResultSet? Calling rs.next() a few thousand times is just wayyyy too kludgey for me, so don't suggest that, please.

And relying on an auto-increment field to provide literal record numbers doesn't work either; they are not guaranteed to be sequential, especially if rows have been deleted from the table.

So, I guess the questions are:

1) Does anyone know of a JDBC driver for MySQL that supports the absolute method? If so, I'm happy. If not, then... 2) Is there a way of approaching this problem differently that will give me the same functionality?

Thanks for whatever help/advice/sanity anyone can offer.

== Ross ==