Java EE 5

Java is the fastest-growing programming language today. The main reason this object-oriented language is so popular is that it's simple, easy to learn and portable.

Java has several core APIs, one of which is the JDBC API. JDBC is based on the X/Open SQL Call Level Interface (CLI) ­ the basis of ODBC. JDBC gives Java developers a common API to access most databases. This includes relational databases such as Oracle, DB2 UDB, Sybase and Informix as well as legacy databases like IMS. JDBC is used mainly to create n-tier client/server database applications or Web-enabled database applications.

The JDBC API allows developers to easily write applications that access data from a database. JDBC API users aren't required to understand low-level database-related functions like memory management and byte alignment. Not only is JDBC easy to use, it also gives programmers a powerful set of APIs they can use to quickly build sophisticated and real-world applications.

As the first in a multipart series focusing on the enterprise features of JDBC 2.0, this article explores several key features introduced in JDBC 2.0.

JDBC 1.0
Sun announced JDBC 1.0 in February of 1996. Since then, database vendors and independent software vendors (ISVs) have implemented JDBC drivers that conform to its specifications. If you needed to access data from an IBM DB2 UDB or Oracle database, you had to use the database vendor-supplied JDBC driver or a third-party driver like INTERSOLV's DataDirect SequeLink Java Edition.

As the number of Java database applications grew, application developers found the features available in JDBC 1.0 inadequate. To implement the desired database functions, application developers had to write a lot of code. For example, JDBC 1.0 supports retrieving records only in the forward direction.

To develop an application allowing end users to scroll database records in both directions, the developer, using a JDBC driver, had to cache all the records as they were retrieved from the database locally on the client side. The application quickly became even more complex if it had to support modification of these records. Developing and maintaining these features, which should have been supported by JDBC 1.0, resulted in an unnecessary burden on application developers.

To make up for the deficiencies in JDBC, development tool and JavaBean vendors developed commercial products to support these features. Examples include IBM VisualAge for Java's Data Access Beans and Specialized Software's ROAD:BeanBox.

JDBC 2.0
With the introduction of the JDBC 2.0 API and its rich set of new features, developers can now concentrate on the overall development of applications, e.g., implementing business logic rather than writing nonbusiness-specific database functionality. Some of the new features include support for bidirectional result sets, batch updates, connection pools and connectionless result sets.

JDBC 2.0 is fully compatible with JDBC 1.0. Applications developed using JDBC 1.0 are upwardly compatible and don't require any programming changes. All interfaces and classes found in JDBC 1.0 are present in JDBC 2.0.

The JDBC 2.0 API consists of two main components from Sun: the Core API and the Standard Extension. The Core API can be found in the java.sql package and the Standard Extension API in the javax.sql package.

Release 2.0 for the JDBC Core API has many new features, including:

• Scrollable result sets
• Result sets that can be updated
• Batch updates
• SQL3 data-type support (SQL types ARRAY, BLOB, CLOB, DISTINCT, STRUCT and REF)
• Custom mapping of SQL3 user-defined types to Java classes
• Storing of Java objects in an object-relational database

The JDBC 2.0 Standard Extension introduces a wide variety of new features that address the needs of enterprise application developers. Using this API you can:

• Locate and maintain database connections using Java Naming and Directory Interface (JNDI) and DataSource objects.
• Use connection pooling to pool and share a set of database connections between a larger set of end users.
• Implement distributed transactional applications.

Overview of New Features in JDBC 2.0
Result Set Enhancements
JDBC 1.0 API provided result sets that scrolled only in a forward direction. Once a result set was created, users could only access information one record at a time. With the introduction of scrollable result sets in JDBC 2.0, you can now create applications that let you scroll both forward and backward through the contents of a result set. In addition, scrollable result sets allow for relative and absolute positioning. For example, it's now possible to move directly to the tenth row in a scrollable result set, or to the fifth row following the current row. These result sets can be updated as well.

Result Set Types
The JDBC 2.0 API supports three result set types: forward-only, scroll-insensitive and scroll-sensitive. They all support scrolling in one form or another, but differ in their ability to display changes while they are open.

A forward-only result set allows you to move "forward" in the rows returned. This can be one of the lightest-weight cursors you can build. Depending on the JDBC driver's implementation, a forward-only result set may take up the least amount of client-side resources and could dramatically improve the performance. Forward-only result sets are best suited for Web-enabled database applications where users are using a Web browser to query data.

