The ResultSet Interface

The ResultSet Interface

A ResultSet is essentially a Java object. When you execute a query on the database, you will get a ResultSet, that is, a tabular representation of the data retrieved from the database table. Technically, ResultSet is a Java interface that a vendor of a JDBC driver must implement. The ResultSet interface contains methods that retrieve the tabular data of the results of the executed query. A result set maintains a cursor on the row in the database. Result sets are normally traversed in a while loop using the next() method of the ResultSet interface.

In the JDBC 1.0 API, the ResultSet interface had methods to retrieve the data from the result set. In JDBC 2.0 API, many new features were added to the ResultSet interface. Now look at the main features provided in the JDBC 2.0 API that relate to result sets.

Note

A result set always begins from column number 1. If you traverse a result set using column number 0, you may get an SQL exception.


Scrollable Result Sets

Prior to the JDBC 2.0 API, data could be retrieved from the ResultSet only by traversing the result set in the forward direction. Once a row or column of the result set data was traversed, you could not go back to that row or column and refetch the data. Also, there was no way to arbitrarily read data from any row in the result set. This meant that if, for example, you needed to read the data from the last row of the result set, you would still have to traverse through the entire result set. In some cases this limited the applications that needed to interpret the already-retrieved data. To get around this, programmers would store the retrieved data in a two-dimensional Vector data structure. The JDBC 2.0 API devised a mechanism called scrollable result sets to solve this problem.

A scrollable result set (ResultSet) is a special result set that allows traversal of a ResultSet in the forward and reverse directions. You can position the cursor of the result set at any position in the result set.

Because a scrollable result set is just a special type of result set, now examine how you go about creating scrollable result sets.

To create a scrollable result set, you need to define the scrollable type and the concurrency mode for the result set. The scrollable type and the concurrency mode are constants defined in the ResultSet interface. There are three types of result sets:

  • TYPE_FORWARD_ONLY The default type for a result set. Setting this type allows the result set to be scrolled in the forward direction only. To create a result set with this type, take a look at the following code snippet:

     Statement myStmt =  myCon.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 

    where CONCUR_READ_ONLY is the concurrencyMode for a result set. (You will find an explanation of what a result set's concurrency mode is after the following result set type examples.) For a forward-only scrolling result set, the concurrency mode is CONCUR_READ_ONLY.

  • TYPE_SCROLL_SENSITIVE Defined for a result set to enable it to scroll in both the forward and reverse directions. In addition to allowing scrolling of the result set, the TYPE_SCROLL_SENSITIVE can detect changes made by other applications to the data in the database that has been retrieved by the result set as well. Making a result set type TYPE_SCROLL_SENSITIVE is useful when you need your result set to continuously reflect the very latest data in the database. To create a result set of this type, take a look at this code snippet:

     Statement myStmt = myCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,          ResultSet.CONCUR_READ_ONLY); 
  • TYPE_SCROLL_INSENSITIVE Also allows the result set to be scrollable in both the forward and reverse directions. But making the type of the result set TYPE_SCROLL_INSENSITIVE makes the result set impervious to any changes made to the data in the database that is captured in the result set by another application. Consequently, your result set will not reflect the latest data in the database, but it will still be scrollable. To see a result set of this type, look at this code snippet:

     Statement myStmt =  myCon.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,         ResultSet.CONCUR_READ_ONLY); 

Before you use a result set that you created using any of these types, you need to verify whether the result set type has been set by the JDBC driver. In cases where the driver does not support some of these result set types, the default TYPE_FORWARD_ONLY result set will be generated. To verify the result set type, you need to call the "getType()" method on the result set object after you create the statement (with the appropriate result type) and retrieve the result set object. The following code snippet shows you how:

 // create the scrollable result set  Statement myStmt = myCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,         ResultSet.CONCUR_READ_ONLY); // execute the query and get the ResultSet object ResultSet myRes = myStmt.executeQuery(sqlQuery); // check whether the result set is scrollable and of the correct // type expected int resType = myRes.getType(); 

