ResultSet usage and resultset usage

ResultSet usage and resultset usage

ResultSet usage highlights

 
The ResultSet is an object returned by the query results in the data. It can be said that the result set is an object that stores the query results, but the result set not only has the storage function, it also has the data manipulation function and may update the data.

The main method for reading data from the result set is getXXX (). Its parameter can be an integer that indicates the column number (starting from 1) or a column name. The return value belongs to the XXX type. If the column corresponds to a null value and XXX is an object, null values of the XXX type are returned. If XXX is a number, such as Float, 0 is returned, and boolean is returned, false. you can use getString () to return the values of all columns, but the returned values are of the string type. XXX can represent the following types: basic data types such as INTEGER (int), Boolean (Boolean), Float (Float, Double), bit (byte ), it also includes some special types, such as date type (java. SQL. date), time type (java. SQL. time), timestamp type (java. SQL. timestamp), large numbers (BigDecimal, BigInteger, etc. You can also use getArray (intcolindex/String columnname) to obtain an array of objects consisting of elements in the column where colindex is located in the current row. Use getAsciiStream (intcolindex/String colname) to obtain the ascii stream of the current row corresponding to this column. That is to say, all getXXX Methods Operate on the current row.

Result sets can be divided into four types based on their usage characteristics. The features of these four types of result sets are related to the creation of Statement statements, because the result set is generated after execution of the Statement, it can be said that the characteristics of the result set depend entirely on Statement. Of course, I mean the following four features, three types are available when Statement is created. First, there is no parameter type. It corresponds to the Statement corresponding to the basic ResultSet described below. The Connection used in the following code is not initialized. The variable conn represents the object corresponding to the Connection. SqlStr indicates the response SQL statement.

 

1. The most basic ResultSet.
The most basic ResultSet is that the ResultSet is used to store the query results. It can only be read once and cannot be read back and forth. You can create a result set as follows:

Statement st = conn. CreateStatement ()
ResultSet rs = Statement. excuteQuery (sqlStr );

Because this result set does not support the scroll reading function, if you obtain such a result set, you can only use the next () method in it to read data one by one.

 

2. The ResultSet type that can be rolled.
This type supports rolling back and forth to get the record next (), previous (), back to the first line first (), and also supports the row absolute (int n) in the ResultSet to be retrieved ), and the number of rows relative to the current row in relative (int n). To implement this ResultSet, use the following method when creating the Statement.

Statement st = conn. createStatement (int resultSetType, int resultSetConcurrency)
ResultSet rs = st.exe cuteQuery (sqlStr)

The meanings of the two parameters are as follows:
ResultSetType indicates that the type of the ResultSet object can be scrolled or not. The values are as follows:

 

ResultSet. TYPE_FORWARD_ONLY	Only scroll forward (this is the default value)
ResultSet. TYPE_SCROLL_INSENSITIVE	Both methods can implement any front and back scrolling and use various moving ResultSet pointers. The difference between the two lies in that the former is not sensitive to modification, while the latter is sensitive to modification.
Result. TYPE_SCROLL_SENSITIVE

 

ResultSetConcurency indicates that the ResultSet object can be modified. The values are as follows:

 

ResultSet. CONCUR_READ_ONLY	Set to read-only parameters.
ResultSet. CONCUR_UPDATABLE	Set to modifiable parameters.

So if you only want the Result of the type that can be rolled, you just need to assign the following value to the Statement.

Statement st = conn. createStatement (Result. TYPE_SCROLL_INSENITIVE, ResultSet. CONCUR_READ_ONLY );
ResultSet rs = st. excuteQuery (sqlStr );

The query Statement executed with this Statement produces a rolling ResultSet.

 

