Scrollable Result Sets

From Apache OpenOffice Wiki
Jump to: navigation, search



The interface com.sun.star.sdbc.XResultSet offers methods to move the cursor back and forth to an arbitrary row, and get the current position of the cursor. Scrollable result sets are necessary to create GUI tools that can browse result sets. It also may be required to move a specific row to work with it. Before taking advantage of these features, create a scrollable ResultSet object. The following lines of code illustrate one way to create a scrollable ResultSet object:

  XStatement xStatement = xConnection.createStatement();
  XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class, xStatement);
 
  xProp.setPropertyValue("ResultSetType",new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
  xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.UPDATABLE));
 
  XResultSet xResult = xStatement.executeQuery("SELECT FIRSTNAME, LASTNAME FROM SALES");

This code is similar to what was used earlier, except that it sets two property values at the Statementobject. These properties have to be set before the statement is executed.

The value of the property ResultSetType must be one of three constants defined in com.sun.star.sdbc.ResultSetType: FORWARD_ONLY, SCROLL_INSENSITIVE and SCROLL_SENSITIVE.

The property ResultSetConcurrency must be one out of the two com.sun.star.sdbc.ResultSetConcurrency constants READ_ONLY and UPDATABLE. When a ResultSetType is specified, it must be specified if it is read-only or modifiable.

If any constants for the type and modifiability of a ResultSet object are not specified, FORWARD_ONLY and READ_ONLY will automatically be created.

Specifying the constant FORWARD_ONLY creates a non-scrollable result set, that is, the cursor moves forward only. A scrollable ResultSet is obtained by specifying SCROLL_INSENSITIVE or SCROLL_SENSITIVE. Sensitive or insensitive refers to changes made to the underlying data after the result set has been opened. A SCROLL_INSENSITIVE result set does not reflect changes to the underlying data, while a SCROLL_SENSITIVE result set shows changes. However, not all drivers and databases support change sensitivity.

In scrollable result sets, the counterpart to next() is the method previous(), which moves the cursor backward. Both methods return false when the cursor goes to the position after the last row or before the first row. This allows them to be used in a while loop.

The following two examples show the usage of next() and previous() together with while:

  XStatement stmt = con.createStatement();
 
  XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class, stmt);
  xProp.setPropertyValue("ResultSetType",new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
  xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY));
 
  XResultSet srs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
 
  XRow row = (XRow)UnoRuntime.queryInterface(XRow.class, srs);
 
  while (srs.next()) {
      String name = row.getString(1);
      float price = row.getFloat(2);
      System.out.println(name + " " + price);
  }

The printout will look similar to this:

 Linux           32
 Beef            15.78
 Orange juice    1.50

To process the rows going backward, the cursor must start out after the last row. The cursor is moved to the position after the last row with the method afterLast(). Then previous() moves the cursor from the position after the last row to the last row, and then up to the first row with each iteration through the while loop. The loop ends when the cursor reaches the position before the first row, where previous() returns false.

  XStatement stmt = con.createStatement();
 
  XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class,stmt);
  xProp.setPropertyValue("ResultSetType", new java.lang.Integer(ResultSetType.SCROLL_INSENSITIVE));
  xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY));
 
  XResultSet srs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
 
  XRow row = (XRow)UnoRuntime.queryInterface(XRow.class, srs);
 
  srs.afterLast();
  while (srs.previous()) {
      String name = row.getString(1);
      float price = row.getFloat(2);
      System.out.println(name + " " + price);
  }

The printout will look similar to this:

 Orange juice    1.50
 Beef            15.78
 Linux           32

The column values are the same, but the rows are in the reverse order.

The cursor can be moved to a specific row in a ResultSet object. The methods first(), last(), beforeFirst(), and afterLast() move the cursor to the row indicated by the method names.

The method absolute() moves the cursor to the row number indicated in the argument passed. If the number is positive, the cursor moves the given number from the beginning. Calling absolute(1) moves the cursor to the first row. If the number is negative, the cursor moves the given number of rows from the end. Calling absolute(-1) sets the cursor to the last row. The following line of code moves the cursor to the fourth row of srs:

  srs.absolute(4);

If srs has 500 rows, the following line of code moves the cursor to row 497:

  srs.absolute(-4);

The method relative() moves the cursor by an arbitrary number of rows from the current row. A positive number moves the cursor forward, and a negative number moves the cursor backwards. For example, in the following code fragment, the cursor moves to the fourth row, then to the first row, and finally to the third row:

  srs.absolute(4); // cursor is on the fourth row
  ... 
  srs.relative(-3); // cursor is on the first row
  ... 
  srs.relative(2); // cursor is on the third row

The method getRow() returns the number of the current row. For example, use getRow() to verify the current position of the cursor in the previous example using the following code:

  srs.absolute(4); 
  int rowNum = srs.getRow(); // rowNum should be 4
  srs.relative(-3); 
  rowNum = srs.getRow(); // rowNum should be 1
  srs.relative(2); 
  rowNum = srs.getRow(); // rowNum should be 3

Note that some drivers do not support the getRow method. They always return 0.

There are four methods to verify if the cursor is at a particular position. The position is stated in their names: isFirst(), isLast(), isBeforeFirst(), and isAfterLast(). These methods return a boolean that can be used in a conditional statement. For example, the following code fragment tests if the cursor is after the last row before invoking the method previous() in a while loop. If the method isAfterLast() returns false, the cursor is not after the last row, so the method afterLast can be invoked. This guarantees that the cursor is after the last row and that using the method previous() in the while loop stop at every row in srs.

  if (!srs.isAfterLast()) {
      srs.afterLast(); 
  }
  while (srs.previous()) {
      String name = row.getString(1);
      float price = row.getFloat(2);
      System.out.println(name + " " + price);
  }

How to use the two methods from the XResultSetUpdate interface to move the cursor: moveToInsertRow() and moveToCurrentRow() are discussed in the next section. There are examples illustrating why moving the cursor to certain positions may be required.

Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages