The JDBC API (Java Servlet Programming)

Previously, we've assumed that you have a general working knowledge of the various Java APIs. Because even experienced Java programmers may have had relatively little experience with databases, this section provides a general introduction to JDBC. If this is your first foray into the world of databases, we strongly recommend that you take a breather and find a book on general database and JDBC concepts. You may want to read Database Programming with JDBC and Java, by George Reese (O'Reilly), orJDBC Database Access with Java , by Graham Hamilton, Rick Cattell, and Maydene Fisher (Addison-Wesley). The official JDBC specification is also available online at http://java.sun.com/products/jdbc .

JDBC is a SQL-level API--one that allows you to execute SQL statements and retrieve the results, if any. The API itself is a set of interfaces and classes designed to perform actions against any database. Figure 9-2 shows how JDBC programs interact with databases.

Figure 9-2. Java and the database

9.2.1. JDBC Drivers

The JDBC API, found in the java.sql package, contains only a few concrete classes. Much of the API is distributed as database-neutral interface classes that specify behavior without providing any implementation. The actual implementations are provided by third-party vendors.

An individual database system is accessed via a specific JDBC driver that implements the java.sql.Driver interface. Drivers exist for nearly all popular RDBMS systems, though few are available for free. Sun bundles a free JDBC-ODBC bridge driver with the JDK to allow access to standard ODBC data sources, such as a Microsoft Access database. However, Sun advises against using the bridge driver for anything other than development and very limited deployment. Servlet developers in particular should heed this warning because any problem in the JDBC-ODBC bridge driver's native code section can crash the entire server, not just your servlets.

JDBC drivers are available for most database platforms, from a number of vendors and in a number of different flavors. There are four driver categories:

9.2.2. Getting a Connection

The first step in using a JDBC driver to get a database connection involves loading the specific driver class into the application's JVM. This makes the driver available later, when we need it for opening the connection. An easy way to load the driver class is to use the Class.forName() method:

When the driver is loaded into memory, it registers itself with the java.sql.DriverManager class as an available database driver.

The next step is to ask the DriverManager class to open a connection to a given database, where the database is specified by a specially formatted URL. The method used to open the connection is DriverManager.getConnection() . It returns a class that implements the java.sql.Connection interface:

A JDBC URL identifies an individual database in a driver-specific manner. Different drivers may need different information in the URL to specify the host database. JDBC URLs usually begin with jdbc:subprotocol:subname. For example, the Oracle JDBC-Thin driver uses a URL of the form of jdbc:oracle:thin:@dbhostport:sid;the JDBC-ODBC bridge uses jdbc:odbc:data- sourcename ;odbcoptions.

During the call to getConnection() , the DriverManager object asks each registered driver if it recognizes the URL. If a driver says yes, the driver manager uses that driver to create the Connection object. Here is a snippet of code a servlet might use to load its database driver with the JDBC-ODBC bridge and create an initial connection:

9.2.3. Executing SQL Queries

To really use a database, we need to have some way to execute queries. The simplest way to execute a query is to use thejava.sql.Statement class. Statement objects are never instantiated directly; instead, a program calls the createStatement() method of Connection to obtain a new Statement object:

A query that returns data can be executed using the executeQuery() method of Statement. This method executes the statement and returns a java.sql.ResultSet that encapsulates the retrieved data:

You can think of a ResultSet object as a representation of the query result returned one row at a time. You use the next()method of ResultSet to move from row to row. The ResultSet interface also boasts a multitude of methods designed for retrieving data from the current row. The getString()and getObject()methods are among the most frequently used for retrieving column values:

You should know that the ResultSet is linked to its parent Statement. Therefore, if a Statement is closed or used to execute another query, any related ResultSet objects are closed automatically.

Example 9-1 shows a very simple servlet that uses the Oracle JDBC driver to perform a simple query, printing names and phone numbers for all employees listed in a database table. We assume that the database contains a table named EMPLOYEES, with at least two fields, NAME and PHONE.

