17.1 Using JDBC in General

In this section we present the seven standard steps for querying databases. In Section 17.2 we give two simple examples (a command-line program and a servlet) illustrating these steps to query a Microsoft Access database.

Following is a summary; details are given in the rest of the section.

1. Load the JDBC driver. To load a driver, you specify the classname of the database driver in the Class.forName method. By doing so, you automatically create a driver instance and register it with the JDBC driver manager.

2. Define the connection URL. In JDBC, a connection URL specifies the server host, port, and database name with which to establish a connection.

3. Establish the connection. With the connection URL, username, and password, a network connection to the database can be established. Once the connection is established, database queries can be performed until the connection is closed.

4. Create a Statement object. Creating a Statement object enables you to send queries and commands to the database.

5. Execute a query or update. Given a Statement object, you can send SQL statements to the database by using the execute , executeQuery , executeUpdate , or executeBatch methods .

6. Process the results. When a database query is executed, a ResultSet is returned. The ResultSet represents a set of rows and columns that you can process by calls to next and various get Xxx methods.

7. Close the connection. When you are finished performing queries and processing results, you should close the connection, releasing resources to the database.

Load the JDBC Driver

The driver is the piece of software that knows how to talk to the actual database server. To load the driver, you just load the appropriate class; a static block in the driver class itself automatically makes a driver instance and registers it with the JDBC driver manager. To make your code as flexible as possible, avoid hard-coding the reference to the classname. In Section 17.3 (Simplifying Database Access with JDBC Utilities) we present a utility class to load drivers from a Properties file so that the classname is not hard-coded in the program.

These requirements bring up two interesting questions. First, how do you load a class without making an instance of it? Second, how can you refer to a class whose name isn't known when the code is compiled? The answer to both questions is to use Class.forName . This method takes a string representing a fully qualified classname (i.e., one that includes package names ) and loads the corresponding class. This call could throw a ClassNotFoundException , so it should be inside a try / catch block as shown below.

 try {   Class.forName("connect.microsoft.MicrosoftDriver");   Class.forName("oracle.jdbc.driver.OracleDriver");   Class.forName("com.sybase.jdbc.SybDriver"); } catch(ClassNotFoundException cnfe) {   System.err.println("Error loading driver: " + cnfe); } 

One of the beauties of the JDBC approach is that the database server requires no changes whatsoever. Instead, the JDBC driver (which is on the client) translates calls written in the Java programming language into the native format required by the server. This approach means that you have to obtain a JDBC driver specific to the database you are using and that you will need to check the vendor's documentation for the fully qualified class name to use.

In principle, you can use Class.forName for any class in your CLASSPATH . In practice, however, most JDBC driver vendors distribute their drivers inside JAR files. So, during development be sure to include the path to the driver JAR file in your CLASSPATH setting. For deployment on a Web server, put the JAR file in the WEB-INF/lib directory of your Web application (see Chapter 2, "Server Setup and Configuration"). Check with your Web server administrator, though. Often, if multiple Web applications are using the same database drivers, the administrator will place the JAR file in a common directory used by the server. For example, in Apache Tomcat, JAR files common to multiple applications can be placed in install_dir /common/lib .

Core Note

You can place your JDBC driver file (JAR file) in the WEB-INF/lib directory for deployment of your application. However, the administrator may choose to move the JAR file to a common library directory on the server.

Figure 17-1 illustrates two common JDBC driver implementations . The first approach is a JDBC-ODBC bridge, and the second approach is a pure Java implementation. A driver that uses the JDBC-ODBC bridge approach is known as a Type I driver. Since many databases support Open DataBase Connectivity (ODBC) access, the JDK includes a JDBC-ODBC bridge to connect to databases. However, you should use the vendor's pure Java driver, if available, because the JDBC-ODBC driver implementation is slower than a pure Java implementation. Pure Java drivers are known as Type IV. The JDBC specification defines two other driver types, Type II and Type III; however, they are less common. For additional details on driver types, see http://java.sun.com/products/jdbc/driverdesc.html.

Figure 17-1. Two common JDBC driver implementations. JDK 1.4 includes a JDBC-ODBC bridge; however, a pure JDBC driver (provided by the vendor) yields better performance.

In the initial examples in this chapter, we use the JDBC-ODBC bridge, included with JDK 1.4, to connect to a Microsoft Access database. In later examples we use pure Java drivers to connect to MySQL and Oracle9i databases.

