A.3.4 Other New Features

The JDBC 2.0 core API provides various other new features, which are summarized in the following list.

• Performance enhancements-new methods that allow a programmer to fine tune the retrieval of rows from the database. These methods provide the ability to specify (1) the number of result set rows fetched from the database when more rows are needed and (2) the direction in which rows are fetched from the database.

• ResultSet methods for getting and setting the current fetch size and fetch direction
• Statement, PreparedStatement, and CallableStatement methods for getting and setting the default fetch size and default fetch direction that result sets generated by executing a query will have when they are first created

• Character streams-new methods that allow character data to be retrieved or sent to the database as a stream of internationalized Unicode characters. These methods replace the deprecated getUnicodeStream and setUnicodeStream methods.

• ResultSet.getCharacterStream
• CallableStatement.getCharacterStream
• PreparedStatement.setCharacterStream

• Full precision for java.math.BigDecimal values-new versions of themethods that retrieve a java.math.BigDecimal value with full precision. Unlike the deprecated versions they replace, these new versions do not take a specified precision.

• ResultSet.getBigDecimal
• CallableStatement.getBigDecimal

• Support for time zones-methods with new versions that take a Calendar object as a parameter, which allows the driver to use a specified time zone rather than the default when calculating a value for a date, time, or timestamp

• ResultSet.getDate
• ResultSet.getTime
• ResultSet.getTimestamp
• CallableStatement.getDate
• CallableStatement.getTime
• CallableStatement.getTimestamp
• PreparedStatement.setDate
• PreparedStatement.setTime
• PreparedStatement.setTimestamp

A.3.3 Programmatic Updates

Programmatic updates provide the ability to make updates using the JDBC API rather than SQL statements. The following interfaces have new methods and constants that support programmatic updates.

• ResultSet

• an updater method for updating each data type
• methods for inserting, deleting, or updating a row
• methods indicating whether a row was inserted, deleted, or updated
• method for cancelling a row update
• constants indicating the updatability of a result set

• DatabaseMetaData

• methods indicating the visibility of changes to a result set
• methods indicating whether a result set detects inserts, deletes, or updates
• method indicating whether the DBMS and driver support updatable result sets

A.3.2 Batch Updates

The new batch update facility provides the ability to send multiple updates to the database to be executed as a batch rather than sending each update separately. The following interfaces add methods that support batch updates, and the exception BatchUpdateException is new.

• Statement, PreparedStatement, and CallableStatement

• methods for adding update statements to a batch, clearing all update statements, and executing a batch

• DatabaseMetaData

• method indicating whether the DBMS and driver support batch updates

• BatchUpdateException

• exception thrown when an error occurs in a batch update

A.3.1 Scrollable Result Sets

Scrollable result sets provide the ability to move the cursor forward and backward to a specified position or to a position relative to the current position. The following interfaces have new methods that support scrollable result sets.

• ResultSet

• methods for moving the cursor to a particular row or to a relative position (either forward or backward)
• methods for ascertaining the current position of the cursor
• constants indicating the scrollability of a result set

• Connection

• new versions of the methods for creating Statement, PreparedStatement, and CallableStatement objects that make the result sets they produce scrollable

• DatabaseMetaData

• method indicating whether the DBMS and driver support scrollable result sets

A.3 Summary of New Functionality

The JDBC 2.0 core API adds important new functionality. The following sections briefly explain each new area of functionality and summarize the supporting API.

A.2 Overview of JDBC 2.0 Core API Changes

The JDBC 2.0 core API includes the JDBC 1.0 API and adds enhancements and new functionality to it. These additions put the Java programming language at the forefront of database computing, providing both universal data access and improved performance.
Applications that use earlier versions of the JDBC API can be run using the Java 2 platform with no problem, in keeping with the goal of backward compatibility. However, an application that takes advantage of the new 2.0 features must be run with a driver that implements those features.

The new features in the JDBC 2.0 core API fall into two broad categories: support for new functionality and support for the SQL99 data types.

16. Support for new functionality

• scrollable result sets
• batch updates
• programmatic inserts, deletes, and updates
• other

• performance hints
• character streams for streams of internationalized Unicode characters
• full precision for java.math.BigDecimal values
• support for time zones in Date, Time, and Timestamp values


17. Support for advanced data types

• new SQL data types (SQL99 types)
• increased support for storing persistent objects in the Java programming language

In addition to making the retrieval, storage, and manipulation of data more convenient, the new features make JDBC applications more efficient. For example, batch updates can increase performance dramatically. The new interfaces Blob, Clob, and Array allow applications to operate on large amounts of data without having to materialize the data on the client, which can mean a significant savings in transfer time and the amount of memory needed. Also, new methods for setting the fetch size and fetch direction let a programmer fine tune an application for more efficient data retrieval and processing.