3. updatable ResultSet
This ResultSet object can be used to modify tables in the database, but I know that ResultSet is only equivalent to the view of tables in the database. Therefore, not all resultsets can be updated as long as they are set to update, the SQL statement of the ResultSet that can be updated must have the following attributes:
A. Only a single table is referenced.
B. It does not include join or group by clauses.
C. The columns must contain primary keywords.
If the above conditions are met, the updatable ResultSet can modify the data. The updatable result set can be created:
Statement st = createstatement (Result. TYPE_SCROLL_INSENSITIVE, Result. CONCUR_UPDATABLE)
The execution result of such Statement is an updatable result set. The update method is to move the ResultSet cursor to the row you want to update and then call updateXXX (). The XXX method has the same meaning as getXXX. The updateXXX () method has two parameters. The first one is the column to be updated, which can be the column name or serial number. The second is the data to be updated. The data type must be the same as that of XXX. UpdateRow () is called to write data to the database for each update of a row. The modification is not submitted until the ResultSet cursor does not leave the row.
You can use the updateXXX method to complete the insert operation. But first, we need to introduce two methods:
MoveToInsertRow () is to move the ResultSet to the inserted row, which is a special row in the table and does not need to specify the specific row. The system will automatically move the row by calling this method.
MoveToCurrentRow () This is to move the ResultSet to a row in memory, usually the current row. If the insert operation is not used, this method has no effect. If the insert operation is used, this method is used to return the row before the insert operation and leave the insert row, of course, you can also use the next (), previous () and other methods to leave the inserted row.
To complete database insertion, first call moveToInsertRow () to move to the inserted row, and then call the updateXXX method to update the data in each column. After the update is completed, it is the same as the update operation, to write data to the database, but insertRow () is used here. Make sure that the ResultSet does not leave the insert column before the method is executed. Otherwise, the insert is not executed, and the update of the inserted row will be lost.

 

4. retainable ResultSet
Under normal circumstances, if you use Statement to execute a query and then execute another query, the result set of the first query will be disabled, that is, the result set corresponding to all Statement queries is one. If you call the commit () method of Connection, the result set is closed. Persistence means whether the ResultSet result is closed or not closed when it is submitted. After both JDBC2.0 and 1.0 are submitted, the ResultSet will be disabled. However, in JDBC3.0, we can set whether the ResultSet is disabled. To complete the creation of such a ResultSet object, the Statement to be used must have three parameters. The Statement creation method is the third method of Statement. As follows:

 

Statementst = createStatement (int resultsetscrollable, int resultsetupdateable, intresultsetholdability)
ResultSet rs = st. excuteQuery (sqlStr );

 

The first two parameters are exactly the same as those in the createStatement method. Here we only introduce the third parameter:
ResultSetHoldability indicates whether to enable the result set after the result set is submitted. The values include:

 

ResultSet. HOLD_CURSORS_OVER_COMMIT	Indicates that the ResultSet is not closed when the modification is submitted.
ResultSet. CLOSE_CURSORS_AT_COMMIT	Indicates that the ResultSet is closed when the modification is submitted.

 

However, this function can only be set up with the JDBC3.0 driver.

 

 

 

 

 

Summary:

JDBCAPI 2.0/3.0MediumResultSetRecord set
Simple and practical new features of the ResultSet record set in jdbc api 2.0/3.0

1. Several constants are defined. These constants are used to specify the direction and other properties of the type cursor movement of the ResultSet, as shown below:

FETCH_FORWARD	This constant is used to specify the order of rows in the processing record set, from the beginning to the end, that is, from the first row to the last row.
FETCH_REVERSE	This constant is used to specify the order of rows in the processing record set, starting from the last row to the first row.
FETCH_UNKNOWN	This constant does not specify the order of rows in the processing record set, which is determined by the JDBC driver and database system.
TYPE_FORWARD_ONLY	This constant is used to specify that the direction of moving the database cursor is forward. If backward movement is not allowed, only the next () method of the ResultSet interface can be used instead of the previous () method. Otherwise, an error will occur.
TYPE_SCROLL_INSENSITIVE	This constant is used to specify that the database cursor can be moved before and after the record set, and the record set obtained by the current database user is not sensitive to the operations of other users; that is, the current user is browsing the data in the record set. At the same time, other users have updated the data in the database, but the data in the record set obtained by the current user will not be affected.
TYPE_SCROLL_SENSITIVE	This constant is used to specify that the database cursor can be moved before and after the record set, and the record set obtained by the current database user is sensitive to other users' operations, that is, the current user is browsing the record set, however, the operations of other users change the data in the database, and the data in the record set obtained by the current user also changes synchronously, this may cause serious errors. Therefore, we recommend that you use this constant with caution.
CONCUR_READ_ONLY	This constant is used to specify the concurrencymode of the current record set as read-only. Once this constant is used, you cannot update the data in the record set.
CONCUR_UPDATABLE	This constant is used to specify the concurrencymode of the current record set to be updated. Once this constant is used, you can use updateXXX () to update the record.
CLOSE_CURSORS_AT_COMMIT	Indicates that the ResultSet is closed when the modification is submitted.
HOLD_CURSORS_OVER_COMMIT	Indicates that the ResultSet is not closed when the modification is submitted.

2. The ResultSet interface provides a complete set of locating methods.

