New Features in JDBC 2.0 API

The JDBC 2.0 includes many new features in the java.sql package as well as the javax.sql Standard Extension Package. This section discusses three of the new features from the core 2.0 API and a later section briefly describes a few features found in the Standard Extension Package. The JDBC 2.0 core APIs found in the java.sql package is available as part of the standard SDK 1.3 download. The Optional Package APIs, which are within the javax.sql package, are available as a separate download. You can download the Optional Package at the following URL:

 http://java.sun.com/products/jdbc/download.html 

The new JDBC 2.0 core features discussed in this chapter are

• Scrollable ResultSets

• Updatable ResultSets

• Batch Updates

Caution

Be careful when choosing a JDBC 2.0 driver. Some driver vendors report that their drivers are JDBC 2.0 compliant, but when you start testing some of the newer 2.0 features, you get an exception that says that 2.0 feature is not yet implemented. Always understand which features are implemented and which ones are not for a particular driver or database.

Using Scrollable ResultSets

In some of the previous examples with ResultSet objects that were returned from statement executions, the results were moved through forward one row at a time. With the JDBC 2.0 API, you can also move backward. There are also methods that move the cursor to a particular row in the ResultSet Having a cursor that can move to a particular row is useful when you have a record in the ResultSet that needs to be updated. There are other benefits to the bidirectional movement of the cursor in a ResultSet You'll see some of those benefits in the examples. ResultSets are not scrollable by default. You need to set up a ResultSet that is scrollable.

Caution

There is some overhead in setting up and using a scrollable ResultSet, so make sure you really need one before using it. Otherwise, just use a standard ResultSet that moves only in the forward direction.

To create a scrollable ResultSet object, you need to specify two new parameters to the createStatement method on the Connection class. Both parameters are required, and the order is important here. The first parameter is the scroll type. The options are

• TYPE_FORWARD_ONLY

• TYPE_SCROLL_INSENSITIVE

• TYPE_SCROLL_SENSITIVE

The TYPE_FORWARD_ONLY constant is the default for a ResultSet when no type is specified. This is a ResultSet that has a cursor that can only move forward. Using one of the other constants creates a scrollable ResultSet Using the TYPE_SCROLL_INSENSITIVE constant creates a ResultSet that is not sensitive to changes made to it while it remains open , whereas the TYPE_SCROLL_SENSITIVE constant sets up a ResultSet that is sensitive to changes while it's open. Of course, when the ResultSet is closed and then reopened, it will always reflect the changes.

The second parameter that must be used determines whether the ResultSet is updatable or read-only. Here is a code fragment that shows how to set up a scrollable ResultSet :

 Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,                        ResultSet.CONCUR_READ_ONLY); ResultSet srs = stmt.executeQuery("SELECT * FROM EMPLOYEES"); 

Caution

Because both parameters are int primitives, the compiler will not complain when you have them backward. Make sure that you have the order correct or you might not actually get a scrollable ResultSet

The driver or database that you are using might not support scrollable ResultSets In that case, you will get the default ResultSet, which is forward scrolling and read-only. Make sure to check with the database vendor and JDBC driver vendor to see whether they support this feature. If you attempt to use one of the new JDBC 2.0 features that is not supported, you will get an SQLException and probably a message saying that it's not yet supported.

You can also determine programmatically whether the ResultSet is scrollable. When you create a scrollable ResultSet as describe previously, you can use the getType method on the ResultSet instance. The method returns an int value. The possible values are

• 1003 means ResultSet.TYPE_FORWARD_ONLY

• 1004 means ResultSet.TYPE_SCROLL_INSENSITIVE

• 1005 means ResultSet.TYPE_SCROLL_SENSITIVE

In the previous examples, the next method was used to scroll through the ResultSet in the forward direction to the next row, if there was one. As you can may have guessed, to go backward one row, you can use the previous method. As with the forward scrolling ResultSet, the cursor is still positioned before the first row with a scrollable ResultSet Here is a code fragment using the previous method. This fragment assumes that the ResultSet is scrollable:

 while (rs.previous()) {   String name = rs.getString("EMP_NO");   String salaryStr = rs.getString("EMP_SALARY");   System.out.println(name + " - " + salaryStr); } 

