11.1 Architecture

Working with leaders in the database field, Sun developed JDBC as a unified API for database access. As part of the JDBC design process, they kept in mind three main goals:

• JDBC should be an SQL-level API.

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

• JDBC should be relatively simple.

As an SQL-level API, JDBC enables you to construct SQL statements and embed them inside API calls. You are essentially using JDBC to make a smooth transition between the SQL and Java. Your application sends a query to the database as SQL and gets the results back through a Java object. Any database error you encounter is thrown to your application as a Java exception.

Database programming in most languages today is very different from what it was just seven years ago. It used to be that each database engine had its own proprietary C API. If you were programming against Sybase, you had to learn Sybase's API. On the other hand, you had to learn a very different Oracle API if you needed to talk to Oracle. For programming in languages other than C, you had to write a bridge to the C API.

JDBC leverages several attempts to provide unified database APIs, including ODBC and X/OPEN SQL. ODBC (Open Database Connectivity) was initially created as a Windows API for open database access. Although the industry has accepted ODBC as a standard, it does not translate well into the Java world:

• ODBC is primarily a Windows API and best suited for applications on the Windows platform.

• ODBC has an overly complex design with a steep learning curve.

In addition to ODBC, the X/OPEN SQL Call Level Interface (CLI) heavily influenced JDBC. Sun wanted to reuse the key abstractions from both ODBC and X/OPEN in order to ease acceptance of the API by database vendors and thus capitalize on the existing knowledge capital in the incumbent APIs. In addition, Sun realized that deriving an API from existing ones can provide quick development of solutions for database engines supporting only the old protocols. Specifically, Sun worked in parallel with Intersolv to create an ODBC bridge that maps JDBC calls to ODBC calls. Consequently, every Java VM has the ability to talk to any ODBC-supported database using this bridge.

The JDBC-ODBC bridge is a great tool for developers who are interested in learning JDBC but may not want to invest in anything beyond the Microsoft Access database that comes with Microsoft Office. When developing for production sites, however, you almost certainly want to move to a JDBC driver that is native to your deployment database engine.

JDBC attempts to remain as simple as possible while providing developers with maximum flexibility. A key criterion employed by Sun is simply asking whether database applications read well. The simple and common tasks use simple interfaces, while more uncommon or bizarre tasks are enabled through specialized interfaces. For example, a handful of method calls in three interfaces manage the vast majority of database access. This list of key methods and interfaces has changed very little since the first JDBC specification in March 1996. JDBC nevertheless provides many other interfaces for handling more complex and unusual tasks.

11.1.1 The Core Interfaces

JDBC accomplishes its goals through a set of Java interfaces, each implemented differently by individual vendors. The set of classes that implements the JDBC interfaces for a particular database engine combine into a tool 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 enable you to focus on your application logic. Figure 11-1 illustrates the JDBC architecture.

Figure 11-1. The JDBC architecture

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 any results. Example 11-1 is the JSP code for a very simple SELECT call that pulls all rows from a table in a MySQL database. It gets a connection from a data source configured in a JNDI directory service and then uses that connection to execute the query. The values in each row then appear in the generated HTML.

Example 11-1. A simple JSP page displaying the data in a table

<%@ page info="Table Results Page""%>
   
<%@ page import="java.sql.*""%>
<%@ page import="java.naming.*""%>
<%@ pagge import="javax.sql.DataSource""%>
   
<html>
  <head>
    <title>Table Results Page</title>
  </head>
  <body>
    <table>
      <tr>
        <th>ID</th>
        <th>Value</th>
      </tr>
      <%
        InitialContext ctx = new InitialContext( );
        DataSource ds = ctx.lookup("jdbc/ora");
        Connection conn = ds.getConnection( );
        Statement stmt = conn.createStatement( );
        ResultSet rs;
  
        rs = stmt.executeQuery("SELECT id, val FROM test");
        while( rs.next( ) ) {
      %>
      <tr>
        <td><%=rs.getint(1)%></td>
        <td><%=rs.getString(2)%></td>
      </tr>
      <%
        }
        conn.close( );
      %>
    </table>
  </body>
</html>

If you are an experienced JSP programmer, you should be able to follow the flow of this JSP page without knowing any JDBC. No references to any vendor-specific procedures exist in the sample code. Instead, this page uses only JDBC interfaces to provide an abstraction of the DBMS-specific implementation. The JDBC implementation, in turn, performs the actual database access somewhere behind the scenes. Figure 11-2 is a UML class diagram of the basic JDBC classes and interfaces.

Figure 11-2. The basic classes and interfaces of the JDBC API

In the simple JSP page of Example 11-1, the JSP code asks JNDI for a DataSource object stored in the directory service under the name jdbc/ora. Someone should have configured your directory service to hold the information JDBC needs to make a database connection. We will talk more about this configuration later in the chapter.

The JSP page then gets a Connection instance from the DataSource. This Connection is the JDBC representation of a physical connection to your database. You can use it to execute all the SQL you like.

11.1.2 Databases and Drivers

Before you can get a connection from JNDI, you need to have a database to connect to. Whatever database you have the most immediate access to will work. If you do not have access to a database, I recommend using an open source database like MySQL (www.mysql.com) or PostgreSQL (www.postgresql.org). Both are free and will enable you to learn JDBC and develop solid database applications. PostgreSQL has the advantage of supporting a fuller range of JDBC features, whereas MySQL has the advantage of being easier to install and set up.

Once your database engine is installed and your database is all set up, you will need a JDBC driver to connect to that database engine. Whatever your database of choice, it should be very simple to find a driver free of charge. Most commercial databases ship with a JDBC driver. Other database engines allow you to download a driver from their web site. In addition, you can opt for a commercially developed driver that probably performs better than the vendor-supported driver.

JDBC drivers come in four distinct categories. Sun has named the categories types 1-4.

Type 1

A type 1 driver is a bridge between JDBC and another database-independent API like ODBC. The JDBC-ODBC driver that comes with the Java SDK is the primary example of a type 1 driver.

Type 2

A type 2 driver translates JDBC calls into a native API provided by the database vendor.

Type 3

Type 3 drivers are network bridges that enable an application to take advantage of the WORA (Write Once, Run Anywhere) capabilities of type 4 drivers even when your database of choice supports only type 2 drivers.

Type 4

A type 4 driver talks directly to a database using a network protocol. Because it makes no native calls, it can run on any JVM.

For a list of all currently known drivers, their types, and the version of JDBC they support, visit http://industry.java.sun.com/products/jdbc/drivers.