The getType() method of the result set object returns an integer value. The following values determine the type of result set created:

  • 1003 indicates the result set is of TYPE_FORWARD_ONLY

  • 1004 indicates the result set is of TYPE_SCROLL_INSENSITIVE

  • 1005 indicates the result set is of TYPE_SCROLL_SENSITIVE

Now consider the other parameter that is set along with the result set type: the concurrency mode. The concurrency mode of a result set in effect is a type of lock acquired on the table that is being queried. Recall that databases have read and write locks for records and tables in the database. There are two concurrency modes available in conjunction with result sets.

  • CONCUR_READ_ONLY When you set the concurrency mode of a result set to CONCUR_READ_ONLY it makes the result set read only. No updates to the data of the result set are possible in this case. The CONCUR_READ_ONLY concurrency mode is the default concurrency mode for a result set. The following code snippet shows you how to define the concurrency mode for a result set:

     Statement myStmt =  myCon.createStatement(resultSetType, ResultSet.CONCUR_READ_ONLY); 

    where resultSetType can be TYPE_FORWARD_ONLY, TYPE_SCROLL_SENSITIVE and TYPE_SCROLL_INSENSITIVE.

  • CONCUR_UPDATABLE When you set the concurrency mode of a result set as CONCUR_UPDATABLE, it allows updates to the data contained in the result set. Updates to the result set can be persisted to the database as well.

     Statement myStmt =  myCon.createStatement(resultSetType, ResultSet.CONCUR_UPDATABLE); 

The preceding discussion explained how to set the result type of a result set to make it scrollable or non-scrollable. Now discover some of the methods in the result set that enable you to utilize the scrolling ability of the result set.

  • next() Enables traversing of the result set in the forward direction.

  • previous() Allows traversing of the result set in the reverse direction. To use the previous() method, position the cursor of the result set at the end of the result set by calling the afterLast() method of the ResultSet interface before you traverse the result set in reverse.

  • absolute() Positions the cursor in a specific row in the result set. Pass the row number to the location where the cursor needs to be positioned as the parameter for this method.

  • relative() Positions the cursor in a result set relative to the current cursor position. The relative() method takes a row number as a parameter.

  • getRow() Determines the row number of the current cursor position in the result set.

Caution

Scrollable result sets are a significant overhead. Use them only when absolutely required.


Updatable Result Sets

JDBC 2.0 API allows developers to programmatically insert and update data retrieved from the database as a result of executing a query, all without the use of additional SQL statements. To achieve this, another type of result set was introduced in the JDBC 2.0 API, namely, the updatable result set. As you learned in the earlier section, scrollable result sets are created by passing the result set type and the concurrency mode to the createStatement() method of the Statement interface. Updatable result sets use the same technique. To create an updatable result set, you need to use the TYPE_SCROLL_SENSITIVE result set type and set the concurrency mode as CONCUR_UPDATABLE for the createStatement() method. A result set created as a result of a query executed by this Statement object will function as an updatable result set.

See the following code snippet for creating an updatable result set:

 Statement myStmt =  myCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,         ResultSet.CONCUR_UPDATABLE); 

The getConcurrencyMethod() is used as a verification mechanism to check whether the result set is an updatable result set. JDBC drivers that do not support an updatable result set will return a read-only result set. The following code snippet shows you how:

 // create the scrollable result set  Statement myStmt = myCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,         ResultSet.CONCUR_UPDATABLE); // execute the query and get the ResultSet object ResultSet myRes = myStmt.executeQuery(sqlQuery); // check whether the result set is scrollable and of the correct // type expected int resType = myRes.getConcurrency(); 

The getConcurrency() method of the result set object returns an integer value. The following values determine the type of the result set created:

  • 1007 indicates the result set is of CURSOR_READ_ONLY

  • 1008 indicates the result set is of CURSOR_UPDATABLE

