Goalsof JAVA-JDBC

The JDBC API is a mature technology, having first been specified in January 1997. In
its initial release, the JDBC API focused on providing a basic call-level interface to
SQL databases. The JDBC 2.1 specification and the 2.0 Optional Package
specification then broadened the scope of the API to include support for more
advanced applications and for the features required by application servers to
manage use of the JDBC API on behalf of their applications.
The overall goal of the JDBC 3.0 specification is to “round out” the API by filling in
smaller areas of missing functionality. The following list outlines the goals and
design philosophy for the JDBC API in general and the JDBC 3.0 API in particular:
1. Fit into the J2EE and J2SE platforms
The JDBC API is a constituent technology of the Java platform. The JDBC 3.0 API
should be aligned with the overall direction of the Java 2 Enterprise Edition and
Java 2 Standard Edition platforms.
2. Be consistent with SQL99
The JDBC API provides programmatic access from applications written in the
Java programming language to standard SQL. At the time the JDBC 2.0 API was
in development, the SQL99 specification was a moving target. SQL99 is now a
published standard and includes features that are widely supported among
DBMS vendors as well as features that only a few vendors support. The intent of
the JDBC 3.0 API is to provide access to the subset of SQL99 features that are
likely to be widely supported within the next five years.
3. Consolidate predecessor specifications
This document incorporates content from three prior JDBC specifications to
provide a single standalone specification of the JDBC API.
4. Offer vendor-neutral access to common features
The JDBC API strives to provide high-bandwidth access to features commonly
supported across different vendor implementations. The goal is to provide a
degree of feature access comparable to what can be achieved by native
applications. However, the API must be general and flexible enough to allow for
a wide range of implementations.
5. Maintain the focus on SQL
The focus of the JDBC API has always been on accessing relational data from the
Java programming language. This continues to be true with the JDBC 3.0 API. The
JDBC 3.0 API does not preclude interacting with other technologies, including
XML, CORBA, or non-relational data, but the primary target will still be relational
data and SQL.
6. Provide a foundation for tools and higher-level APIs
The JDBC API presents a standard API to access a wide range of underlying data
sources or legacy systems. Implementation differences are made transparent
through JDBC API abstractions, making it a valuable target platform for tools
vendors who want to create portable tools and applications.
Because it is a “call-level” interface from the Java programming language to SQL,
the JDBC API is also suitable as a base layer for higher-level facilities such as EJB
2.0 container-managed persistence and SQLJ.
7. Keep it simple
The JDBC API is intended to be a simple-to-use, straightforward interface upon
which more complex entities can be built. This goal is achieved by defining many
compact, single-purpose methods instead of a smaller number of complex, multipurpose
ones with control flag parameters.
8. Enhance reliability, availability, and scalability
Reliability, availability, and scalability are the themes of the J2EE and J2SE
platforms, as well as the direction for future Java platforms. The JDBC 3.0 API
stays true to these themes by enhancing support in several areas, including
resource management, the reuse of prepared statements across logical
connections, and error handling.
9. Maintain backward compatibility with existing applications and drivers
Existing JDBC technology-enabled drivers (“JDBC drivers”) and the applications
that use them must continue to work in an implementation of the Java virtual
machine that supports the JDBC 3.0 API. Applications that use only features
defined in earlier releases of the JDBC API, excluding those that were deprecated
by JDBC 2.0, will not require changes to continue running. It should be
straightforward for existing applications to migrate to JDBC 3.0 technology.
10. Allow forward compatibility with Connectors
The Connector architecture defines a standard way to package and deploy a
resource adapter that allows a J2EE container to integrate its connection,
transaction, and security management with those of an external resource.
The JDBC 3.0 API provides the migration path for JDBC drivers to the Connector
architecture. It should be possible for vendors whose products use JDBC
technology to move incrementally towards implementing the Connector API. The
expectation is that these implementors will write “resource manager wrappers”
around their existing data source implementations so that they can be reused in a
Connector framework.
11. Specify requirements unambiguously
The requirements for JDBC compliance need to be unambiguous and easy to
identify. The JDBC 3.0 specification and the API documentation (Javadoc) will
clarify which features are required and which are optional.

Overview of JAVA-JDBC

The JDBC API provides a way for Java programs to access one or more sources of
data. In the majority of cases, the data source is a relational DBMS, and its data is accessed using SQL. However, it is also possible for JDBC technology-enabled drivers to be implemented on top of other data sources, including legacy file systems and object-oriented systems. A primary motivation for the JDBC API is to provide a standard API for applications to access a wide variety of data sources. This chapter introduces some of the key concepts of the JDBC API. In addition, it describes two common environments for JDBC applications, with a discussion of
how different functional roles are implemented in each one. The two-tier and three-tier models are logical configurations that can be implemented on a variety of physical configurations.

Establishing a Connection

The JDBC API defines the Connection interface to represent a connection to an underlying data source. In a typical scenario, a JDBC application will connect to a target data source using
one of two mechanisms:

• DriverManager — this fully implemented class was introduced in the original JDBC 1.0 API and requires the application to load a specific driver using a hardcoded URL.

• DataSource — this interface was introduced in the JDBC 2.0 Optional Package API. It is preferred over DriverManager because it allows details about the underlying data source to be transparent to the application. A DataSource object’s properties are set so that it represents a particular data source. When its getConnection method is invoked, the DataSource instance will return a connection to that data source. An application can be directed to a different data source by simply changing the DataSource object’s properties; no change in application code is needed. Likewise, a DataSource implementation can be changed without changing the application code that uses it. The JDBC API also defines two important extensions of the DataSource interface tosupport enterprise applications. These extensions are the following two interfaces:
  

• ConnectionPoolDataSource — supports caching and reusing of physical connections, which improves application performance and scalability
• XADataSource — provides connections that can participate in a distributed transaction

Executing SQL Statements and

Manipulating Results

Once a connection has been established, an application using the JDBC API can execute queries and updates against the target data source. The JDBC 3.0 API provides access to the most commonly implemented features of SQL99. Because different vendors vary in their level of support for these features, the JDBC API includes the DatabaseMetadata interface. Applications can use this interface to determine whether a particular feature is supported by the data source they are using. The JDBC API also defines escape syntax to allow an application to access non-standard vendor-specific features. The use of escape syntax has the advantage
of giving JDBC applications access to the same feature set as native applications and at the same time maintaining the portability of the application. Applications use methods in the Connection interface to specify transaction attributes and create Statement, PreparedStatement, or CallableStatement objects. These statements are used to execute SQL statements and retrieve results. The ResultSet interface encapsulates the results of an SQL query. Statements may
also be batched, allowing an application to submit multiple updates to a data source
as a single unit of execution. The JDBC API extends the ResultSet interface with the RowSet interface, thereby providing a container for tabular data that is much more versatile than a standard result set. A RowSet object is a JavaBeansTM component, and it may operate without
being connected to its data source. For example, a RowSet implementation can be serializable and therefore sent across a network, which is particularly useful for small-footprint clients that want to operate on tabular data without incurring the overhead of a JDBC driver and data source connection. Another feature of a RowSet implementation is that it can include a custom reader for accessing any data in tabular format, not just data in a relational database. Further, a RowSet object can update its rows while it is disconnected from its data source, and its implementation can include a custom writer that writes those updates back to the underlying data source.

Support for SQL Advanced Data Types

The JDBC API defines standard mappings to convert SQL data types to JDBC data types and back. This includes support for SQL99 advanced data types such as BLOB, CLOB, ARRAY, REF, STRUCT, and DISTINCT. JDBC drivers may also implement one or more customized type mappings for user-defined types (UDTs), in which the UDT is mapped to a class in the Java programming language. The JDBC 3.0 API also adds support for externally managed data, for example, data in a file outside the data source.

Two-tier Model

A two-tier model divides functionality into a client layer and a server layer
The client layer includes the application(s) and one or more JDBC drivers, with the
application handling these areas of responsibility:

Application
data source
JDBC Driver

• presentation logic
• business logic
• transaction management for multiple-statement transactions or distributed transactions
• resource management

In this model, the application interacts directly with the JDBC driver(s), including establishing and managing the physical connection(s) and dealing with the details of specific underlying data source implementations. The application may use its knowledge of a specific implementation to take advantage of nonstandard features or do performance tuning.
Some drawbacks of this model include:

• mingling presentation and business logic with infrastructure and system-level functions. This presents an obstacle to producing maintainable code with a welldefined architecture.

• making applications less portable because they are tuned to a particular database implementation. Applications that require connections to multiple databases must be aware of the differences between the different vendors’ implementations.

• limiting scalability. Typically, the application will hold onto one or more physical database connections until it terminates, limiting the number of concurrent applications that can be supported. In this model, issues of performance, scalability and availability are handled by the JDBC driver and the corresponding underlying data source. If an application deals with multiple drivers, it may also need to be aware of the different ways in which each driver/data source pair resolves these issues.
  

