The ResultSet InterfaceA 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 SetsPrior 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:
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:
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.
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.
Caution Scrollable result sets are a significant overhead. Use them only when absolutely required. Updatable Result SetsJDBC 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:
Manipulating DataOnce 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.
Inserting and Persisting DataYou 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.
Batch UpdatesInvoking 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.
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(); |