Chapter 7. Java Programming

Knowledge of JDBC/SQLJ object model

[Note]

Interface ResultSet

A table of data representing a database result set, which is usually generated by executing a statement that queries the database.

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next() method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

A default ResultSet object is NOT updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options.

 Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");
// rs will be scrollable, will not show changes made by others, and will be updatable
					

The ResultSet interface provides getter methods (getBoolean, getLong, and so on) 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.

For the getter methods, a JDBC driver attempts to convert the underlying data to the Java type specified in the getter method and returns a suitable Java value. The JDBC specification has a table showing the allowable mappings from SQL types to Java types that can be used by the ResultSet getter methods.

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 will be 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 programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.

A set of updater methods were added to this interface in the JDBC 2.0 API.

The updater methods may be used in two ways:

  1. to update a column value in the current row. In a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row. The following code fragment updates the NAME column in the fifth row of the ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived:

    rs.absolute(5); // moves the cursor to the fifth row of rs
    rs.updateString("NAME", "AINSWORTH"); // updates the NAME column of row 5 to be AINSWORTH
    rs.updateRow(); // updates the row in the data source
    								

  2. to insert column values into the insert row. An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it into rs and into the data source table using the method insertRow:

    rs.moveToInsertRow(); // moves cursor to the insert row
    rs.updateString(1, "AINSWORTH"); // updates the first column of the insert row to be AINSWORTH
    rs.updateInt(2,35); // updates the second column to be 35
    rs.updateBoolean(3, true); // updates the third column to true
    rs.insertRow();
    rs.moveToCurrentRow();
    								

    A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

    The number, types and properties of a ResultSet object's columns are provided by the ResulSetMetaData object returned by the ResultSet.getMetaData method.

ResultSet fields summary:

static int 	CLOSE_CURSORS_AT_COMMIT
          The constant indicating that open ResultSet objects with this holdability will be closed when the current 
          transaction is commited.
          
static int 	CONCUR_READ_ONLY
          The constant indicating the concurrency mode for a ResultSet object that may NOT be updated.
          
static int 	CONCUR_UPDATABLE
          The constant indicating the concurrency mode for a ResultSet object that may be updated.
          
static int 	FETCH_FORWARD
          The constant indicating that the rows in a result set will be processed in a forward direction; first-to-last.
          
static int 	FETCH_REVERSE
          The constant indicating that the rows in a result set will be processed in a reverse direction; last-to-first.
          
static int 	FETCH_UNKNOWN
          The constant indicating that the order in which rows in a result set will be processed is unknown.
          
static int 	HOLD_CURSORS_OVER_COMMIT
          The constant indicating that open ResultSet objects with this holdability will remain open when the current 
          transaction is commited.
          
static int 	TYPE_FORWARD_ONLY
          The constant indicating the type for a ResultSet object whose cursor may move only forward.
          
static int 	TYPE_SCROLL_INSENSITIVE
          The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to 
          changes to the data that underlies the ResultSet.
          
static int 	TYPE_SCROLL_SENSITIVE
          The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes 
          to the data that underlies the ResultSet.
					

Interface Statement

The object used for executing a static SQL statement and returning the results it produces.

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

Statement methods summary:

ResultSet executeQuery(String sql)
          Executes the given SQL statement, which returns a single ResultSet object.
          
int executeUpdate(String sql)
          Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE 
          statement or an SQL statement that returns nothing, such as an SQL DDL 
          statement.
          
SQLWarning getWarnings()
          Retrieves the first warning reported by calls on this Statement object.
					

Interface PreparedStatement

An object that represents a precompiled SQL statement.

A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

Note: The setter methods (setShort, setString, and so on) for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter. For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used.

If arbitrary parameter type conversions are required, the method setObject should be used with a target SQL type.

In the following example of setting a parameter, con represents an active connection:

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00)
pstmt.setInt(2, 110592)
					

PreparedStatement methods summary:

boolean execute()
          Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.
          
ResultSet executeQuery()
          Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.
          
int executeUpdate()
          Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language 
          (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL 
          statement.
					

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     SCDJWS 5.0 Quiz