Java Enterprise in a Nutshell (In a Nutshell (OReilly))

8.1. JDBC Architecture

Different database systems have surprisingly little in common: just a similar purpose and a mostly compatible query language. Beyond that, every database has its own API that you must learn in order to write programs that interact with the database. This has meant that writing code capable of interfacing with databases from more than one vendor has been a daunting challenge. Cross-database APIs exist, most notably Microsoft's ODBC API, but these tend to find themselves, at best, limited to a particular platform.

JDBC is Sun's attempt to create a platform-neutral interface between databases and Java. With JDBC, you can count on a standard set of database access features and (usually) a particular subset of SQL, SQL-92 . The JDBC API defines a set of interfaces that encapsulates major database functionality, including running queries, processing results, and determining configuration information. A database vendor or third-party developer writes a JDBC driver, which is a set of classes that implements these interfaces for a particular database system. An application can use a number of drivers interchangeably. Figure 8-1 shows how an application uses JDBC to interact with one or more databases without knowing about the underlying driver implementations.

8.1.1. JDBC Basics

Before we discuss all of the individual components of JDBC, let's look at a simple example that incorporates most of the major pieces of JDBC functionality. Example 8-1 loads a driver, connects to the database, executes some SQL, and retrieves the results. It also keeps an eye out for any database-related errors.

Example 8-1. A simple JDBC example

import java.sql.*; public class JDBCSample { public static void main(java.lang.String[] args) { try { // This is where we load the driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); } catch (ClassNotFoundException e) { System.out.println("Unable to load Driver Class"); return; } try { // All database access is within a try/catch block. Connect to database, // specifying particular database, username, and password. Connection con = DriverManager.getConnection("jdbc:odbc:companydb", "", ""); // Create and execute a SQL statement Statement stmt = con.createStatement( ); ResultSet rs = stmt.executeQuery("SELECT FIRST_NAME FROM EMPLOYEES"); // Display the SQL results while(rs.next( )) { System.out.println(rs.getString("FIRST_NAME")); } // Make sure our database resources are released rs.close( ); stmt.close( ); con.close( ); } catch (SQLException se) { // Inform user of any SQL errors System.out.println("SQL Exception: " + se.getMessage( )); se.printStackTrace(System.out); } } }

Example 8-1 starts out by loading a JDBC driver class (in this case, Sun's JDBC-ODBC bridge). Then it creates a database connection, represented by a Connection object, using that driver. With the database connection, we can create a Statement object to represent a SQL statement. Executing a SQL statement produces a ResultSet that contains the results of a query. The program displays the results and then cleans up the resources it has used. If an error occurs, a SQLException is thrown, so our program traps that exception and displays some of the information it encapsulates.

Clearly, there is a lot going on in this simple program. Every Java application that uses JDBC follows these basic steps, so the following sections discuss each step in much more detail.

8.1.2. JDBC Drivers

Before you can use a driver, it must be registered with the JDBC DriverManager. This is typically done by loading the driver class using the Class.forName( ) method:

try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Class.forName("com.oracle.jdbc.OracleDriver"); } catch (ClassNotFoundException e) { /* Handle Exception */ }

One reason most programs call Class.forName( ) is that this method accepts a String argument, meaning that the program can store driver selection information dynamically (e.g., in a properties file).

Drivers can be loaded by a J2EE server and connections made using these drivers can be provided to the application via JNDI. We'll see more about that toward the end of this chapter. For most applications, server-managed connections have replaced the use of DriverManager.

JDBC drivers are available for most database platforms, from a number of vendors and in a number of different flavors. There are four categories of drivers:

Type 1: JDBC-ODBC bridge drivers

Type 1 drivers use a bridge technology to connect a Java client to an ODBC database system. Type 1 drivers require some sort of non-Java software to be installed on the machine running your code, and they are implemented using native code.

Type 2: Native-API partly Java drivers

Type 2 drivers use a native code library to access a database, wrapping a thin layer of Java around the native library. For example, with Oracle databases, the native access might be through the Oracle Call Interface (OCI) libraries that were originally designed for C/C++ programmers. Type 2 drivers are implemented with native code, so they may perform better than all-Java drivers, but they also add an element of risk, as a defect in the native code can crash the Java virtual machine.

Type 3: Net-protocol all-Java drivers

Type 3 drivers define a generic network protocol that interfaces with a piece of custom middleware. The middleware component might use any other type of driver to provide the actual database access. BEA's WebLogic product line (formerly known as WebLogic Tengah and before that as jdbcKona/T3) is an example. These drivers are especially useful for applet deployment since the actual JDBC classes can be written entirely in Java and downloaded by the client on the fly.

Type 4: Native-protocol all-Java drivers

Type 4 drivers are written entirely in Java. They understand database-specific networking protocols and can access the database directly without any additional software. These drivers are also well-suited for applet programming, provided that the Java security manager allows TCP/IP connections to the database server.

When you are selecting a driver, you need to balance speed, reliability, and portability. Different applications have different needs. A standalone, GUI-intensive program that always runs on a Windows NT system will benefit from the additional speed of a Type 2, native-code driver. An applet might need to use a Type 3 driver to get around a firewall. A servlet that is deployed across multiple platforms might require the flexibility of a Type 4 driver.

A list of JDBC drivers is available at http://developers.sun.com/product/jdbc/drivers.

8.1.3. JDBC URLs

A JDBC driver uses a JDBC URL to identify and connect to a particular database. These URLs are generally of the form:

jdbc:driver:databasename

The actual standard is quite fluid, however, as different databases require different information to connect successfully. For example, the Oracle JDBC-Thin driver uses a URL of the form:

jdbc:oracle:thin:@site:port:database

while the JDBC-ODBC bridge uses:

jdbc:odbc:datasource;odbcoptions

The only requirement is that a driver be able to recognize its own URLs.

8.1.4. The JDBC-ODBC Bridge

The JDBC-ODBC bridge ships with Sun's JDK. The bridge provides an interface between JDBC and database drivers written using Microsoft's Open Database Connectivity (ODBC) API. The bridge was originally written to allow the developer community to get up and running quickly with JDBC. Since the bridge makes extensive use of native method calls, it is not recommended for long-term or high-volume deployment. The bridge also doesn't support JDBC 3.0 functionality (the driver bundled with JDK 1.4 and Java 5.0 supports JDBC 2.0).

The bridge is not a required component of the JDK, so it is not supported by most web browsers or other runtime environments. Using the bridge in an applet requires a browser with a JVM that supports the JDBC-ODBC bridge, as well as a properly configured ODBC driver and data source on the client side.^[*]

^[*] Finally, due to different implementations of the native methods interface, the bridge doesn't work with some development environments, most notably Microsoft Visual J++. This was a big deal when VJ++ was introduced, but since the product was discontinued years ago, you're likely to encounter this only when maintaining legacy code.

The JDBC URL subprotocol odbc has been reserved for the bridge. Like most JDBC URLs, it allows programs to encode extra information about the connection. ODBC URLs are of the form:

jdbc:odbc:datasourcename[;attribute-name=attribute-value]*

For instance, a JDBC URL pointing to an ODBC data source named companydb with the CacheSize attribute set to 10 looks like this:

jdbc:odbc:companydb;CacheSize=10