Three-tier Model

The three-tier model introduces a middle-tier server to house business logic and
infrastructure. This architecture is designed to provide improved performance, scalability and
availability for enterprise applications. Functionality is divided among the tiers as follows:

1. Client tier — a thin layer implementing presentation logic for human interaction.
Java programs, web browsers and PDAs are typical client-tier implementations.
The client interacts with the middle-tier application and does not need to include
any knowledge of infrastructure or underlying data source functions.
2. Middle-tier server — a middle tier that includes:
n Applications to interact with the client and implement business logic. If the
application includes interaction with a data source, it will deal with higherlevel
abstractions, such as DataSource objects and logical connections rather
than lower-level driver API.

data source
Web Client
(Browser) Application
Server
transaction
manager
JDBC
Driver
JDBC
Driver
data source
Middle-tier Server
Application Application

• An application server to provide supporting infrastructure for a wide range ofapplications. This can include management and pooling of physical connections, transaction management, and the masking of differences between different JDBC drivers. This last point makes it easier to write portable applications. The application server role can be implemented by a J2EE server. Application servers implement the higher-level abstractions used by applications and interact directly with JDBC drivers.
  

• JDBC driver(s) to provide connectivity to the underlying data sources. Each driver implements the standard JDBC API on top of whatever features are supported by its underlying data source. The driver layer may mask differences between standard SQL99 syntax and the native dialect supported by the data source. If the data source is not a relational DBMS, the driver implements the relational layer used by the application server.
  

3. Underlying data source — the tier where the data resides. It can include
relational DBMSs, legacy file systems, object-oriented DBMSs, data warehouses,
spreadsheets, or other means of packaging and presenting data. The only
requirement is a corresponding driver that supports the JDBC API.

JDBC in the J2EE Platform

J2EE components, such as JavaServerTM Pages, Servlets, and Enterprise Java BeansTM
(EJBTM) components, often require access to relational data and use the JDBC API for this access. When J2EE components use the JDBC API, the container manages their transactions and data sources. This means that J2EE component developers do not directly use the JDBC API’s transaction and datasource management facilities. See the J2EE Platform Specification for further details.

Classes and Interfaces in JAVA-JDBC

The following classes and interfaces make up the JDBC API.

The java.sql Package

The core JDBC API is contained in the package java.sql. The classes and interfaces
in java.sql are listed below. Classes are bold type; interfaces are in standard type.

• java.sql.Array
• java.sql.BatchUpdateException
• java.sql.Blob
• java.sql.CallableStatement
• java.sql.Clob
• java.sql.Connection
• java.sql.DataTruncation
• java.sql.DatabaseMetaData
• java.sql.Date
• java.sql.Driver
• java.sql.DriverManager
• java.sql.DriverPropertyInfo
• java.sql.ParameterMetaData
• java.sql.PreparedStatement
• java.sql.Ref
• java.sql.ResultSet
• java.sql.ResultSetMetaData
• Chapter 5 Classes and Interfaces 30
• java.sql.Savepoint
• java.sql.SQLData
• java.sql.SQLException
• java.sql.SQLInput
• java.sql.SQLOutput
• java.sql.SQLPermission
• java.sql.SQLWarning
• java.sql.Statement
• java.sql.Struct
• java.sql.Time
• java.sql.Timestamp
• java.sql.Types

The following classes and interfaces are either new or updated in the JDBC 3.0 API.
New classes and interfaces are highlighted in bold.

• java.sql.Array
• java.sql.Blob
• java.sql.CallableStatement
• java.sql.Clob
• java.sql.Connection
• java.sql.DatabaseMetaData
• java.sql.ParameterMetaData
• java.sql.PreparedStatement
• java.sql.Ref
• Java.sql.ResultSet
• java.sql.Savepoint
• java.sql.SQLInput
• java.sqlOutput
• java.sql.Statement
• java.sql.Types

Relationships between major classes and interface in the java.sql package

• Connection
• Statement
• Data types
• CallableStatement
• ResultSet
• PreparedStatement
• subclasses
• prepareStatement
• prepareCall
• createStatement
• executeQuery
• executeQuery
• executeQuery
• Input to
• getXXX
• Input/Output of
• getMoreResults / getResultSet
• subclasses
• PreparedStatement
• CallableStatement

The javax.sql Package

The following list contains the classes and interfaces that are contained in the
javax.sql package. Classes are highlighted in bold; interfaces are in normal type.

• javax.sql.ConnectionEvent
• javax.sql.ConnectionEventListener
• javax.sql.ConnectionPoolDataSource
• javax.sql.DataSource
• javax.sql.PooledConnection
• javax.sql.RowSet
• javax.sql.RowSetEvent
• javax.sql.RowSetInternal
• javax.sql.RowSetListener
• javax.sql.RowSetMetaData
• javax.sql.RowSetReader
• javax.sql.RowSetWriter
• javax.sql.XAConnection
• javax.sql.XADataSource

Note – The classes and interfaces in the javax.sql package were first made available as the JDBC 2.0 Optional Package. This optional package was previously separate from the java.sql package, which was part of J2SE 1.2. Both packages (java.sql and javax.sql) are now part of J2SE 1.4.

classes and interfaces in these areas of functionality: DataSource objects, connection pooling, distributed transactions, and rowsets.


Relationship between javax.sql.DataSource and java.sql.Connection
Relationships involved in connection pooling DataSource Connection

• java.sql javax.sql
• getConnection
• Connection PooledConnection
• javax.sql java.sql
• getConnection
• ConnectionPoolDataSource
• getConnection
• ConnectionEvent
• ConnectionEventListener
• close or error event

Relationships involved in distributed transaction support

XAConnection
PooledConnection
ConnectionEvent
XAResource
ConnectionEventListener
XADataSource
Connection
java.sql javax.sql javax.transaction.xa
getConnection
getXAConnection
getXAResource
subclasses
close or error event

Compliance in JAVA-JDBC

This chapter identifies the features that a JDBC API implementation is required to support for each level of compliance. A JDBC API implementation includes a JDBC
technology-enabled driver and its underlying data source. Therefore, compliance is defined in terms of what features are available above the driver layer. Any features not identified here are optional. In general, a driver is not required to implement any feature that its underlying data source does not support.

Definitions

To avoid ambiguity, we will use these terms in our discussion of compliance:

• JDBC API implementation — a JDBC technology-enabled driver and its underlying data source. The driver may provide support for features that are not implemented by the underlying data source. It may also provide the mapping between standard syntax/semantics and the native API implemented by the data source.

• Relevant specifications — this document, the API specification, and the relevant SQL specification. This is also the order of precedence if a feature is described in more than one of these documents. For the JDBC 1.0 API, the relevant SQL specification is SQL92 and X/Open SQL CLI. For the JDBC 2.0 and 3.0 APIs, it is SQL92 plus the relevant sections of SQL99 and X/Open SQL CLI.
• Supported feature — a feature for which the JDBC API implementation supports standard syntax and semantics for that feature as defined in the relevant specifications.
• Extension — a feature that is not covered by any of the relevant specifications or a non-standard implementation of a feature that is covered.
  
• Fully implemented — a term applied to an interface that has all of its methods implemented to support the semantics defined in the relevant specifications.

• Required interface — an interface that must be included although it might not be fully implemented. Methods that are not implemented should throw an SQLException to indicate that the corresponding feature is not supported.

Guidelines and Requirements
The following guidelines apply to all levels of compliance:

• A JDBC API implementation must support Entry Level SQL92 plus the SQL command Drop Table (see note.)

Entry Level SQL92 represents a "floor" for the level of SQL that a JDBC API
implementation must support. Access to features based on SQL99 should be
provided in a way that is compatible with the relevant part of the SQL99
specification.

• Drivers must support escape syntax. Escape syntax is described in “Statements”.
• Drivers must support transactions. See Chapter 10 “Transactions” for details.
• Drivers should provide access to every feature implemented by the underlying data source, including features that extend the JDBC API. When a feature is not supported, the corresponding methods throw an SQLException. The intent is for applications using the JDBC API to have access to the same feature set as native applications.
• If a DatabaseMetaData method indicates that a given feature is supported, it must be supported via standard syntax and semantics as described in the relevant specifications. This may require the driver to provide the mapping to the data source’s native API or SQL dialect if it differs from the standard.
• If a feature is supported, all of the relevant metadata methods must be implemented. For example, if a JDBC API implementation supports the RowSet interface, it must also implement the RowSetMetaData interface.
• If a feature is not supported, the corresponding DatabaseMetaData method must say so. Attempting to access the unsupported feature causes an SQLException to be thrown.
  