Example 9-1. A JDBC-enabled servlet

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class DBPhoneLookup extends HttpServlet {

  public void doGet(HttpServletRequest req, HttpServletResponse res)
                               throws ServletException, IOException {
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

    res.setContentType("text/html");
    PrintWriter out = res.getWriter(); 

    try {
      // Load (and therefore register) the Oracle Driver
      Class.forName("oracle.jdbc.driver.OracleDriver"); 
     
      // Get a Connection to the database
      con = DriverManager.getConnection(
        "jdbc:oracle:thin:@dbhost:1528:ORCL", "user", "passwd");
     
      // Create a Statement object
      stmt = con.createStatement();
     
      // Execute an SQL query, get a ResultSet
      rs = stmt.executeQuery("SELECT NAME, PHONE FROM EMPLOYEES");
     
      // Display the result set as a list
      out.println("<HTML><HEAD><TITLE>Phonebook</TITLE></HEAD>");
      out.println("<BODY>");
      out.println("<UL>");
      while(rs.next()) {
        out.println("<LI>" + rs.getString("name") + " " + rs.getString("phone"));  
      } 
      out.println("</UL>");
      out.println("</BODY></HTML>");
    }
    catch(ClassNotFoundException e) { 
      out.println("Couldn't load database driver: " + e.getMessage());
    }
    catch(SQLException e) { 
      out.println("SQLException caught: " + e.getMessage());
    }
    finally {
      // Always close the database connection.
      try {
        if (con != null) con.close();
      }
      catch (SQLException ignored) { }
    }
  }
}

This is about as simple a database servlet as you are likely to see. All DBPhoneLookup does is connect to the database, run a query that retrieves the names and phone numbers of everyone in the employees table, and display the list to the user.

9.2.4. Handling SQL Exceptions

DBPhoneLookup encloses most of its code in a try/catch block. This block catches two exceptions: ClassNotFoundException and SQLException . The former is thrown by the Class.forName() method when the JDBC driver class can not be loaded. The latter is thrown by any JDBC method that has a problem. SQLException objects are just like any other exception type, with the additional feature that they can chain. The SQLException class defines an extra method, getNextException(), that allows the exception to encapsulate additional Exception objects. We didn't bother with this feature in the previous example, but here's how to use it:

This code displays the message from the first exception and then loops through all the remaining exceptions, outputting the error message associated with each one. In practice, the first exception will generally include the most relevant information.

9.2.5. Result Sets in Detail

Before we continue, we should take a closer look at the ResultSet interface and the related ResultSetMetaData interface. In Example 9-1, we knew what our query looked like, and we knew what we expected to get back, so we formatted the output appropriately. But, if we want to display the results of a query in an HTML table, it would nice to have some Java code that builds the table automatically from the ResultSet rather than having to write the same loop-and-display code over and over. As an added bonus, this kind of code makes it possible to change the contents of the table simply by changing the query.

