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
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