A scroll-insensitive result set is generally not sensitive to changes made while it's open. When you create such a result set, you get a snapshot of the underlying data. The rows, order and column values are fixed when the result set is created. A scroll-insensitive result set is not your best choice for data that's constantly changing. However, this choice makes a lot of sense when you're accessing data from tables that contain values not likely to change.

A scroll-sensitive result set is sensitive to changes made while it's open and provides a dynamic view of the underlying data. For example, if you're viewing data from a table using a scroll-sensitive result set and somebody else makes changes in the underlying values, the changes are made visible to you. Driver vendors typically implement this feature by constantly reexecuting the query used to generate the result set. Because of this repetitive activity, dynamic cursors are expensive to implement and are comparatively slow. This type of result set is best suited for applications that need to display the latest data.

Concurrency Types
A result set can have one of two different concurrency types: read-only or updatable. A result set that uses read-only concurrency doesn't allow updates of its contents, and, since locks aren't placed on read-only database records, the overall concurrency of transactions is increased. A result set that's updatable allows updates and may use write-locks to mediate access to the same data item by different transactions. Since only one write-lock can be held on a database item, this can reduce concurrency. Alternatively, you could use optimistic concurrency control if you think conflicting access to the data will be rare.

Tuning Data Access Performance
You can improve the performance of your application by indicating to the JDBC driver how you intend to use the data being accessed. One way to tune data access is to use the FetchSize property of the statement. This allows you to specify the number of rows to be fetched from the database each time more rows are requested. Instead of making a round-trip for a single row, the driver fetches FetchSize rows and works on these rows in memory. The moment your code steps outside this subset of rows, the driver makes a trip to the database to fetch a new set of FetchSize rows. You can improve the responsiveness of the query being executed by fine-tuning this property depending on your application needs.

You should also remember that if you specify a large value for FetchSize property, data on the client could get stale very quickly. You can further fine-tune the performance of a JDBC driver by specifying the direction for processing the rows ­ forward, reverse or unknown. By setting these properties you can dramatically improve the performance of your applications. These two hints are just suggestions, and the driver can choose to ignore them.

Creating a Result Set
The following code example creates a scrollable result set that's sensitive to updates. The FetchSize property has been set to 50, meaning 50 rows of data will be fetched at a time. Note that we have specified the result set will be updatable by setting the concurrency type to CONCUR_UPDATABLE.

Connection con = DriverManager.getConnection ("jdbc:subprotocol: sampleDB");

PreparedStatement pstmt = con.prepareStatement ("SELECT * FROM DEPT",
Resultset.TYPE_SCROLL_SENSITIVE, Resultset.CONCUR_UPDATABLE);
pstmt.setFetchSize(50);

Resultset rs = pstmt.executeQuery();

In some instances the actual result set returned might not be the one you wanted. For example, if the query contains a table join and the result set isn't updatable, the JDBC driver may not produce an updatable result set. When this occurs, the driver issues a SQLWarning. You can determine the actual result set type and concurrency type of a result set by calling result set's getType() and getConcurrency() methods, respectively.

Updating a Result Set
A result set is updatable if its concurrency type is set to CONCUR_UPDATABLE. You can update, insert or delete rows of an updatable result set. The example below updates the first row of a result set.

The example below updates the first row of a result set. The result set's updateXXX() methods are used to modify the value of an individual column in the current row. Calling these methods doesn't update the underlying database. The database is updated only when the updateRow() method is called. Names or numbers can be used to specify columns.

rs.first();

rs.updateString(1, "Hello World");
rs.updateFloat("distance", 100000.0f);

rs.updateRow();

If you move to another row after modifying individual columns but before you call updateRow(), the changes are discarded. You can explicitly cancel the changes made to individual columns of a row by calling the Resultset.cancelRowUpdates() method. This method should be called after calling the updateXXX() methods and before calling updateRow(); otherwise it has no effect.

The following example deletes the tenth row in the result set from the database.

rs.absolute(10);
rs.deleteRow( );

JDBC 2.0 introduced the concept of an insert row. The example below shows how to insert a new row into a result set.

rs.moveToInsertRow();
rs.updateString(1, "Insert example");
rs.updateFloat("distance", 100.10f);
rs.insertRow();
rs.first();

