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.