9.2 The JDBC API

Java Servlet Programming, 2nd Edition > 9. Database Connectivity > 9.2 The JDBC API

 
< BACKCONTINUE >

9.2 The JDBC API

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. This chapter discusses primarily JDBC Version 1.2, the most widely supported version of JDBC. A small amount of coverage at the end of the chapter is given to JDBC Version 2.0.

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), or JDBC Database Access with Java, by Graham Hamilton, Rick Cattell, and Maydene Fisher (Addison-Wesley). A quick overview is also presented in Java Enterprise in a Nutshell by David Flanagan et al. (O'Reilly). The official JDBC specification is 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 not all 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:

Type 1: JDBC-ODBC Bridge Driver

Type 1 drivers use a bridge technology to connect a Java client to an ODBC database service. Sun's JDBC-ODBC bridge is the most common Type 1 driver. These drivers are implemented using native code.

Type 2: Native-API Partly Java Driver

Type 2 drivers wrap a thin layer of Java around database-specific native code libraries. For Oracle databases, the native code libraries might be based on the OCI (Oracle Call Interface) libraries, which were originally designed for C/C++ programmers. Because Type 2 drivers are implemented using native code, in some cases they have better performance than their all-Java counterparts. They add an element of risk, however, because a defect in a driver's native code section can crash the entire server.

Type 3: Net-Protocol All-Java Driver

Type 3 drivers communicate via a generic network protocol to a piece of custom middleware. The middleware component might use any type of driver to provide the actual database access. These drivers are all Java, which makes them useful for applet deployment and safe for servlet deployment.

Type 4: Native-Protocol All-Java Driver

Type 4 drivers are the most direct of the lot. Written entirely in Java, Type 4 drivers understand database-specific networking protocols and can access the database directly without any additional software.

A list of currently available JDBC drivers can be found at http://industry.java.sun.com/products/jdbc/drivers.

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:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

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:

Connection con =   DriverManager.getConnection("jdbc:odbc:somedb", "user", "passwd");

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:@dbhost:port:sid ; the JDBC-ODBC bridge uses jdbc:odbc:datasourcename;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:

Connection con = null; try {   // Load (and therefore register) the JDBC-ODBC Bridge   // Might throw a ClassNotFoundException   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   // Get a connection to the database   // Might throw an SQLException   con = DriverManager.getConnection("jdbc:odbc:somedb", "user", "passwd");   // The rest of the code goes here. } catch (ClassNotFoundException e) {   // Handle an error loading the driver } catch (SQLException e) {   // Handle an error getting the connection } finally {   // Close the Connection to release the database resources immediately.   try {     if (con != null) con.close();   }   catch (SQLException ignored) { } }

There are actually three forms of getConnection( ) available. In addition to the preceding one shown, there's a simpler one that takes just a URL: getConnection(String url). This method can be used when there are no login requirements or when the login information has to be placed in the URL. There's also one form that takes a URL and Properties object: getConnection(String url, Properties props). This method provides the most flexibility. The Properties object (a Hashtable with all keys and values of type String) holds the standard user and password properties and in addition may hold any additional properties that might need to be passed to the underlying database driver. For example, some drivers respect the cacherows property specifying how many rows to cache at a time. Using this method makes it easy to establish a database connection based on an external .properties file.

For example, the driver, URL, and credentials to use can be specified in the following sql.properties file. This name=value format is standard for Java properties files:

connection.driver=sun.jdbc.odbc.JdbcOdbcDriver connection.url=jdbc:odbc:somedb user=user password=passwd

The code shown in Example 9-1 establishes a database connection using the values stored within this sql.properties file. Note that the user and password properties are standard, the connection.driver and connection.url properties are special names used by the code, and any additional property values will just be passed on to the underlying driver as well.

Example 9-1. Using a Properties File to Open a Database Connection
// Get the properties for the database connection Properties props = new Properties(); InputStream in = new FileInputStream("sql.properties"); props.load(in); in.close();  // should really be in a finally block // Load the driver Class.forName(props.getProperty("connection.driver")); // Get the connection con = DriverManager.getConnection(         props.getProperty("connection.url"), props);

First the Properties object is constructed and filled with the values read from the sql.properties file. Then the various properties are used to establish the database connection. Following this approach allows all database connection information to be changed without so much as a recompile of the Java code!

9.2.3 Getting a Connection from a Servlet

A servlet can use the same approach to load database connection information from a properties file stored under the web application's WEB-INF directory. The getResourceAsStream( ) method can retrieve the file's contents:

Properties props = new Properties(); InputStream in = getServletContext().getResourceAsStream(                                      "/WEB-INF/sql.properties"); props.load(in); in.close();

However, because servlets are often deployed using graphical deployment tools, we can go one step further and use the deployment tools to configure the database. One way to accomplish this is to place database connections inside a JNDI server where a servlet can locate the connection by name. This approach is generally employed by servlets designed to run inside J2EE servers. Chapter 12 discusses this in more detail. Another approach that takes advantage of graphical deployment tools but doesn't require a JNDI server is to use context init parameters to hold configuration information. These init parameters can be configured using graphical deployment tools, then saved in the web.xml file for easy portable deployment. (For more information on context init parameters, see Chapter 4.)

The ContextProperties class shown in Example 9-2 makes the context init parameters available as a Properties object. This allows all the init parameter name/value pairs to be passed to the DriverManager.getConnection( ) method.

Example 9-2. The ContextProperties Class
import java.util.*; import javax.servlet.*; import javax.servlet.http.*; public class ContextProperties extends Properties {   public ContextProperties(ServletContext context) {     Enumeration props = context.getInitParameterNames();     while (props.hasMoreElements()) {       String name = (String) props.nextElement();       String value = (String) context.getInitParameter(name);       put(name, value);     }   } }

A servlet uses this class instead of loading data from sql.properties, as shown here:

// Get the context init params as a Properties object ContextProperties props = new ContextProperties(getServletContext()); // Load the driver Class.forName(props.getProperty("connection.driver")); // etc...

Just set the context init parameters to the appropriate values for your database, either manually in the web.xml file or using a deployment tool, and everything's ready to go.

9.2.4 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 the java.sql.Statement class. Statement objects are never instantiated directly; instead, a program calls the createStatement( ) method of Connection to obtain a new Statement object:

Statement stmt = con.createStatement();

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:

ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMERS");

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:

while(rs.next()) {   String event = rs.getString("event");   Object count = (Integer) rs.getObject("count"); }

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-3 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-3. 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.5 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 cannot 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:

catch (SQLException e) {   out.println(e.getMessage());   while((e = e.getNextException()) != null) {     out.println(e.getMessage());   } }

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.6 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 be 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-4. Many Java HTML content creation tools have a similar capability, as discussed in Chapter 14 through Chapter 18.

Example 9-4. 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 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 datatypes from a database. No matter what the type, you can always use the getObject( ) method of ResultSet, 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 datatypes they return. Remember that supported SQL datatypes vary from database to database.

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

SQL Data Type

Java Type Returned by getObject()

Recommended Alternative to getObject()

BIGINT

Long

long getLong()

BINARY

byte[]

byte[] getBytes()

BIT

Boolean

boolean getBoolean()

CHAR

String

String getString()

DATE

java.sql.Date

java.sql.Date getDate()

DECIMAL

java.math.BigDecimal

java.math.BigDecimal getBigDecimal( )

DOUBLE

Double

double getDouble()

FLOAT

Double

double getDouble()

INTEGER

Integer

int getInt()

LONGVARBINARY

byte[]

InputStream getBinaryStream()

LONGVARCHAR

String

InputStream getAsciiStream() InputStream getUnicodeStream( )

NUMERIC

java.math.BigDecimal

java.math.BigDecimal getBigDecimal( )

REAL

Float

float getFloat()

SMALLINT

Integer

short getShort()

TIME

Java.sql.Time

java.sql.Time getTime()

TIMESTAMP

Java.sql.Timestamp

java.sql.Timestamp getTimestamp( )

TINYINT

Integer

byte getByte()

VARBINARY

byte[]

byte[] getBytes()

VARCHAR

String

String getString( )

9.2.7 Handling Null Fields

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. Handling null database values with JDBC can be a little tricky. 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. Any special value, like 0, 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:

int age = rs.getInt("age"); if (!rs.wasNull())   out.println("Age: " + age);

Another way to check for null values is to use the getObject( ) method. If a column is null, getObject( ) always returns null. Using getObject( ) can eliminate the need to call wasNull( ) and result in simpler code, but the objects returned aren't easy-to-use primitives.

9.2.8 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:

int count =   stmt.executeUpdate("DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 5");

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:

boolean b = stmt.execute(sql);

The getResultSet( ) and getUpdateCount( ) methods of Statement provide access to the results of the execute( ) method. Example 9-5 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-5. A Class to Generate an HTML Table from a ResultSet Using 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.9 Using Prepared Statements

A PreparedStatement object is like 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:

PreparedStatement pstmt = con.prepareStatement(   "INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, TOTAL) VALUES (?,?,?)"); // Other code pstmt.clearParameters();   // clear any previous parameter values pstmt.setInt(1, 2);        // set ORDER_ID pstmt.setInt(2, 4);        // set CUSTOMER_ID pstmt.setDouble(3, 53.43); // set TOTAL pstmt.executeUpdate();     // execute the stored SQL

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:

INSERT INTO MUSKETEERS (NAME) VALUES ('John d'Artagan')

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 easy 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 next. The PreparedStatement automatically escapes the string as necessary for your database:

PreparedStatement pstmt = con.prepareStatement(   "INSERT INTO MUSKETEERS (NAME) VALUES (?)"); pstmt.setString(1, "John d'Artagan"); pstmt.executeUpdate();


Last updated on 3/20/2003
Java Servlet Programming, 2nd Edition, © 2001 O'Reilly

< BACKCONTINUE >


Java servlet programming
Java Servlet Programming (Java Series)
ISBN: 0596000405
EAN: 2147483647
Year: 2000
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net