JDBC:Retrieving and Modifying Values from Result Sets

Retrieving and Modifying Values from Result Sets

The following method, CoffeesTable.viewTable outputs the contents of the COFFEES tables, and demonstrates the use of ResultSet objects and cursors:

  public static void viewTable(Connection con) throws SQLException {
    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from " + dbName + ".COFFEES";
    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        String coffeeName = rs.getString("COF_NAME");
        int supplierID = rs.getInt("SUP_ID");
        float price = rs.getFloat("PRICE");
        int sales = rs.getInt("SALES");
        int total = rs.getInt("TOTAL");
        System.out.println(coffeeName + "\t" + supplierID + "\t" + price + "\t" + sales + "\t" + total);
      }
    } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

A ResultSet object is a table of data representing a database result set, which is usually generated by executing a statement that queries the database. For example, the CoffeeTables.viewTable method creates a ResultSet, rs, when it executes the query through the Statement object, stmt. Note that a ResultSet object can be created through any object that implements the Statement interface, including PreparedStatement, CallableStatement, and RowSet.
You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet. Initially, the cursor is positioned before the first row. The method ResultSet.next moves the cursor to the next row. This method returns false if the cursor is positioned after the last row. This method repeatedly calls the ResultSet.next method with a while loop to iterate through all the data in the ResultSet.

ResultSet Interface

The ResultSet interface provides methods for retrieving and manipulating the results of executed queries, and ResultSet objects can have different functionality and characteristics. These characteristics are type, concurrency, and cursor holdability.

ResultSet Types

The type of a ResultSet object determines the level of its functionality in two areas: the ways in which the cursor can be manipulated, and how concurrent changes made to the underlying data source are reflected by the ResultSet object.
The sensitivity of a ResultSet object is determined by one of three different ResultSet types:

• TYPE_FORWARD_ONLY: The result set cannot be scrolled; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database generates the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.
• TYPE_SCROLL_INSENSITIVE: The result cannot be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set is insensitive to changes made to the underlying data source while it is open. It contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.
• TYPE_SCROLL_SENSITIVE: The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set reflects changes made to the underlying data source while the result set remains open.

The default ResultSet type is TYPE_FORWARD_ONLY.

ResultSet Concurrency

The concurrency of a ResultSet object determines what level of update functionality is supported.
There are two concurrency levels:

• CONCUR_READ_ONLY: The ResultSet object cannot be updated using the ResultSet interface.
• CONCUR_UPDATABLE: The ResultSet object can be updated using the ResultSet interface.

The default ResultSet concurrency is CONCUR_READ_ONLY.
Note: Not all JDBD drivers and databases support concurrency. The method DatabaseMetaData.supportsResultSetConcurrency returns true if the specified concurrency level is supported by the driver and false otherwise.
The method CoffeesTable.modifyPrices demonstrates how to use a ResultSet object whose concurrency level is CONCUR_UPDATABLE.

Cursor Holdability

Calling the method Connection.commit can close the ResultSet objects that have been created during the current transaction. In some cases, however, this may not be the desired behavior. The ResultSet property holdability gives the application control over whether ResultSet objects (cursors) are closed when commit is called.
The following ResultSet constants may be supplied to the Connection methods createStatement, prepareStatement, and prepareCall:

• HOLD_CURSORS_OVER_COMMIT: ResultSet cursors are not closed; they are holdable: they are held open when the method commit is called. Holdable cursors might be ideal if your application uses mostly read-only ResultSet objects.
• CLOSE_CURSORS_AT_COMMIT: ResultSet objects (cursors) are closed when the commit method is called. Closing cursors when this method is called can result in better performance for some applications.

The default cursor holdability varies depending on your DBMS.
Note: Not all JDBD drivers and databases support holdable and non-holdable cursors. The following method, JDBCTutorialUtilities.cursorHoldabilitySupport, outputs the default cursor holdability of ResultSet objects and whether HOLD_CURSORS_OVER_COMMIT and CLOSE_CURSORS_AT_COMMIT are supported:

  public static void cursorHoldabilitySupport(Connection conn) throws SQLException {
    DatabaseMetaData dbMetaData = conn.getMetaData();
    System.out.println("ResultSet.HOLD_CURSORS_OVER_COMMIT = " + ResultSet.HOLD_CURSORS_OVER_COMMIT);
    System.out.println("ResultSet.CLOSE_CURSORS_AT_COMMIT = " + ResultSet.CLOSE_CURSORS_AT_COMMIT);
    System.out.println("Default cursor holdability: " +
      dbMetaData.getResultSetHoldability());
    System.out.println("Supports HOLD_CURSORS_OVER_COMMIT? " +
      dbMetaData.supportsResultSetHoldability(
        ResultSet.HOLD_CURSORS_OVER_COMMIT));
    System.out.println("Supports CLOSE_CURSORS_AT_COMMIT? " +
      dbMetaData.supportsResultSetHoldability(
        ResultSet.CLOSE_CURSORS_AT_COMMIT));
  }

