JDBC

What Is JDBC?

Working with leaders in the database field, JavaSoft developed a single API for database access--JDBC. As part of this process, they kept three main goals in mind:

* JDBC should be an SQL-level API.

* JDBC should capitalize on the experience of existing database APIs.

* JDBC should be simple.

An SQL-level API means that JDBC allows us to construct SQL statements and embed them inside Java API calls. In short, you are basically using SQL. But JDBC lets you smoothly translate between the world of the database and the world of the Java application. Your results from the database, for instance, are returned as Java variables, and access problems get thrown as exceptions. Later on in the book, we go a step further and talk about how we can completely hide the existence of the database from a Java application using a database class library.

Because of the confusion caused by the proliferation of proprietary database access APIs, the idea of a universal database access API to solve this problem is not a new one. In fact, JavaSoft drew upon the successful aspects of one such API, Open DataBase Connectivity (ODBC). ODBC was developed to create a single standard for database access in the Windows environment. Although the industry has accepted ODBC as the primary means of talking to databases in Windows, it does not translate well into the Java world. First of all, ODBC is a C API that requires intermediate APIs for other languages. But even for C developers, ODBC has suffered from an overly complex design that has made its transition outside of the controlled Windows environment a failure. ODBC's complexity arises from the fact that complex, uncommon tasks are wrapped up in the API with its simpler and more common functionality. In other words, in order for you to understand a little of ODBC, you have to understand a lot.

In addition to ODBC, JDBC is heavily influenced by existing database programming APIs such as X/OPEN SQL Call Level Interface. JavaSoft wanted to re-use the key abstractions from these APIs, which would ease acceptance by database vendors and capitalize on the existing knowledge capital of ODBC and SQL CLI developers. In addition, JavaSoft also realized that deriving an API from existing ones can provide quick development of solutions for database engines that support the old protocols. Specifically, JavaSoft worked in parallel with Intersolv to create an ODBC bridge that maps JDBC calls to ODBC calls, thus giving Java applications access to any database management system (DBMS) that supports ODBC.

JDBC attempts to remain as simple as possible while providing developers with maximum flexibility. A key criterion employed by JavaSoft is simply asking whether database access applications read well. The simple and common tasks use simple interfaces, while more uncommon or bizarre tasks are enabled through extra interfaces. For example, three interfaces handle a vast majority of database access. JDBC nevertheless provides several other interfaces for handling more complex and unusual tasks.

The Structure of JDBC

JDBC accomplishes its goals through a set of Java interfaces, each implemented differently by individual vendors. The set of classes that implement the JDBC interfaces for a particular database engine is called a JDBC driver. In building a database application, you do not have to think about the implementation of these underlying classes at all; the whole point of JDBC is to hide the specifics of each database and let you worry about just your application. Figure 4-1 shows the JDBC classes and interfaces.

Figure 4-1. The classes and interfaces of java.sql, the JDBC API package

If you think about a database query for any database engine, it requires you to connect to the database, issue your SELECT statement, and process the result set. In Example 4-1, we have the full code listing for a simple SELECT application from the Imaginary JDBC Driver for mSQL.[1] I wrote this driver for the Center for Imaginary Environments (http://www.imaginary.com), which is a non-commercial organization that promotes the development of virtual environment technologies like muds. This application is a single class that gets all of the rows from a table in an mSQL database located on my Sun box. First, it connects to the database by getting a database connection under my user id, borg, from the JDBC DriverManager class. It uses that database connection to create a Statement object that performs the SELECT query. A ResultSet object then provides the application with the key and val fields from the t_test table.

Example: A Simple SELECT Application from the Imaginary JDBC Implementation for mSQL

import java.sql.*;

public class SelectApp {

public static void main(String args[]) {

String url = "jdbc:msql://athens.imaginary.com:4333/db_web";

try {

Class.forName("imaginary.sql.iMsqlDriver");

}

catch( Exception e ) {

System.out.println("Failed to load mSQL driver.");

return;

}

try {

Connection con = DriverManager.getConnection(url, "borg", "");

Statement select = con.createStatement();

ResultSet result = select.executeQuery

("SELECT key, val FROM t_test");

System.out.println("Got results:");

while(result.next()) { // process results one row at a time

int key = result.getInt(1);

String val = result.getString(2);

System.out.println("key = " + key);

System.out.println("val = " + val);

}

select.close();

con.close();

}

catch( Exception e ) {

e.printStackTrace();

}

}

}

If you already have Java experience, then you should be able to understand the flow of the code in Example 4-1 without knowing any JDBC. There are no references to specific database engine classes. Instead, the code simply uses JDBC interfaces to provide a facade for the DBMS-specific implementation. The JDBC implementation, in turn, performs the actual database access somewhere behind the scenes.

In this simple application, the SelectApp class asks the JDBC DriverManager to hand it the proper database implementation based on a database URL. The database URL looks similar to other Internet URLs. The actual content of the URL is loosely specified as jdbc:subprotocol:subname. The subprotocol identifies which driver to use, and the subname provides the driver with any required connection information. For the Imaginary JDBC Implementation for mSQL that I used in testing the above example, the URL is jdbc:msql://athens.imaginary.com:4333/db_web. In other words, this URL says to use the mSQL JDBC driver to connect to the database db_web on the server running at port 4333 on athens.imaginary.com. Each URL, however, is specific to the JDBC implementation being sought, and so I can't say anything more explicit about it. Whatever its format, the primary function of a database URL is to uniquely identify the implementation needed by the application and pass that implementation any information it needs in order to connect to the proper database.

Databases and Drivers

In putting together the examples in this book, I used both an mSQL database for the simple Chapter 4 examples and an Oracle database for the more complex examples of Chapter 5. If you do not have a corporate pocketbook to back up your database purchase, mSQL is probably the most feasible solution. You should keep in mind, however, that mSQL does not allow you to abort transactions and does not support the stored procedures used in Chapter 5. Whatever your database choice, you must set up your database engine, create a database, and create the tables shown in the Chapter 3 data model before you can begin writing JDBC code.

Once your database engine is installed and your database is all set up, you will need a JDBC driver for that database engine. You can find an mSQL JDBC driver at http://www.imaginary.com/Java. The more commercial database engines like Oracle have commercial JDBC drivers. Most of them, however, allow you to have a free trial period for experimenting with the driver. Follow the install instructions for the driver you choose, and remember that some JDBC drivers require to you install native code on client machines. To help you understand what different drivers require, JavaSoft has defined the following driver categorization system:

type 1

These drivers use a bridging technology to access a database. The JDBC-ODBC bridge that comes with the JDK 1.1 is a good example of this kind of driver. It provides a gateway to the ODBC API. Implementations of that API in turn do the actual database access. Bridge solutions generally require software to be installed on client systems, meaning that they are not good solutions for applications that do not allow you to install software on the client.

type 2

The type 2 drivers are native API drivers. This means that the driver contains Java code that calls native C or C++ methods provided by the individual database vendors that perform the database access. Again, this solution requires software on the client system.

type 3

Type 3 drivers provide a client with a generic network API that is then translated into database specific access at the server level. In other words, the JDBC driver on the client uses sockets to call a middleware application on the server that translates the client requests into an API specific to the desired driver. As it turns out, this kind of driver is extremely flexible since it requires no code installed on the client and a single driver can actually provide access to multiple databases.

type 4

Using network protocols built into the database engine, type 4 drivers talk directly to the database using Java sockets. This is the most direct pure Java solution. In nearly every case, this type of driver will come only from the database vendor.