How to get the execution result of the SQL statement?
The ResultSet contains all rows that meet the conditions in the SQL statement, and it provides access to data in these rows through a GET method (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, making the next row as the current line.
The following code segment is an example of executing a SQL statement. The SQL statement will return a set of rows, where column 1 is int, column 2 is String, and column 3 is byte arrays:
Java.sql.StatementStmt = conn.createstatement (); resultset r = stmt.executeQuery ("SELECT A, B, C from Table1"); while (r.next ()) {// Print the value of the current line. INT i = R.GETINT ("a"); string s = r.getstring ("b"); float f = r.Getfloat ("c"); system.out.println ("Row =" i "" S "" f);
Row and cursor
ResultSet maintains the cursor of its current data line. Each time the next method is called, the cursor moves down.
Initially, it was located before the first line, so the first call next time, the cursor will be placed on the first line, making it a current line. As the NEXT that causes the NEXT to cause the cursor to move down, obtain the Resultset line according to the order from the top.
The cursor remains valid until the ResultSet object or its parent Statement object is turned off. In SQL, the cursor of the result table is named. If the database allows positioning updates or locating deletion, you need to provide the name of the cursor as a parameter to the update or delete command. The light scalar can be obtained by calling method getcursorname.
DatabaseMetadata.supportsPositionedDelete and SupportSpositionedEDUpdate methods Check if specific connections are supported. When DBMS supports positioning updates and deleting operations, the DBMS / driver must ensure that the selected line is properly locked so that the positioning update does not cause the update of exceptions or other concurrency issues.
2. Column
Method GetXXX provides a way to get a column value in the current line. In each line, you can get column values in any order. However, in order to ensure portability, the column value should be obtained from left to right and read the column value at once.
Column names or column numbers can be used to identify columns to get data from it. For example, if the RSET object RS is named "Title", and the value is stored as a string, then any of the following code will get the value stored in the column:
String s = rs.getstring ("Title"); string s = rs.getstring (2);
Note that the column is numbered from left to right and starts from column 1. At the same time, the column name used as the input of the getxxx method is not case sensitive.
The purpose of providing the list name is to allow users who specify the column name 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 (eg, when it is exported in "select * from table1"), the column number should be used. In these cases, the user will not know how to know the column name. In some cases, there may be multiple columns with the same name in the result set of SQL queries. If the column name is used as a parameter of the getxxx method, Getxxx will return the value of the first match column name. Thus, if multiple columns have the same name, you need to use the column index to ensure that the correct column value is retrieved. At this time, the use of the rating efficiency is slightly higher.
About the information in the resultSet can be obtained by calling the method ResultSet.getMetadata. The returned ResultSetMetadata object will give the number, type, and properties of each column of its ResultSet object.
If the column name is known, but do not know the index, you can use the method FINDCOLUMN to get its column number.
3. Data type and conversion
For Getxxx methods, the JDBC driver attempts to convert basic data into a specified Java type.
Then return the suitable Java value. For example, if the getxxx method is getString, and the data type in the basic database is VARCHAR, the JDBC driver will convert VARCHAR to JavaString. GetString's return value will be a JavaString object.
4. Very large row value uses stream ResultSet to get anything average Longvarbinary or LongVarchar data. Methods getBytes and getString return data to large blocks (maximum return value for Statement.getMaxFieldSize). However, it may be more convenient to get very large data with a smaller fixed block, and this can be done by returning the ResultSet class to java.Io.input. The data can be read from the stream. Note: These streams must be accessed immediately because they will automatically turn off when the result is called getxxx next time (this is due to the basic implementation of large block data access).
JDBCAPI has three ways to get flow, with different return values:
· GetBinaryStream: Returns only the stream that only provides database origin without any conversion.
· GetasciistReam returns the stream that provides single-byte ASCII characters.
· GetUnicODestream returns a stream that provides a double-byte Unicode character.
Note: It is different from the Java stream, and the latter returns non-type bytes and can be (for example, universal in ASCII and Unicode characters. The following code demonstrates the usage of getasciistream:
Java.sql.StatementStmt = con.createstatement (); ResultSet R = Stmt.executeQuery ("SELECT X from Table2"); // Now get column 1 results in 4k block 1 Result: Byte Buff = Newbyte [4096]; While (R) .next ()) {java.io.inputStream Fin = r.Getasciistream (1); for (;;) {INTSIZE = Fin.Read (buff); if (size == - 1) {// arrive at the end of the stream Break } // Send the newly filled buffer to the ASCII output stream: Output.write (buff, 0, size);}} 5. null result value
To determine if a given result value is a JDBC NULL, you must read the column first, then use the ResultSet.wasnull method to check if the read is returned to JDBC NULL.
When using the RESULTSET.GETXXX method, the method WASNULL will return one of the following values:
(1) javanull value
For the Getxxx method that returns a Java object (for example, getString, GetBigDecimal, GetTimeStamp, GetDate, GetTime, GetTimeStamp, Getasciistream, GetUnicodeStream, GetBinaryStream, getObject, etc.).
(2) Zero: For GetByte, Getshort, GetInt, Getlong, Getfloat, and GetDouble.
(3) FALSE value: For getBoolean.
6. Optional result set or multi-result set
EXECUTEQUERY is usually used (it returns a single resultset) or ExecuteUpdate (it can be used for any database modification statement, and the number of update rows) can perform SQL statements. However, in some cases, the application does not know if the statement returns the result set before executing the statement. In addition, some stored procedures may return several different result sets and / or update counts.
In order to adapt to these, JDBC provides a mechanism that allows applications to perform statements, and then handle any set consisting of results set and update counts. The principle of this mechanism is to first call a fully universal Execute method, then call the other three methods, getResultSet, GetUpdateCount, and getMoreresults. These methods allow the application to study the results of the statement once, and determine that the given result is the resulting result is a renewal count.
The user does not have to close the ResultSet; when it generates its Statement shutdown, re-execute or use the next result from the multi-result sequence, the ResultSet will be automatically turned off by STATEMENT.
