24.1 JDBC Basics

The JDBC API is a set of classes and interfaces that allows a Java application to send SQL statements to a database in a vendor-independent way. The API consists mostly of interfaces that define the methods you use in your program. Database engine vendors and third parties provide implementations of these interfaces for a specific database engine; such an implementation is called a JDBC driver. This allows you to develop your program in a database-independent way and connect to a specific database engine by plugging in the appropriate JDBC driver at deployment time. There are JDBC drivers for most database engines on the market, both commercial and open source. If you can't get one from your vendor, check out Sun's list of third-party drivers at http://industry.java.sun.com/products/jdbc/drivers.

Figure 24-1 shows how the main interfaces and classes are related.

Figure 24-1. Main JDBC interfaces and classes

All JDBC core classes and interfaces belong to the java.sql package. Of the types shown in Figure 24-1, only the DriverManager is a class (part of the standard J2SE package); the rest are interfaces implemented by each unique JDBC driver.

The Driver implementation is the entry point to all the other interface implementations. When the Driver is loaded, it register itself with the DriverManager. When the JDBC application needs a connection to a database, it asks the DriverManager for one, and the DriverManager asks each registered Driver if it knows how to create connections for the requested database. When a Driver replies "yes," the DriverManager asks it for a Connection on the application's behalf; the Driver attempts to create one and return it to the application.

The Connection is another core JDBC type. Through the Connection instance, the JDBC application can create Statement instances of different types. The main Statement type can execute a plain SQL statement, such as SELECT, UPDATE, or DELETE. When a SELECT statement is executed, the result is returned as an instance of ResultSet. The ResultSet has methods for navigating the result rows and asking for the column values in the current row.

There are two specialized Statement types: PreparedStatement and CallableStatement. For a PreparedStatement, you can specify an SQL statement where, instead of literal column values, the statement contains parameter placeholders, symbolized by question marks:

SELECT * FROM Enployee WHERE UserName = ?

Special setter methods assign values to the placeholders before the SQL statement is executed. The same PreparedStatement can then be assigned new placeholder values and executed again. This allows a database to parse the statement once, typically caching a strategy for how to execute it in the most efficient way, and then execute it over and over again with new values. This can result in dramatically improved performance over using a regular Statement. The PreparedStatement is also useful in other ways, as we will discuss later.

The CallableStatement is for stored procedures. The same as for a PreparedStatement, you can assign values to input arguments, but in addition, there are methods for declaring the types of output arguments.

Other interfaces in the JDBC API provide access to metadata about the database and the JDBC driver itself (DatabaseMetaData, available from the Connection, containing information about supported features) as well as about a ResultSet (ResultSetMetaData, available from the ResultSet, containing information about column data types, null values, etc.).

To see how it all fits together, here's a simple program that uses most of these classes:

import java.sql.*;
public class DBTest {
    public static void main(String[] args) throws Exception {
        // Load the JDBC Driver
        Class.forName("oracle.jdbc.OracleDriver");
  
        // Get a Connection
        String url = "jdbc:oracle:thin:@myhost:1521:ORASID";
        Connection conn = DriverManager.getConnection(url, "scott",
            "tiger");
  
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String sql = "SELECT * From Employee WHERE UserName = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "hans");
            rs = pstmt.executeQuery(  );
  
            while (rs.next(  )) {
                System.out.println(rs.getString("FirstName"));
                System.out.println(rs.getString("LastName"));
            }
        }
        finally {
            if (rs != null) {
                try {rs.close(  );} catch (SQLException e) {}
            }
            if (pstmt != null) {
                try {pstmt.close(  );} catch (SQLException e) {}
            }
            if (conn != null) {
                try {conn.close(  );} catch (SQLException e) {}
            }
        }
    }
}

It first loads a Driver (an Oracle JDBC driver in this example) and then gets a Connection. The getConnection( ) argument is a JDBC URL that identifies a specific database. Different JDBC drivers use different URL syntax. All JDBC URLs starts with jdbc: followed by a JDBC driver identifier, such as oracle: for Oracle's drivers. The rest of the URL is used to identify other details for the driver and database instance. For the Oracle driver used here, it's the type of driver, the host and port where the database runs, and the database instance system identifier. Consult the documentation for your JDBC driver to see how the URL should look like if you use a different driver.

The program then creates a PreparedStatement for an SQL statement with a placeholder symbol, assigns a value to the placeholder, executes the query, and loops through all result rows represented by the ResultSet.

To run a program that uses JDBC, you need to include the JDBC driver classes for your database in the class path. They are typically delivered as a JAR file, so for a web application you just place the JAR file in the WEB-INF/lib directory. If they are delivered as a ZIP file (as some of Oracle's JDBC drivers are, for instance), you can still place it in the WEB-INF/lib directory if you change the file extension from .zip to .jar.