Note – A JDBC API implementation is required to support the DROP TABLE
command as specified by SQL92, Transitional Level. However, support for the
CASCADE and RESTRICT options of DROP TABLE is optional. In addition, the
behaviour of DROP TABLE is implementation-defined when there are views or
integrity constraints defined that reference the table being dropped.

JDBC 1.0 API Compliance

A driver that is compliant with the JDBC 1.0 API must do the following:

• Adhere to the preceding guidelines and requirements
• Fully implement the following interfaces:
• java.sql.Driver
• java.sql.DatabaseMetaData (excepting those methods introduced in the JDBC 2.0 API and the JDBC 3.0 API)
• java.sql.ResultSetMetaData (excepting those methods introduced in the JDBC 2.0 API and the JDBC 3.0 API)
• Include the following required interfaces:
• java.sql.CallableStatement
• java.sql.Connection
• java.sql PreparedStatement
• java.sql.ResultSet
• java.sql.Statement

JDBC 2.0 API Compliance

A driver that is compliant with the JDBC 2.0 API must do the following:

• Comply with the JDBC 1.0 API requirements
• Implement the following additional DatabaseMetaData methods:
• deletesAreDetected
• getConnection
• getUDTs
• insertsAreDetetced
• othersDeletesAreVisible
• othersInsertsAreVisible
• othersUpdatesAreVisible
• ownDeletesAreVisible
• ownInsertsAreVisible
• ownUpdatesAreVisible
• supportsBatchUpdates
• supportsResultSetConcurrency
• supportsResultSetType
• updatesAreDetected
• Implement the following additional ResultSetMetaData methods:
• getColumnClassName
• getColumnType
• getColumnTypeName

JDBC 3.0 API Compliance

A driver that is compliant with the JDBC 3.0 API must do the following:

• Comply with the JDBC 2.0 API requirements
• Include the following required interfaces:
• java.sql.ParameterMetaData
• java.sql.Savepoint
• It must implement the following additional DatabaseMetaData methods:
• supportsSavepoints
• supportsNamedParameters
• supportsMultipleOpenResults
• supportsGetGeneratedKeys
• getSuperTypes
• getSuperTables
• getAttributes
• getResultSetHoldability
• supportsResultSetHoldability
• getSQLStateType
• getDatabaseMajorVersion
• getDatabaseMinorVersion
• getJDBCMajorVersion
• getJDBCMinorVersion

Determining Compliance Level

The JDBC API is a constituent technology of the Java platform. Compliance with the
JDBC API specification is determined as a subset of evaluating compliance with the
overall platform.
Note – As of this writing, there is no separate evaluation of compliance level for the JDBC API.

Deprecated APIs

Deprecation refers to a class, interface, constructor, method or field that is no longer recommended and may cease to exist in a future version. The following constructors and methods were deprecated in the JDBC 2.0 API:

java.sql.CallableStatement.getBigDecimal(int, int)
java.sql.Date(int, int, int)
java.sql.Date.getHours()
java.sql.Date.getMinutes()
java.sql.Date.getSeconds()
java.sql.Date.setHours(int)
java.sql.Date.setMinutes(int)
java.sql.Date.setSeconds(int)
java.sql.DriverManager.getLogStream()
java.sql.DriverManager.setLogStream(PrintStream)
java.sql.PreparedStatement.setUnicodeStream(int, InputStream,int)
java.sql.ResultSet.getBigDecimal(int, int)
java.sql.ResultSet.getBigDecimal(String, int)
java.sql.ResultSet.getUnicodeStream(int)
java.sql.ResultSet.getUnicodeStream(String)
java.sql.Time(int, int, int)
java.sql.Time.getDate()
java.sql.Time.getDay()
java.sql.Time.getMonth()
java.sql.Time.getYear()
java.sql.Time.setDate(int)
java.sql.Time.setMonth(int)
java.sql.Time.setYear(int)
java.sql.Timestamp(int, int, int, int, int, int, int)

Database Metadata

The DatabaseMetaData interface is implemented by JDBC drivers to provide
information about their underlying data sources. It is used primarily by application servers and tools to determine how to interact with a given data source. Applications may also use DatabaseMetaData methods to get information about a data source, but this is less typical.
The DatabaseMetaData interface includes over 150 methods, which can be categorized according to the types of information they provide:

• general information about the data source
• whether or not the data source supports a given feature or capability
• data source limits
• what SQL objects the data source contains and attributes of those objects
• transaction support offered by the data source

The DatabaseMetaData interface also contains over 40 fields, which are constants used as return values for various DatabaseMetaData methods. This chapter presents an overview of the DatabaseMetaData interface, gives examples to illustrate the categories of metadata methods, and introduces some new methods. For a comprehensive listing, however, the reader should consult the JDBC

API specification.

JDBC also defines the ResultSetMetaData interface, which is discussed in “Result Sets”.

Creating a DatabaseMetadata Object

A DatabaseMetaData object is created with the Connection method getMetaData. Once created, it can be used to dynamically discover information about the underlying data source. CODE EXAMPLE creates a DatabaseMetadata object and uses it to determine the maximum number of characters allowed for a table name.

// con is a Connection object
DatabaseMetaData dbmd = con.getMetadata();
int maxLen = dbmd.getMaxTableNameLength();

CODE EXAMPLE Creating and using a DatabaseMetadata object

Retrieving General Information

Some DatabaseMetaData methods are used to dynamically discover general
information about a data source as well as some details about its implementation.
Some of the methods in this category are:

• getURL
• getUserName
• getDatabaseProductVersion, getDriverMajorVersion and getDriverMinorVersion
• getSchemaTerm, getCatalogTerm and getProcedureTerm
• nullsAreSortedHigh and nullsAreSortedLow
• usesLocalFiles and usesLocalFilePerTable
• getSQLKeywords

Determining Feature Support

A large group of DatabaseMetaData methods can be used to determine whether a given feature or set of features is supported by the driver or underlying data source. Beyond this, some of the methods describe what level of support is provided. Some of the methods that describe support for individual features are:

• supportsAlterTableWithDropColumn
• supportsBatchUpdates
• supportsTableCorrelationNames
• supportsPositionedDelete
• supportsFullOuterJoins
• supportsStoredProcedures
• supportsMixedCaseQuotedIdentifiers

Methods to describe a level of feature support include:

• supportsANSI92EntryLevelSQL
• supportsCoreSQLGrammar

Data Source Limits

Another group of methods provides the limits imposed by a given data source. Some of the methods in this category are:

• getMaxRowSize
• getMaxStatementLength
• getMaxTablesInSelect
• getMaxConnections
• getMaxCharLiteralLength
• getMaxColumnsInTable

Methods in this group return the limit as an int. A return value of zero means that there is no limit or the limit is unknown.

SQL Objects and Their Attributes

Some DatabaseMetaData methods provide information about the SQL objects that populate a given data source. This group also includes methods to determine the attributes of those objects. Methods in this group return ResultSet objects in which each row describes a particular object. For example, the method getUDTs returns a ResultSet object in which there is a row for each UDT that has been defined in the data source. Examples of this category are:

• getSchemas and getCatalogs
• getTables
• getPrimaryKeys
• getProcedures and getProcedureColumns
• getUDTs

Transaction Support

A small group of methods provides information about the transaction semantics
supported by the data source. Examples of this category include:

• supportsMultipleTransactions
• getDefaultTransactionIsolation

New Methods

The JDBC 3.0 API introduces the following new DatabaseMetaData methods:

• getSuperTypes — returns a description of the user-defined type hierarchies defined in a given schema in the underlying data source
• getSuperTables — returns a description of the table hierarchies defined in a given schema in the underlying data source
• getAttributes — returns a description of user-defined type attributes available from a given catalog in the underlying data source
• getSQLStateType — returns the type of SQLSTATEs that will be returned by the method SQLException.getSQLState, described in “SQLException”.
  
• supportsSavepoints — returns true if the driver or underlying data source supports savepoints, described in “Savepoints” .
  
• supportsNamedParameters — returns true if the driver or underlying data source supports named parameters for CallableStatement objects, described in “Setting Parameters”.
  
• supportsMultipleOpenResults — returns true if the driver or underlying data source supports multiple open result sets for CallableStatement objects, described in “Returning Unknown or Multiple Results”.
  
• supportsGetGeneratedKeys — returns true if the driver or underlying data source supports the retrieval of automatically generated keys, described in “Retrieving Auto Generated Keys” A complete definition of these methods may be found in the JDBC 3.0 API specification (javadoc).
  

Modified Methods

The JDBC 3.0 API modifies the definitions of these existing DatabaseMetaData
methods, adding support for type hierarchies:

• getTables — returns descriptions of the tables that match the given catalog, schema, table name, and type criteria
• getColumns — returns descriptions of the columns that match the given catalog, schema, table name, and column name criteria
• getUDTs — returns descriptions of the user-defined types that match the given catalog, schema, type name, and type criteria
• getSchemas — now returns catalog for each schema as well as the schemata. The JDBC 3.0 API specification includes updated definitions of these methods.

