JDBCTM Guide: Getting Started 5-resultset

5-resultset
This overview is quoted in the book Jdbctm Database Access from Javatm:a Tutorial and annotated Reference. JavaSoft is currently preparing the book. This is a tutorial, as well as an important reference manual for JDBC, which will be published as part of the Java family in the spring of 1997 by Addison-wesley Publishing Company.


5.1 Overview
ResultSet contains all the rows that conform to the conditions in the SQL statement, and it provides access to the data in those rows through a set of get methods that can access different columns in the current row. The Resultset.next method is used to move to the next line in ResultSet so that the next row becomes the current row.

The result set is typically a table with the column headings and corresponding values returned by the query. For example, if the query is SELECT A, B, C from Table1, the result set will have the following form:


A b C
-------- --------- --------
12345 Cupertino CA
83472 Redmond WA
83492 Boston MA


The following code snippet is an example of executing an SQL statement. The SQL statement returns the rowset, where column 1 is int, column 2 is String, and column 3 is an array of bytes:


Java.sql.Statement stmt = Conn.createstatement ();
ResultSet r = Stmt.executequery ("Select a, B, C from Table1");
while (R.next ())
{
Prints the value of 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);
}

5.1.1 Rows and Cursors
ResultSet maintains the cursor that points to its current data row. Each time the next method is invoked, the cursor moves down one line. Initially it is before the first row, so the first call to next will place the cursor on the first line, making it the current line. With each call to next causes the cursor to move down one line, getting the ResultSet row from top to bottom.

The cursor remains in effect until the ResultSet object or its parent Statement object is closed.

In SQL, the cursor of the result table has a name. If the database allows you to locate updates or locate deletes, you need to provide the name of the cursor as an argument to the update or delete command. You can get the cursor name by calling method Getcursorname.

Note: Not all DBMS support locating updates and deletes. You can use the Databasemetadata.supportspositioneddelete and Supportspositionedupdate methods to check whether a particular connection supports these operations. When these operations are supported, the dbms/driver must ensure that the selected row is properly locked so that the location update does not cause an update exception or other concurrency problem.


5.1.2 Column
Method GetXXX provides a way to get a column value in the current row. Within each row, you can get the column values in any order. However, to ensure portability, you should get the column values from left to right and read the column values once.

The column name or column number can be used to identify the column from which to get the data. For example, if the second column of the ResultSet object RS is named "title" and the value is stored as a string, any of the following code gets the value stored in the column:

String s = rs.getstring ("title");
String s = rs.getstring (2);

Note that the columns are numbered from left to right, and starting with column 1. Also, the column names used as input for the GetXXX method are case-insensitive.

The option to use column names is provided to allow users who specify column names in the query to use the same name as the parameters of the GetXXX method. On the other hand, if the SELECT statement does not specify a column name (for example, in "SELECT * FROM table1" or when the column is exported), the column number should be used. In these cases, the user will not know exactly what the column name is.

In some cases, the result set returned by the SQL query may have multiple columns with the same name. If the column name is used as an argument to the GetXXX method, GETXXX returns the value of the first matching column name. Thus, if more than one column has the same name, you need to use a column index to ensure that the correct column values are retrieved. At this point, the use of column number efficiency is slightly higher.

Information about the columns in ResultSet can be obtained by invoking the method Resultset.getmetadata. The returned ResultSetMetaData object gives the number, type, and properties of each column of its ResultSet object.

If the column name is known, but its index is unknown, the method findcolumn can be used to get its column number.


5.1.3 Data types and conversions
For the GetXXX method, the JDBC driver attempts to convert the base data to the specified Java type and then returns the appropriate Java value. For example, if the GetXXX method is getString and the data type in the base database is VARCHAR, the JDBC driver converts VARCHAR to a Java String. The return value of the getString will be a Java String object.

The following table shows the types of JDBC that are allowed to be obtained with getxxx and the type of JDBC that is recommended for it (common SQL type). A lowercase x indicates that the GetXXX method is allowed to get the data type, and an uppercase x indicates that the GetXXX method is recommended for that data type. For example, any getxxx method other than GetBytes and Getbinarystream can be used to get longvarchar values, but it is recommended that you use the Getasciistream or Getunicodestream method based on the data type that is returned. Method GetObject returns any data type to Java Object. This is useful when the base data type is a database-specific abstract type or when a generic application needs to accept any data type.