A.1.2 Features Introduced in the JDBC 2.0 Optional Package

The following features, which were introduced in the javax.sql package, are now part of the JDBC 3.0 API and are included in the J2SE version 1.4:

• Using a DataSource Object to Get a Connection
Using the JNDI API and the new DataSource interface, an application does not need to specify a driver name in its code to make a connection to a data source. It can specify a logical name that has been registered with a JNDI naming service and retrieve a DataSource object that will get a connection to the desired data source. This capability makes code more portable and much easier to maintain.

• Connection Pooling
Connection pooling allows an application to (re)use database connections that have already been established instead of always having to create new connections. Because creating and destroying database connections is expensive, this feature is important for good performance, especially for server applications.
The JDBC 2.0 Optional Package API provides hooks that allow connection pooling to be implemented on top of the JDBC driver layer. This makes it possible to have a single cache of connections available for all of the JDBC drivers in use.

• Support for Distributed Transactions
The JDBC 2.0 Optional Package API allows a JDBC driver to support the standard two-phase commit protocol defined in the Java Transaction API (JTA). This means that a transaction may be distributed over multiple servers, which lets developers write enterprise applications using Enterprise JavaBeansTM components that are transactional across multiple DBMS servers.

• Rowsets
RowSet objects are simply containers for tabular data that can be implemented on top of the JDBC API. Rowsets make it possible to pass rows of data across a network, so they are likely to be used extensively in distributed applications. Rowsets may be very lean by being disconnected from a data source, making it possible to display data on a thin client. They also make it possible to use scrolling when the underlying DBMS does not support scrollable result sets. A rowset is a JavaBeansTM component and consequently easy to use in building an application, especially with a development tool.
Typically, a third party will provide a RowSet implementation, and the application programmer just uses it. The API for a RowSet implementation is generally very easy to use because most of a rowset's functionality is inherited from the ResultSet interface. The more complicated aspects of a rowset take place internally and are invisible to the application programmer. There are currently three Early Access implementations of the RowSet interface available from the Java Developer Connection (http://developer.java.sun.com/developer).

A.1.1 Features Introduced in the JDBC 3.0 API

The JDBC 3.0 API introduces new material and changes in these areas:

• Savepoint support
Added the Savepoint interface, which contains new methods to set a savepoint, to release a savepoint, and to roll back a transaction to a designated savepoint.

• Reuse of prepared statements by connection pools
Added the ability for deployers to control how prepared statements are pooled and reused by connections.

• Connection pool configuration
Defined a number of properties for the ConnectionPoolDataSource interface. These properties can be used to describe how PooledConnection objects created by DataSource objects should be pooled.

• Retrieval of parameter metadata
Added the new interface ParameterMetaData, which describes the number, type, and properties of parameters to prepared statements.

• Retrieval of auto-generated keys
Added a means of retrieving values from columns containing automatically generated values.

• Ability to have multiple open ResultSet objects
Added the new method getMoreResults(int) that takes an argument that specifies whether ResultSet objects returned by a Statement should be closed before returning any subsequent ResultSet objects.

• Passing parameters to CallableStatement objects by name
Added methods to allow a string to identify the parameter to be set for a CallableStatement object.

• Holdable cursor support
Added the ability to specify whether a ResultSet object is kept open after a transaction has been committed.

• BOOLEAN data type
Added the data type java.sql.Types.BOOLEAN. BOOLEAN is logically equivalent to BIT.

• Making internal updates to the data in Blob and Clob objects
Added methods to allow the data contained in Blob and Clob objects to be altered.

• Retrieving and updating the object referenced by a Ref object
Added methods to retrieve the object referenced by a Ref object. Also added the ability to update a referenced object through the Ref object.

• Updating of columns containing BLOB, CLOB, ARRAY and REF types
Addition of the updateBlob, updateClob, updateArray, and updateRef methods to the ResultSet interface.

• DATALINK/URL data type
Added the data type java.sql.Types.DATALINK, allowing JDBC drivers to store and retrieve references to external data.

• Transform groups and type mapping
Described the effect of transform groups and how this is reflected in the metadata.

• Relationship between the JDBC SPI (Service Provider Interface) and the Connector architecture
Described the relationship between the JDBC SPI and the connector architecture.

• DatabaseMetadata APIs
Added metadata for retrieving SQL type hierarchies and various other kinds of information relating to new features.

A.1 Overview of JDBC 3.0 API Changes

One of the major changes in the JDBC 3.0 API is that it includes the package javax.sql (the JDBC Optional Package) as well as the package java.sql. This overview is divided into two sections, the first giving new features introduced in the JDBC 3.0 API for both packages, and the second giving the features introduced in the JDBC 2.0 Optional Package. See the JDBC 3.0 API Specification for more detailed information.