Exceptions::JAVA-JDBC

The SQLException class and its subtypes provide information about errors and
warnings that occur while a data source is being accessed.

SQLException

An instance of SQLException is thrown when an error occurs during an interaction
with a data source. The exception contains the following information:

• a textual description of the error. The String containing the description can be retrieved by calling the method SQLException.getMessage.
  

• a SQLState. The String containing the SQLState can be retrieved by calling the method SQLException.getSQLState.The value of the SQLState string will depend on the underlying data source setting the value. Both X/Open and SQL99 define SQLState values and the conditions in which they should be set. Although the sets of values overlap, the values defined by SQL99 are not a superset of X/Open.The DatabaseMetaData method getSQLStateType allows an application todetermine if the SQLStates being returned by a data source are X/Open or SQL99.
  

• n an error code. This is an integer value identifying the error that caused the SQLException to be thrown. Its value and meaning are implementation specific and may be the actual error code returned by the underlying data source. The error code can be retrieved using the SQLException.getErrorCode method.

• a reference to any "chained" exceptions. If more than one error occurs or the event leading up to the exception being thrown can be described as a chain of events, the exceptions are referenced via this chain. A chained exception can be retrieved by calling the SQLException.getNextException method on the exception that was thrown. If no more exceptions are chained, the getNextException method returns null. SQLWarning, BatchUpdateException and DataTruncation are the three subclasses that extend SQLException. These subclasses are described in the following sections.
  

SQLWarning

Methods in the following interfaces will generate an SQLWarning object if they cause a database access warning:

• Connection
• Statement and its subtypes, PreparedStatement and CallableStatement
• ResultSet

When a method generates an SQLWarning object, the caller is not informed that a data access warning has occurred. The method getWarnings must be called on the appropriate object to retrieve the SQLWarning object. However, the DataTruncation sub-class of SQLWarning may be thrown in some circumstances, see “DataTruncation” on page 50 for more details.
If multiple data access warnings occur, they are chained to the first one and can be retrieved by calling the SQLWarning.getNextWarning method. If there are no more warnings in the chain, getNextWarning returns null. Subsequent SQLWarning objects continue to be added to the chain until the next statement is executed or, in the case of a ResultSet object, when the cursor is repositioned, at which point all SQLWarning objects in the chain are removed.

DataTruncation

The DataTruncation class, a sub-class of SQLWarning, provides information when data is truncated. When data truncation occurs on a write to the data source, a DataTruncation object is thrown. The data value that has been truncated may have been written to the data source even if a warning has been generated. When data trucation occurs on a read from the data source, a SQLWarning is reported. A DataTruncation object contains the following information:

• the descriptive String "Data truncation"
• the SQLState "01004"
• a boolean to indicated whether a column value or a parameter was truncated. The method DataTruncation.getParameter returns true if a parameter wastruncated and false if a column value was truncated.
• an int giving the index of the column or parameter that was truncated. If the index of the column or parameter is unknown, the method DataTruncation.getIndex returns -1. If the index is unknown, the values returned by the methods DataTruncation.getParameter and DataTruncation.getRead are undefined.
• a boolean to indicate whether the truncation occurred on a read or a write operation. The method DataTruncation.getRead returns true if the truncation occurred on a read and false if the truncation occurred on a write.
• an int indicating the the size of the target field in bytes. The method DataTruncation.getDataSize returns the number of bytes of data that could have been transferred or -1 if the number of bytes is unknown.
• an int indicating the actual number of bytes that were transferred. The method DataTruncation.getTransferSize returns the number of bytes actually transferred or -1 if the number of bytes is unknown.
  

Silent Truncation

The Statement.setMaxFieldSize method allows a maximum size (in bytes) to
be set. This limit applies only to the BINARY, VARBINARY, LONGVARBINARY, CHAR,
VARCHAR and LONGVARCHAR data types.

If a limit has been set using setMaxFieldSize and there is an attempt to read or
write data that exceeds the limit, any truncation that occurs as a result of exceeding
the set limit will not be reported.

BatchUpdateException

A BatchUpdateException object provides information about errors that occur
while a batch of statements is being executed. This exception’s behavior is described
in “Batch Updates”.

Connections::Drivers

A Connection object represents a connection to a data source via a JDBC technology-enabled driver. The data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver. A single application using the JDBC API may maintain multiple connections. These connections may access multiple data sources, or they may all access a single data source. From the JDBC driver perspective, a Connection object represents a client session. It has associated state information such as user ID, a set of SQL statements and result sets being used in that session, and what transaction semantics are in effect.

To obtain a connection, the application may interact with either:

• the DriverManager class working with one or more Driver implementations OR
• a DataSource implementation

Using a DataSource object is the preferred method because it enhances application portability, it makes code maintenance easier, and it makes it possible for an application to transparently make use of connection pooling and distributed transactions. All J2EE components that establish a connection to a data source use a DataSource object to get a connection. This chapter describes the various types of JDBC drivers and the use of the Driver interface, the DriverManager class, and the basic DataSource interface. DataSource implementations that support connection pooling and distributed transactions are discussed in Chapters “Distributed Transactions”.

Types of Drivers

There are many possible implementations of JDBC drivers. These implementations
are categorized as follows:

• Type 1 — drivers that implement the JDBC API as a mapping to another data access API, such as ODBC. Drivers of this type are generally dependent on a native library, which limits their portability. The JDBC-ODBC Bridge driver is an
  

example of a Type 1 driver.

• Type 2 — drivers that are written partly in the Java programming language and partly in native code. These drivers use a native client library specific to the data source to which they connect. Again, because of the native code, their portability is limited.
  

• Type 3 — drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates the client’s requests to the data source.
  

• Type 4 — drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source.
  

The Driver Interface

JDBC drivers must implement the Driver interface, and the implementation must contain a static initializer that will be called when the driver is loaded. This initializer registers a new instance of itself with the DriverManager, as shown in

CODE EXAMPLE 9-1.
public class AcmeJdbcDriver implements java.sql.Driver {
static {
java.sql.DriverManager.registerDriver(new AcmeJdbcDriver());
}
...
}
CODE EXAMPLE Example static initializer for a driver implementing
java.sql.Driver

When an application loads a Driver implementation, which is shown in
CODE EXAMPLE the static initializer will automatically register an instance of the driver.

Class.forName(“com.acme.jdbc.AcmeJdbcDriver”);

CODE EXAMPLE Loading a driver that implements java.sql.Driver To insure that drivers can be loaded using this mechanism, drivers are required to provide a niladic constructor.
The DriverManager class invokes Driver methods when it wishes to interact with a registered driver. The Driver interface also includes the method acceptsURL. The DriverManager can use this method to determine which of its registered drivers it should use for a given URL.
When the DriverManager is trying to establish a connection, it calls that driver’s connect method and passes the driver the URL. If the Driver implementation understands the URL, it will return a Connection object; otherwise it returns null.

The DriverManager Class

The DriverManager class works with the Driver interface to manage the set of drivers available to a JDBC client. When the client requests a connection and provides a URL, the DriverManager is responsible for finding a driver that recognizes the URL and using it to connect to the corresponding data source.
Key DriverManager methods include:

• registerDriver — this method adds a driver to the set of available drivers and is invoked implicitly when the driver is loaded. The registerDriver method is typically called by the static initializer provided by each driver.
  

• getConnection — the method the JDBC client invokes to establish a connection. The invocation includes a JDBC URL, which the DriverManager passes to each driver in its list until it finds one whose Driver.connect method recognizes the URL. That driver returns a Connection object to the DriverManager, which in turn passes it to the application.
  

CODE EXAMPLE illustrates how a JDBC client obtains a connection from the DriverManager.

// Load the driver. This creates an instance of the driver
// and calls the registerDriver method to make acme.db.Driver
// available to clients.
Class.forName(“acme.db.Driver”);
Chapter 9 Connections 56
// Set up arguments for the call to the getConnection method.
// The sub-protocol “odbc” in the driver URL indicates the
// use of the JDBC-ODBC bridge.
String url = “jdbc:odbc:DSN”;
String user = “SomeUser”;
String passwd = “SomePwd”;
// Get a connection from the first driver in the DriverManager
// list that recognizes the URL “jdbc:odbc:DSN”.
Connection con = DriverManager.getConnection(url, user, passwd);

CODE EXAMPLE Loading a driver and getting a connection using the DriverManager The DriverManager class also provides two other getConnection methods:

• getConnection(String url) for connecting to data sources that do not use username and passwords.
  

• getConnection(String url, java.util.Properties prop), which allows the client to connect using a set of properties describing the user name and password along with any addition information that may be required. The DriverPropertyInfo class provides information on the properties that the JDBC driver can understand. See the JDBC 3.0 API Specification for more details.
  

