A database works in the classic client/server way. There is one database and many clients talk to it. (Larger enterprises may have multiple databases, but these can be considered independently for our purposes.) The clients are typically remote systems communicating over TCP/IP networks. They may talk directly to the database (called a “2-tier” system) or to a business logic server that talks to the database (known as a “3-tier” system).
How does a client or business logic program open a dialog with a database manager? JDBC uses a piece of software called a database driver. The database driver is specific to each vendor, and it is a library-level example of the Adaptor design pattern. It knows how to connect to its database, send requests over TCP/IP, and how to listen for replies from the database and pass them on to your code. Just as an operating system device driver hides the peculiarities of an I/O device from the kernel and presents a standard interface for system calls, each JDBC driver hides the vagaries of its particular database and presents a standard interface to Java programs that use JDBC.
Putting it another way, the purpose of a JDBC database driver is to know the low-level protocol for talking with its database at one end, and with JDBC classes and methods at the other end. It acts like a human language interpreter, moving information from one end and putting it in a standard form that is comprehensible to the other end (see Figure 24-1). You typically get a JDBC database driver from the database vendor. There are several different kinds of database drivers, depending on whether it is written in Java or native code, or whether it talks directly to the database or through another data access protocol such as Microsoft's ODBC. None of that matters much to the applications programmer. As long as you have a working JDBC driver, you don't care how it works. Essentially, all commercial and non-commercial databases now have excellent support for access from Java programs. There are good third-party libraries that can be used to access the Microsoft database products.
First, you do a class.forName on the JDBC driver name. That causes its class to be loaded into the JVM that's executing your program.
Class.forName("com.mckoi.JDBCDriver");
You don't need to create an instance of the driver class. Simply getting the class loaded is enough. Here's how it works. Each JDBC driver has a static initializer that is run when the class is loaded, and in that code the driver registers itself with the JDBC. You can also load a JDBC driver into the DriverManager by adding an entry to the sql.drivers property of the JVM. For example:
java -Dsql.drivers=com.mckoi.JDBCDriver ...
If you do this, you don't need the previous call to Class.forName. Whichever approach you take, JDBC now knows about this driver, and can make calls to it. The JDBC driver does 90% of the work that is done in JDBC. Since Mckoi is an open source product, you can inspect the code to confirm how this works for yourself.
Next, your Java application program asks for a connection to the database, using a string that looks like a URL as an argument. The JDBC library has a class called java.sql.Connection that knows how to use that string to guide it in its search for the right database.
The exact format of the pseudo-URL string will vary with each database, but it typically starts with “jdbc:” to indicate the protocol you will be using, just as “http:” indicates to a web server that you will be using the hypertext transport protocol. The string will then go on to give some indication of the database host name, the port number, and a database-specific subprotocol to use. The Mckoi database uses a pseudo-URL like this:
String url = "jdbc:mckoi:local://ExampleDB.conf?create=true";
That URL names a file called “ExampleDB.conf” on the local host in the current working directory that holds configuration information on the database. The parameter in the URL “create=true” says that we intend to create the database in our program. Sun seems to want database implementors to cope with database creation commands based on SQL, not attributes passed in the URL. Sun's preferred approach adds complexity because it requires a database to be able to parse SQL commands before it exists.
The “create=true” parameter to create a database is vendor-specific, but it's also how Cloudscape (see Table 24-1) works for embedded databases. The exact form of the pseudo-URL will vary from vendor to vendor. You need to read the documentation that comes with the database. Then your code will call a static method of the overall JDBC driver manager to get you a connection based on that string, and strings representing a username and password.
connection = java.sql.DriverManager.getConnection(url, user, passwd);
Behind the scenes, the DriverManager calls every JDBC driver that has registered, and asks it if that is a URL it can use to guide it to its database. If we have prepared the ground correctly, the URL will be recognized by at least one of the drivers. The first driver to connect to its database with this URL, username, and password, will be used as the channel of communication. The application program gets back a “Connection” object. (Strictly speaking, it gets an object that implements the Connection interface.) The session has been established, and the connection is now used for communication between your program and the database. Why doesn't the application simply talk directly to the driver? It could do that, but then you don't have a single standard library anymore—you have a collection of 50 different protocols and conventions for talking to 50 different databases. The point of the JDBC is to avoid that.
Connecting to a database is an expensive (time-consuming) operation. You would never design a servlet system that opened a new connection for every doPost() request. Most databases have a way to share connections among several different processes. This arrangement is known as “connection pooling.” JDBC 2.0 introduced a new and preferred approach to getting a connection. Instead of using a Driver directly, you use a DataSource object which you configure and register with a naming service (e.g., LDAP, YP, NIS+) that uses the Java Naming and Directory Interface (JNDI). That is intended for enterprise-level software and takes a lot more setting up, so we'll stick to simple drivers in this chapter.
In summary, your application program knows which database it wants to talk to, and hence which database driver it needs to load. The JDBC driver manager knows how to establish the JDBC end of a database connection, and the driver knows how to establish the database end. They do it. The driver manager gives you back a connection into which you can pour standard SQL queries and get results.