Now, take a look at a complete example using a scrollable ResultSet However, before you do that, you need to know about JDBC version 2.0 compatibility. The JDBC Type II driver for Oracle8i Lite doesn't support some of these new features introduced in JDBC 2.0. There is an alternative that is used for this example. That alternative is the JDBC-ODBC Bridge from Sun. This Bridge is a Type I driver that will allow you to connect to any ODBC datasource and make JDBC calls on it. The performance is not that great because a Type I driver is being used, but it does support these new JDBC 2.0 features.

The changes you need to make to use the new driver affect only the DatabaseManager class that you have been using from the previous JDBC chapter. This is why it is nice to isolate the database connection management code. The only changes to switch to the newer driver are contained with the DatabaseManager class. Listing 27.1 shows the new DatabaseManager class that uses the JDBC-ODBC Bridge.

Listing 27.1 Source Code for New DatabaseManager.java

 import java.sql.*; import java.util.*; import sun.jdbc.odbc.*; public class DatabaseManager {   static private String urlHeader = "jdbc:odbc:";   static private String databaseName = "POLITE";   static private String user = "SYSTEM";   static private String password = "";   static private String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";   public static Connection getConnection()   {     // Define the local Connection instance that gets returned     Connection conn = null;     try     {       // Load the JDBC driver       Class.forName(driverName);       // Get an instance of the Connection. Really the Connection is an       // interface that some class implements.       // Concat the three parts together to build the full URL       String databaseURL = urlHeader + databaseName;       conn = DriverManager.getConnection(databaseURL, user, password);     }     catch(ClassNotFoundException ex)     {       // The JDBC driver class could not be found       ex.printStackTrace();       System.exit(-1);     }     catch(SQLException ex)     {       // A sql exception occurred, for now just print out the stack trace       ex.printStackTrace();     }     return conn;   } } 

In Listing 27.1, the only changes that have to be made are the urlHeader String and the driverName String This is why it's nice to isolate the necessary database parameters. The JDBC-ODBC Bridge driver comes with the SDK so you will not have to download a different driver.

Listing 27.2 shows the ScrollableResultSetExample using the new DatabaseManager to scroll backward through the Employee records in the database.

Listing 27.2 Source Code for ScrollableResultSetExample.java

 import java.sql.*; public class ScrollableResultSetExample {   // Private reference to an instance of the Connection   Connection connection = null;   // Default Constructor   public ScrollableResultSetExample(Connection conn)   {     super();     connection = conn;   }   // Private accessor for the connection   private Connection getConnection()   {     return connection;   }   // Print out the Employee Records backwards using a scrollable   // ResultSet   public void printEmployeeRecordsBackwards()   {     Statement stmt = null;     ResultSet rs = null;     try     {       // Get a scrollable ResultSet       Connection conn = getConnection();       stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,                                   ResultSet.CONCUR_READ_ONLY);       // Get some fields from the employee table       String sqlQuery =  "SELECT EMPNO, EName, Job, MGR, HIREDATE FROM EMP";      rs = stmt.executeQuery(sqlQuery);       // Move the cursor directly to the last position in the ResultSet       // You can't do this like this before JDBC 2.0       rs.last();       // Move the cursor backwards through the ResultSet       while(rs.previous())       {         String nbr = rs.getString(1);         String name = rs.getString(2);         String job = rs.getString(3);         String mgr = rs.getString(4);         Timestamp hireDate = rs.getTimestamp(5);         // Create a new Employee using the data         Employee emp = new Employee(nbr, name, job, mgr, hireDate);         // Call the Employee's default toString() method         System.out.println(emp.toString());       }      rs.close();     }     catch(SQLException ex)     {       ex.printStackTrace();     }   }   public static void main(String[] args)   {      // Use the previous DatabaseManager     Connection conn = DatabaseManager.getConnection();     ScrollableResultSetExample example = new ScrollableResultSetExample(conn);     example.printEmployeeRecordsBackwards();     // Always make sure to close the connection when you are finished     try     {       conn.close();     }     catch(SQLException ex)     {       ex.printStackTrace();     }     catch(Exception ex)     {       ex.printStackTrace();     }   } } 

Listing 27.3 shows the output from ScrollableResultExample

Listing 27.3 Output for ScrollableResultSetExample

 C:\jdk1.3se_book\classes>java ScrollableResultSetExample ID: 7876 - Name: ADAMS      - Job: CLERK     - Mgr: 7788 - Hire Date: 1983-01-12  00:00:00.0 ID: 7788 - Name: SCOTT      - Job: ANALYST   - Mgr: 7566 - Hire Date: 1982-12-09  00:00:00.0 ID: 7369 - Name: SMITH      - Job: CLERK     - Mgr: 7902 - Hire Date: 1980-12-17  00:00:00.0 ID: 7902 - Name: FORD       - Job: ANALYST   - Mgr: 7566 - Hire Date: 1981-12-03  00:00:00.0 ID: 7521 - Name: WARD       - Job: SALESMAN  - Mgr: 7698 - Hire Date: 1981-02-22  00:00:00.0 ID: 7900 - Name: JAMES      - Job: CLERK     - Mgr: 7698 - Hire Date: 1981-12-03  00:00:00.0 ID: 7844 - Name: TURNER     - Job: SALESMAN  - Mgr: 7698 - Hire Date: 1981-09-08  00:00:00.0 ID: 7499 - Name: ALLEN      - Job: SALESMAN  - Mgr: 7698 - Hire Date: 1981-02-20  00:00:00.0 ID: 7654 - Name: MARTIN     - Job: SALESMAN  - Mgr: 7698 - Hire Date: 1981-09-28  00:00:00.0 ID: 7566 - Name: JONES      - Job: MANAGER   - Mgr: 7839 - Hire Date: 1981-04-02  00:00:00.0 ID: 7782 - Name: CLARK      - Job: MANAGER   - Mgr: 7839 - Hire Date: 1981-06-09  00:00:00.0 ID: 7698 - Name: BLAKE      - Job: MANAGER   - Mgr: 7839 - Hire Date: 1981-05-01  00:00:00.0 ID: 7839 - Name: KING       - Job: PRESIDENT - Mgr: null - Hire Date: 1981-11-17  00:00:00.0 

Note

The type of message that you will see when your JDBC driver does not implement a JDBC 2.0 feature will vary among vendors. Some print out a nice message that the particular 2.0 feature is not implemented yet, and some print out just a stack trace like this:

 Exception in thread "main" java.lang.AbstractMethodError at ScrollableResultSetExample.printEmployeeRecordsBackwards (ScrollableResultSetExample.java:  31) at ScrollableResultSetExample.main (ScrollableResultSetExample.java:68) 

Just beware that all vendors are not clear as to what the actual problem is.

As mentioned before, you can also jump to a specific row within the ResultSet There are several convenience methods that take the cursor to regularly traversed rows. The convenience methods are

• first()

• last()

• beforeFirst()

• afterLast()

You can also use the absolute method to move the cursor to the row number indicated in the argument passed in to it. So, performing this method call on a scrollable ResultSet

 rs.absolute(1) 

will move the cursor to the first record. If the rowIndex parameter is a positive number, the cursor starts from the beginning of the ResultSet, whereas a negative number will start from the end of the ResultSet So passing a (-1) into the absolute method will move the cursor to the last row in the ResultSet

You can also use the relative method on the ResultSet, which will cause the cursor to move that many rows from the current position. A positive index number will cause the cursor to move forward, whereas a negative number will move the cursor backward.

To check to see which row the cursor is currently at, you can use the getRow method. This method returns the row index of the current position. There are also convenience methods that allow you to find the location more directly. The methods are

• isFirst()

• isLast()

• isBeforeFirst()

• isAfterLast()

All these methods return true or false depending on the actual position. Take a look at another example using some of these new methods. Listing 27.4 shows a class called MovingCursorExample that shows the various methods for moving the cursor to starting positions other than the first row.

Listing 27.4 Source Code for MovingCursorExample.java

 import java.sql.*; public class MovingCursorExample {   // Private reference to an instance of the Connection   Connection connection = null;   // Default Constructor   public MovingCursorExample(Connection conn)   {     super();     connection = conn;   }   // Private accessor for the connection   private Connection getConnection()   {     return connection;   }   // Print out the Employee Records backwards using a scrollable   // ResultSet   public void performExample()   {     Statement stmt = null;     ResultSet rs = null;     try     {       // These two variables are used throughout this example       String name = null;       int cursorPosition = 0;       // Get a scrollable ResultSet       Connection conn = getConnection();       stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,                                   ResultSet.CONCUR_READ_ONLY);       // Get some fields from the employee table       String sqlQuery =           "SELECT EMPNO, EName, Job, MGR, HIREDATE FROM EMP";       rs = stmt.executeQuery(sqlQuery);       // Count the rows       int rowSize = 0;       while(rs.next())       {         rowSize++;       }       System.out.println("Number of Rows in ResultSet is: " + rowSize);       if (rowSize == 0)       {         System.out.println("Since there are no rows, exiting");         System.exit(0);       }       // Go to the halfway point in the ResultSet       cursorPosition = Math.round(rowSize /  2);       System.out.println("Moving to position: " + cursorPosition);       // Get the name at the halfway position       rs.absolute(cursorPosition);       System.out.println("Name: " + rs.getString(2));       // Try to go back one row relative from the current position       rs.relative(-1);       cursorPosition = rs.getRow();       System.out.println("Moving to position: " + cursorPosition);       System.out.println("Name: " + rs.getString(2));       System.out.println("Moving to the first row");       // Move to the first row backwards, one row at a time       while(!rs.isFirst())       {         rs.previous();       }       System.out.println("Name: " + rs.getString(2));       rs.close();     }     catch(SQLException ex)     {       ex.printStackTrace();     }   }   public static void main(String[] args)   {      // Use the previous DatabaseManager     Connection conn = DatabaseManager.getConnection();     MovingCursorExample example = new MovingCursorExample(conn);     example.performExample();     // Always make sure to close the connection when you are finished     try     {       conn.close();     }     catch(SQLException ex)     {       ex.printStackTrace();     }     catch(Exception ex)     {       ex.printStackTrace();     }   } } 

Listing 27.5 shows the output from the MovingCursorExample

Listing 27.5 Output from MovingCursorExample.java

 C:\jdk1.3se_book\classes>java MovingCursorExample Number of Rows in ResultSet is: 14 Moving to position: 7 Name: TURNER Moving to position: 6 Name: ALLEN Moving to the first row Name: KING C:\jdk1.3se_book\classes> 

Using Updatable ResultSets

Another new feature in the JDBC 2.0 is the capability to update rows in a ResultSet Rather than relying on update statements, you can update the current ResultSet Take a look at how you might have updated a column prior to the JDBC 2.0 API. The following code fragment shows an SQL statement being executed just like in the previous examples:

 stmt.executeUpdate("UPDATE EMPLOYEE SET SALARY = 45000 " +     "WHERE EMP_NO = '2719'"); 

Doing the same thing using the JDBC 2.0 API would look like the following:

 rs.absolute(5); rs.updateFloat("SAL", 45000.00f); rs.upateRow(); 

The previous fragment is assuming that the fifth record in the ResultSet is the employee record that needs to be updated, but you should get the idea.

The current row in the ResultSet is affected by the update, so you need to move the cursor to the appropriate position before updating any column values. There are many different updateXXX methods in the ResultSet to use. The updateXXX methods generally take two parameters. The first one is the column to update, and the second is the value to update it to. As before, you can specify either the column index or the column name in the first parameter field. The effect will not take place on data in the database until the updateRow method is called. If you move the cursor to another row before calling updateRow, the update changes will be lost.

If you need to cancel the changes that you have made using the updateXXX methods, you can call the cancelRowUpdates method. However, this must be called before updateRow or the changes will take effect on the database. You can also call the RefreshRow method to cause the current row to refresh the data from the database. By default, a ResultSet is not updatable. As with the scrollable ResultSet, you must inform the API that you want an updatable ResultSet Here is a code fragment showing you how to do this:

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

Notice that you can also create a scrollable ResultSet at the same time. You don't need a scrollable ResultSet to do updates on it, but generally you will want to move the cursor around to update different rows.

As with the scrollable ResultSet, you can check to make sure that you have an updatable ResultSet You do this by calling the getConcurrency method on the ResultSet The getConcurrency method will return one of these two values:

• 1007 indicates the ResultSet is CONCUR_READ_ONLY

• 1008 indicates the ResultSet is CONCUR_UPDATABLE

Listing 27.6 shows an example of using the updatable ResultSet

Listing 27.6 Source Code for UpdateEmployeeExample1.java

 import java.sql.*; public class UpdateEmployeeExample1 {   // Private reference to an instance of the Connection   Connection connection = null;   // Default Constructor   public UpdateEmployeeExample1(Connection conn)   {     super();     connection = conn;   }   // Private accessor for the connection   private Connection getConnection()   {     return connection;   }   // Print out the Employee Records backwards using a scrollable   // ResultSet   public void updateEmployees()   {     Statement stmt = null;     ResultSet rs = null;     try     {       // Get a scrollable ResultSet       Connection conn = getConnection();       stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,                                   ResultSet.CONCUR_UPDATABLE);       // Get some fields from the employee table       String sqlQuery =         "SELECT EMPNO, EName, Job, MGR, HIREDATE FRO M EMP";       rs = stmt.executeQuery(sqlQuery); while(rs.next())       {         Timestamp ts = new Timestamp(System.currentTimeMillis());         rs.updateTimestamp("HIREDATE", ts);         // Cause the update changes to be made persistent        rs.updateRow();       }       rs.first();       while(rs.next())       {         String name = rs.getString(2);         Timestamp hireDate = rs.getTimestamp(5);         System.out.println("Name: " + name + " Hire Date: " + hireDate);       }       rs.close();     }     catch(SQLException ex)     {       ex.printStackTrace();     }   }   public static void main(String[] args)   {      // Use the previous DatabaseManager     Connection conn = DatabaseManager.getConnection();     UpdateEmployeeExample1 example = new UpdateEmployeeExample1(conn);     example.updateEmployees();     // Always make sure to close the connection when you are finished     try     {       conn.close();     }     catch(SQLException ex)     {       ex.printStackTrace();     }     catch(Exception ex)     {       ex.printStackTrace();     }   } } 

The new features in the JDBC 2.0 API also allow you to insert and delete rows using an updatable ResultSet This removes the necessity of having to use Insert or Delete SQL statements in your application, for the most part. Listing 27.7 shows an example of inserting a new employee and then deleting it back out of the database.

Listing 27.7 Source Code for InsertAndDelete.java

 import java.sql.*; public class InsertAndDelete {   // Private reference to an instance of the Connection   Connection connection = null;   // Default Constructor   public InsertAndDelete(Connection conn)   {     super();     connection = conn;   }   // Private accessor for the connection   private Connection getConnection()   {     return connection;   }   // Insert and Delete a new Employee Record   public void insertAndDelete()   {     Statement stmt = null;     ResultSet rs = null;     try     {       // Get a scrollable ResultSet       stmt = getConnection().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,                                               ResultSet.CONCUR_UPDATABLE);       // Get some fields from the employee table       rs = stmt.executeQuery("SELECT * FROM EMP");       //Move the cursor to where you need to insert a new row       rs.moveToInsertRow();       // Even though an insert is being performed, the       // update methods have to be used       rs.updateInt("EMPNO", 3100);       rs.updateTimestamp("HIREDATE", new Timestamp(System.currentTimeMillis())); rs.updateString("SAL", "35.00");       rs.updateInt("DEPTNO", 10);       // Cause the update changes to take effect       rs.insertRow();       // Move to the last row and delete it       rs.last();       rs.deleteRow();       // Close the connection       rs.close();     }     catch(SQLException ex)     {       ex.printStackTrace();     }   }   public static void main(String[] args)   {      // Use the previous DatabaseManager     Connection conn = DatabaseManager.getConnection();     InsertAndDelete example = new InsertAndDelete(conn);     example.insertAndDelete();     // Always make sure to close the connection when you are finished     try     {       conn.close();     }     catch(SQLException ex)     {       ex.printStackTrace();     }     catch(Exception ex)     {       ex.printStackTrace();     }   } } 

Using Batch Updates

A batch update is when a set of update statements are queued up and executed together against the database. In some situations, this can increase performance of a database application. Whether it increases performance for your particular application depends on several factors such as how many transactions or update statements are being requested and how long each one is taking.

Prior to JDBC 2.0, each update statement was executed by itself. Even if it was part of a transaction of other update statements, the updates were done one by one. With JDBC 2.0, the Statement class has the capability to add a SQL command to a batch and then execute the entire batch as a set.

To add a new SQL command to the batch, you can call the addBatch method on the Statement class like this:

 Statement stmt = conn.createStatement(); stmt.addBatch("INSERT INTO EMP (EMPNO) " + "VALUES(2346)"); 

To execute the commands in the batch, just call the executeBatch method like this:

 int [] updateCount = stmt.executeBatch(); 

You should always perform a batch inside a transaction to properly handle errors. Remember, to do this you will need to turn off autoCommit by calling the setAutoCommit(false) on the Connection class. Listing 27.8 shows a complete example of performing a batch update.

Note

Batch updates cannot be done for SQL statements that return a ResultSet

Listing 27.8 Source Code for BatchUpdateExample.java

 import java.sql.*; public class BatchUpdateExample {   // Private reference to an instance of the Connection   Connection connection = null;   // Default Constructor   public BatchUpdateExample(Connection conn)   {     super();     connection = conn;   }   // Private accessor for the connection   private Connection getConnection()   {     return connection;   }   // Print out the Employee Records backward using a scrollable   // ResultSet   public void batchUpdates()   {     Statement stmt = null;     ResultSet rs = null;     try     {       // Start a transaction       getConnection().setAutoCommit(false);       stmt = getConnection().createStatement();       stmt.addBatch("UPDATE EMP SET JOB = 1");       stmt.addBatch("UPDATE EMP SET HIREDATE = '15-DEC-1999'");       // Submit the batch of commands for this statement to the database       stmt.executeBatch();       // Commit the transaction       getConnection().commit();     }     catch(SQLException ex)     {       ex.printStackTrace();     }   }   public static void main(String[] args)   {      // Use the previous DatabaseManager     Connection conn = DatabaseManager.getConnection();     BatchUpdateExample example = new BatchUpdateExample(conn);     example.batchUpdates();     // Always make sure to close the connection when you are finished     try     {       conn.close();     }     catch(SQLException ex)     {       ex.printStackTrace();     }     catch(Exception ex)     {       ex.printStackTrace();     }   } } 

Listing 27.9 shows the output after the example in Listing 27.8 is finished executing. All the job id's and hireDate values are the same. This is because the two batch update statements did not provide a where clause. They just updated every record.

Listing 27.9 Output for BatchUpdateExample.java

 C:\jdk1.3se_book\classes>java BatchUpdateExample 7839:1:1999-12-15 00:00:00.000000 7698:1:1999-12-15 00:00:00.000000 7782:1:1999-12-15 00:00:00.000000 7566:1:1999-12-15 00:00:00.000000 7654:1:1999-12-15 00:00:00.000000 7499:1:1999-12-15 00:00:00.000000 7844:1:1999-12-15 00:00:00.000000 7900:1:1999-12-15 00:00:00.000000 7521:1:1999-12-15 00:00:00.000000 7902:1:1999-12-15 00:00:00.000000 7369:1:1999-12-15 00:00:00.000000 7788:1:1999-12-15 00:00:00.000000 7876:1:1999-12-15 00:00:00.000000 C:\jdk1.3se_book\classes> 

Troubleshooting Tip

If you are having trouble with your JDBC driver in that it doesn't support the new 2.0 features, see "JDBC 2.0 Feature Not Implemented" in the "Troubleshooting" section at the end of this chapter.