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. (JDBC4 clarification:) 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 (JavaTM 2 SDK, Standard Edition, version 1.2). The comments regarding parameters to the getter methods also apply to parameters to the updater methods.
The updater methods may be used in two ways:
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 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 beAINSWORTHrs.updateInt(2,35); // updates the second column to be35rs.updateBoolean(3, true); // updates the third column totruers.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.
A ResultSet object generated by HSQLDB is by default of ResultSet.TYPE_FORWARD_ONLY (as is standard JDBC behavior) and does not allow the use of absolute and relative positioning methods. If a statement is created with:
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);then the
ResultSet objects it produces support using all of the absolute and relative positioning methods of JDBC2 to set the position of the current row, for example:
rs.absolute(5); String fifthRowValue = rs.getString(1); rs.relative(4); String ninthRowValue = rs.getString(1);Note: An HSQLDB
ResultSet object persists, even after its connection is closed. This is regardless of the operational mode of the {@link org.hsqldb.Database Database} from which it came. That is, theypersist whether originating from a Server, WebServer or in-process mode Database. From HSQLDB 2.0, there is full support for updatable result sets. Supported methods include all updateXXX methods for the supported types, as well as the {@link #insertRow}, {@link #updateRow}, {@link #deleteRow}, {@link #moveToInsertRow}methods.
The Statement must be created with ResultSet.CONCUR_UPDATABLE instead of CONCUR_READ_ONLY.
Updatability of a result set follows the SQL standards. Some or all columns of an updatable result set can be updated. The current row in such result sets can be deleted using the {@link #deleteRow} method. Some updatable result setcan also be inserted into and support {@link #moveToInsertRow}.
A result set is updatable if the SELECT statement is updatable. This includes SELECT from TABLE and updatable VIEW objects. An updatable SELECT statement has a single uderlying table or view. HSQLDB supports both scrollable and forward-only result sets for updatability.
-- In the SELECT below, columns A and B are updatable, any row can be -- deleted, but it is not insertable-into as column C is not directly from -- the table. SELECT A, B, A + B AS C FROM T WHERE ... -- The SELECT below can be insertable-into so long as other columns of the -- table that do not appear in the SELECT list have a default value. SELECT A, B FROM T WHERE ...JRE 1.1.x Notes:
In general, JDBC 2 support requires Java 1.2 and above, and JDBC 3 requires Java 1.4 and above. In HSQLDB, support for methods introduced in different versions of JDBC depends on the JDK version used for compiling and building HSQLDB.
Since 1.7.0, it is possible to build the product so that all JDBC 2 methods can be called while executing under the version 1.1.x Java Runtime EnvironmentTM. However, some of these method calls require int values that are defined only in the JDBC 2 or greater version of the {@link java.sql.ResultSet ResultSet} interface. For this reason, when theproduct is compiled under JDK 1.1.x, these values are defined here, in this class.
In a JRE 1.1.x environment, calling JDBC 2 methods that take or return the JDBC2-only ResultSet values can be achieved by referring to them in parameter specifications and return value comparisons, respectively, as follows:
JDBCResultSet.FETCH_FORWARD JDBCResultSet.TYPE_FORWARD_ONLY JDBCResultSet.TYPE_SCROLL_INSENSITIVE JDBCResultSet.CONCUR_READ_ONLY // etc.However, please note that code written in such a manner will not be compatible for use with other JDBC 2 drivers, since they expect and use
ResultSet, rather than JDBCResultSet. Also note, this feature is offered solely as a convenience to developers who must work under JDK 1.1.x due to operating constraints, yet wish to use some of the more advanced features available under the JDBC 2 specification. (fredt@users)
(boucherb@users)
| |
| |
| |