JDBC Basic teaching Thread resultset objects

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())
{
　 // 打印当前行的值。
　 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.

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.

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. 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.