In Section 18.1 (Configuring Microsoft Access for Use with JDBC), we provide driver information for Microsoft Access. Driver information for MySQL is provided in Section 18.2 (Installing and Configuring MySQL), and driver information for Oracle is provided in Section 18.3 (Installing and Configuring Oracle9i Database). Most other database vendors supply free JDBC drivers for their databases. For an up-to-date list of these and third-party drivers, see http://industry.java.sun.com/products/jdbc/drivers/.

Define the Connection URL

Once you have loaded the JDBC driver, you must specify the location of the database server. URLs referring to databases use the jdbc: protocol and embed the server host, port, and database name (or reference) within the URL. The exact format is defined in the documentation that comes with the particular driver, but here are a few representative examples.

 String host = "dbhost.yourcompany.com"; String dbName = "someName"; int port = 1234; String oracleURL = "jdbc:oracle:thin:@" + host +                    ":" + port + ":" + dbName; String sybaseURL = "jdbc:sybase:Tds:" + host +                    ":" + port + ":" + "?SERVICENAME=" + dbName; String msAccessURL = "jdbc:odbc:" + dbName; 

Establish the Connection

To make the actual network connection, pass the URL, database username, and database password to the getConnection method of the DriverManager class, as illustrated in the following example. Note that getConnection throws an SQLException , so you need to use a try / catch block. We're omitting this block from the following example since the methods in the following steps throw the same exception, and thus you typically use a single try / catch block for all of them.

 String username = "jay_debesee"; String password = "secret"; Connection connection =   DriverManager.getConnection(oracleURL, username, password); 

The Connection class includes other useful methods, which we briefly describe below. The first three methods are covered in detail in Sections 17.417.6.

• prepareStatement . Creates precompiled queries for submission to the database. See Section 17.4 (Using Prepared Statements) for details.

• prepareCall . Accesses stored procedures in the database. For details, see Section 17.5 (Creating Callable Statements).

• rollback / commit . Controls transaction management. See Section 17.6 (Using Database Transactions) for details.

• close . Terminates the open connection.

• isClosed . Determines whether the connection timed out or was explicitly closed.

An optional part of establishing the connection is to look up information about the database with the getMetaData method. This method returns a DatabaseMetaData object that has methods with which you can discover the name and version of the database itself ( getDatabaseProductName , getDatabaseProductVersion ) or of the JDBC driver ( getDriverName , getDriverVersion ). Here is an example.

 DatabaseMetaData dbMetaData = connection.getMetaData(); String productName =   dbMetaData.getDatabaseProductName(); System.out.println("Database: " + productName); String productVersion =   dbMetaData.getDatabaseProductVersion(); System.out.println("Version: " + productVersion); 

Create a Statement Object

A Statement object is used to send queries and commands to the database. It is created from the Connection using createStatement as follows .

 Statement statement = connection.createStatement(); 

Most, but not all, database drivers permit multiple concurrent Statement objects to be open on the same connection.

Execute a Query or Update

Once you have a Statement object, you can use it to send SQL queries by using the executeQuery method, which returns an object of type ResultSet . Here is an example.

 String query = "SELECT col1, col2, col3 FROM sometable"; ResultSet resultSet = statement.executeQuery(query); 

The following list summarizes commonly used methods in the Statement class.

• executeQuery . Executes an SQL query and returns the data in a ResultSet . The ResultSet may be empty, but never null .

• executeUpdate . Used for UPDATE , INSERT , or DELETE commands. Returns the number of rows affected, which could be zero. Also provides support for Data Definition Language (DDL) commands, for example, CREATE TABLE , DROP TABLE , and ALTER TABLE .

• executeBatch . Executes a group of commands as a unit, returning an array with the update counts for each command. Use addBatch to add a command to the batch group. Note that vendors are not required to implement this method in their driver to be JDBC compliant.

• setQueryTimeout . Specifies the amount of time a driver waits for the result before throwing an SQLException .

• getMaxRows / setMaxRows . Determines the number of rows a ResultSet may contain. Excess rows are silently dropped. The default is zero for no limit.

In addition to using the methods described here to send arbitrary commands, you can use a Statement object to create parameterized queries by which values are supplied to a precompiled fixed-format query. See Section 17.4 (Using Prepared Statements) for details.

Process the Results

The simplest way to handle the results is to use the next method of ResultSet to move through the table a row at a time. Within a row, ResultSet provides various get Xxx methods that take a column name or column index as an argument and return the result in a variety of different Java types. For instance, use getInt if the value should be an integer, getString for a String , and so on for most other data types. If you just want to display the results, you can use getString for most of the column types. However, if you use the version of get Xxx that takes a column index (rather than a column name), note that columns are indexed starting at 1 (following the SQL convention), not at 0 as with arrays, vectors, and most other data structures in the Java programming language.