Retrieving Column Values from Rows

The ResultSet interface declares getter methods (for example, getBoolean and getLong) for retrieving column values from the current row. You can retrieve values using either the index number of the column or the name of the column. The column index is usually more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.
For example, the following method, CoffeesTable.alternateViewTable, retrieves column values by number:

  public static void alternateViewTable(Connection con) throws SQLException {
    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        String coffeeName = rs.getString(1);
        int supplierID = rs.getInt(2);
        float price = rs.getFloat(3);
        int sales = rs.getInt(4);
        int total = rs.getInt(5);
        System.out.println(coffeeName + "\t" + supplierID + "\t" + price + "\t" + sales + "\t" + total);
      }
    } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

Column names used as input to getter methods are case-insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column is returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are not explicitly named in the query, it is best to use column numbers. If column names are used, the developer should guarantee that they uniquely refer to the intended columns, which can be ensured by using the SQL AS clause.
The getter method of the appropriate type retrieves the value in each column. For example, in the method CoffeeTables.viewTable, the first column in each row of the ResultSet rs is COF_NAME, which stores a value of SQL type VARCHAR. The method for retrieving a value of SQL type VARCHAR is getString. The second column in each row stores a value of SQL type INTEGER, and the method for retrieving values of that type is getInt.
Note that although the method getString is recommended for retrieving the SQL types CHAR and VARCHAR, it is possible to retrieve any of the basic SQL types with it. Getting all values with getString can be very useful, but it also has its limitations. For instance, if it is used to retrieve a numeric type, getString converts the numeric value to a Java String object, and the value has to be converted back to a numeric type before it can be operated on as a number. In cases where the value is treated as a string anyway, there is no drawback. Furthermore, if you want an application to retrieve values of any standard SQL type other than SQL3 types, use the getString method.

Cursors

As mentioned previously, you access the data in a ResultSet object through a cursor, which points to one row in the ResultSet object. However, when a ResultSet object is first created, the cursor is positioned before the first row. The method CoffeeTables.viewTable moves the cursor by calling the ResultSet.next method. There are other methods available to move the cursor:

• next: Moves the cursor forward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row.
• previous: Moves the cursor backward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row.
• first: Moves the cursor to the first row in the ResultSet object. Returns true if the cursor is now positioned on the first row and false if the ResultSet object does not contain any rows.
• last:: Moves the cursor to the last row in the ResultSet object. Returns true if the cursor is now positioned on the last row and false if the ResultSet object does not contain any rows.
• beforeFirst: Positions the cursor at the start of the ResultSet object, before the first row. If the ResultSet object does not contain any rows, this method has no effect.
• afterLast: Positions the cursor at the end of the ResultSet object, after the last row. If the ResultSet object does not contain any rows, this method has no effect.
• relative(int rows): Moves the cursor relative to its current position.
• absolute(int row): Positions the cursor on the row specified by the parameter row.

Note that the default sensitivity of a ResultSet is TYPE_FORWARD_ONLY, which means that it cannot be scrolled; you cannot call any of these methods that move the cursor, except next, if your ResultSet cannot be scrolled. The method CoffeesTable.modifyPrices, described in the following section, demonstrates how you can move the cursor of a ResultSet.

Updating Rows in ResultSet Objects

