Paginating Results: skipRows and maxRows

In the examples shown so far, the XML-SQL Utility (XSU) takes the ResultSet or the query and generates the whole document from all the rows of the query. For getting say, 100 rows at a time, the user would then have to fire off different queries to get the first 100 rows, the next 100 and so on. Also it is not possible to skip say the first 5 rows of the query and then generate the Result. For getting these desired results, use XSU's skipRows and maxRows settings.

The skipRows parameter when set will force the generation to skip the desired number of rows before starting to generate the result. The maxRows on the other hand, would limit the number of rows that are converted to XML. If you set the skipRows to a value of 5 and maxRows to a value of 10, then the utility would skip the first 5 rows, and then generate the XML for the next 10 rows.

Keeping the Object Open

In web scenarios, you might want to keep the query object open for the duration of the user's session. For example, take the case of a web search engine which gives the results of a user's search in a paginated fashion. The first page lists 10 results, the next page lists 10 more results and so on. To achieve this, ask the utility to convert 10 rows at a time and to keep the ResultSet state alive, so that the next time we ask it for more results, it will start generating from the place the last generation finished.

When the Number of Rows or Columns in a Row Are Too Large

There is also the case that the number of rows, or the number of columns in a row may be very large. In this case, you can generate multiple documents each of a smaller size.

These cases can be handled by using the maxRows parameter and using the keepObjectOpen functionality.

keepObjectOpen Function

Typically, as soon as all results are generated, OracleXMLQuery internally closes the ResultSet, if it created one using the SQL query string given, since it assumes you no longer want any more results. However, in the case described above, we need to maintain that state, so we need to call the keepObjectOpen function to keep the cursor alive.

XSU Example 3. Paginating Results: Generating an XML Page When Called (Java)

The following example, writes a simple class which maintains the state and generates the next page every time it is called.

import org.w3c.dom.*;
import oracle.xml.parser.v2.*;
import java.sql.*;
import oracle.xml.sql.query.OracleXMLQuery;
import java.io.*;
public class pageTest
{
   Connection conn;
   OracleXMLQuery qry;
   ResultSet rset;
   Statement stmt;
   int lastRow = 0;

   public pageTest(String sqlQuery)
   {
     try{
     conn  = getConnection("scott","tiger");
     //stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
     //              ResultSet.CONCUR_READ_ONLY);// create a scrollable Rset     
     //stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
     //              ResultSet.CONCUR_READ_ONLY);// create a scrollable Rset     
     stmt = conn.createStatement();
     ResultSet rset = stmt.executeQuery(sqlQuery);  // get the result set..
     rset.first();
     qry = new OracleXMLQuery(conn,rset);   // create a OracleXMLQuery instance
     qry.keepCursorState(true); // Don't lose state after the first fetch
     qry.setRaiseNoRowsException(true);
     qry.setRaiseException(true);
     }catch(SQLException e){
      System.out.println(e.toString());
     }
   }
  
   // Returns the next XML page..!
   public String getResult(int startRow, int endRow)  throws SQLException
   {
     //rset.relative(lastRow-startRow);  // scroll inside the result set
     //rset.absolute(startRow);  // scroll inside the result set
     qry.setMaxRows(endRow-startRow); // set the max # of rows to retrieve..!
     //System.out.println("before getxml");
     return qry.getXMLString();
   }

   // Function to still perform the next page. 
   public String nextPage() throws SQLException
   {
     String result = getResult(lastRow,lastRow+10);
     lastRow+= 10;
     return result;
   }

   public void close() throws SQLException
   {
     stmt.close();   // close the statement..
     conn.close();   // close the connection
     qry.close();    // close the query..
   }
  
   public static void main(String[] argv)
   {
     String str;

     try{
     pageTest test = new pageTest("select e.* from emp e");

     int i = 0;
     // Get the data one page at a time..!!!!!
     while ((str = test.getResult(i,i+10))!= null)
     {
         System.out.println(str);
         i+= 10;
     }
     test.close();
    }catch(Exception e){
     e.printStackTrace(System.out);
    }
   }
 // Get the connection given the user name and password..!
    private static Connection getConnection(String user, String passwd)
      throws SQLException
    {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      Connection conn =
          DriverManager.getConnection("jdbc:oracle:oci8:@",user,passwd);
     return conn;
   }

}