Pagination JDBC

Discussions

General J2EE: Pagination JDBC

  1. Pagination JDBC (9 messages)

    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?

    Threaded Messages (9)

  2. scrollable resultset :([ Go to top ]

    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)...
  3. //---------------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
  4. 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.........

     

     

     

     

  5. Pagination JDBC[ Go to top ]

    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
  6. Pagination JDBC[ Go to top ]

    Have you checked out Sun's Rowset Implementation 1.0.1 ? The CachedRowset implementation contains paging functionality. Let us know how that works.
  7. 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();
    //---------
  8. I find a key reason! maybe[ Go to top ]

    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!
  9. cached[ Go to top ]

    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"
  10. cached[ Go to top ]

    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 ?