Core Warning

The first column in a ResultSet row has index 1, not 0.

Here is an example that prints the values of the first two columns and the first name and last name, for all rows of a ResultSet .

 while(resultSet.next()) {   System.out.println(resultSet.getString(1) + " " +                      resultSet.getString(2) + " " +                      resultSet.getString("firstname") + "  "                      resultSet.getString("lastname")); } 

We suggest that when you access the columns of a ResultSet , you use the column name instead of the column index. That way, if the column structure of the table changes, the code interacting with the ResultSet will be less likely to fail.

Core Approach

Use the column name instead of the column index when accessing data in a ResultSet .

In JDBC 1.0, you can only move forward in the ResultSet ; however, in JDBC 2.0, you can move forward ( next ) and backward ( previous ) in the ResultSet as well as move to a particular row ( relative , absolute ). In Volume 2 of this book, we present several custom tags that illustrate the JDBC 2.0 methods available in a ResultSet .

Be aware that neither JDBC 1.0 nor JDBC 2.0 provides a direct mechanism to determine the JDBC version of the driver. In JDBC 3.0, this problem is resolved by the addition of getJDBCMajorVersion and getJDBCMinorVersion methods to the DatabaseMetaData class. If the JDBC version is not clear from the vendor's documentation, you can write a short program to obtain a ResultSet and attempt a previous operation on the ResultSet . Since resultSet.previous is only available in JDBC 2.0 and later, a JDBC 1.0 driver would throw an exception at this point. See Section 18.4 (Testing Your Database Through a JDBC Connection) for an example program that performs a nonrigorous test to determine the JDBC version of your database driver.

The following list summarizes useful ResultSet methods.

• next / previous . Moves the cursor to the next (any JDBC version) or previous (JDBC version 2.0 or later) row in the ResultSet , respectively.

• relative / absolute . The relative method moves the cursor a relative number of rows, either positive (up) or negative (down). The absolute method moves the cursor to the given row number. If the absolute value is negative, the cursor is positioned relative to the end of the ResultSet (JDBC 2.0).

• get Xxx . Returns the value from the column specified by the column name or column index as an Xxx Java type (see java.sql.Types ). Can return 0 or null if the value is an SQL NULL .

• wasNull . Checks whether the last get Xxx read was an SQL NULL . This check is important if the column type is a primitive ( int , float , etc.) and the value in the database is 0. A zero value would be indistinguishable from a database value of NULL , which is also returned as a 0. If the column type is an object ( String , Date , etc.), you can simply compare the return value to null .

• findColumn . Returns the index in the ResultSet corresponding to the specified column name.

• getRow . Returns the current row number, with the first row starting at 1 (JDBC 2.0).

• getMetaData . Returns a ResultSetMetaData object describing the ResultSet . ResultSetMetaData gives the number of columns and the column names.

The getMetaData method is particularly useful. Given only a ResultSet , you have to know the name, number, and type of the columns to be able to process the table properly. For most fixed-format queries, this is a reasonable expectation. For ad hoc queries, however, it is useful to be able to dynamically discover high-level information about the result. That is the role of the ResultSetMetaData class: it lets you determine the number, names, and types of the columns in the ResultSet .

Useful ResultSetMetaData methods are described below.

• getColumnCount . Returns the number of columns in the ResultSet .

• getColumnName . Returns the database name of a column (indexed starting at 1).

• getColumnType . Returns the SQL type, to compare with entries in java.sql.Types .

• isReadOnly . Indicates whether the entry is a read-only value.

• isSearchable . Indicates whether the column can be used in a WHERE clause.

• isNullable . Indicates whether storing NULL is legal for the column.

ResultSetMetaData does not include information about the number of rows; however, if your driver complies with JDBC 2.0, you can call last on the ResultSet to move the cursor to the last row and then call getRow to retrieve the current row number. In JDBC 1.0, the only way to determine the number of rows is to repeatedly call next on the ResultSet until it returns false .

Core Note

ResultSet and ResultSetMetaData do not directly provide a method to return the number of rows returned from a query. However, in JDBC 2.0, you can position the cursor at the last row in the ResultSet by calling last , and then obtain the current row number by calling getRow .

Close the Connection

To close the connection explicitly, you would do:

 connection.close(); 

Closing the connection also closes the corresponding Statement and ResultSet objects.

You should postpone closing the connection if you expect to perform additional database operations, since the overhead of opening a connection is usually large. In fact, reusing existing connections is such an important optimization that the JDBC 2.0 API defines a ConnectionPoolDataSource interface for obtaining pooled connections. Pooled connections are discussed in Volume 2 of this book.