The SQLPermission Class

The SQLPermission class represents a set of permissions that a codebase may be
granted. Currently the only permission defined is setLog. The SecurityManager will check for the setLog permission when an Applet calls one of the DriverManager methods setLogWriter and setLogStream. If the codebase does not have the setLog permission a java.lang.SecurityException exception will be thrown. See the JDBC 3.0 API Specification for more details.

Connections::The DataSource Interface

The DataSource Interface

The DataSource interface, introduced in JDBC 2.0 Optional Package, is the preferred approach to obtaining data source connections. A JDBC driver that implements the Datasource interface returns connections that implement the same interface, Connection, as those returned by a DriverManager using the Driver interface. Using a Datasource object increases application portability by making it possible for an application to use a logical name for a data source instead
of having to supply information specific to a particular driver. A logical name is mapped to a DataSource object via a naming service that uses the Java Naming and Directory InterfaceTM (JNDI). The DataSource object, represents a physical data source and provides connections to that data source. If the data source or information about it changes, the properties of the DataSource object can simply be modified to reflect the changes; no change in application code is necessary.

The DataSource interface can be implemented so that it transparently provides the
following:

• Increased performance and scalability through connection pooling
• Support for distributed transactions through the XADataSource interface

The next three sections discuss (1) basic DataSource properties, (2) how logical
naming using the JNDI API improves an applications portability and makes it easier
to maintain, and (3) how to obtain a connection.
Connection pooling and distributed transactions will be discussed in Chapter 11
“Connection Pooling” and Chapter 12 “Distributed Transactions”.

DataSource Properties

The JDBC API defines a set of properties to identify and describe a DataSource implementation. The actual set required for a specific implementation depends on the type of DataSource object, that is, whether it is a basic DataSource object, a ConnectionPoolDataSource object, or an XADataSource object. The only property required for all DataSource implementations is description.

DataSource properties follow the convention specified for properties of
JavaBeansTM components in the JavaBeans 1.01 Specification. DataSource
implementations may augment this set with implementation-specific properties. If
new properties are added, they must be given names that do not conflict with the
standard property names.DataSource implementations must provide “getter” and “setter” methods for eachproperty they support. These properties typically are initialized when the
DataSource object is deployed, as in

CODE EXAMPLE in which a VendorDataSource object implements the DataSource interface.

VendorDataSource vds = new VendorDataSource();
vds.setServerName(“my_database_server”);
String name = vds.getServerName();

CODE EXAMPLE Setting and getting a DataSource property DataSource properties are not intended to be directly accessible by JDBC clients. This design is reinforced by defining the access methods on the implementation class rather than on the public DataSource interface used by applications. Furthermore, the object that the client manipulates can be a wrapper that only implements the DataSource interface. The setter and getter methods for the properties need not be exposed to the client.

Management tools that need to manipulate the properties of a DataSource implementation can access those properties using introspection. The JNDI API and Application Portability
The Java Naming and Directory Interface (JNDI) API provides a uniform way for applications to access remote services over the network. This section describes how it is used to register and access a JDBC DataSource object. See the JNDI specification for a complete description of this interface. Using the JNDI API, applications can access a DataSource object by specifying its
logical name. A naming service using the JNDI API maps this logical name to a corresponding data source. This scheme greatly enhances portability because any of the DataSource properties, such as portNumber or serverName, can be changed without impacting the JDBC client code. In fact, the application can be re-directed to a different underlying data source in a completely transparent fashion. This is particularly useful in the three-tier environment, where an application server hides the details of accessing different data sources.

CODE EXAMPLE illustrates the use of a JNDI-based naming service to deploy a new VendorDataSource object.

// Create a VendorDataSource object and set some properties
VendorDataSource vds = new VendorDataSource();
vds.setServerName(“my_database_server”);
vds.setDatabaseName(“my_database”);
vds.setDescription(“data source for inventory and personnel”);
// Use the JNDI API to register the new VendorDataSource object.
// Reference the root JNDI naming context and then bind the
// logical name “jdbc/AcmeDB” to the new VendorDataSource object.
Context ctx = new InitialContext();
ctx.bind(“jdbc/AcmeDB”, vds);

CODE EXAMPLE Registering a DataSource object with a JNDI-based naming service
Note – J2EE components use a special convention for naming their data sources —
see Chapter 5 "Naming" in the J2EE platform specification for more details.

Getting a Connection with a DataSource Object

Once a DataSource object has been registered with a JNDI-based naming service,
an application can use it to obtain a connection to the physical data source that it
represents, as is done in

CODE EXAMPLE

// Get the initial JNDI naming context
Context ctx = new InitialContext();
// Get the DataSource object associated with the logical name
// “jdbc/AcmeDB” and use it to obtain a database connection
DataSource ds = (DataSource)ctx.lookup(“jdbc/AcmeDB”);
Connection con = ds.getConnection(“user”, “pwd”);

CODE EXAMPLE Getting a Connection object using a DataSource object
The DataSource implementation bound to the name “jdbc/AcmeDB” can be
modified or replaced without affecting the application code.

Transactions::JAVA-JDBC

Transaction Boundaries and Autocommit

When to start a new transaction is a decision made implicitly by either the JDBC driver or the underlying data source. Although some data sources implement an explicit “begin transaction” statement, there is no JDBC API to do so. Typically, a new transaction is started when the current SQL statement requires one and there is no transaction already in place. Whether or not a given SQL statement requires a transaction is also specified by SQL99.

The Connection attribute auto-commit specifies when to end transactions. Enabling auto-commit causes the JDBC driver to do a transaction commit after each individual SQL statement as soon as it is complete. The point at which a statement is considered to be “complete” depends on the type of SQL statement as well as what the application does after executing it:

• For Insert, Update, Delete, and DDL statements, the statement is complete as soon as it has finished executing.
• For Select statements, the statement is complete when the associated result set is closed. The result set is closed as soon as one of the following occurs:
• all of the rows have been retrieved
• the associated Statement object is re-executed
• For CallableStatement objects, the statement is complete when all of the associated result sets have been closed.

Disabling Auto-commit Mode

CODE EXAMPLE shows how to disable auto-commit mode.

// Assume con is a Connection object
con.setAutoCommit(false);

CODE EXAMPLE Setting auto-commit off When auto-commit is disabled, each transaction must be explicitly commited by calling the Connection method commit or explicitly rolled back by calling the Connection method rollback, respectively. This is appropriate for cases where transaction management is being done in a layer above the driver, such as:

• when the application needs to group multiple SQL statements into a single transaction
• when the transaction is being managed by the application server

The default is for auto-commit mode to be enabled when the Connection object is created. If the value of auto-commit is changed in the middle of a transaction, the current transaction is committed. It is an error to enable auto-commit for a connection participating in a distributed transaction, as described in “Distributed Transactions”.

Transactions::Transaction Isolation Levels

Transaction Isolation Levels

Transaction isolation levels specify what data is “visible” to the statements within a
transaction. They directly impact the level of concurrent access by defining what
interaction, if any, is possible between transactions against the same target data
source. Possible interaction between concurrent transactions is categorized as
follows:

• dirty reads occur when transactions are allowed to see uncommitted changes to the data. In other words, changes made inside a transaction are visible outside the transaction before it is commited. If the changes are rolled back instead of being committed, it is possible for other transactions to have done work based on incorrect, transient data.
  

• nonrepeatable reads occur when:

a. Transaction A reads a row
b. Transaction B changes the row
c. Transaction A reads the same row a second time and gets different results

• phantom reads occur when:

a. Transaction A reads all rows that satisfy a WHERE condition
b. Transaction B inserts an additional row that satisfies the same condition
c. Transaction A reevaluates the WHERE condition and picks up the additional “phantom” row

JDBC augments the four levels of transaction isolation defined by SQL99, by adding
TRANSACTION_NONE. From least restrictive to most restrictive, the transaction
isolation levels are:

1. TRANSACTION_NONE — indicates that the driver does not support transactions,
which means that it is not a JDBC compliant driver.
2. TRANSACTION_READ_UNCOMMITTED — allows transactions to see uncommitted
changes to the data. This means that dirty reads, nonrepeatable reads, and
phantom reads are possible.
3. TRANSACTION_READ_COMMITTED — means that any changes made inside a
transaction are not visible outside the transaction until the transaction is
committed. This prevents dirty reads, but nonrepeatable reads and phantom
reads are still possible.
4. TRANSACTION_REPEATABLE_READ — disallows dirty reads and nonrepeatable
reads. Phantom read are still possible.
5. TRANSACTION_SERIALIZABLE — specifies that dirty reads, nonrepeatable reads,
and phantom reads are prevented.

Using the setTransactionIsolation Method

