java - JDBC Pagination

Question

I want to implement pagination using JDBC. The actual thing I want to know is "How can i get first 50 and then next 50 records from database for page 1 and 2 respectively"

My Query is Select * from data [data table contains 20,000 rows]

For page #1 I get 50 records and for page #2 I want to get next 50 records. How can I implement it efficiently in JDBC?

I have searched and found that rs.absolute(row) is the way to skip first page records but it takes some amount of time on large result sets and I don't want to bear this amount of time. Also, I don't want to use rownum and limit + offset in query because these are not good to use in query, I dont know why, still I don't want to use it in query.

Can anyone help me how to get limited ResultSet for pagination or is there any way JDBC is giving us?

Answer 1

There is no efficient way of doing this by simply using JDBC. You have to formulate the limit to n rows and start from i-th item clauses directly to the SQL for it to be efficient. Depending on the database this might actually be quite easy (see MySQL's LIMIT -keyword), on other databases such as Oracle it can be a little trickier (involves subquery and using rownum pseudo column).

See this JDBC Pagination Tutorial: http://java.avdiel.com/Tutorials/JDBCPaging.html