7.5   PERFORM DATA MANIPULATION USING UPDATABLE RESULTSET

As we discussed in Section 6.3.3.4 in Chapter 6, a ResultSet object can be considered a Table of data representing a database result set, which is usually generated by executing a statement that queries the database.

The ResultSet interface provides getXXX() methods for retrieving column values from the current row. Values can be retrieved using either the index number of the column or the name of the column. In general, using the column index will be 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.

A default ResultSet object is not updaTable with a cursor that moves forward only. Thus, it is possible to iterate through it only once from the first row to the last row. New methods in the JDBC 4.0 API make it possible to produce ResultSet objects that are scrollable and/or updaTable.

Before we can use the ResultSet object to perform data manipulation against our sample data-base, let’s first get a clear picture of the ResultSet additional functionalities and categories supported in JDBC 4.0.

7.5.1   Introduction to ResultSet Enhanced Functionalities and Categories

ResultSet functionality in JDBC 4.0 includes enhancements for scrollability and positioning, sensi-tivity to changes by others and updatability.

  • Scrollability: the ability to move backward as well as forward through a ResultSet object. Associated with scrollability is the ability to move to any particular position in the ResultSet through either relative positioning or absolute positioning.
  • Positioning: the ability to move a specified number of rows forward or backward from the current row. Absolute positioning enables you to move to a specified row number, counting from either the beginning or the end of the ResultSet.
  • Sensitivity: the ability to see changes made to the database while the ResultSet is open, providing a dynamic view of the underlying data. Changes made to the underlying column values of rows in the ResultSet are visible.

Two parameters can be used to set up the properties of a ResultSet object when it is created:

ResultSet Type and Concurrency Type of a ResultSet. Table 7.5 lists these types and their functions. Under JDBC 4.0, the Connection class has the following methods that take a ResultSet type and a concurrency type as input to define a new created ResultSet object:

  • Statement createStatement(int resultSetType, int resultSetConcurrency)
  • PreparedStatementprepareStatement(Stringsql,intresultSetType,intresultSetConcurrency)
  • CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency)

You can specify one of the following static constant values for ResultSet type:

  • ResultSet.TYPE _ FORWARD _ ONLY
  • ResultSet.TYPE _ SCROLL _ INSENSITIVE
  • ResultSet.TYPE _ SCROLL _ SENSITIVE

And you can specify one of the following static constant values for concurrency type:

  • ResultSet.CONCUR _ READ _ ONLY
  • ResultSet.CONCUR _ UPDATABLE

The following code fragment, in which conn is a valid Connection object and ora is a defined query string, illustrates how to make a ResultSet that is scrollable, sensitive to updates by others and updaTable.

PreparedStatement pstmt=conn.prepareStatement (ora, ResultSet.TYPE _

SCROLL _ SENSITIVE,

ResultSet.CONCUR _ UPDATABLE);

Now that we have a basic understanding of the ResultSet and its enhanced functionalities, we can go ahead to perform data manipulation against our sample database using the UpdaTable ResultSet object.