JDBCTM guide: Getting started 5-ResultSet

5-ResultSet
This overview is taken from JDBCTM Database Access from JavaTM: A Tutorial and Annotated Reference. JavaSoft is currently preparing this book. This is a tutorial and an important reference manual for JDBC, which will be published by Addison-Wesley in the spring of 1997 as part of the Java series.


5.1 Overview
The ResultSet contains all rows that meet the conditions in the SQL statement, and provides access to the data in these rows through a set of get methods (these get methods can access different columns in the current row. The ResultSet. next method is used to move to the next row in the ResultSet to make the next row the current row.

A result set is generally a table with the title and value of the column returned by the query. For example, if the query is SELECT a, B, c FROM Table1, The result set will take 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. This SQL statement returns a set of rows, where column 1 is int, column 2 is String, and column 3 is a byte array:


Java. SQL. Statement stmt = conn. createStatement ();
ResultSet r = stmt.exe cuteQuery ("SELECT a, B, c FROM Table1 ");
While (r. next ())
{
// Print the value of the current row.
Int I = r. getInt ("");
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 pointing to its current data row. Each time the next method is called, The cursor moves down a row. Initially it is located before the first line, so the first call next will place the cursor on the first line to make it the current line. With each call to next, the cursor moves down a row and obtains the ResultSet row in the order from top to bottom.

The cursor remains valid 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 location update or location deletion, you need to provide the cursor name as a parameter to the update or deletion command. You can call the getCursorName method to obtain the cursor name.

Note: not all DBMS support location update and deletion. You can use the DatabaseMetaData. supportsPositionedDelete and supportsPositionedUpdate methods to check whether these operations are supported by a specific connection. When these operations are supported, the DBMS/driver must make sure that the selected row is properly locked so that location updates do not cause update exceptions or other concurrency problems.


5.1.2 Column
The getXXX method provides a way to obtain the value of a column in the current row. You can obtain column values in any order within each row. However, to ensure portability, you should obtain the column value from left to right and read the column value at a time.

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

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

Note that columns are numbered from left to right and start from column 1. The names of input columns used as the getXXX method are case insensitive.

The purpose of using the column name option is to allow users who specify the column name in the query to use the same name as the parameter 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, you cannot know the column name exactly.

In some cases, multiple columns in the result set returned by an SQL query may have the same name. If the column name is used as a parameter of the getXXX method, getXXX returns the value of the first matching column name. Therefore, if multiple columns have the same name, you must use the column index to ensure that the correct column value is retrieved. In this case, the efficiency of using the column number is slightly higher.

You can call ResultSet. getMetaData to obtain the column information in ResultSet. The returned ResultSetMetaData object provides the numbers, types, and attributes of each column of its ResultSet object.

If the column name is known but does not know its index, use findColumn to obtain its column number.


5.1.3 data type and conversion
For the getXXX method, the JDBC driver attempts to convert the basic data to a specified Java type and then returns the appropriate Java value. For example, if the getXXX method is getString and the data type in the basic database is VARCHAR, The JDBC driver converts VARCHAR to Java String. The return value of getString is a Java String object.

The following table shows the JDBC Type that can be obtained using getXXX and the JDBC Type (Common SQL type) that is recommended to be obtained using getXXX ). The lower-case x indicates that the getXXX method is allowed to obtain the data type. The upper-case X indicates that the getXXX method is recommended for this data type. For example, any getXXX method except getBytes and getBinaryStream can be used to obtain the LONGVARCHAR value. However, the getAsciiStream or getUnicodeStream method is recommended based on the returned data type. The getObject method returns any data type as a Java Object. It is useful when the basic data type is a database-specific abstract type or when a common application needs to accept any data type.

You can use the ResultSet. getXXX method to obtain common JDBC data types.

"X" indicates that the getXXX method can be used legally to obtain the specified JDBC Type.

"X" indicates that the getXXX method is recommended to obtain the specified JDBC Type.

T
I

Y
I

T S
M
A
L
L
I

T I

T
E
G
E
R B
I
G

N
T R
E
A
L F
L
O
A
T D
O
U

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

G
V
A
R
C
H
A
R B
I

A
R
Y V
A
R

I

A
R
Y L
O

G
V
A
R

I

A
R
Y D
A
T
E T
I
M
E T
I
M
E

T
A
M

GetByte X x
GetShort x X
GetInt x X x
GetLong x X x
GetFloat x X x
GetDouble x X x
GetBigDecimal x X x
GetBoolean x X x
GetString x X x
GetBytes X x
GetDate x X x
GetTime x X x
GetTimestamp x X
GetAsciiStream x X x
GetUnicodeStream x X x
GetBinaryStream x X
GetObject x



5.1.4 use stream for very large row values
ResultSet can obtain any large LONGVARBINARY or LONGVARCHAR data. The getBytes and getString Methods return the data to a large block (the maximum value is the return value of Statement. getMaxFieldSize ). However, it may be easier to obtain very large data with a small fixed block, but this can be done by returning the java. io. Input stream to the ResultSet class. Data can be read in blocks from the stream. Note: You must access these streams immediately, because they will be automatically disabled the next time you call getXXX on the ResultSet (this is due to restrictions on access to large data blocks ).

The jdbc api has three methods for getting streams, with different return values:


GetBinaryStream returns a stream that only provides the original database bytes without any conversion.


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


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


Note: Unlike Java streams, the latter returns non-type bytes and can (for example) be used for ASCII and Unicode characters.

The following code demonstrates the usage of getAsciiStream:

Java. SQL. Statement stmt = con. createStatement ();
ResultSet r = stmt.exe cuteQuery ("SELECT x FROM Table2 ");
// Obtain the result of column 1 in 4 K blocks:
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 the end of the stream
Break;
}
// Send the newly filled buffer to the ASCII output stream:
Output. write (buff, 0, size );
}
}

5.1.5 NULL result value
To determine whether the given result value is jdbc null, you must first read the column and then use ResultSet. wasNull to check whether jdbc null is returned for this read.

When the ResultSet. getXXX method is used to read jdbc null, The wasNull method 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, and getObject ).


Zero value: For getByte, getShort, getInt, getLong, getFloat, and getDouble.


False: For getBoolean.


5.1.6 optional result set or multi-result set
ExecuteQuery (which returns a single ResultSet) or executeUpdate (which can be used for any database modification statement and returns the number of updated rows) can be used to execute SQL statements. However, in some cases, the application does not know whether to return the result set before executing the statement. In addition, some stored procedures may return several different result sets and/or update counts.

To adapt to these situations, JDBC provides a mechanism that allows applications to execute statements and then process any set of result sets and update counts. The principle of this mechanism is to call a fully common execute method, and then call the other three methods, getResultSet, getUpdateCount, and getMoreResults. These methods allow the application to study statement results one by one and determine whether the given results are ResultSet or update count.

You do not have to disable the ResultSet. When the Statement generated is closed, re-executed, or used to obtain the next result from the Multi-result sequence, the ResultSet is automatically disabled by the Statement.