An insert row is associated with a result set and is used as a staging area before it's inserted into the result set itself. To position the result set's cursor on the insert row, you must call the result set's moveToInsertRow() method.

Use the result set's updateXXX() and getXXX() methods to update and retrieve individual columns of the insert row. Immediately after moving to the insert row, using the moveToInsertRow( ) method, the contents of the insert row are undefined. Calling the getXXX() method on a column in the insert row immediately after calling the moveToInsertRow() would return an undefined value until the value is set by calling up-dateXXX() method.

Calling updateXXX() methods on an insert row doesn't affect the underlying database or the result set. For the changes to affect the underlying database, the insertRow() method should be called. When inserting a row, columns must allow null values. For example, if the column in the result set hasn't been assigned a value, or if a column in the result set being inserted isn't present in the underlying table and the columns don't accept null values, the insertRow() method will throw a SQLException.

Though different database implementations can produce either updatable or read-only result sets for the same SQL query, you can generally expect queries that meet the following criteria to produce an updatable result set:

• The query references only a single table in the database.
• The query does not contain any join operations.
• The query selects the primary key of the table it references.

In addition, a SQL query should also satisfy the conditions listed below if inserts are to be performed:

• The query selects all of the nonnullable columns in the underlying table.
• The query selects all columns that don't have a default value.

Moving Around a Result Set
Earlier we said that result sets in JDBC 2.0 support both forward and backward scrolling as well as relative and absolute positioning. In this section we'll discuss these features in more detail.

A JDBC 2.0 result set maintains an internal pointer called a cursor that indicates the row in the result set currently being accessed. A result set cursor is analogous to the cursor on a computer screen that indicates the current cursor position. The cursor maintained by a forward-only result set can only move forward through the contents of the result set.

In the JDBC 1.0 API, the only way to move the cursor was to call the method next(). This is still the appropriate mechanism to use in JDBC 2.0 when accessing rows in the forward direction. JDBC 2.0 also provides additional ways to move the cursor. The new method previous() moves the cursor in the backward direction, one row at a time, toward the beginning of the result set. Both the next() and previous() methods return false when you scroll beyond the last row or above the first row. The following code example loops through all the rows of a result set from first to last, and once it scrolls beyond the last row it loops in the reverse direction until it scrolls before the first row.

Statement stmt = con.createStatement(Result
set.TYPE_SCROLL_SENSITIVE, Resultset.CON
CUR_UPDATABLE);

Resultset rs = stmt.executeQuery("SELECT
FIRSTNAME, LASTNAME FROM EMPLOYEES");
// print first name and last name from first // to last order
while(rs.next()){
String fname = rs.getString("FIRSTNAME");
String lname = rs.getString("LASTNAME");
System.out.println(fname + " " + lname);
}

// print first name and last name in the
// opposite order

while (rs.previous()) {
String fname = rs.getString("FIRSTNAME");
String lname = rs.getString("LASTNAME");
System.out.println(fname + " " + lname);
}

Using the methods first(), last(), beforeFirst() and afterLast(), you can move the cursor to the row indicated in their names. The method absolute() will move the cursor to the row number indicated in the argument passed to it. If the number is positive, the cursor moves to the given row number from the beginning. If it's negative, the cursor moves to the given row number from the end. For example, absolute(1) puts the cursor on the first row and absolute(-1) puts the cursor on the last row.

Along with the next() and previous() methods, the reverse() method moves the cursor with respect to the current position. With the relative() method you can specify the number of rows you want to move the cursor from the current position. As in the absolute() method, specifying a positive number will move the cursor forward the given number of rows; specifying a negative number will move it backward a given number of rows. In the following example the cursor moves to the fifth row, then to the second row and finally to the fourth row.

rs.absolute(5); //cursor on the fifth row
rs.relative(-3); //cursor on the second row
rs.relative(2); //cursor on the fourth row

Other methods are available. For example, getRow(), isFirst() and isLast() can help you position and control the cursor better.

In the next article we'll continue to explore this API and the new features of JDBC 2.0. Meanwhile, for more information you can refer to the JDBC specification document available at Sun's Web site.

Add Your Feedback

In order to post a comment you need to be registered and logged in.

Register | Log in

Please wait while we process your request...