JSP Learning--Comprehensive analysis of JDBC (5)

How does JS get the execution result of the SQL statement?

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 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.statementstmt=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);
}

1. Line and cursor

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.

Databasemetadata.supportspositioneddelete and Supportspositionedupdate methods to check whether a particular connection supports these operations. When the DBMS supports locating update and delete operations, 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.

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.

3. Data types and conversions

For the GetXXX method, the JDBC driver attempts to convert the base data to the specified Java type.

The appropriate Java values are then returned. For example, if the GetXXX method is getstring and the data type in the base database is varchar, the JDBC driver converts varchar to javastring. The return value of the GetString will be the Javastring object.

4. Using 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).

Jdbcapi has three methods for obtaining a stream, each with a different return value:

Getbinarystream: Returns a stream that supplies 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.statementstmt=con.createstatement ();
ResultSet r=stmt.executequery ("Select X from Table2");
Now get column 1 result with 4 K block size:
BYTE buff=newbyte[4096];
while (R.next ()) {
Java.io.InputStream Fin=r.getasciistream (1);
for (;;) {
Intsize=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. Null result value

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

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

(1) Javanull value

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

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

(3) False value: for Getboolean.

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.