[D]NOTE: The material in this chapter is based on JDBCtm API Tutorial and Reference, Second Edition: Universal Data Access for the Javatm 2 Platform, published by Addison Wesley as part of the Java series, ISBN 0-201-43328-1.
A ResultSet is a Java object that contains the results of executing an SQL query. In other words, it contains the rows that satisfy the conditions of the query. The data stored in a ResultSet object is retrieved through a set of get methods that allows access to the various columns of the current row. The ResultSet.next method is used to move to the next row of the ResultSet, making it the current row.
The general form of a result set is a table with column headings and the corresponding values returned by a query. For example, if your query is SELECT a, b, c FROM Table1, your result set will have the following form:
a b c ---------- ------------ ----------- 12345 Cupertino 2459723.495 83472 Redmond 1.0 83492 Boston 35069473.43
The following code fragment is an example of executing an SQL statement that will return a collection of rows, with column a as an int, column b as a String, and column c as a float:
java.sql.Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
while (rs.next()) {
// retrieve and print the values for the current row
int i = rs.getInt("a");
String s = rs.getString("b");
float f = rs.getFloat("c");
System.out.println("ROW = " + i + " " + s + " " + f);
}
A relational database is made up of tables, with each table consisting of rows and columns. A row in a relational database table can be thought of as representing an instance of the entity that the table represents. For example, if there is a table of employees, each row will contain information about a particular employee. Each piece of data about the employee is stored in a column, so, for instance, the table of employees could have columns for an identification number, a name, a salary, and a date of hire. The columns in a row would contain the ID number, name, salary, and date of hire for a particular employee.
A result set is also a table with rows and columns, but it contains only the column values from a database table that satisfy the conditions of a query. In other words, a result set row will contain a subset of the columns in the underlying database table (unless the query selects everything in the table, in which case the result set table will include all of the column values for every row in the database table). In the past, a column value in a relational database table (and consequently in a result set table) had to be atomic; that is, it could be only one indivisible value. For instance, an array could not be a column value because an array may be made up of multiple elements. With the advent of SQL3 data types, however, the permissible content of table columns has expanded dramatically. It is now possible for an array or even a user-defined structured type to be a column value. Because this new capability allows a relational database to store instances of complex types as column values, it makes a relational database more like an object database, blurring the distinction between relational and object databases. Programmers can take advantage of these new data types if they use a JDBC 2.0 driver that supports SQL3 types.
A ResultSet object maintains a cursor, which points to its current row of data. The cursor moves down one row each time the method next is called. When a ResultSet object is first created, the cursor is positioned before the first row, so the first call to the next method puts the cursor on the first row, making it the current row. ResultSet rows can be retrieved in sequence from top to bottom as the cursor moves down one row with each successive call to the method next. This ability to move its cursor only forward is the default behavior for a ResultSet and is the only cursor movement possible with drivers that implement only the JDBC 1.0 API. This kind of result set has the type ResultSet.TYPE_FORWARD_ONLY and is referred to as a forward only result set.
If a driver implements the cursor movement methods in the JDBC 2.0 core API, its result sets can be scrollable. A scrollable result set's cursor can move both forward and backward as well as to a particular row. The following methods move the cursor backward, to the first row, to the last row, to a particular row number, to a specified number of rows from the current row, and so on: previous, first, last, absolute, relative, afterLast, and beforeFirst. An explanation and example of how to make a result set scrollable will be presented in the section "Creating Different Types of Result Sets" on page 55.
When a cursor is positioned on a row in a ResultSet object (not before the first row or after the last row), that row becomes the current row. This means that any methods called while the cursor is positioned on that row will (1) operate on values in that row (methods such as getXXX and updateXXX), (2) operate on the row as a whole (such as the methods updateRow, insertRow, deleteRow, refresh-Row), or (3) use that row as a starting point for moving to other rows (such as the method relative).
A cursor remains valid until the ResultSet object or its parent Statement object is closed.
As stated in the previous section, the standard cursor movement for forward only result sets is to use the method next to iterate through each row of a result set once from top to bottom. With scrollable result sets, it is possible to revisit a row or to iterate through the result set multiple times. This is possible because the cursor can be moved before the first row at any time (with the method beforeFirst). The cursor can begin another iteration through the result set with the method next. The following example positions the cursor before the first row and then iterates forward through the contents of the result set. The methods getString and getFloat retrieve the column values for each row until there are no more rows, at which time the method next returns the value false.
rs.beforeFirst();
while (rs.next()) {
System.out.println(rs.getString("EMP_NO") +
" " + rs.getFloat("SALARY");
}
It is also possible to iterate through a result set backwards, as is shown in the next example. The cursor is first moved to the very end of the result set (with the method afterLast), and then the method previous is invoked within a while loop to iterate through the contents of the result set by moving to the previous row with each iteration. The method previous returns false when there are no more rows, so the loop ends after all the rows have been visited.
rs.afterLast();
while (rs.previous()) {
System.out.println(rs.getString("EMP_NO") +
" " + rs.getFloat("SALARY");
}
The interface ResultSet offers still other ways to iterate through the rows of a scrollable result set. Care should be taken, however, to avoid incorrect alternatives such as the one illustrated in the following example:
// incorrect!
while (!rs.isAfterLast()) {
rs.relative(1);
System.out.println(
rs.getString("EMP_NO") + " " + rs.getFloat("SALARY"));
}
This example attempts to iterate forward through a scrollable result set and is incorrect for several reasons. One error is that if ResultSet.isAfterLast is called when the result set is empty, it will return a value of false since there is no last row. The loop body will be executed, which is not what is wanted. An additional problem occurs when the cursor is positioned before the first row of a result set that contains data. In this case, calling rs.relative(1) is erroneous because there is no current row. The method relative moves the cursor the specified number of rows from the current row, and it must be invoked only while the cursor is on the current row.
The following code fragment fixes the problems in the previous example. Here a call to the method ResultSet.first is used to distinguish the case of an empty result set from one that contains data. Because ResultSet.isAfterLast is called only when the result set is non-empty, the loop control works correctly. Since ResultSet.ResultSet.relative(1) steps through the rows of the result set as expected.
if (rs.first()) {
while (!rs.isAfterLast()) {
System.out.println(
rs.getString("EMP_NO") + " " + rs.getFloat("SALARY"));
rs.relative(1);
}
}
With the new cursor movement methods, it is easy to see how many rows a scrollable ResultSet object contains. All that is necessary is to go to the last row of the result set and get the number of that row. In the following example, rs will have one row for each employee.
ResultSet rs = stmt.executeQuery(
"SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES");
rs.last();
int numberOfRows = rs.getRow();
System.out.println("XYZ, Inc. has " + numberOfRows + " employees");
rs.beforeFirst();
while (next()) {
. . . // retrieve first and last names of each employee
}
Though not as convenient, it is also possible to find out how many rows a nonscrollable result set has. The following example shows one way to determine the number of rows.
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM EMPLOYEES");
rs.next();
int count = rs.getInt(1);
System.out.println("XYZ, Inc. has " + count + " employees");
ResultSet rs2 = stmt.executeQuery(
"SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES");
while (rs2.next()) {
. . . // retrieve first and last names of each employee
}
With the scrollable result set, the cursor was just repositioned to start iterating through the same result set to retrieve its data. In the preceding example, however, one query is needed to get the count, and another query is needed to get a result set with the data that is desired. Both queries must, of course, produce result sets of the same size for the count to be accurate.
A second way to determine the number of rows in a forward-only result set is to iterate through the result set, incrementing a variable with each iteration, which is shown in the following example. Because an application can iterate through a forward-only result set just once, the same query needs to be executed twice. In the iteration through the first rs, the number of rows is counted; in the iteration through the second rs, the data is retrieved.
ResultSet rs = stmt.executeQuery(
"SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES");
int count = 0;
while (rs.next()) {
count++;
}
System.out.println("Company XYZ has " + count " employees.");
rs = stmt.executeQuery(
"SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES");
while (rs.next()) {
. . . // retrieve first and last names of each employee
}
The ResultSet.getXXX methods provide the means for retrieving column values from the current row. For maximum portability with forward only result sets, values should be retrieved from left to right, and column values should be read only once. With scrollable result sets, however, there are no such restrictions.
Either the column name or the column number can be used to designate the column from which to retrieve data. For example, if the second column of a ResultSet object rs is named TITLE, and it stores values as strings, either of the following will retrieve the value stored in that column:
String s = rs.getString(2);
String s = rs.getString("TITLE");
Note that columns are numbered from left to right starting with column 1. Also, column names used as input to getXXX methods are case insensitive.
The option of using the column name was provided so that a user who specifies column names in a query can use those same names as the arguments to getXXX methods. If, on the other hand, the SELECT statement does not specify column names (as in "SELECT * FROM TABLE1" or in cases where a column is derived), column numbers should be used. In such situations, there is no way for the user to know for sure what the column names are.
In some cases, it is possible for an SQL query to return a result set that has more than one column with the same name. If a column name is used as the parameter to a getXXX method, getXXX will return the value of the first matching column name. Thus, if there are multiple columns with the same name, one needs to use a column index to be sure that the correct column value is retrieved. It may also be slightly more efficient to use column numbers.
If the name of a column is known but not its index, the method findColumn can be used to find the column number.
Information about the columns in a ResultSet is available by calling the method ResultSet.getMetaData. The ResultSetMetaData object returned gives the number, types, and properties of its ResultSet object's columns.
JDBC drivers support type coercion. When a getXXX method is invoked, the driver attempts to convert the underlying data to the type XXX in the Java programming language and then returns a suitable value. For example, if the getXXX method is getString, and the data type of the data in the underlying database is VARCHAR, the JDBC Compliant driver will convert the VARCHAR value to a String object in the Java programming language. That String object will be the value returned by getString.
The JDBC 2.0 API adds new ResultSet.getXXX methods for retrieving the new SQL3 data types. These methods work the same way the getXXX methods in the JDBC 1.0 API work; that is, they map the SQL3 JDBC type to a type in the Java programming language and return that type. For example, the method getClob retrieves a JDBC CLOB value from the database and returns a Clob object, which is an instance of the java.sql.Clob interface.
The method getObject will retrieve any data type. This is possible because Object, being the type from which every other object type in the Java programming language is derived, is the most generic type. This is especially useful when the underlying data type is a database-specific type or when a generic application needs to be able to accept any data type. The method getObject, as would be expected from its name, returns a Java Object that must be narrowed if it is to be used as a more specific type. In other words, it must be cast from its generic Object type to its more derived type before it can be used as that derived type. The following code fragment illustrates using the method getObject to retrieve a Struct value from the column ADDRESS in the current row of the ResultSet object rs. The Object that getObject returns is narrowed to a Struct before assigning it to the variable address.
Struct address = (Struct)rs.getObject("ADDRESS");
The method getObject is not only the one method capable of retrieving values of any data type but also the only ResultSet.getXXX method that does custom mapping. Therefore, to be custom mapped, a data type has to be retrieved with the method getObject. The two SQL data types that can be custom mapped are the user-defined types, SQL structured types and DISTINCT types. A JDBC DISTINCT value is normally retrieved with the getXXX method appropriate for its underlying type, but if it has a custom mapping, it must be retrieved by the method getObject in order to be custom mapped. A JDBC STRUCT can only be retrieved with the method getObject, guaranteeing that if there is a custom mapping for a JDBC STRUCT value, it will be used.
[D]
An "x" indicates that the getXXX method may legally be used to retrieve the given JDBC type.
An "X" indicates that the getXXX method is recommended for retrieving the given JDBC type.
Results sets may have different levels of functionality. For example, they may be scrollable or nonscrollable. A scrollable result set has a cursor that moves both forward and backward and can be moved to a particular row. Also, result sets may be sensitive or insensitive to changes made while they are open; that is, they may or may not reflect changes to column values that are modified in the database. A developer should always keep in mind the fact that adding capabilities to a ResultSet object incurs additional overhead, so it should be done only as necessary.
Based on the capabilities of scrollability and sensitivity to changes, there are three types of result sets available with the JDBC 2.0 core API. The following constants, defined in the ResultSet interface, are used to specify these three types of result sets:
A result set may have different update capabilities. As with scrollability, making a ResultSet object updatable increases overhead and should be done only when necessary. That said, it is often more convenient to make updates programmatically, and that can only be done if a result set is made updatable. The JDBC 2.0 core API offers two update capabilities, specified by the following constants in the ResultSet interface:
ResultSet object with read-only concurrency needs to set a lock, it uses a read-only lock. This allow users to read data but not to change it. Because there is no limit to the number of read-only locks that may be held on data at one time, there is no limit to the number of concurrent users unless the DBMS or driver imposes one.
To allow a higher level of concurrency, an updatable result set may be implemented so that it uses an optimistic concurrency control scheme. This implementation assumes that conflicts will be rare and avoids using write-only locks, thereby permitting more users concurrent access to data. Before committing any updates, it determines whether a conflict has occurred by comparing rows either by value or by a version number. If there has been an update conflict between two transactions, one of the transactions will be aborted in order to maintain consistency. Optimistic concurrency control implementations can increase concurrency; however, if there are too many conflicts, they may actually reduce performance.
Many DBMSs and drivers are optimized to give the best performance under various circumstances, which means that generally a database programmer is best advised to use their default settings. However, for programmers who may want to fine tune database performance for a particular application, the JDBC 2.0 API provides methods that give hints to the driver for making access to result set data more efficient. These performance hints are exactly that, just hints; a JDBC Compliant driver may choose to ignore them.
The following two hints give the driver suggestions for improving performance:
Statement.setFetchSize and ResultSet.setFetchSize. The statement that creates a ResultSet object sets the default fetch size for that ResultSet object, using the Statement method setFetchSize. The following code fragment sets the fetch size for the ResultSet object rs to 25. Until the fetch size is changed, any result set created by the Statement object stmt will automatically have a fetch size of 25.
A result set can, at any time, change its default fetch size by setting a new fetch size with theStatement stmt = con.createStatement(); stmt.setFetchSize(25); ResultSet rs = stmt.executeQuery(SELECT * FROM EMPLOYEES);
ResultSet version of the method setFetchSize. Continuing from the previous code fragment, the following line of code changes the fetch size of rs to 50:
Normally the most efficient fetch size is already the default for the driver. The methodrs.setFetchSize(50);
setFetchSize simply allows a programmer to experiment to see if a certain fetch size is more efficient than the default for a particular application.
The interface ResultSet defines the following three constants for specifying the direction in which to process rows: FETCH_FORWARD, FETCH_REVERSE, and FETCH_UNKNOWN. As with the fetch size, there are two methods for setting the fetch direction, one in the interface Statement, and the other in the interface ResultSet. The statement that creates the result set determines the default fetch direction by using the Statement method setFetchDirection. The following code fragment sets the fetch direction for the ResultSet object rs so that it will process rows from the bottom up. Until the fetch direction is changed, any result set created by the Statement object stmt will automatically have a fetch direction of backward.
A result set can, at any time, change its default fetch direction by setting a new fetch direction with theStatement stmt = con.createStatement(); stmt.setFetchDirection(FETCH_REVERSE); ResultSet rs = stmt.executeQuery(SELECT * FROM EMPLOYEES);
ResultSet method setFetchDirection. Continuing from the previous code fragment, the following line of code changes the fetch direction of rs to forward.
Thers.setFetchDirection(FETCH_FORWARD);
ResultSet object rs will hint that the driver process rows in a forward direction. This hint will be in effect until the method ResultSet.setFetchDirection is again called on rs to change the suggested fetch direction.
As with the fetch size, drivers are commonly optimized to use the most efficient fetch direction, and changing the default may actually work against this optimization. The method setFetchDirection simply allows a programmer to try to fine tune an application for even better performance.
A result set is created by executing a query, and the type of result set depends on the arguments that are supplied to the Connection method createStatement (or prepareStatement or prepareCall). The following code fragment, which uses only JDBC 1.0 API, supplies no arguments to the method createStatement and thus creates a default ResultSet object, one that is forward-only and uses read-only concurrency.
Connection con = DriverManager.getConnection( "jdbc:my_subprotocol:my_subname"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT EMP_NO, SALARY FROM EMPLOYEES");
The variable rs represents a ResultSet object that contains the values for the columns EMP_NO and SALARY from every row in the table EMPLOYEES. This result set is not scrollable, so only the method next can be used to move the cursor from the top down through the rows of the result set. The ResultSet object rs cannot be updated, and since no performance hints were given, the driver is free to do whatever it thinks will produce the best performance. The transaction isolation level was likewise not set, so rs will use the default transaction isolation level of the underlying database. (See "Transaction Isolation Levels" on page 22 for an explanation of transaction isolation levels.)
The next example uses the new JDBC 2.0 core API to create a scrollable result set that is sensitive to updates (by specifying ResultSet.TYPE_SCROLL_SENSITIVE) and that is updatable (by specifying ResultSet.CONCUR_UPDATABLE).
Connection con = DriverManager.getConnection( "jdbc:my_subprotocol:my_subname"); Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); stmt.setFetchSize(25); ResultSet rs2 = stmt.executeQuery( "SELECT EMP_NO, SALARY FROM EMPLOYEES");
The variable rs2 contains the same values as rs, from the previous example, but unlike rs, it is scrollable, updatable, and sensitive to changes in the underlying table's data. It also hints that the driver should fetch 25 rows from the database each time new rows are needed. Each time the Statement object stmt is executed, it will create a result set that is scrollable, is updatable, is sensitive to changes in its data, and has a fetch size of 25. The result set may change its fetch size, but it cannot change its type or concurrency.
As stated previously, there is a cost to making a result set scrollable or updatable, so it is good practice to create result sets with these features only when they are needed.
Because PreparedStatement and CallableStatement objects inherit the methods defined in the Statement interface, they, too, can create different types of ResultSet objects.
The following code fragment creates a result set using a PreparedStatement object instead of a Statement object. The result set has the same attributes as in the previous example, except that a transaction isolation level is set for the connection.
Connection con = DriverManager.getConnection( "jdbc:my_subprotocol:my_subname"); con.setTransactionIsolation(TRANSACTION_READ_COMMITTED); PreparedStatement pstmt = con.prepareStatement( "SELECT EMP_NO, SALARY FROM EMPLOYEES WHERE EMP_NO = ?", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); pstmt.setFetchSize(25); pstmt.setString(1, "1000010"); ResultSet rs3 = pstmt.executeQuery();
The variable rs3 contains the values from the columns EMP_NO and SALARY for the row where the value for EMP_NO is 1000010. The ResultSet object rs3 is like rs2 in that it is scrollable, is updatable, is sensitive to changes in its data, and hints that the driver should fetch 25 rows at a time from the database. It is different in that its connection specifies that dirty reads (reading values before they are committed) will be prevented. Because no transaction isolation level was set for rs2, it will by default have the isolation level of the underlying database.
With the addition of new functionality in the JDBC 2.0 API, it is possible for an application to request features that a DBMS or driver do not support. If the driver does not support scrollable result sets, for example, it may return a forward-only result set. Also, some queries will return a result set that cannot be updated, so requesting an updatable result set would have no effect for those queries. A general rule is that a query should include the primary key as one of the columns it selects, and it should reference only one table.
New methods in the JDBC 2.0 API let an application discover which result set features a driver supports. If there is any doubt about whether a feature is supported, it is advisable to call these methods before requesting the feature. The following DatabaseMetaData methods indicate whether a driver supports a given result set type or a given result set concurrency:
DatabaseMetaData.supportsResultSetType - returns a boolean indicating whether the driver supports the given result set type
DatabaseMetaData.supportsResultSetConcurrency - returns a boolean indicating whether the driver supports the given concurrency type in combination with the given result set type
The following ResultSet methods return the result set type and result set concurrency for the particular result set on which the method is called:
ResultSet.getType - returns the type of this result set
ResultSet.getConcurrency - returns the concurrency mode of this result set
If an application specifies a scrollable result set and the driver does not support scrolling, the driver will issue a warning on the Connection object that produced the statement and return a result set that is forward-only. Even if the driver supports scrollable result sets, it is possible for an application to request a scrollable type that the driver does not support. In such a case, the driver will issue an SQLWarning on the Connection object that produced the statement and return a scrollable result set of a type that it does support, even if it differs from the exact type requested. For example, if an application requests a TYPE_SCROLL_SENSITIVE result set and the driver does not support that type, it could return a TYPE_SCROLL_INSENSITIVE result set if it supports that type. The driver would also alert the application that it did not return the exact type requested by issuing an SQLWarning on the Connection object that produced the statement requesting the unsupported result set type.
Similarly, if an application specifies an updatable result set, a driver that does not support updatable result sets will issue an SQLWarning on the Connection object that produced the statement and return a read-only result set. If the application requests both an unsupported result set type and an unsupported concurrency type, the driver should choose the result set type first.
In some situations, a driver may need to choose an alternate result set type or concurrency type at statement execution time. For example, a SELECT statement that contains a join over multiple tables might produce a result set that is not updatable. In such a situation, the driver will issue an SQLWarning on the Statement, PreparedStatement, or CallableStatement object that tried to create the result set instead of issuing it on the Connection object. The driver will then choose an appropriate result set type and/or concurrency type according to the guidelines in the preceding two paragraphs.
A ResultSet object may be updated (have its rows modified, inserted, or deleted) programmatically if its concurrency type is CONCUR_UPDATABLE. The JDBC 2.0 API adds updateXXX methods and various other methods to the ResultSet interface so that rows can be programmatically updated in both the ResultSet object and the database.
The new updateXXX methods make it possible to update values in a result set without using SQL commands. There is an updateXXX method for each data type, and as with the getXXX and setXXX methods, the XXX is a data type in the Java programming language. As with the setXXX methods, the driver converts this data type to an SQL data type before sending it to the database. So for example, the method updateBoolean sends a JDBC BIT value to the database, and the method updateCharacterStream sends a JDBC LONGVARCHAR value to the database.
The updateXXX methods take two parameters, the first to indicate which column is to be updated, and the second to give the value to assign to the specified column. As is true with the getXXX methods, the column can be specified by giving either its name or its index. If an application retrieved a value from a result set by using the column name, it will generally use the column name when it wants to update that value. Similarly, if the getXXX method was given a column index to retrieve a value, the corresponding updateXXX method will generally use the column index to update that value.
Note that the column index used with ResultSet methods refers to the column number in the result set, not the column number in the database table, which might well be different. (The column numbers will be the same only in the case where all of a table's columns are selected.) In both result set tables and database tables, the index for the first column is 1, the index for the second column is 2, and so on.
In the following code fragment, the value in the third column of the ResultSet object rs is retrieved using the method getInt, and the method updateInt is used to update that column value with an int value of 88:
int n = rs.getInt(3); // n contains the value in column 3 of rs . . . rs.updateInt(3, 88); // the value in column 3 of rs is set to 88 int n = rs.getInt(3); // n = 88
If the third column is named SCORES, the following lines of code will also update the third column of the ResultSet object rs by assigning it the int value 88:
int n = rs.getInt("SCORES");
. . .
rs.updateInt("SCORES", 88);
The updateXXX methods update a value in the current row of the result set, but they do not update the value in the underlying database table. It is the method updateRow that updates the database. It is very important that the updateRow method be called while the cursor is still on the current row (the row to be updated). In fact, if an application moves the cursor before it calls updateRow, the driver must discard the update, and neither the result set nor the database will be updated.
An application may explicitly cancel the updates to a row by calling the method cancelRowUpdates. To take effect, it must be called after the method updateXXX is called and before the method updateRow is called. If cancelRowUpdates is called at any other time, it has no effect.
The following example demonstrates updating the second and third columns in the fourth row of the ResultSet object rs. Since updates affect the current row, the cursor is first moved to the row to be updated, which in this case is the fourth row. Next the method updateString is called to change the value in the second column of rs to 321 Kasten. The method updateFloat changes the value in the third column of rs to 10101.0. Finally, the method updateRow is called to update the row in the database that contains the two modified column values.
rs.absolute(4); rs.updateString(2, "321 Kasten"); rs.updateFloat(3, 10101.0f); rs.updateRow();
If the second column is named ADDRESS and the third column is named AMOUNT, the following code will have exactly the same effect as the previous example.
rs.absolute(4);
rs.updateString("ADDRESS", "321 Kasten");
rs.updateFloat("AMOUNT", 10101.0f);
rs.updateRow();
In addition to making updates programmatically, the JDBC 2.0 core API provides the ability to send batch updates. The batch update facility operates through a Statement object, which is explained in the section "Sending Batch Updates" on page 39.
The JDBC 2.0 API provides the method deleteRow so that a row in a ResultSet object can be deleted using only methods in the Java programming language. This method deletes the current row, so before calling deleteRow, an application must position the cursor on the row it wants to delete. Unlike the updateXXX methods, which affect only a row in the result set, this method affects both the current row in the result set and the underlying row in the database. The following two lines of code remove the first row of the ResultSet object rs and also delete the underlying row from the database (which may or may not be the first row of the database table).
rs.first(); rs.deleteRow();
New rows may be inserted into a result set table and into the underlying database table using new methods in the JDBC 2.0 core API. To make this possible, the API defines the concept of an insert row. This is a special row, associated with the result set but not part of it, that serves as a staging area for building the row that is to be inserted. To access the insert row, an application calls the ResultSet method moveToInsertRow, which positions the cursor on the insert row. Then it calls the appropriate updateXXX methods to add column values to the insert row. When all of the columns of the row to be inserted have been set, the application calls the method insertRow. This method adds the insert row to both the result set and the underlying database simultaneously. Finally, the application needs to position the cursor on a row back in the result set.
The following code fragment demonstrates these steps for inserting a row from an application written in the Java programming language.
rs.moveToInsertRow(); rs.updateObject(1, myArray); rs.updateInt(2, 3857); rs.updateString(3, "Mysteries"); rs.insertRow(); rs.first();
Several details deserve attention. First, it is possible to retrieve values from the insert row using the ResultSet.getXXX methods. Until a value has been assigned to the insert row with an updateXXX method, however, its contents are undefined. Therefore, if a getXXX method is called after the moveToInsertRow method has been called but before an updateXXX method has been called, the value it returns will be undefined.
Second, calling an updateXXX method on the insert row is different from calling it on a row in the ResultSet object. When the cursor is on a row in a result set, a call to an updateXXX method changes a value in the result set. When the cursor is on the insert row, a call to an updateXXX method updates a value in the insert row but does nothing to the result set. In both cases, though, the updateXXX method has no effect on the underlying database.
Third, calling the method insertRow, which adds the insert row to both the result set and database, may throw an SQLException if the number of columns in the insert row does not match the number of columns in the database table. For example, if a column is not given a value by calling an updateXXX method, an SQLException will be thrown unless that column allows null values. Also, if the result set is missing a column, that, too, will cause an SQLException to be thrown unless the column allows null values.
Fourth, a result set keeps track of where its cursor was positioned when the cursor moved to the insert row. As a result, a call to the method ResultSet.moveToCurrentRow will return the cursor to the row that was the current row immediately before the method moveToInsertRow was called. The other cursor movement methods also work from the insert row, including those that use positioning relative to the current row.
Before the JDBC 2.0 API made programmatic updates available in the Java programming language, the only way to change a row that had been fetched with a result set was to use what is called a positioned update. A positioned update is done with SQL commands and requires a named cursor to indicate the result set row in which updates are to be made.
The Statement interface provides the method setCursorName, which allows an application to specify a cursor name for the cursor associated with the next result set produced by a statement. This name can then be used in SQL positioned update or delete statements to identify the current row in the ResultSet object generated by the statement. In order to enable a positioned update or delete on a result set, the query that produces it must have the following form:
SELECT . . . FROM . . . WHERE . . . FOR UPDATE . . .
Including the words "FOR UPDATE" ensures that the cursor has the proper isolation level to support an update.
After the method executeQuery has been called on the statement, the cursor name for the resulting ResultSet object can be obtained by calling the ResultSet method getCursorName. If a DBMS allows positioned updates or positioned deletes, the name of the cursor can be supplied as a parameter to the SQL command for updates or deletes. A Statement object other than the one that created the ResultSet object must be used for the positioned update. The following code fragment, in which stmt and stmt2 are two different Statement objects, demonstrates the form for naming a cursor and then using it in an SQL update statement:
stmt.setCursorName("x");
ResultSet rs = stmt.executeQuery(
"SELECT . . . FROM . . . WHERE . . . FOR UPDATE . . .")
String cursorName = rs.getCursorName;
int updateCount = stmt2.executeUpdate(
"UPDATE . . . WHERE CURRENT OF " + cursorName);
Note that just because the method getCursorName has been invoked on a ResultSet object does not necessarily mean that it can be updated using the ResultSet.updateXXX methods available in the JDBC 2.0 core API. In order to update a ResultSet object using the updateXXX methods, the executeQuery statement that produces the result set must include the specification CONCUR_UPDATABLE. Positioned updates, however, are possible for a result set created without this specification if all the proper steps are taken: (1) a cursor is named, (2) the SQL query that produces the result set is of the form SELECT . . . FROM . . . WHERE . . . FOR UPDATE . . ., and (3) the SQL update statement is of the form UPDATE . . . WHERE CURRENT OF <cursorName>.
Not all DBMSs support positioned updates. To verify that a DBMS supports positioned updates, an application can call the DatabaseMetaData methods supportsPositionedDelete and supportsPositionedUpdate to discover whether a particular connection supports these operations. When they are supported, the DBMS/driver must ensure that rows selected are properly locked so that positioned updates do not result in update anomalies or other concurrency problems.
Some queries will produce result sets that cannot be updated no matter what the result set type. For example, a query that does not select the primary key column might generate a result set that cannot be updated. Because of differences in database implementations, the JDBC 2.0 core API does not specify an exact set of SQL queries that must yield updatable result sets. Instead it defines a set of criteria that should generally produce updatable result sets for JDBC Compliant drivers that support updatability. If queries adhere to the following guidelines, a developer can generally expect that they will produce updatable result sets:
GROUP BY clause
If inserts are to be performed on the result set, an SQL query should satisfy conditions one through three plus the following three additional conditions:
The fourth and fifth conditions are necessary because a row to be inserted into a table must have a value for each column in the table unless the column accepts null values or default values. If the result set on which insertion operations are to be performed does not contain every column that requires a value, the insertion will fail.
Result sets created by means other than the execution of a query, such as those returned by several methods in the DatabaseMetaData interface, are not scrollable or updatable, nor are they required to be.
Two new interfaces in the JDBC 2.0 core API, Blob and Clob, are the mapping of the SQL3 data types BLOB (Binary Large Object) and CLOB (Character LargeObject) in the Java programming language. With the availability of these data types, databases will undoubtedly start using them to store very large binary or character objects. If this is the case, the ResultSet methods getBlob and getClob should be used to retrieve them.
Using only the JDBC 1.0 API, a ResultSet object still makes it possible to retrieve arbitrarily large LONGVARBINARY or LONGVARCHAR data. The methods getBytes and getString return data as one large chunk (up to the limits imposed by the return value of Statement.getMaxFieldSize). It is possible to retrieve this large chunk of data in smaller, fixed-size chunks. This is done by having the ResultSet class return java.io.Input streams from which data can be read in chunks. Note that these streams must be accessed immediately because they will be closed automatically when the next getXXX method is called on the ResultSet object. (This behavior is not a limitation of the JDBC API but rather a constraint on large blob access imposed by the underlying implementations in some database -systems.)
The JDBC 1.0 API has three separate methods for getting streams, each with a different return value:
getBinaryStream - returns a stream that simply provides the raw bytes from the database without any conversion
getAsciiStream - returns a stream that provides one-byte ASCII characters. This method can be more efficient for a DBMS that stores characters in ASCII format.
getUnicodeStream - returns a stream that provides two-byte Unicode characters. This method, though still available, has been deprecated in favor of the new method getCharacterStream. (See below.)
The following method for retrieving streams of both ASCII and Unicode characters is new in the JDBC 2.0 core API:
getCharacterStream - returns a java.io.Reader object that provides Unicode characters. No matter how a DBMS stores characters, the driver will return them as a stream of Unicode characters.
Note that the stream returned by getAsciiStream returns a stream of bytes in which each byte is an ASCII character. This differs from getCharacterStream, which returns a stream of two-byte Unicode characters. The method getCharacterStream can be used for both ASCII and Unicode characters because the driver will convert ASCII characters to Unicode before it returns a Reader object. If you must use getUnicodeStream because your DBMS and driver do not support the JDBC 2.0 API, note also that JDBC Unicode streams return big-endian data; that is, they expect data with the high byte first and the low byte second. This conforms to the standard endian defined by the Java programming language, which is important if a program is to be portable. Refer to The Javatm Virtual Machine Specification, by Tim Lindholm and Frank Yellin, for more detailed information about big-endian order.
The following code fragment demonstrates how to use the getAsciiStream method.
java.sql.Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT x FROM Table2");
// Now retrieve the column 1 results in 4 K chunks:
byte [] buff = new byte[4096];
while (rs.next()) {
java.io.InputStream fin = rs.getAsciiStream(1);
for (;;) {
int size = fin.read(buff);
if (size == -1) { // at end of stream
break;
}
// Send the newly-filled buffer to some ASCII output stream
output.write(buff, 0, size);
}
}
To determine if a given result value is JDBC NULL, one must first read the column and then use the method ResultSet.wasNull. This is true because a JDBC NULL retrieved by one of the ResultSet.getXXX methods may be converted to either null, 0, or false, depending on the type of the value.
The following list shows which values are returned by the various getXXX methods when they have retrieved a JDBC NULL.
null-for those getXXX methods that return objects in the Java programming language (getString, getBigDecimal, getBytes, getDate, getTime, getTime-stamp, getAsciiStream, getCharacterStream, getUnicodeStream, getBinary-Stream, getObject, getArray, getBlob, getClob, and getRef)
0 (zero)-for getByte, getShort, getInt, getLong, getFloat, and getDouble
false-for getBoolean
For example, if the method getInt returns 0 from a column that allows null values, an application cannot know for sure whether the value in the database was 0 or NULL until it calls the method wasNull, as shown in the following code fragment, where rs is a ResultSet object.
int n = rs.getInt(3); boolean b = rs.wasNull();
If b is true, the value stored in the third column of the current row of rs is JDBC NULL. The method wasNull checks only the last value retrieved, so to determine whether n was NULL, wasNull had to be called before another getXXX method was invoked.
Normally, nothing needs to be done to close a ResultSet object; it is automatically closed by the Statement object that generated it when that Statement object is closed, is re-executed, or is used to retrieve the next result from a sequence of multiple results. The method close is provided so that a ResultSet object can be closed explicitly, thereby immediately releasing the resources held by the ResultSet object. This could be necessary when several statements are being used and the automatic close does not occur soon enough to prevent database resource conflicts.
Drivers that are JDBC Compliant should normally support scrollable result sets, but they are not required to do so. The intent is for JDBC drivers to implement scrollable result sets using the support provided by the underlying database systems. If the DBMS does not provide support for scrollability, then the driver may omit this feature.
Making scrollability optional is not meant to encourage omitting it. It is simply meant to minimize the complexity of implementing JDBC drivers for data sources that do not support scrollability. Indeed, the recommended alternative is for a driver to implement scrollability as a layer on top of the DBMS. One way to do this is to implement a result set as a rowset. The RowSet interface, which provides methods for doing this, is part of the JDBC Standard Extension API.