The default transaction level for a Connection object is determined by the driver
supplying the connection. Typically, it is the default transaction level supported by
the underlying data source.

The Connection method setTransactionIsolation is provided to allow JDBC clients to change the transaction isolation level for a given Connection object. The new isolation level remains in effect for the remainder of the session or until the next invocation of the setTransactionIsolation method. The result of invoking the method setTransactionIsolation in the middle of a transaction is implementation-defined. The return value of the method getTransactionIsolation should reflect the change in isolation level when it actually occurs. It is recommended that drivers implement the setTransactionIsolation method to change the isolation level
starting with the next transaction. Committing the current transaction to make the effect immediate is also a valid implementation. It is possible for a given JDBC driver to not support all four transaction isolation levels (not counting TRANSACTION_NONE). If a driver does not support the isolation level specified in an invocation of setTransactionIsolation, it is allowed to
substitute a higher, more restrictive transaction isolation level. If a driver is unable to substitute a higher transaction level, it throws an SQLException. The DatabaseMetaData method supportsTransactionIsolationLevel may be used to determine whether or not the driver supports a given level.

Performance Considerations

As the transaction isolation level increases, more locking and other DBMS overhead is required to ensure the correct semantics. This in turn lowers the degree of concurrent access that can be supported. As a result, applications may see decreased performance when they use a higher transaction isolation level. For this reason, the transaction manager, whether it is the application itself or part of the application server, should weigh the need for data consistency against the requirements for performance when determining which transaction isolation level is appropriate.

Transactions::SavePoints

Savepoints

Savepoints provide finer-grained control of transactions by marking intermediate points within a transaction. Once a savepoint has been set, the transaction can be rolled back to that savepoint without affecting preceding work. The DatabaseMetaData.supportsSavepoints method can be used to determine whether a JDBC driver and DBMS support savepoints.

Setting and Rolling Back to a Savepoint

The JDBC 3.0 API adds the method Connection.setSavepoint, which sets a savepoint within the current transaction. The Connection.rollback method has been overloaded to take a savepoint argument.

CODE EXAMPLE inserts a row into a table, sets the savepoint svpt1, and then inserts a second row. When the transaction is later rolled back to svpt1, the second insertion is undone, but the first insertion remains intact. In other words, when the transaction is committed, only the row containing ’FIRST’ will be added to TAB1.

conn.createStatement();
int rows = stmt.executeUpdate("INSERT INTO TAB1 (COL1) VALUES " +
"(’FIRST’)");
// set savepoint
Savepoint svpt1 = conn.setSavepoint("SAVEPOINT_1");
rows = stmt.executeUpdate("INSERT INTO TAB1 (COL1) " +
"VALUES (’SECOND’)");
...
conn.rollback(svpt1);
...
conn.commit();

CODE EXAMPLE Rolling back a transaction to a savepoint

Releasing a Savepoint

The method Connection.releaseSavepoint takes a Savepoint object as a parameter and removes it from the current transaction. Once a savepoint has been released, attempting to reference it in a rollback operation will cause an SQLException to be thrown. Any savepoints that have been created in a transaction are automatically released and become invalid when the transaction is committed or when the entire transaction is rolled back. Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question

Connection Pooling2:JAVA-JDBC

In a basic DataSource implementation, there is a 1:1 correspondence between the
client’s Connection object and the physical database connection. When the Connection object is closed, the physical connection is dropped. Thus, the overhead of opening, initializing, and closing the physical connection is incurred for each client session. A connection pool solves this problem by maintaining a cache of physical database connections that can be reused across client sessions. Connection pooling greatly improves performance and scalability, particularly in a three-tier environment where multiple clients can share a smaller number of physical database connections. In

The algorithm used to manage the connection pool is implementation-specific and varies with application servers. The application server provides its clients with an implementation of the DataSource interface that makes connection pooling transparent to the client. As a result, the client gets better performance and scalability while using the same JNDI and DataSource APIs as before.


This chapter also describes some important differences

Application Server
JDBC
Application
Cache of
PooledConnection objects
JDBC Driver
logical
Connection
object
physical
PooledConnection
object
ConnectionPoolDataSource API
DataSource API

between a basic DataSource object and one that implements connection pooling. In addition, it discusses how a pooled connection can maintain a pool of reusable PrepredStatement objects.
Although much of the discussion in this chapter assumes a three-tier environment, connection pooling is also relevant in a two-tier environment. In a two-tier environment, the JDBC driver implements both the DataSource and ConnectionPoolDataSource interfaces. This implementation allows an application that opens and closes multiple connections to benefit from connection pooling.

ConnectionPoolDataSource and PooledConnection

Typically, a JDBC driver implements the ConnectionPoolDataSource interface, and the application server uses it to obtain PooledConnection objects.
CODE EXAMPLE shows the signatures for the two versions of the getPooledConnection method.

public interface ConnectionPoolDataSource {
PooledConnection getPooledConnection() throws SQLException;
PooledConnection getPooledConnection(String user,
String password) throws SQLException;
...
}

CODE EXAMPLE The ConnectionPoolDataSource interface A PooledConnection object represents a physical connection to a data source. The JDBC driver’s implementation of PooledConnection encapsulates all of the details of maintaining that connection. An application server caches and reuses PooledConnection objects within its implementation of the DataSource interface. When a client calls the method DataSource.getConnection, the application server uses the physical PooledConnection object to obtain a logical Connection object.

CODE EXAMPLE shows the PooledConnection interface definition.

public interface PooledConnection {
Connection getConnection() throws SQLException;
void close() throws SQLException;
void addConnectionEventListener(
ConnectionEventListener listener);
void removeConnectionEventListener(
ConnectionEventListener listener);
}

CODE EXAMPLE The PooledConnection interface When an application is finished using a connection, it closes the logical connection using the method Connection.close. This closes the logical connection but does not close the physical connection. Instead, the physical connection is returned to the pool so that it can be reused. Connection pooling is completely transparent to the client: A client obtains a pooled connection and uses it just the same way it obtains and uses a nonpooled connection.

Connection Events

Recall that when an application calls the method Connection.close, the underlying physical connection—the PooledConnection object—is available for reuse. JavaBeans-style events are used to notify the connection pool manager (the application server) that a PooledConnection object can be recycled. In order to be notified of an event on a PooledConnection object, the connection pool manager must implement the ConnectionEventListener interface and then
be registered as a listener by that PooledConnection object. The ConnectionEventListener interface defines the following two methods, which correspond to the two kinds of events that can occur on a PooledConnection object:

• connectionClosed — triggered when the logical Connection object associated with this PooledConnection object is closed, that is, the application called the method Connection.close
• connectionErrorOccurred — triggered when a fatal error, such as the server crashing, causes the connection to be lost

A connection pool manager registers itself as a listener for a PooledConnection object using the PooledConnection.addConnectionEventListener method. Typically, a connection pool manager registers itself as a ConnectionEventListener before returning a Connection object to an
application. The driver invokes the ConnectionEventListener methods connectionClosed and connectionErrorOccurred when the corresponding events occur. Both methods take a ConnectionEvent object as a parameter, which can be used to determine which PooledConnection object was closed or had an error. When the JDBC application closes its logical connection, the JDBC driver notifies the connection pool manager (the listener) by calling the listener’s implementation of the method connectionClosed. At this point, the connection pool manager can return the PooledConnection object to the pool for reuse. When an error occurs, the JDBC driver notifies the listener by calling its connectionErrorOccurred method and then throws an SQLException object to the application to notify it of the same error. In the event of a fatal error, the bad PooledConnection object is not returned to the pool. Instead, the connection pool manager calls the PooledConnection.close method on the PooledConnection object to close the physical connection.

Connection Pooling in a Three-tier Environment

The following sequence of steps outlines what happens when a JDBC client requests a connection from a DataSource object that implements connection pooling:

• The client calls DataSource.getConnection.
• The application server providing the DataSource implementation looks in its

connection pool to see if there is a suitable PooledConnection object— a physical database connection—available. Determining the suitability of a given PooledConnection object may include matching the client’s user authentication information or application type as well as using other implementation-specific criteria. The lookup method and other methods associated with managing the connection pool are specific to the application server.

• If there are no suitable PooledConnection objects available, the application server calls the ConnectionPoolDataSource.getPooledConnection method to get a new physical connection. The JDBC driver implementing ConnectionPoolDataSource creates a newPooledConnection object and returns it to the application server.
  

• Regardless of whether the PooledConnection was retrieved from the pool or was newly created, the application server does some internal bookkeeping to indicate that the physical connection is now in use.
• The application server calls the method PooledConnection.getConnection to get a logical Connection object. This logical Connection object is actually a “handle” to a physical PooledConnection object, and it is this handle that is returned by the DataSource.getConnection method when connection pooling is in effect.
  

