Introducing JDBC

JDBC is a Java programming interface for accessing databases, which makes it the obvious choice when using databases with Tomcat. JDBC submits SQL query statements to the remote SQL processing engine (part of the database that handles multiple simultaneous connections via a connection manager), and the SQL processing engine returns the result of the query in a set of data called a result set. A result set is typically zero or more rows of data. You can think of result sets as temporary database tables.

Therefore, JDBC operations are designed to do the following:

• Take the JDBC API calls and transform them into a SQL query

• Submit that query to the SQL processing engine on the database

• Retrieve the result set that’s returned from the query and transform it into a Java-accessible data structure

Not all statements return a result set; you may conduct a search that isn’t successful, so the returned result set will be empty (called a null result set). In addition, some SQL statements, such as those you use to create tables, update data, and delete rows, don’t return any result sets.

Running Basic JDBC Operations

In JDBC programming, the typical steps that a developer must follow are as follows:

1. Obtain a connection to the remote database server.

2. Create and prepare a SQL statement for execution (or call a stored procedure in the database).

3. Execute the SQL statement.

4. Obtain the returned result set (if any) and work on it.

5. Disconnect from the remote database.

It’s usually the case that you’ll be concerned only with the first step of this process. Once you’ve connected the Web server to a database, you hand the connection over to any Web applications that need it.

Establishing and Terminating Connections to Databases

Other than providing a unified way of accessing, modifying, and manipulating data in databases, JDBC also provides a unified way of connecting to databases from different vendors. While normal native connections to Oracle will be different from connections to MySQL, which will yet be different from when working with Microsoft’s SQL Server, connecting to any of these databases can be accomplished using the same JDBC API calls.

As you saw in

Figure 10-1: JDBC

JDBC uses JDBC drivers to communicate with the native database drivers, and these vary from database to database. However, application code doesn’t notice the difference between drivers, because they all follow the same standard.

Which JDBC Version?

Under the JDBC 1.0 standard, the code to establish a connection to a database, as well as the code to disconnect from the database, is written by the developer. In fact, even the code to select and activate a JDBC driver is coded by the developer.

Although simple and straightforward to code, this approach creates a problem. In some cases where the driver is written by the developer, the database access code works only with specific database from the vendor. This makes it difficult to swap to a database from another vendor and removes many of the advantages of JDBC described in the previous section.

JDBC 2.0 relaxes this restriction and introduces the concept of a data source, which maps a name to a set of values for obtaining a database connection. A developer can obtain a connection to a data source using its name, allowing the same JDBC code to work with drivers from any vendor. Meanwhile, you can switch database vendor support by configuring a different data source. The name remains the same, but the settings have changed, so the developer doesn’t need to change their code.

While data sources and connection pooling (covered in the “Database Connection Pooling” section) open new possibilities for database users, JDBC 2.0 doesn’t specify how these features should be used. As a result, many architectural issues are left for the JDBC driver writer to solve—and code can quickly become vendor specific again (this time depending on the JDBC driver vendor).

JDBC 3.0 is the first specification that clearly spells out the different architectures that JDBC can operate in, including two-tier and three-tier models. The three-tier model corresponds to the application server model and the model of operation favored by J2EE applications.

The specification also attempts to accommodate JDBC 1.0 and 2.0 drivers and model of operations, while formalizing JNDI as the preferred way for applications to obtain a data source. It also formalizes connection pooling as a value-added service of the application server or servlet container. Tomcat uses the Jakarta Commons DBCP component to implement JDBC 3.0, and all data sources configured in server.xml are JDBC 3.0 data sources (providing you’re using JDK 1.4 or newer).

Regardless of the JDBC version, the JDBC driver still has to translate the JDBC commands into native commands to connect to the different databases. Most JDBC drivers are high-performance Type IV drivers (explained in the next section). However, some legacy systems will support only the older Type I to Type III drivers. It’s a good idea to gain some familiarity with the different types of JDBC driver.

Examining JDBC Driver Types

Four types of JDBC drivers exist. In general, the higher driver types represent an improvement on performance.

• Type I: These are the most primitive JDBC drivers because they’re just data access adapters. They adapt another data access mechanism (such as ODBC) to JDBC. These drivers completely rely on the other data access mechanism and as such have double the administrative and maintenance problems. These drivers are also typically hardware/operating system specific (because of the data access mechanism that they depend on), meaning they aren’t portable at all.

• Type II: These are partially written in Java and partially written in native data access languages (typically C or C++). The non-Java portion of these drivers limits the portability of the final code and platform migration possibilities. The administrative and maintenance burden of Type I still exists.

• Type III: These are pure Java drivers on the client side, which gives them the portability benefit of Java. However, they rely on an external middleware engine to operate. The client code communicates with the middleware engine, and the engine talks to the different types of database. The administration and maintenance burden is somewhat reduced but is far from eliminated.

• Type IV: These are 100 percent Java client drivers that talk directly to database network protocols. This results in the highest performance connection and the most portable application code. Administration and maintenance is greatly simplified (only the driver needs to be updated).

Fortunately, all the major databases have Type IV JDBC drivers available, either through the database vendors themselves or via a third-party driver vendor.

Database Connection Pooling

When a Web application accesses a remote database, it may do so through a JDBC connection. Typically, a physical JDBC connection is established between the client application and the database server via a TCP/IP connection. The action of establishing such a connection is CPU and time intensive. It involves multiple layers of software and the transmission and receipt of network data. A typical physical database connection may take seconds to establish.

Some Web applications consist of JSP pages and servlets that may need data from a database on every HTTP request. For example, an online library application will undoubtedly allow users to search the library catalog. On a heavily loaded server, the time it takes to establish, disconnect, and reestablish physical connections can substantially slow Web application performance.

To create high-performing, scalable Web applications, JDBC driver vendors and application servers are incorporating database connection pooling into their products. Connection pooling reduces expensive connection establishment time by creating a pool of physical connections when the system starts. When an application requires a connection, one of these physical connections is provided. Normally, when the application finishes using the connection, it would be disconnected. However, in the case of connection pooling, it’s merely returned to the pool where it awaits the next application request.