ResultSet
Overview
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);
}
Rows and Columns
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.
Cursors
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.
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.
Cursor Movement Examples
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
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.
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:
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.first
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.
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.
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.
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:
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.
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.
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:
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:
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.
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.
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).
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.
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
The following ResultSet methods return the result set type and result set concurrency for the particular result set on which the method is called:
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.
rs.beforeFirst();
while (rs.next()) {
System.out.println(rs.getString("EMP_NO") +
" " + rs.getFloat("SALARY");
}
rs.afterLast();
while (rs.previous()) {
System.out.println(rs.getString("EMP_NO") +
" " + rs.getFloat("SALARY");
}
// incorrect!
while (!rs.isAfterLast()) {
rs.relative(1);
System.out.println(
rs.getString("EMP_NO") + " " + rs.getFloat("SALARY"));
}
if (rs.first()) {
while (!rs.isAfterLast()) {
System.out.println(
rs.getString("EMP_NO") + " " + rs.getFloat("SALARY"));
rs.relative(1);
}
}
Determining the Number of Rows in a Result Set
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
}
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
}
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
}
Retrieving Column Values
String s = rs.getString(2);
String s = rs.getString("TITLE");
Which getXXX Method to Use
Struct address = (Struct)rs.getObject("ADDRESS");
Types of Result Sets
Concurrency Types
Providing Performance Hints
A result set can, at any time, change its default fetch size by setting a new fetch size with the 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:
Statement stmt = con.createStatement();
stmt.setFetchSize(25);
ResultSet rs = stmt.executeQuery(SELECT * FROM EMPLOYEES);
Normally the most efficient fetch size is already the default for the driver. The method setFetchSize simply allows a programmer to experiment to see if a certain fetch size is more efficient than the default for a particular application.
rs.setFetchSize(50);
A result set can, at any time, change its default fetch direction by setting a new fetch direction with the ResultSet method setFetchDirection. Continuing from the previous code fragment, the following line of code changes the fetch direction of rs to forward.
Statement stmt = con.createStatement();
stmt.setFetchDirection(FETCH_REVERSE);
ResultSet rs = stmt.executeQuery(SELECT * FROM EMPLOYEES);
The 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.
rs.setFetchDirection(FETCH_FORWARD);
Creating Different Types of Result Sets
Connection con = DriverManager.getConnection(
"jdbc:my_subprotocol:my_subname");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT EMP_NO, SALARY FROM EMPLOYEES");
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");
Using a Prepared Statement to Create Result Sets
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();
Requesting Features That Are Not Supported