Manipulating Data

Once you've created an updatable result set in this fashion, you can use methods provided in the ResultSet interface to manipulate the data in the result set and persist the changed data to the database.

  • updateXXX() The updateXXX() methods accept a column name or column number and the new value as parameters. The data in the result set can be updated by calling the update method according to the data type of the column that is modified. For example, to update a column named book_price in the first row of the result set with the new price of the book, you would use the following code snippet:

     // position the cursor to the first row in the result set  myResultSet.first(); // update the column named book_price with the new value myResultSet.updateFloat("book_price", 49.99); 

    Because the book_price column in the database table accepts float data, use the updateFloat() method of the ResultSet interface. The ResultSet interface contains updateXXX () methods for all the data types.

  • updateRow() After updating the data in the result set by calling the updateXXX() methods, you should execute the updateRow() method to persist the changes in the result set to the database.

     // persist changes  myResultSet.updateRow(); 
  • cancelRowUpdates() If you need to revert to the previous data of the result set at any point of time prior to persisting the data to the database, you should call the cancelRowUpdates() method. The cancelRowUpdates() method will not work if you have called the updateRow() method because the updateRow() method persists the changed result set data to the database.

Inserting and Persisting Data

You have learned ways to update data in the result set. Now take a look at some methods for inserting and persisting data in the result set.

  • moveToInsertRow() There is a special row defined in the JDBC 2.0 API called an insert row where an entirely new row of new data to be inserted into the database is present in the result set. This special row is not actually a part of the result set but is an additional space allocated along with the result set. Before creating the data for the new row, you need to position the cursor to this special insert row. The moveToInsertRow() method of the ResultSet interface allows you to move the cursor to the special insert row. The moveToInsertRow() must always be called before any new data can be inserted in the result set.

  • insertRow() The insertRow() method of the ResultSet interface functions in a way that is similar to the updateRow() method.

  • deleteRow() After it updates and inserts data to an updatable result set, the JDBC 2.0 API provides a mechanism to delete any rows in the updatable result set. To delete a row in the updatable result set, you need to position the cursor to the proper position in the result set using the absolute() or relative() methods of the ResultSet interface before you delete the row in the result set.

Batch Updates

Invoking individual update statements to update a database is an overhead, especially if the update statements are part of a logical function. The JDBC 2.0 API provides a way to group update statements together and send them to the database as one set. This grouping together of update statements is termed a batch update.

A batch update is similar to writing a stored procedure in the database with SQL statements. The only difference in a batch update is that the SQL statements are grouped programmatically in Java by the JDBC driver. The methods available to perform batch updates are found in the Statement interface.

  • addBatch() Adds SQL statements to the list of SQL statements for the current Statement object. You can call the addBatch() method any number of times.

  • clearBatch() Resets the list of SQL statements in the Statement object.

  • executeBatch() Sends the SQL statements as a group to the database. The executeBatch() method returns an update count value that indicates how many rows were affected in the database.

The following is a code snippet for batch updates:

 // create a statement  myStmt = myCon.createStatement; // group the statements myStmt.addBatch("INSERT INTO books_tbl VALUES (01,         "Teach yourself Java in 21 days.", 29.99)"); myStmt.addBatch("INSERT INTO books_tbl VALUES (01,         "Teach yourself J2EE in 21 days.", 39.99)"); myStmt.addBatch("INSERT INTO books_tbl VALUES (01,         "Teach yourself JMS in 21 days.", 49.99)"); // send the group of statements to the database int [] updateCountArray = myStmt.executeBatch(); 


Sams Teach Yourself BEA WebLogic Server 7. 0 in 21 Days
Sams Teach Yourself BEA WebLogic Server 7.0 in 21 Days
ISBN: 0672324334
EAN: 2147483647
Year: 2002
Pages: 339

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net