ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

An Introduction to JDBC, Part 2
Pages: 1, 2, 3

As of JDBC 2.0, result sets are also updateable. From this perspective, there are two different kinds of result sets: read-only result sets that don't allow changes to the underlying data and updateable result sets that allow such changes, subject to transaction limitations and so on.



To create an updateable, scroll-sensitive result set, we pass two extra arguments to the createStatement( ) method.

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
 ResultSet.CONCUR_UPDATEABLE);

If you don't pass any arguments to createStatement( ), you get a forward-only, read-only result set, just as you would using JDBC 1.0. Note that if you specify a scrollable result set (either sensitive or insensitive), you must also specify whether or not the result set is updateable. After you have created a scrollable ResultSet, use the methods listed in Table 2-2 to navigate through it. As with JDBC 1.0, when you start working with a ResultSet, you are positioned before the first row of results.

Table 2-2: JDBC 2.0 Record Scrolling Functions

Method

Related Articles:

An Introduction to JDBC, Part 3
In part three of this four-part excerpt on JDBC from Java Enterprise in a Nutshell, learn about error handling, prepared statements, BLOBs and CLOBs.

An Introduction to JDBC, Part 1
In this excerpt from Chapter 2 of Java Enterprise in a Nutshell, the authors introduce the JDBC architecture.

Function

first( )

Move to the first record.

last( )

Move to the last record.

next( )

Move to the next record.

previous( )

Move to the previous record.

beforeFirst( )

Move to immediately before the first record.

afterLast( )

Move to immediately after the last record.

absolute(int)

Move to an absolute row number. Takes a positive or negative argument.

relative(int)

Move backward or forward a specified number of rows. Takes a positive or negative argument.

The JDBC 2.0 API also includes a number of methods that tell you where you are in a ResultSet. You can think of your position in a ResultSet as the location of a cursor in the results. The isFirst( ) and isLast( ) methods return true if the cursor is located on the first or last record, respectively. isAfterLast( ) returns true if the cursor is after the last row in the result set, while isBeforeFirst( ) returns true if the cursor is before the first row.

With an updateable ResultSet, you can change data in an existing row, insert an entirely new row, or delete an existing row. To change data in an existing row, use the newupdateXXX( ) methods of ResultSet. Let's assume we want to update the CUSTOMER_ID field of the first row we retrieve (okay, it's a contrived example, but bear with us):

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
 ResultSet.CONCUR_UPDATEABLE);
ResultSet rs = stmt.executeQuery("SELECT NAME, CUSTOMER_ID FROM CUSTOMERS");
 
rs.first(  );
rs.updateInt(2, 35243); 
rs.updateRow(  );

Here we use first( ) to navigate to the first row of the result set and then call updateInt( ) to change the value of the customer ID column in the result set. After making the change, call updateRow( ) to actually make the change in the database. If you forget to call updateRow( ) before moving to another row in the result set, any changes you made are lost. If you need to make a number of changes in a single row, do so with multiple calls to updateXXX( ) methods and then a single call to updateRow( ). Just be sure you call updateRow( ) before moving on to another row.

The technique for inserting a row is similar to updating data in an existing row, with a few important differences. The first step is to move to what is called the insert row, using the moveToInsertRow( ) method. The insert row is a blank row associated with the ResultSet that contains all the fields, but no data; you can think of it as a pseudo-row in which you can compose a new row. After you have moved to the insert row, use updateXXX( ) methods to load new data into the insert row and then call insertRow( ) to append the new row to the ResultSet and the underlying database. Here's an example that adds a new customer to the database:

ResultSet rs = stmt.executeQuery(
     "SELECT NAME, CUSTOMER_ID FROM CUSTOMERS");
rs.moveToInsertRow(  );
rs.updateString(1, "Tom Flynn");
rs.updateInt(2, 35244); 
rs.insertRow(  );