9.4 Advanced JDBC Data Types

The ISO (International Organization for Standardization) and IEC (the International Electrotechnical Commission) have defined new data types that are commonly referred to as SQL99 types. Of these new SQL99 data types, BLOB, CLOB, ARRAY, and REF are predefined types, whereas the SQL structured type and the DISTINCT type are user-defined types (UDTs). These new types, with the exception of DISTINCT, are mapped to interfaces that were introduced in the JDBC 2.0 core API. This section describes each data type briefly; more complete information on each type can be found in the reference chapter for the corresponding interface. There is a chapter on the DISTINCT data type, but because DISTINCT types are mapped to a built-in type, there is no separate interface for it.
The new data types introduced in the JDBC 2.0 core API represent a significant expansion in the types of data that can be used in a relational database. In general, they are more like objects; in fact, two of the new data types are UDTs that can optionally be custom mapped to classes

9.3.13 DATE, TIME, and TIMESTAMP

There are three JDBC types relating to time:

• The JDBC DATE type represents a date consisting of day, month, and year. The corresponding SQL DATE type is defined in SQL-92, but it is implemented by only a subset of the major databases. Some databases offer alternative SQL types that support similar semantics.
• The JDBC TIME type represents a time consisting of hours, minutes, and seconds. The corresponding SQL TIME type is defined in SQL-92, but it is implemented by only a subset of the major databases. As with DATE, some databases offer alternative SQL types that support similar semantics.
• The JDBC TIMESTAMP type represents DATE plus TIME plus a nanosecond field. The corresponding SQL TIMESTAMP type is defined in SQL-92, but it is implemented by only a very small number of databases.

Because the standard Java class java.util.Date does not match any of these three JDBC date/time types exactly (it includes both DATE and TIME information but has no nanoseconds), JDBC defines three subclasses of java.util.Date to correspond to the SQL types. They are:

• java.sql.Date for SQL DATE information. The hour, minute, second, and millisecond fields of the java.util.Date base class should be set to zero. If the number of milliseconds supplied to the java.sql.Date constructor is negative, the driver will compute the date as the number of milliseconds before January 1, 1970. Otherwise, the date is computed as the specified number of milliseconds after January 1, 1970.
• java.sql.Time for SQL TIME information. The year, month, and day fields of the java.util.Date base class are set to 1970, January, and 1. This is the "zero" date in the Java epoch.
• java.sql.Timestamp for SQL TIMESTAMP information. This class extends java.util.Date by adding a nanoseconds field.
All three of the JDBC time-related classes are subclasses of java.util.Date, and as such, they can be used where a java.util.Date is expected. For example, internationalization methods take a java.util.Date object as an argument, so they can be passed instances of any of the JDBC time-related classes.
A JDBC Timestamp object has its parent's date and time components and also a separate nanoseconds component. If a java.sql.Timestamp object is used where a java.util.Date object is expected, the nanoseconds component is lost. However, since a java.util.Date object is stored with a precision of one millisecond, it is possible to maintain this degree of precision when converting a java.sql.Timestamp object to a java.util.Date object. This is done by converting the nanoseconds in the nanoseconds component to whole milliseconds (by dividing the number of nanoseconds by 1,000,000) and then adding the result to the java.util.Date object. Up to 999,999 nanoseconds may be lost in this conversion, but the resulting java.util.Date object will be accurate to within one millisecond.
The following code fragment is an example of converting a java.sql.Timestamp object to a java.util.Date object that is accurate to within one millisecond.

Timestamp t = new Timestamp(98724573287540L);
java.util.Date d;
d = new java.util.Date(t.getTime() + (t.getNanos() / 1000000));

Methods added in the JDBC 2.0 core API make it possible for the driver to take a specified time zone into account when calculating a date, time, or timestamp. The time zone information is included in a java.util.Calendar object that is passed to new versions of the methods for getting and setting Date, Time, and Timestamp values. When no time zone is specified, the driver uses the time zone of the virtual machine running the application when it calculates a date, time, or timestamp.

9.3.12 DECIMAL and NUMERIC