You cannot update a default ResultSet object, and you can only move its cursor forward. However, you can create ResultSet objects that can be scrolled (the cursor can move backwards or move to an absolute position) and updated.
The following method, CoffeesTable.modifyPrices, multiplies the PRICE column of each row by the argument percentage:

  public void modifyPrices(float percentage) throws SQLException {
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt = con.createStatement(
        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet uprs = stmt.executeQuery(
        "SELECT * FROM " + dbName + ".COFFEES");

      while (uprs.next()) {
        float f = uprs.getFloat("PRICE");
        uprs.updateFloat("PRICE", f * percentage);
        uprs.updateRow();
      }

    } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

The field ResultSet.TYPE_SCROLL_SENSITIVE creates a ResultSet object whose cursor can move both forward and backward relative to the current position and to an absolute position. The field ResultSet.CONCUR_UPDATABLE creates a ResultSet object that can be updated. See the ResultSet Javadoc for other fields you can specify to modify the behavior of ResultSet objects.
The method ResultSet.updateFloat updates the specified column (in this example, PRICE with the specified float value in the row where the cursor is positioned. ResultSet contains various updater methods that enable you to update column values of various data types. However, none of these updater methods modifies the database; you must call the method ResultSet.updateRow to update the database.

Using Statement Objects for Batch Updates

Statement, PreparedStatement and CallableStatement objects have a list of commands that is associated with them. This list may contain statements for updating, inserting, or deleting a row; and it may also contain DDL statements such as CREATE TABLE and DROP TABLE. It cannot, however, contain a statement that would produce a ResultSet object, such as a SELECT statement. In other words, the list can contain only statements that produce an update count.
The list, which is associated with a Statement object at its creation, is initially empty. You can add SQL commands to this list with the method addBatch and empty it with the method clearBatch. When you have finished adding statements to the list, call the method executeBatch to send them all to the database to be executed as a unit, or batch.
For example, the following method CoffeesTable.batchUpdate adds four rows to the COFFEES table with a batch update:

  public void batchUpdate() throws SQLException {

    Statement stmt = null;
    try {

      this.con.setAutoCommit(false);
      stmt = this.con.createStatement();

      stmt.addBatch("INSERT INTO COFFEES " +
               "VALUES('Amaretto', 49, 9.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
               "VALUES('Hazelnut', 49, 9.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
               "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
      stmt.addBatch("INSERT INTO COFFEES " +
               "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");

      int [] updateCounts = stmt.executeBatch();
      this.con.commit();

    } catch(BatchUpdateException b) {
      JDBCTutorialUtilities.printBatchUpdateException(b);
    } catch(SQLException ex) {
      JDBCTutorialUtilities.printSQLException(ex);
    } finally {
      this.con.setAutoCommit(true);
      stmt.close();
    }
  }

The following line disables auto-commit mode for the Connection object con so that the transaction will not be automatically committed or rolled back when the method executeBatch is called.

      this.con.setAutoCommit(false);

To allow for correct error handling, you should always disable auto-commit mode before beginning a batch update.
The method Statement.addBatch adds a command to the list of commands associated with the Statement object stmt. In this example, these commands are all INSERT INTO statements, each one adding a row consisting of five column values. The values for the columns COF_NAME and PRICE are the name of the coffee and its price, respectively. The second value in each row is 49 because that is the identification number for the supplier, Superior Coffee. The last two values, the entries for the columns SALES and TOTAL, all start out being zero because there have been no sales yet. (SALES is the number of pounds of this row's coffee sold in the current week; TOTAL is the total of all the cumulative sales of this coffee.)
The following line sends the four SQL commands that were added to its list of commands to the database to be executed as a batch:

      int [] updateCounts = stmt.executeBatch();

Note that stmt uses the method executeBatch to send the batch of insertions, not the method executeUpdate, which sends only one command and returns a single update count. The DBMS executes the commands in the order in which they were added to the list of commands, so it will first add the row of values for Amaretto, then add the row for Hazelnut, then Amaretto decaf, and finally Hazelnut decaf. If all four commands execute successfully, the DBMS will return an update count for each command in the order in which it was executed. The update counts that indicate how many rows were affected by each command are stored in the array updateCounts.
If all four of the commands in the batch are executed successfully, updateCounts will contain four values, all of which are 1 because an insertion affects one row. The list of commands associated with stmt will now be empty because the four commands added previously were sent to the database when stmt called the method executeBatch. You can at any time explicitly empty this list of commands with the method clearBatch.
The Connection.commit method makes the batch of updates to the COFFEES table permanent. This method needs to be called explicitly because the auto-commit mode for this connection was disabled previously.
The following line enables auto-commit mode for the current Connection object.

      this.con.setAutoCommit(true);

Now each statement in the example will automatically be committed after it is executed, and it no longer needs to invoke the method commit.

Performing Parameterized Batch Update

It is also possible to have a parameterized batch update, as shown in the following code fragment, where con is a Connection object:

  con.setAutoCommit(false);
  PreparedStatement pstmt = con.prepareStatement(
                   "INSERT INTO COFFEES VALUES(
                                ?, ?, ?, ?, ?)");
  pstmt.setString(1, "Amaretto");
  pstmt.setInt(2, 49);
  pstmt.setFloat(3, 9.99);
  pstmt.setInt(4, 0);
  pstmt.setInt(5, 0);
  pstmt.addBatch();

  pstmt.setString(1, "Hazelnut");
  pstmt.setInt(2, 49);
  pstmt.setFloat(3, 9.99);
  pstmt.setInt(4, 0);
  pstmt.setInt(5, 0);
  pstmt.addBatch();

  // ... and so on for each new type of coffee

  int [] updateCounts = pstmt.executeBatch();
  con.commit();
  con.setAutoCommit(true);

Handling Batch Update Exceptions

You will get a BatchUpdateException when you call the method executeBatch if (1) one of the SQL statements you added to the batch produces a result set (usually a query) or (2) one of the SQL statements in the batch does not execute successfully for some other reason.
You should not add a query (a SELECT statement) to a batch of SQL commands because the method executeBatch, which returns an array of update counts, expects an update count from each SQL statement that executes successfully. This means that only commands that return an update count (commands such as INSERT INTO, UPDATE, DELETE) or that return 0 (such as CREATE TABLE, DROP TABLE, ALTER TABLE) can be successfully executed as a batch with the executeBatch method.
A BatchUpdateException contains an array of update counts that is similar to the array returned by the method executeBatch. In both cases, the update counts are in the same order as the commands that produced them. This tells you how many commands in the batch executed successfully and which ones they are. For example, if five commands executed successfully, the array will contain five numbers: the first one being the update count for the first command, the second one being the update count for the second command, and so on.
BatchUpdateException is derived from SQLException. This means that you can use all of the methods available to an SQLException object with it. The following method, JDBCTutorialUtilities.printBatchUpdateException prints all of the SQLException information plus the update counts contained in a BatchUpdateException object. Because BatchUpdateException.getUpdateCounts returns an array of int, the code uses a for loop to print each of the update counts:

  public static void printBatchUpdateException(BatchUpdateException b) {
    System.err.println("----BatchUpdateException----");
    System.err.println("SQLState:  " + b.getSQLState());
    System.err.println("Message:  " + b.getMessage());
    System.err.println("Vendor:  " + b.getErrorCode());
    System.err.print("Update counts:  ");
    int [] updateCounts = b.getUpdateCounts();
    for (int i = 0; i < updateCounts.length; i++) {
      System.err.print(updateCounts[i] + "   ");
    }
  }

Inserting Rows in ResultSet Objects

Note: Not all JDBD drivers support inserting new rows with the ResultSet interface. If you attempt to insert a new row and your JDBC driver database does not support this feature, a SQLFeatureNotSupportedException exception is thrown.
The following method, CoffeesTable.insertRow, inserts a row into the COFFEES through a ResultSet object:

  public void insertRow(String coffeeName, int supplierID, float price, int sales, int total)
    throws SQLException {
    Statement stmt = null;
    try {
      stmt = con.createStatement();
      stmt = con.createStatement(
        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet uprs = stmt.executeQuery(
        "SELECT * FROM " + dbName + ".COFFEES");

      uprs.moveToInsertRow();

      uprs.updateString("COF_NAME", coffeeName);
      uprs.updateInt("SUP_ID", supplierID);
      uprs.updateFloat("PRICE", price);
      uprs.updateInt("SALES", sales);
      uprs.updateInt("TOTAL", total);

      uprs.insertRow();
      uprs.beforeFirst();

    } catch (SQLException e ) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
      stmt.close();
    }
  }

The method ResultSet.moveToInsertRow moves the cursor to the insert row. The insert row is a special row associated with an updatable result set. It is essentially a buffer where a new row can be constructed by calling the updater methods prior to inserting the row into the result set. For example, this method calls the method ResultSet.updateString to update the insert row's COF_NAME column to Kona.
The method ResultSet.insertRow inserts the contents of the insert row into the ResultSet object and into the database.