You can locate any row in the record set:

 

Public boolean absolute (int row): This method is used to set a row in the record set as the current row and move the database cursor to the specified row, the row parameter specifies the row number of the target row. This is an absolute row number. It is calculated from the first row of the record set and is not a relative row number.

Public boolean relative (int rows): This method is used to set a row in the record set to the current row, but its parameter rows indicates the row number of the target row relative to the current row.

Public boolean first (); this method is used to locate the current row to the first row of the database record set.

Public boolean last (); this method works exactly the opposite of the first () method.

Public boolean isFirst (); this method is used to check whether the first row of the current row records the set. If true is returned, false is returned.

Public boolean isLast (); this method is used to check whether the last row of the current row records the set. If true is returned, false is returned.

Public void afterLast (); this method is used to move the database cursor to the end of the record set, after the last row of the record set, this method does not work if the record set does not contain any rows.

Public void beforeFirst (); this method moves the database cursor to the beginning of the record set, before the first row of the record set, this method does not work if the record set does not contain any rows.

Public boolean isAfterLast (); this method checks whether the database cursor is at the end of the record set. If true is returned, false is returned.

Public boolean isBeforeFirst (); this method checks whether the database cursor is at the beginning of the record set. If true is returned, false is returned.

Public boolean next (); this method is used to move the database cursor one byte forward, making the next row the current row. When the record set object is just opened, the database cursor is located at the beginning of the record set. The first use of the next () method will cause the database cursor to locate the first row of the record set, and the second use of next () the method will position the database cursor to the second row of the record set, and so on.

Public boolean previous (); this method is used to move the database cursor backward to make the previous row the current row.

 

3 The ResultSet interface adds support for Row Operations (most exciting)

The modified record set interface (ResultSet Interface) enables it to support a record set that can be rolled. That is, the database cursor can freely scroll forward or backward in the returned record set object, or locate a special row. Using the new method defined in the ResultSet interface, JSP/Servlet programmers can use the Java language to update record sets, such as inserting records and updating data in a row, instead of executing SQL statements, this greatly facilitates the development of programmers and enjoys the fun of Java programming.
Some newly added methods in the ResultSet interface are as follows:

 

Public boolean rowDeleted (); If a row in the current record set is deleted, a blank space is set in the record set; The rowDeleted () method is called. If the empty space is detected, true is returned. If no space is detected, false is returned.

Public boolean rowInserted (); if a new row is inserted in the current record set, this method returns true; otherwise, false.

Public boolean rowUpdated (); if the data of the current row in the current record set is updated, this method returns true; otherwise, false.

Public void insertRow (); this method inserts a new row into the current record set.

Public void updateRow (); this method updates the data of the current row of the current record set.

Public void deleteRow (); this method deletes the current row of the current record set.

Public void updateString (int columnIndex, String x); this method updates the value of a column in the current row of the current record set. The data type of this column is String (that is, the Java data type is String, the corresponding JDBC data type is VARCHAR, NVARCHAR, and other data types ). The columnIndex parameter of this method specifies the column index of the column to be updated. The index of the first column is 1, and so on. The second parameter x represents the new value. This method does not perform database operations, only after the insertRow () method or updateRow () method is executed can the data in the record set and database be truly updated.

Public void updateString (String columnName, String x); this method is similar to the method with the same name described above. However, the first parameter of this method is columnName, which indicates the name of the column to be updated, instead of columnIndex.

4. Basic operations:
The procedure for inserting a new row into the current record set of the database is as follows:
1. Call the moveToInsertRow () method;
2. Call the updateXXX () method to specify the values of each column in the inserted row;
3. Call the insertRow () method to Insert a new row into the database.

The method for updating the value of a record (the value of a row) in the database is:
1. Locate the row to be modified (locate using absolute () relative () and other methods );
2. Use the updateXXX () method to set the new value of a column in a row. the Java data type represented by XXX must be mapped to the JDBC data type of a column, if you want rollback to perform this operation, use the cancelRowUpdates () method before calling the updateRow () method. This method can restore the values of a column in a row;
3. Use the updateRow () method to complete the UPDATE operation.

To delete a row in a record set (that is, to delete a record:
1. Locate the row to be modified (locate using absolute () relative () and other methods );
2 Use deleteRow ()

To delete a row in a record set (that is, to delete a record:
1. Locate the row to be modified (locate using absolute () relative () and other methods );
2. Use the deleteRow () method.

<To: http://soft-development.iteye.com/blog/1420323>