The JDBC types DECIMAL and NUMERIC are very similar. They both represent fixed-precision decimal values.
The corresponding SQL types DECIMAL and NUMERIC are defined in SQL-92 and are very widely implemented. These SQL types take precision and scale parameters. The precision is the total number of decimal digits supported, and the scale is the number of decimal digits after the decimal point. For most DBMSs, the scale is less than or equal to the precision. So for example, the value "12.345" has a precision of 5 and a scale of 3, and the value ".11" has a precision of 2 and a scale of 2. JDBC requires that all DECIMAL and NUMERIC types support both a precision and a scale of at least 15.
The sole distinction between DECIMAL and NUMERIC is that the SQL-92 specification requires that NUMERIC types be represented with exactly the specified precision, whereas for DECIMAL types, it allows an implementation to add additional precision beyond that specified when the type was created. Thus a column created with type NUMERIC(12,4) will always be represented with exactly 12 digits, whereas a column created with type DECIMAL(12,4) might be represented by some larger number of digits.
The recommended Java mapping for the DECIMAL and NUMERIC types is java.math.BigDecimal. The java.math.BigDecimal type provides math operations to allow BigDecimal types to be added, subtracted, multiplied, and divided with other BigDecimal types, with integer types, and with floating point types.
The method recommended for retrieving DECIMAL and NUMERIC values is ResultSet.getBigDecimal. JDBC also allows access to these SQL types as simple Strings or arrays of char. Thus, Java programmers can use getString to receive a DECIMAL or NUMERIC result. However, this makes the common case where DECIMAL or NUMERIC are used for currency values rather awkward, since it means that application writers have to perform math on strings. It is also possible to retrieve these SQL types as any of the Java numeric types.

9.3.11 FLOAT

The JDBC type FLOAT is basically equivalent to the JDBC type DOUBLE. We provided both FLOAT and DOUBLE in a possibly misguided attempt at consistency with previous database APIs. FLOAT represents a "double precision" floating point number that supports 15 digits of mantissa.
The corresponding SQL type FLOAT is defined in SQL-92. The SQL-92 standard leaves the precision of FLOAT up to the implementation, but in practice all the major databases supporting FLOAT support a mantissa precision of at least 15 digits.
The recommended Java mapping for the FLOAT type is as a Java double. However, because of the potential confusion between the double precision SQL FLOAT and the single precision Java float, we recommend that JDBC programmers should normally use the JDBC DOUBLE type in preference to FLOAT.

9.3.10 DOUBLE

The JDBC type DOUBLE represents a "double precision" floating point number that supports 15 digits of mantissa.
The corresponding SQL type is DOUBLE PRECISION, which is defined in SQL-92 and is widely supported by the major databases. The SQL-92 standard leaves the precision of DOUBLE PRECISION up to the implementation, but in practice all the major databases supporting DOUBLE PRECISION support a mantissa precision of at least 15 digits.
The recommended Java mapping for the DOUBLE type is as a Java double.

9.3.9 REAL

The JDBC type REAL represents a "single precision" floating point number that supports seven digits of mantissa.
The corresponding SQL type REAL is defined in SQL-92 and is widely, though not universally, supported by the major databases. The SQL-92 standard leaves the precision of REAL up to the implementation, but in practice all the major databases supporting REAL support a mantissa precision of at least seven digits.
The recommended Java mapping for the REAL type is as a Java float.

9.3.8 BIGINT

The JDBC type BIGINT represents a 64-bit signed integer value between -9223372036854775808 and 9223372036854775807.
The corresponding SQL type BIGINT is a nonstandard extension to SQL. In practice the SQL BIGINT type is not yet currently implemented by any of the major databases, and we recommend that its use be avoided in code that is intended to be portable.
The recommended Java mapping for the BIGINT type is as a Java long.

9.3.7 INTEGER

The JDBC type INTEGER represents a 32-bit signed integer value ranging between -2147483648 and 2147483647.
The corresponding SQL type, INTEGER, is defined in SQL-92 and is widely supported by all the major databases. The SQL-92 standard leaves the precision of INTEGER up to the implementation, but in practice all the major databases support at least 32 bits.
The recommended Java mapping for the INTEGER type is as a Java int.

9.3.6 SMALLINT

The JDBC type SMALLINT represents a 16-bit signed integer value between -32768 and 32767.
The corresponding SQL type, SMALLINT, is defined in SQL-92 and is supported by all the major databases. The SQL-92 standard leaves the precision of SMALLINT up to the implementation, but in practice, all the major databases support at least 16 bits.
The recommended Java mapping for the JDBC SMALLINT type is as a Java short.

9.3.5 TINYINT

The JDBC type TINYINT represents an 8-bit integer value between 0 and 255 that may be signed or unsigned.
The corresponding SQL type, TINYINT, is currently supported by only a subset of the major databases. Portable code may therefore prefer to use the JDBC SMALLINT type, which is widely supported.
The recommended Java mapping for the JDBC TINYINT type is as either a Java byte or a Java short. The 8-bit Java byte type represents a signed value from -128 to 127, so it may not always be appropriate for larger TINYINT values, whereas the 16-bit Java short will always be able to hold all TINYINT values.

9.3.4 BOOLEAN

The JDBC type BOOLEAN, which is new in the JDBC 3.0 API, maps to a boolean in the Java programming language. It provides a representation of true and false, and therefore is a better match than the JDBC type BIT, which is either 1 or 0.