Resultset paging using jsp
843854Jan 20 2003 — edited Feb 10 2003I am creating a small search program using jsp. I want to know how can I implement paging using JDBC.
It is very easy using asp. In asp only a specified no. or records can be retrieved from database using combination of PageSize and AbsolutePage properties of Recordset Object.
In jdbc I think there is no such method available. Do I have to use database base specific options to limit the number of records returned like limit keyword in mysql.
Only way I can think of is to create a resultset object and store it in session and then use the absolute() method of the resultset object to go to a specific record and then next(). But still all records have to returned by db and have to be kept in memory and not only specific no. of records (e.g 10 per page).
If I doesn't store the recordset in session then every time this search page is accessed, a new recordset is returned with all records. I then have to determine how many records to skip according to page selected in the query string and then use absolute() method of recordset to go to specific record. And then loop, say ten times to display 10 records. Again when next is clicked same procedure is repeated. Here retrieving all records every time page is accessed is very expensive operation.
In my case even this isn't possible because I am using jdbc odbc bridge, which is not allowing scrollable resultset and jvm is throwing exception in native code. Without scrollable resultset I cannot know how many records are returned. If I try to count using a loop and resultset's next() then I don't have any way to go back to first record again when I actually want to retrieve records to display. Only other way to count the records is executing query twice which isn't very good as records may have been added or deleted between two calls.
Am I wrong anywhere above?
If not then question is, can this be done efficiently with jdbc without using any database specific keywords?
If yes then which method can be used on a busy web site with huge database.
--Sukhwinder Singh