ONJava.com    
 Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples


O'Reilly Book Excerpts: Java Enterprise in a Nutshell, 2nd Edition

An Introduction to JDBC, Part 1

Related Reading

Java Enterprise in a Nutshell
By William Crawford, Jim Farley, David Flanagan

by William Crawford, Jim Farley and David Flanagan

Editor's Note: This is the first part in a series of excerpts from O'Reilly's Java Enterprise in a Nutshell, Second Edition. These excerpts are from Chapter 2, JDBC. The full chapter is also available on oreilly.com.

The JDBC API provides Java applications with mid-level access to most database systems, via the Structured Query Language (SQL). JDBC is a key enterprise API, as it's hard to imagine an enterprise application that doesn't use a database in some way. (According to Sun, JDBC is not an acronym for Java Database Connectivity.)

In the first edition of this book, we focused on the original JDBC 1.0 API, and touched briefly on the new features provided by the JDBC 2.0 API. JDBC 2.1 is now a standard component of the J2SE platform, and drivers supporting the upgraded specification are widely available. In this edition, we discuss the JDBC 2.1 API and the JDBC 2.0 Optional Packages (previously known as the JDBC 2.0 Standard Extension) and take a look at the upcoming JDBC 3.0 API.

A word of caution: while the java.sql package is not tremendously complex, it does require grounding in general database concepts and the SQL language itself. This book includes a brief SQL reference (see Chapter 12), but if you have never worked with a relational database system before, this chapter is not the place to start. For a more complete treatment of JDBC and general database concepts, we recommend Database Programming with JDBC and Java by George Reese (O'Reilly).

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 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 encapsulate 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 2-1 shows how an application uses JDBC to interact with one or more databases without knowing about the underlying driver implementations.

Diagram.
Figure 2-1. JDBC-database interaction

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 2-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 2-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 an 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 2-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 an SQL statement. Executing an 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.

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).

Another way to register drivers is to add the driver classes to the jdbc.drivers property. To use this technique, add a line like the following to ~/.hotjava/properties (on Windows systems this file can be found in your Java SDK installation directory):

jdbc.drivers=com.oracle.jdbc.OracleDriver:foo.driver.dbDriver:
     com.al.AlDriver;

Separate the names of individual drivers with colons and be sure the line ends with a semicolon. (Programs rarely use this approach, as it requires additional configuration work on the part of end users.) Every user needs to have the appropriate JDBC driver classes specified in his properties file.

Finally, drivers can be loaded by a J2EE server and provided to the application via JNDI. We'll see more about that the end of this chapter.

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. The JDBC-ODBC Bridge from Sun and InterSolv is the only existing example of a Type 1 driver. 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 currently available JDBC drivers is available at http://java.sun.com/products/jdbc/jdbc.drivers.html.

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.

The JDBC-ODBC Bridge

The JDBC-ODBC Bridge ships with JDK 1.1 and the Java 2 SDK for Windows and Solaris systems. 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 is not a required component of the Java SDK, 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++.

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

Next, learn connecting to the database.

William Crawford, Jim Farley is a coauthor of Java Enterprise in a Nutshell, 2nd Edition, and has been developing web-based enterprise applications since 1995. He is currently the Director of the Informatics Solutions Group at Children's Hospital, Boston, where he and his team are building open source Personally Controlled Health Record systems and tools for managing agile development projects in healthcare and regulated industries.

David Flanagan is the author of a number of O'Reilly books, including Java in a Nutshell, Java Examples in a Nutshell, Java Foundation Classes in a Nutshell, JavaScript: The Definitive Guide, and JavaScript Pocket Reference.


View catalog information for Java Enterprise in a Nutshell, Second Edition

Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.