The ResultSetMetaData interface provides a way for a program to learn about the underlying structure of a query result on the fly. We can use it to build an object that dynamically generates an HTML table from a ResultSet, as shown in Example 9-2. Many Java HTML generation tools (such as WebLogic's htmlKona toolkit discussed in Chapter 5, "Sending HTML Information") have a similar capability.

Example 9-2. A class to generate an HTML table from a ResultSet using ResultSetMetaData

import java.sql.*;

public class HtmlResultSet {

  private ResultSet rs;

  public HtmlResultSet(ResultSet rs) {
    this.rs = rs;
  }

  public String toString() {  // can be called at most once
    StringBuffer out = new StringBuffer();
    // Start a table to display the result set
    out.append("<TABLE>\n");

    try {
      ResultSetMetaData rsmd = rs.getMetaData();

      int numcols = rsmd.getColumnCount();
		
      // Title the table with the result set's column labels
      out.append("<TR>");
      for (int i = 1; i <= numcols; i++) {
        out.append("<TH>" + rsmd.getColumnLabel(i));
      }
      out.append("</TR>\n");

      while(rs.next()) {
        out.append("<TR>"); // start a new row
        for (int i = 1; i <= numcols; i++) {
          out.append("<TD>"); // start a new data element
          Object obj = rs.getObject(i);
          if (obj != null)
            out.append(obj.toString());
          else
            out.append("&nbsp;");
        } 
        out.append("</TR>\n");
      }

      // End the table
      out.append("</TABLE>\n");
    }
    catch (SQLException e) {
      out.append("</TABLE><H1>ERROR:</H1> " + e.getMessage() + "\n");
    }       
		
    return out.toString();
  }
}

This example shows how to use two basic methods of ResultSetMetaData: getColumnCount()and getColumnLabel(). The first returns the number of columns in the ResultSet, while the second retrieves the name of a particular column in a result set based on its numerical index. Indexes in ResultSet objects follow the RDBMS standard rather than the C++/Java standard, which means they are numbered from 1 to n rather than from 0 to n-1.

This example also uses the getObject() method of ResultSet to retrieve the value of each column. All of the getXXX() methods work with column indexes as well as with column names. Accessing data this way is more efficient, and, with well-written SQL, is more portable. Here we use getObject().toString() instead of getString() to simplify the handling of null values, as discussed in the next section.

Table 9-1 shows the Java methods you can use to retrieve some common SQL data types from a database. No matter what the type, you can always use the getObject() method ofResultSet, in which case the type of the object returned is shown in the second column. You can also use a specific getXXX() method. These methods are shown in the third column, along with the Java data types they return. Remember that supported SQL data types vary from database to database.

Table 9-1. Methods to Retrieve Data from a ResultSet

9.2.6. Handling Null Fields

SQL Data Type	Java Type Returned by getObject()	Recommended Alternative to getObject()
`CHAR`	`String`	`String getString()`
`VARCHAR`	`String`	`String getString()`
`LONGVARCHAR`	`String`	`InputStream getAsciiStream() InputStream getUnicodeStream()`
`NUMERIC`	`java.math.BigDecimal`	`java.math.BigDecimal getBigDecimal()`
`DECIMAL`	`java.math.BigDecimal`	`java.math.BigDecimal getBigDecimal()`
`BIT`	`Boolean`	`boolean getBoolean()`
`TINYINT`	`Integer`	`byte getByte()`
`SMALLINT`	`Integer`	`short getShort()`
`INTEGER`	`Integer`	`int getInt()`
`BIGINT`	`Long`	`long getLong()`
`REAL`	`Float`	`float getFloat()`
`FLOAT`	`Double`	`double getDouble()`
`DOUBLE`	`Double`	`double getDouble()`
`BINARY`	`byte[]`	`byte[] getBytes()`
`VARBINARY`	`byte[]`	`byte[] getBytes()`
`LONGVARBINARY`	`byte[]`	`getBinaryStream()`
`DATE`	`java.sql.Date`	`java.sql.Date getDate()`
`TIME`	`Java.sql.Time`	`java.sql.Time getTime()`
`TIMESTAMP`	`Java.sql.Timestamp`	`java.sql.Timestamp getTimestamp()`

Handling nulldatabase values with JDBC can be a little tricky. (A database field can be set to null to indicate that no value is present, in much the same way that a Java object can be set to null.) A method that doesn't return an object, like getInt(), has no way of indicating whether a column is null or whether it contains actual information. (Some drivers return a string that contains the text "null" when getString() is called on a null column!) Any special value like -1, might be a legitimate value. Therefore, JDBC includes the wasNull() method in ResultSet, which returns true or false depending on whether the last column read was a true database null. This means that you must read data from the ResultSet into a variable, call wasNull(), and proceed accordingly. It's not pretty, but it works. Here's an example:

Another way to check for null values is to use the getObject() method. If a column is null, getObject() always returns null. Compare this to the getString() method that has been known, in some implementations, to return the empty string if a column is null. Using getObject()eliminates the need to call wasNull() and leads to simpler code.

9.2.7. Updating the Database

Most database-enabled web sites need to do more than just perform queries. When a client submits an order or provides some kind of information, the data needs to be entered into the database. When you know you're executing a SQL UPDATE, INSERT, or DELETE statement and you know you don't expect a ResultSet, you can use the executeUpdate() method of Statement. It returns a count that indicates the number of rows modified by the statement. It's used like this:

If you are executing SQL that may return either a ResultSet or a count (say, if you're handling user-submitted SQL or building generic data-handling classes), use the generic execute() method of Statement. It returns a boolean whose value is true if the SQL statement produced one or more ResultSet objects or false if it resulted in an update count:

The getResultSet()and getUpdateCount()methods of Statement provide access to the results of the execute() method. Example 9-3 demonstrates the use of these methods with a new version of HtmlResultSet, named HtmlSQLResult, that creates an HTML table from any kind of SQL statement.

Example 9-3. A class to generate an HTML table from a ResultSet using the ResultSetMetaData

import java.sql.*;

public class HtmlSQLResult {
  private String sql;
  private Connection con;

  public HtmlSQLResult(String sql, Connection con) {
    this.sql = sql;
    this.con = con;
  }

  public String toString() {  // can be called at most once
    StringBuffer out = new StringBuffer();

    // Uncomment the following line to display the SQL command at start of table
    // out.append("Results of SQL Statement: " + sql + "<P>\n");

    try {
      Statement stmt = con.createStatement();

      if (stmt.execute(sql)) { 
        // There's a ResultSet to be had
        ResultSet rs = stmt.getResultSet();    
        out.append("<TABLE>\n");

        ResultSetMetaData rsmd = rs.getMetaData();

        int numcols = rsmd.getColumnCount();
		
        // Title the table with the result set's column labels
        out.append("<TR>");
        for (int i = 1; i <= numcols; i++)
          out.append("<TH>" + rsmd.getColumnLabel(i));
        out.append("</TR>\n");

        while(rs.next()) {
          out.append("<TR>");  // start a new row
          for(int i = 1; i <= numcols; i++) {
            out.append("<TD>");  // start a new data element
            Object obj = rs.getObject(i);
            if (obj != null)
              out.append(obj.toString());
            else
              out.append("&nbsp;");
            }
          out.append("</TR>\n");
        }

        // End the table
        out.append("</TABLE>\n");
      }
      else {
        // There's a count to be had
        out.append("<B>Records Affected:</B> " + stmt.getUpdateCount()); 
      }
    }
    catch (SQLException e) {
      out.append("</TABLE><H1>ERROR:</H1> " + e.getMessage());
    }       
		
    return out.toString();
  }
}

This example uses execute() to execute whatever SQL statement is passed to the HtmlSQLResult constructor. Then, depending on the return value, it either calls getResultSet() or getUpdateCount(). Note that neither getResultSet() nor getUpdateCount() should be called more than once per query.

9.2.8. Using Prepared Statements

A PreparedStatement object islike a regular Statement object, in that it can be used to execute SQL statements. The important difference is that the SQL in a PreparedStatement is precompiled by the database for faster execution. Once a PreparedStatement has been compiled, it can still be customized by adjusting predefined parameters. Prepared statements are useful in applications that have to run the same general SQL command over and over.

Use the prepareStatement(String) method of Connection to create PreparedStatement objects. Use the ? character as a placeholder for values to be substituted later. For example:

The clearParameters() method removes any previously defined parameter values, while the setXXX() methods are used to assign actual values to each of the placeholder question marks. Once you have assigned values for all the parameters, call executeUpdate() to execute the PreparedStatement.

The PreparedStatement class has an important application in conjunction with servlets. When loading user-submitted text into the database using Statement objects and dynamic SQL, you must be careful not to accidentally introduce any SQL control characters (such as " or ') without escaping them in the manner required by your database. With a database like Oracle that surrounds strings with single quotes, an attempt to insert "John d'Artagan" into the database results in this corrupted SQL:

As you can see, the string terminates twice. One solution is to manually replace the single quote ' with two single quotes '', the Oracle escape sequence for one single quote. This solution, requires you to escape every character that your database treats as special--not an easy task and not consistent with writing platform-independent code. A far better solution is to use a PreparedStatement and pass the string using its setString() method, as shown below. The PreparedStatement automatically escapes the string as necessary for your database:


9.1. Relational Databases		9.3. Reusing Database Objects

9.2. The JDBC API