• The application server registers itself as a ConnectionEventListener by calling the method PooledConnection.addConnectionEventListener. This is done so that the application server will be notified when the physical connection is available for reuse.
  

• The logical Connection object is returned to the JDBC client, which uses the same Connection API as in the basic DataSource case. Note that the underlying physical connection cannot be reused until the client calls the method Connection.close. Connection pooling can also be implemented in a two-tier environment where there is no application server. In this case, the JDBC driver provides both the implementation of DataSource which is visible to the client and the underlying ConnectionPoolDataSource implementation.
  

DataSource Implementations and Connection Pooling

Aside from improved performance and scalability, a JDBC application should not see any difference between accessing a DataSource object that implements connection pooling and one that does not. However, there are some important differences in the application server and driver level implementations. A basic DataSource implementation, that is, one that does not implement connection pooling, is typically provided by a JDBC driver vendor. In a basic
DataSource implementation, the following are true:

• The DataSource.getConnection method creates a new Connection object that represents a physical connection and encapsulates all of the work to set up and manage that connection.
• The Connection.close method shuts down the physical connection and frees the associated resources. In a DataSource implementation that includes connection pooling, a great deal happens behind the scenes. In such an implementation, the following are true:
• The DataSource implementation includes an implementation-specific connection pooling module that manages a cache of PooledConnection objects. The DataSource object is typically implemented by the application server as a layer on top of the driver’s implementations of the ConnectionPoolDataSource and PooledConnection interfaces.
• The DataSource.getConnection method calls PooledConnection.getConnection to get a logical handle to an underlying physical connection. The overhead of setting up a new physical connection is incurred only if there are no existing connections available in the connection pool. When a new physical connection is needed, the connection pool manager will call the ConnectionPoolDataSource method getPooledConnection to create one. The work to manage the physical connection is delegated to the PooledConnection object.
  

• The Connection.close method closes the logical handle, but the physical connection is maintained. The connection pool manager is notified that the underlying PooledConnection object is now available for reuse. If the application attempts to reuse the logical handle, the Connection implementation throws an SQLException.
  

• A single physical PooledConnection object may generate many logical Connection objects during its lifetime. For a given PooledConnection object, only the most recently produced logical Connection object will be valid. Any previously existing Connection object is automatically closed when the associated PooledConnection.getConnection method is called. Listeners (connection pool managers) are not notified in this case. This gives the application server a way to take a connection away from a client. This is an unlikely scenario but may be useful if the application server is trying to force an orderly shutdown.
  

• A connection pool manager shuts down a physical connection by calling the method PooledConnection.close. This method is typically called only in certain circumstances: when the application server is undergoing an orderly shutdown, when the connection cache is being reinitialized, or when the application server receives an event indicating that an unrecoverable error has occurred on the connection.
  

Deployment

Deploying a DataSource object that implements connection pooling requires that both a client-visible DataSource object and an underlying ConnectionPoolDataSource object be registered with a JNDI-based naming service.

The first step is to deploy the ConnectionPoolDataSource implementation, as is
done in CODE EXAMPLE

// ConnectionPoolDS implements the ConnectionPoolDataSource
// interface. Create an instance and set properties.
com.acme.jdbc.ConnectionPoolDS cpds =
new com.acme.jdbc.ConnectionPoolDS();
cpds.setServerName(“bookserver”);
cpds.setDatabaseName(“booklist”);
cpds.setPortNumber(9040);
cpds.setDescription(“Connection pooling for bookserver”);
// Register the ConnectionPoolDS with JNDI, using the logical name
// “jdbc/pool/bookserver_pool”
Context ctx = new InitialContext();
ctx.bind(“jdbc/pool/bookserver_pool”, cpds);
CODE EXAMPLE 11-3 Deploying a ConnectionPoolDataSource object
Once this step is complete, the ConnectionPoolDataSource implementation is
available as a foundation for the client-visible DataSource implementation. The
DataSource implementation is deployed such that it references the
ConnectionPoolDataSource implementation, as shown in CODE EXAMPLE 11-4.
// PooledDataSource implements the DataSource interface.
// Create an instance and set properties.
com.acme.appserver.PooledDataSource ds =
new com.acme.appserver.PooledDataSource();
ds.setDescription(“Datasource with connection pooling”);
// Reference the previously registered ConnectionPoolDataSource
ds.setDataSourceName(“jdbc/pool/bookserver_pool”);
// Register the DataSource implementation with JNDI, using the logical
// name “jdbc/bookserver”.
Context ctx = new InitialContext();
ctx.bind(“jdbc/bookserver”, ds);
CODE EXAMPLE Deploying a DataSource object backed by a
ConnectionPoolDataSource object

Connection Pooling

Reuse of Statements by Pooled Connections

The JDBC 3.0 specification introduces the feature of statement pooling. This feature, which allows an application to reuse a PreparedStatement object in much the same way it can reuse a connection, is made available through a pooled connection. FIGURE 11-2 provides a logical view of how a pool of PreparedStatement objects can be associated with a PooledConnection object. As with the PooledConnection object itself, the PreparedStatement objects can be reused
by multiple logical connections in a transparent manner.

Using a Pooled Statement

If a pooled connection reuses statements, the reuse must be completely transparent to an application. In other words, from the application’s point of view, using a PreparedStatement object that participates in statement pooling is exactly the

JDBC
Application
JDBC
Application
data source
PooledConnection
PooledConnection
Connection Pool
Application Server
Pool of
PreparedStatement
Objects
Pool of
PreparedStatement
Objects

same as using one that does not. Statements are kept open for reuse entirely under the covers, so there is no change in application code. If an application closes a PreparedStatement object, it must still call Connection.prepareStatement in order to use it again. The only visible effect of statement pooling is a possible improvement in performance. An application may find out whether a data source supports statement pooling by calling the DatabaseMetaData method supportsStatementPooling. If the return value is true, the application can then choose to use PreparedStatement objects knowing that they are being pooled. In many cases, reusing statements is a significant optimization. This is especially true for complex prepared statements. However, it should also be noted that leaving large numbers of statements open may have an adverse impact on the use of resources.

Closing a Pooled Statement

An application closes a pooled statement exactly the same way it closes a nonpooled statement. Whether it is pooled or not, a statement that has been closed is no longer available for use by the application, and an attempt to reuse it will cause an exception to be thrown.

The following methods can close a pooled statement:

• Statement.close — called by an application; if the statement is being pooled, closes the logical statement used by the application but does not close the physical statement being pooled
• Connection.close — called by an application
• Nonpooled connection — closes the physical connection and all statements created by that connection. This is necessary because the garbage collection mechanism is unable to detect when externally managed resources can be released.
• Pooled connection — closes the logical connection and the logical statements it returned but leaves open the underlying PooledConnection object and any associated pooled statements
• PooledConnection.closeAll — called by the connection pool manager to close all of the physical statements being pooled by the PooledConnection object

An application cannot directly close a physical statement that is being pooled; instead, this is done by the connection pool manager. The method PooledConnection.closeAll closes all of the statements open on a given physical connection, which releases the resources associated with those statements.

An application also has no direct control over how statements are pooled. A pool of statements is associated with a PooledConnection object, whose behaviour is determined by the properties of the ConnectionPoolDataSource object that produced it. “ConnectionPoolDataSource Properties” discusses these properties.

ConnectionPoolDataSource Properties

As with the DataSource interface, the JDBC API defines a set of properties that can used to configure the behaviour of connection pools. These are shown in Connection pool configuration properties follow the convention specified for JavaBeans components in the JavaBeans specification. Connection pool vendors may choose to augment this set with implementation-specific properties. If so, the additional properties must be given names that do not conflict with the standard property names.

Standard Connection Pool Properties

Property Name Type Description maxStatements int The total number of statements that the pool should keep open. 0 (zero) indicates that caching of statements is disabled. initialPoolSize int The number of physical connections the pool should contain when it is created minPoolSize int The number of physical connections the pool should keep available at all times. 0 (zero) indicates that connections should be created as needed. maxPoolSize int The maximum number of physical connections that the pool should contain. 0 (zero) indicates no maximum size.
maxIdleTime int The number of seconds that a physical connection should remain unused in the pool before the connection is closed. 0 (zero) indicates no limit. propertyCycle int The interval, in seconds, that the pool should wait before enforcing the current policy defined by the values of the above connection pool properties

Like DataSource implementations, ConnectionPoolDataSource implementations must provide “getter” and “setter” methods for each property they support. These properties are typically initialized when the ConnectionPoolDataSource object is deployed.

CODE EXAMPLE illustratessetting properties in a vendor’s implementation of the ConnectionPoolDataSource interface.

VendorConnectionPoolDS vcp = new VendorConnectionPoolDS();
vcp.setMaxStatements(25);
vcp.setInitialPoolSize(10);
vcp.setMinPoolSize(1);
vcp.setMaxPoolSize(0);
vcp.setMaxIdleTime(0);
vcp.setPropertyCycle(300);

