I need to implement pagination for huge amount of records from Oracle 9i db. What's the best way to do it? Are there any readymade classes available for the same? Is it good practice to use scrollable resultset?

unfortunatly the scrollable resultset still reads the values from the database:( Better to use a nested inline view to limit the results:

SELECT * FROM
(
  SELECT ...
)
WHERE rownum between (20, 40)...

//---------------My CODE------------------------//
CachedRowSet crs = new CachedRowSetImpl();
DBConnector connector=new DBConnector("conf/conf.properties");
Connection con=connector.getConnection();// getting database conncection
    
String sql="Select * from "+TablName;
crs.setPageSize(10);
crs.setCommand(sql);
crs.execute(con);
int row = 0;
int page = 0;
 while(crs.nextPage()) -------------->Error Line
    {
      while(crs.next())
      {
        row++;
        String col1 = crs.getString("CustomerID");
        System.out.println(col1 + row + page );
      }
           page++;
       }

       crs.close();
       con.close();

  }
//---------------My CODE------------------------//

//---------------Exception :
java.sql.SQLException: Populate data before calling
at com.sun.rowset.CachedRowSetImpl.nextPage(Unknown Source)
at Test.main(Test.java:28)

---------------Exception ://



I am not getting why its giving error ,when i use without pagination it works fine

This error comes whenever u dont populate a RowSet and try to perform operations on it.

What u can do is

CachedRowSet crs = new CachedRowSetImpl();
DBConnector connector=new DBConnector("conf/conf.properties");
Connection con=connector.getConnection();// getting database conncection

Statement stmt=con.createStatement();

ResultSet rs=stmt.executeQuery();

crs.populate(rs);

con.close();

// Now u can perform operations on RowSet like

crs.setPageSize(10);
int row = 0;
int page = 0;
 while(crs.nextPage()) -------------->Error Line
    {
      while(crs.next())
      {
        row++;
        String col1 = crs.getString("CustomerID");
        System.out.println(col1 + row + page );
      }
           page++;
    }

I think this will solve ur problem.........

 

 

 

 

You can use application server caching option and avoid the database trip for paging. It also depends upon the application if the data is not updated by the other application you can write your own caching layer in which you cached the data and whenever there is any update in data update your cached data this approach will only be useful if your data is shared by different user and you are performing read option frequently

Have you checked out Sun's Rowset Implementation 1.0.1 ? The CachedRowset implementation contains paging functionality. Let us know how that works.

RowSet paging works for me in the following environment :
JDK1.4.2
Oracle 8i
ojdbc14.jar (It is available with Oracle 10g JDBC downloads. JDBC driver version is 10.1.0.2.0).
rowset.jar (Release 1.0.1 available from http://java.sun.com/products/jdbc/download.html)

The code sample provided at jdbc-rowset/docs/spec/index.html
doesn't seem to work
//------DOESNT SEEM TO WORK
CachedRowSet crs = CachedRowSetImpl();
crs.setPageSize(100);
crs.execute(conHandle);
  while(crs.next() {
   // operate on first chunk of 100 rows in crs, row by row
  }
  while(crs.nextPage()) {
    while(crs.next()) { //<--- ERROR ENCOUNTERED HERE
   // operate on the subsequent chunks (of 100 rows each) in crs,
   // row by row
   }
  }
//-----
The code sample that comes along with sun's rowset implementation download works just fine.
// ------ This WORKS
        CachedRowSet crs = new CachedRowSetImpl();
        crs.setPageSize(1000);
        crs.setCommand(sql);
        crs.execute(con);
        int row = 0;
        int page = 0;

        while(crs.nextPage()) {
            while(crs.next()) {
                row++;
                String col1 = crs.getString("COL1");
                System.out.println(col1 + row + page );
            }
            page++;
        }

        crs.close();
        con.close();
//---------

public int store_userpage() throws Exception {
// TODO 自动生成方法存根

Cache cache1 = CacheFactory.getInstance();
Connection connection = null;
CachedRowSet rs = new CachedRowSetImpl();

int k = 0;
try {
connection = datasource.getConnection();
PreparedStatement statement = connection.prepareStatement(sqls, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet resultSet = statement.executeQuery();

//rs.setMaxRows(20);
            rs.setPageSize(10);
            
            rs.populate(resultSet,1);

    
    

     //cache1.put(String.valueOf(k), rs);


while (rs.nextPage()) {
System.out.println("===== +++=====================================");
// cache1.put(String.valueOf(k), rs);
k++;
CachedRowSet rsclone=(CachedRowSet)rs.createShared();

cache1.put(String.valueOf(k), rsclone);
System.out.println("put into page:" + k);

if (rs != null) {
System.out.println("get from cache");
rs.beforeFirst();
while (rs.next()) {

System.out.println("one user:" + rs.getString("UserID") + "||"
+ rs.getString("UserName"));
}

} else
System.out.println("no user find!!");


}
return k ;



} catch (SQLException ex) {
ex.printStackTrace();
return 0;

} finally {
closeConnection(connection);

}

}

//--------above is my code,it can run--------

// step 1: you must create a "TYPE_SCROLL" resultset like below line!!!!
//----- PreparedStatement statement = connection.prepareStatement(sqls, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
//step 2: set rowset pagesize like below line!!
//----- rs.setPageSize(10);

//step 3: set rowset pagesize like below line!!
//----- rs.setPageSize(10);

//step 4:populate your resultset like below line!!;

// -----rs.populate(resultSet,1);

//caution!!DO not use populate(resultSet)!!I find this is a "sun engineer" 's Ugly joke!! You will populate whole resultSet ,no pages,I do not know why??

//I hope this reply will rescue some person from sun's puzzle!

Quote:
//I hope this reply will rescue some person from sun's puzzle!
End Quote:

It certainly did rescue me. I struggled for a while trying to make sense of it when it returned all the records without paging.

I crossed the first hurdle , hope there are no more surprises or developer's "ugly jokes"

Okay , I guess there are more surprises than I think.

I tries to close the connection after populating the CachedResultSet and tries to iterate the CRS and get the data. As per specs ( as I understand) , the CRS is supposed to establish a connection based on the parameters defined to get more pages. But it only displays the first page and shoes an error message that the ResultSet is closed.

I have also tried the second methos to populate , i.e. use the CRS.execute(conn) , this gives me the connection closed error if the close the connection after the execute.

Any one able to read all the pages by closing the connection ?