You can use the Resultset.getxxx method to get common JDBC data types.

"X" means that the GetXXX method can legitimately be used to get the given JDBC type.

"X" indicates the recommended use of the GetXXX method to get the given JDBC type.

T
I
N
Y
I
N
Ts
M
A
L
L
I
N
T I
N
T
E
G
E
R B
I
G

N
T R
E
A
L F
L
O
A
T D
O
U
B
L
E D
E
C
I
M
A
L N
U
M
E
R
I
C B
I
T C
H
A
R V
A
R
C
H
A
R
L
O
N
G
V
A
R
C
H
A
R B
I
N
A
R
Y V
A
R
B
I
N
A
R
Y L
O
N
G
V
A
R
B
I
N
A
R
Y D
A
T
Et
I
M
Et
I
M
E
S
T
A
M
P
GetByte x x x x x x x × X × x x x x
Getshort x x x x x x x × X × x x x x
GetInt x x x x x x x × X × x x x x
Getlong x x x x x x x × X × x x x x
GetFloat x x x x x x x × X × x x x x
getdouble x x x x x x x × X × x x x x
Getbigdecimal x x x x x x x × X × x x x x
Getboolean x x x x x x x × X × x x x x
getString x x x x x x x × X × x x x x x x
GetBytes x x x
GetDate x x x x x
GetTime x x x x x
Gettimestamp x x x x x
Getasciistream x x x x x x
Getunicodestream x x x x x x
Getbinarystream x x x
GetObject x x x x x x x × X × x x x x x x



5.1.4 uses a stream for very large row values
ResultSet can obtain any large longvarbinary or LongVarChar data. Methods GetBytes and GetString return the data to a large block (the maximum is the Statement.getmaxfieldsize return value). However, it may be more convenient to get very large data with smaller fixed blocks, which can be accomplished by having the ResultSet class return to the Java.io.Input stream. Data can be read in chunks from this stream. Note: These streams must be accessed immediately because they will automatically shut down the next time you call GetXXX on ResultSet (this is because the basic implementation restricts large data access).

The JDBC API has three methods for fetching streams, each with a different return value:


Getbinarystream returns a stream that provides only the original byte of the database without any transformations.


Getasciistream returns a stream that provides single-byte ASCII characters.


Getunicodestream returns a stream that provides double-byte Unicode characters.


Note: It differs from the Java stream, which returns an untyped byte and can (for example) be universal to ASCII and Unicode characters.

The following code demonstrates the use of Getasciistream:

Java.sql.Statement stmt = Con.createstatement ();
ResultSet r = Stmt.executequery ("Select X from Table2");
Now get column 1 result with 4 K block size:
byte buff = new byte[4096];
while (R.next ()) {
Java.io.InputStream fin = r.getasciistream (1);
for (;;) {
int size = Fin.read (buff);
if (size = = 1) {//reach end of stream
Break
}
To send a newly populated buffer to the ASCII output stream:
Output.write (buff, 0, size);
}
}

5.1.5 NULL Result Value
To determine whether a given result value is JDBC NULL, you must read the column first, and then use the Resultset.wasnull method to check whether the read returns JDBC null.

When you use the Resultset.getxxx method to read JDBC NULL, method Wasnull returns one of the following values:


Java null value: For GETXXX methods that return Java objects (such as getString, Getbigdecimal, GetBytes, GetDate, GetTime, Gettimestamp, Getasciistream, Getunicodestream, Getbinarystream, GetObject, etc.).


0 values: For GetByte, Getshort, GetInt, Getlong, GetFloat, and getdouble.


False value: for Getboolean.


5.1.6 Optional result set or multiple result set
You typically use ExecuteQuery (which returns a single ResultSet) or executeupdate (which can be used with any database modification statement and returns the number of update rows) to execute an SQL statement. In some cases, however, the application does not know whether the statement returns a result set before executing the statement. In addition, some stored procedures may return several different result sets and/or update counts.

To accommodate these situations, JDBC provides a mechanism that allows an application to execute statements and then process any set of result sets and update counts. The rationale for this mechanism is to call a fully generic Execute method first, and then call another three methods, Getresultset, Getupdatecount, and Getmoreresults. These methods allow the application to study the statement results one at a time and determine whether a given result is a ResultSet or an update count.

The user does not have to close ResultSet, and when the Statement that produces it closes, executes, or is used to get the next result from a multiple-result sequence, the ResultSet is automatically closed by Statement.