CODE EXAMPLE Setting connection pool configuration properties The properties set on a ConnectionPoolDataSource object apply to the PooledConnection objects that it creates. An application server managing a pool of PooledConnection objects uses these properties to determine how to manage its pool. ConnectionPoolDataSource configuration properties are not intended to be directly accessible by JDBC clients. Management tools that need to manipulate the properties of a ConnectionPoolDataSource implementation can access those properties using introspection.

Distributed Transactions

Up to this point, the discussion of transactions has focused on the local case—
transactions involving a single data source. This chapter introduces the distributed
case where a single transaction involves multiple connections to one or more
underlying data sources.

The following discussion includes these topics:

• distributed transaction infrastructure
• transaction managers and resource managers
• the XADataSource, XAConnection, and XAResource interfaces
• two-phase commit

Transaction management in the JDBC API is designed to fit with the Java Transaction
APITM (JTATM) . The examples presented here are high-level; the JTA specification
should be consulted for a more substantial discussion.

Infrastructure

Distributed transactions require an infrastructure that provides these roles:

• Transaction manager — controls transaction boundaries and manages the twophase commit protocol. This typically will be an implementation of JTA.
• JDBC drivers that implement the XADataSource, XAConnection, and XAResource interfaces. These are described in the next section.
• An application-visible implementation of DataSource to “sit on top of” each XADataSource object and interact with the transaction manager. The DataSource implementation is typically provided by an application server. Because the XADataSource interface extends the ConnectionPoolDataSource interface, the DataSource implementation will usually include a connection pooling module as well.
• Resource manager(s) to manage the underlying data. In the context of the JDBC

API, a resource manager is a DBMS server. The term “resource manager” is borrowed from JTA to emphasize the point that distributed transactions using the JDBC API follow the architecture specified in that document. This infrastructure is most often implemented in a three-tier architecture that includes the following:

1. A client tier
2. A middle tier that includes applications, an EJB server working with an external transaction manager, and a set of JDBC drivers
3. Multiple resource managers

Distributed transactions can also be implemented in two tiers. In a two-tier
architecture, the application itself acts as the transaction manager and interacts
directly with the JDBC drivers’ XADataSource implementations.
The following diagram illustrates the distributed transaction infrastructure:

Distributed Transactions::JAVA-JDBC2

XADataSource and XAConnection

The XADataSource and XAConnection interfaces, which are defined in the package javax.sql, are implemented by JDBC drivers that support distributed transactions. An XAConnection object is a pooled connection that can participate in a distributed transaction. More precisely, XAConnection extends the PooledConnection interface by adding the method getXAResource. This method produces an XAResource object that can be used by a transaction manager to coordinate the work done on this connection with the other participants in the distributed transaction.

CODE EXAMPLE gives the definition of the XAConnection interface.

public interface XAConnection extends PooledConnection {
javax.transaction.xa.XAResource getXAResource()
throws SQLException;
}

CODE EXAMPLE The XAConnection interface Because they extend the PooledConnection interface, XAConnection objects support all the methods of PooledConnection objects. They are reusable physical connections to an underlying data source and produce logical connection handles that can be passed back to a JDBC application. XAConnection objects are produced by an XADataSource object. There is some similarity between ConnectionPoolDataSource objects and XADataSource objects in that they are both implemented below a DataSource layer that is visible to the JDBC application. This architecture allows JDBC drivers to support distributed transactions in a way that is transparent to the application.

CODE EXAMPLE shows the signatures for the two getXAConnection methods

defined in XADataSource.
public interface XADataSource {
XAConnection getXAConnection() throws SQLException;
XAConnection getXAConnection(String user,
String password) throws SQLException;
...
}

CODE EXAMPLE The XADataSource interface Typically, DataSource implementations built on top of an XADataSource implementation will also include a connection pooling module.
12.2.1 Deploying an XADataSource Object Deploying an XADataSource object is done in exactly the same manner as previously described for ConnectionPoolDataSource objects. The two-step
process includes deploying the XADataSource object and the application-visible DataSource object, as is done in

CODE EXAMPLE

// com.acme.jdbc.XADataSource implements the
// XADataSource interface.
// Create an instance and set properties.
com.acme.jdbc.XADataSource xads = new com.acme.jdbc.XADataSource();
xads.setServerName(“bookstore”);
xads.setDatabaseName(“bookinventory”);
xads.setPortNumber(9040);
xads.setDescription(“XADataSource for inventory”);
// First register xads with a JNDI naming service, using the
// logical name “jdbc/xa/inventory_xa”
Context ctx = new InitialContext();
ctx.bind(“jdbc/xa/inventory_xa”, xads);
// Next register the overlying DataSource object for application
// access. com.acme.appserver.DataSource is an implementation of
// the DataSource interface.
// Create an instance and set properties.
com.acme.appserver.DataSource ds =
new com.acme.appserver.DataSource();
ds.setDescription(“Datasource supporting distributed transactions”);
// Reference the previously registered XADataSource
ds.setDataSourceName(“jdbc/xa/inventory_xa”);
// Register the DataSource implementation with a JNDI naming service,
// using the logical name “jdbc/inventory”.
ctx.bind(“jdbc/inventory”, ds);

CODE EXAMPLE Deploying a DataSource object backed by an XADataSource object

Getting a Connection

As in the connection pooling case, the application call to the method DataSource.getConnection returns a logical handle produced by the physical XAConnection object. The application code to get a logical connection is shown in

CODE EXAMPLE

Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup(“jdbc/inventory”);
Connection con = ds.getConnection(“myID”,“mypasswd”);

CODE EXAMPLE Application code to get a logical connection
CODE EXAMPLE is an example of code from the middle-tier server’s implementation of the method DataSource.getConnection.

// Assume xads is a driver’s implementation of XADataSource
XADataSource xads = (XADataSource)ctx.lookup(“jdbc/xa/" +
"inventory_xa”);
// xacon implements XAConnection
XAConnection xacon = xads.getXAConnection(“myID”, “mypasswd”);
// Get a logical connection to pass back up to the application
Connection con = xacon.getConnection();

CODE EXAMPLE Getting a logical connection from an XAConnection object

XAResource

The XAResource interface is defined in the JTA specification and is the mapping in the Java programming language of the X/Open Group XA interface. An XAResource object is produced by calling the XAConnection.getXAResource method and is used to associate an XAConnection object with a distributed transaction. A given XAConnection object may be associated with at most one transaction at a time. The JDBC driver maintains a one-to-one correspondence
between an XAResource object and its associated XAConnection object; that is, multiple calls to the getXAResource method must all return the same object. In a typical scenario, the middle-tier application server calls the method XAConnection.getXAResource and passes the returned object to an external transaction manager. The transaction manager uses the XAResource object exclusively—it does not access an XAConnection object directly. The transaction manager coordinates the work of multiple XAResource objects, each of which represents a resource manager participating in the distributed transaction. Note that two XAResource objects may “point” to the same resource manager, that is, they may be associated with XAConnection objects that were produced by the same XADataSource. The following XAResource methods are used by the transaction manager to implement a two-phase commit protocol.

Each method takes an xid parameter that identifies the distributed transaction:

• start — tells the resource manager that the subsequent operations are part of the distributed transaction.
• end — marks the end of this resource manager’s part of the distributed transaction.
• prepare — gets the resource manager’s vote on whether to commit or roll back the distributed transaction.
• commit — tells the resource manager to commit its part of the distributed transaction. This method is invoked only if all the participating resource managers voted to commit the transaction.
• rollback — tells the resource manager to roll back its part of the distributed transaction. This method is invoked if one or more of the participating resource managers voted to roll back the transaction.

See the JTA specification for a complete description of the XAResouce interface.

Transaction Management

Participation in a distributed transaction is defined as the work done between invocations of the methods XAResource.start and XAResource.end. Outside these boundaries, the transaction mode is local, and a connection behaves exactly like a local connection. With one exception, there is no difference in how an application participating in a distributed transaction is coded. In contrast to the local case, the boundaries of a distributed transaction must be controlled by an external transaction manager that is coordinating the work of multiple connections. For this reason, it is an error for applications to call any of the following Connection methods while they are participating in a distributed transaction:

• setAutoCommit(true)
• commit
• rollback
• setSavepoint

The JDBC driver throws an SQLException if one of these operations is attempted on a connection that is participating in a distributed transaction. If the connection is later used for a local transaction, these operations are legal at that point. Applications should also refrain from calling Connection.setTransactionIsolation within the bounds of a distributed transaction. The resulting behavior is implementation-defined. If a connection has auto-commit mode already enabled at the time it joins a global transaction, the attribute will be ignored. The auto-commit behavior will resume when the connection returns to local transaction mode.