A ResultSet contains all of the rows which satisfied the conditions in an SQL statement, and it provides access to the data in those rows through a set of get methods that allow 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 the next row become 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:
The following code fragment is an example of executing an SQL statement that will return a collection of rows, with column 1 as an int, column 2 as a String, and column 3 as an array of bytes:
java.sql.Statement stmt = conn.createStatement();
ResultSet r = stmt.executeQuery("SELECT a, b, c FROM Table1");
// print the values for the current row.
int i = r.getInt("a");
String s = r.getString("b");
float f = r.getFloat("c");
System.out.println("ROW = " + i + " " + s + " " + f);
Rows and Cursors
A ResultSet maintains a cursor which points to its current row of data. The cursor moves down one row each time the method next is called. Initially it is positioned before the first row, so that the first call to next puts the cursor on the first row, making it the current row. ResultSet rows are retrieved in sequence from the top row down as the cursor moves down one row with each successive call to next. A cursor remains valid until the ResultSet object or its parent Statement object is closed.
In SQL, the cursor for a result table is named. If a database allows positioned updates or positioned deletes, the name of the cursor needs to be supplied as a parameter to the update or delete command. This cursor name can be obtained by calling the method getCursorName.
Note that not all DBMSs support positioned update and delete. The DatabaseMetaData.supportsPositionedDelete and supportsPositionedUpdate methods can be used 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.
The getXXX methods provide the means for retrieving column values from the current row. Within each row, column values may be retrieved in any order, but for maximum portability, one should retrieve values from left to right and read column values only once.
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 stores values as strings, either of the following will retrieve the value stored in that column:
String s = rs.getString("title");
String s = rs.getString(2);
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 a 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 multi0ple 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.
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.
If the name of a column is known, but not its index, the method findColumn can be used to find the column number.
Wednesday, April 15, 2009