What's New in JDBC Technology?

 


In mid-1999, Addison-Wesley will be publishing a successor to JDBC Database Access with Java , the only JDBC book endorsed by Java Software. The new book, tentatively titled JDBC: Universal Data Access for the Java 2 Platform , builds on the first book, adding reference material and tutorials for the new JDBC 2.0 API, including both core and Standard Extension APIs. The following article, written by Maydene Fisher, one of the authors, previews some of the new features that will be examined in the upcoming book.


By Maydene Fisher

 With the new JDBC 2.0 API, you can access virtually any kind of data and write industrial strength database applications. The JDBC 2.0 API includes the java.sql package, which is part of the core JDK API, and the javax.sql package, which is a Standard Extension.

This article covers the new features in the java.sql package: convenient use of advanced data types, result sets with scrollable cursors, batch updates, the ability to make updates using Java API rather than SQL. A later article will discuss the features in the javax.sql package.

Support for Advanced Data Types

The new advanced data types open up the kinds of data that can be stored in a relational database and make relational databases more like object databases.

The JDBC 2.0 API maps the new SQL3 data types to JDBC interfaces and makes them as easy to use as the basic data types. For example, the new SQL data type ARRAY , which allows an array to be used as a column value in a database table, maps to the Array interface. Using an Array object is as easy as using a basic data type. Just as an SQL INTEGER is retrieved from a result set with the method getInt, an SQL ARRAY value is retrieved with the method getArray.

Let's look at a simple example of how an SQL ARRAY value might be retrieved. Suppose that MEMBERS is a column in the table COMMITTEES and that it stores SQL ARRAY values. The following code snippet, in which stmt is a Statement object, retrieves the array containing the names of the members of the finance committee.

ResultSet rs = stmt.executeQuery(
"SELECT MEMBERS FROM COMMITTEES 
WHERE COMM_NAME = 'FINANCE'");
rs.next();
Array financeMembers = rs.getArray("MEMBERS");

The variable financeMembers now represents the array that contains the names of the finance committee members. However, it does not contain the names themselves; rather, it is a logical pointer to the names that reside on the server. This means that you can operate on financeMembers just as if you were operating on the SQL ARRAY object itself but without incurring the overhead of bringing all the names in the array over to the client. To store the array of names back in the database, you simply pass financeMembers to the PreparedStatement method setArray . When you use the getArray and setArray methods, you are operating on an instance of Array , which designates an SQL ARRAY value but does not contain its data. If, on the other hand, you want to bring the array elements over to the client as a Java array, you can use methods in the the Array interface to do that.

The new SQL3 data types BLOB (Binary Large Object) and CLOB (Character Large Object) work in a similar fashion. The Blob and Clob interfaces are also implemented so that instances are logical pointers, meaning that you can operate on potentially huge amounts of data without having to bring the data over to the client. But you still have the ability to bring all or part of the data to the client if that is what you want to do.

It is now easier to store user-defined data types (UDTs) as column values. The two new SLQ3 UDTs are SQL structured types and DISTINCT types. (A structured type has multiple attributes, and you can think of a DISTINCT type as a structured type with only one attribute.) It is also possible to create an SQL REF type that references an instance of a structured type. An SQL structured type maps to the Struct interface, and an SQL REF maps to the Ref interface. A DISTINCT type is based on another data type, so it just maps to that data type. You store and retrieve Struc , Ref , and DISTINCT instances with set and get methods, just as with the other data types.

In addition to their standard mappings, the SQL3 UDTs can be mapped to Java classes. This feature can make your life a lot easier because it is often much more convenient to manipulate a UDT as a Java object. Note that an SQL UDT is defined in SQL and can be mapped to a Java object, but it is always stored as an SQL object. It is now also possible to store an instance of a class defined in the Java programming language as a Java object. This means that it is not converted to an SQL object before being stored in a database. In DBMSs that support storing Java objects, an instance of a Java class may be stored as a serialized Java object or in any other format that a DBMS wants to use.

New Functionality

The JDBC 2.0 API adds important new functionality, making it easier than ever to write database applications.

Scrollable Result Sets

The first area of new functionality is scrollable result sets. After you have created a result set that is scrollable, you can move a result set's cursor wherever you want and also check on where it is currently positioned. It has always been possible to move through the rows of a result set with the method next , starting at the first row and going to the last row. Now with the method previous , you can move back one row at any time or start at the last row and iterate through a result set's rows backwards. There are methods for moving to the nth row or moving a specified number of rows from the current row. You can also move the cursor to the first row, the last row, the position before the first row, and the position after the last row. The following code fragment shows how to move the cursor to various rows in the scrollable ResultSet object rs.

  ResultSet rs = stmt.executeQuery
            
("SELECT * FROM COMMITTEES");
while (rs.next()) {
. . . // iterates forward through rs
}
. . .
rs.absolute(5); // moves cursor to the fifth row
. . .
rs.relative(-2); // moves cursor to the third row
. . .
rs.relative(4); // moves cursor to the seventh row
. . .
rs.previous(); // moves cursor to sixth row
. . .
int rowNumber = rs.getRow(); // rowNumber should be 6
rs.moveAfterLast(); // moves cursor to position // after last row
while (previous()) {
. . . // iterates backward through rs
}

Batch Updates

You can now send multiple updates to the database to be executed as a batch rather than sending each update separately. This can cut down the traffic between the server and client and dramatically improve performance.

Statement objects, used to send SQL statements to the database, have a list of commands associated with them. This list starts out empty at creation, and update statements can be added to it with the method addBatch . The list is emptied when it is sent to the database for execution or is explicitly cleared by a call to the method clearBatch.

The following code fragment demonstrates how to send a batch update to a database. In this example, a new row is inserted into three different tables in order to add a new employee to a company database. The code fragment starts by turning off the Connection object con 's auto-commit mode in order to allow multiple statements to be sent together as a transaction. After creating the Statement object stmt , it adds three SQL INSERT INTO commands to the batch with the method addBatch and then sends the batch to the database with the method executeBatch . The code looks like this:

  con.setAutoCommit(false); 
// con is valid Connection object
            
Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, '260')");
int [] updateCounts = stmt.executeBatch();