Note that you don't have to supply a value for every column, as long as the columns you omit can accept null values. If you don't specify a value for a column that can't be null, you'll get a SQLException. After you call insertRow( ), you can create another new row, or you can move back to the ResultSet using the various navigation methods shown in Table 2-2. One final navigation method that isn't listed in the table is moveToCurrentRow( ). This method takes you back to where you were before you called moveToInsertRow( ); it can only be called while you are in the insert row.

Deleting a row from an updateable result set is easy. Simply move to the row you want to delete and call the deleteRow( ) method. Here's how to delete the last record in a ResultSet:

rs.last(  );
rs.deleteRow(  );

Calling deleteRow( ) also deletes the row from the underlying database.

Note that not all ResultSet objects are updateable. In general, the query must reference only a single table without any joins. Due to differences in database implementations, there is no single set of requirements for what makes an updateable ResultSet.

As useful as scrollable and updateable result sets are, the JDBC 2.0 specification doesn't require driver vendors to support them. If you are building middleware or some other kind of system that requires interaction with a wide range of database drivers, you should avoid this functionality for the time being. The extended JDBC 2.0 DatabaseMetaData object can provide information about scrolling and concurrency support.

Java-Aware Databases

This is Sun's term. We have yet to see any packages actually marketed as Java-relational databases, but many newer packages, including Oracle 8i, are capable of storing Java classes. A number of these products also use Java as a trigger language, generally in a JDBC structure.

Java is object-oriented; relational databases aren't. As a result, it's decidedly difficult to shoehorn a Java object into a stubbornly primitive-oriented database table. Luckily, the wind is changing, and newer database systems, including object-oriented database management systems (OODBMS) and Java-relational database management systems (see sidebar), provide direct support for storing and manipulating objects. While a regular relational database can store only a limited number of primitive types, a JDBMS system can store entire, arbitrary Java objects.

Say we want to store a customized Java Account object in the ACCOUNTS table in a database. With a standard DBMS and JDBC 1.0, we have to pull each piece of data (account number, account holder, balance, etc.) out of the Account object and write it to a complicated database table. To get data out, we reverse the process. Short of serializing the Account object and writing it to a binary field (a rather complex operation), we're stuck with this clumsy approach. (Various commercial products, such as Sun's Forte developer tool, automatically handle mapping objects to database records and vice versa. Check this site for more information.)

With JDBC 2.0, the getObject( ) method has been extended to support these new Java-aware databases. Provided that the database supports a Java-object type, we can read the Account object just like any primitive type:

ResultSet rs = stmt.executeQuery("SELECT ACCOUNT FROM ACCOUNTS");
rs.next(  );
Account a = (Account)rs.getObject(1);

To store an object, we use a PreparedStatement and the setObject( ) method:

Account a = new Account(  ); 
// Fill in appropriate fields in Account object
 
PreparedStatement stmt = con.prepareStatement(
 "INSERT INTO ACCOUNTS (ACCOUNT) VALUE (?)");
stmt.setObject(1, a);
stmt.executeUpdate(  );

A column that stores a Java object has a type of Types.JAVA_OBJECT. The JDBC API doesn't take any special steps to locate the bytecodes associated with any particular class, so you should make sure that any necessary objects can be instantiated with a call to Class.forName( ).

Next time, learn about errors and more.

William Crawford, Jim Farley is a coauthor of Java Enterprise in a Nutshell, 2nd Edition, and has been developing web-based enterprise applications since 1995. He is currently the Director of the Informatics Solutions Group at Children's Hospital, Boston, where he and his team are building open source Personally Controlled Health Record systems and tools for managing agile development projects in healthcare and regulated industries.

David Flanagan is the author of a number of O'Reilly books, including Java in a Nutshell, Java Examples in a Nutshell, Java Foundation Classes in a Nutshell, JavaScript: The Definitive Guide, and JavaScript Pocket Reference.


View catalog information for Java Enterprise in a Nutshell, Second Edition

Return to ONJava.com.