Programmatic Updates

You can now modify column values and insert or delete a row using the JDBC API instead of SQL statements.

The ResultSet interface has new updateXXX methods, one for each data type, as is true with the getXXX and setXXX methods. The following code fragment moves the cursor to the fourth row to change the ADDRESS and AMOUNT columns in that row. The method updateString changes the value in the ADDRESS column, which stores String values, to the String "321 Kasten" . Similarly, the updateFloat method changes the value stored in the AMOUNT column to the float value 10101.0f . The method updateRow sends the updates to the database.

  rs.absolute(4);
            
rs.updateString("ADDRESS", "321 Kasten"); rs.updateFloat("AMOUNT", 10101.0f);
rs.updateRow();

To insert a new row, you move the cursor to a special row, called the insert row , build the new row, send the new row to the database, and move the cursor back to the result set. An insertion of a three-column row might look like this:

  rs.moveToInsertRow();
            
// column 1 gets the value myArray rs.updateObject(1, myArray);
// column 2 gets the value 3857 rs.updateInt(2, 3857); // column 3 is "Mysteries"
rs.updateString(3, "Mysteries");
rs.insertRow(); rs.first();

To delete a row, you just move the cursor to the row you want to delete and call the method deleteRow.

  rs.first();
rs.deleteRow(); // deletes the first row  

Summary

This article covers the high points of the new functionality and data types offered by the methods and interfaces added to the java.sql package in the JDBC 2.0 API. You can now navigate scrollable result sets, improve performance with batch updates, and more conveniently make updates, insertions, and deletions. The new data types let you handle large amounts of data efficiently and also greatly expand the kinds of data you can store. In fact, the new API moves JDBC technology from a convenient way to send SQL statements to a techcnology for universal data access.

The limited size of an article dictates that the new java.sql features are covered only superficially, but the book from which the information is gleaned covers all the details and gives many examples, including a tutorial. Tentatively titled JDBC: Universal Data Access for the Java 2 Platform, the book is part of the Addison Wesley Longman series and will be available in mid-1999.

Left Curve
Java SDKs and Tools
Right Curve
Left Curve
Java Resources
Right Curve
JavaOne Banner
Java 8 banner (182)