Chapter 6: Database Connectivity, Step by Step

This chapter explains how to make a successful connection to a database, how to send SQL statements to this database, and how to retrieve the results. The procedure is very straightforward; typically, every database application developed using Java and JDBC uses this procedure.

First Steps

JDBC is composed of a set of interfaces and classes that implement the functions needed to deal with a database management system (DBMS). I use these interfaces and classes in this chapter:

• java.sql.DriverManager

• java.sql.Driver

• java.sql.Connection

• java.sql.Statement

• java.sql.ResultSet

• java.sql.Date

• java.sql.Time

• java.sql.Timestamp

• java.sql.Types

• java.sql.DataTruncation

First, it is important to make sure that the JDBC Application Programming Interface (API) classes and specific drivers implementations are available on your system and are reachable by scanning the value of the CLASSPATH environment variable. Note that if you are using the JDBC-ODBC Bridge, which is included in the JDK, the CLASSPATH shouldn’t include additional driver classes. This environment variable should normally point to one or more directories where the Java classes are located. If necessary, update it or move the JDBC and drivers package subdirectories to an appropriate location on your hard drive. When done, the Java class loader will be able to find these classes on the file system.

XREF 

A list of JDBC drivers is provided in http://java.sun.com/products/jdbc.

Typically, you will see a subtree resembling Listing 6-1 when scanning your CLASSPATH environment variable. (jdbc/odbc/... was used for Listing 6-1; you may choose not to install it.)

Listing 6-1: A Typical Package Tree

classes/ +--      java/    |         +--    .../ |         | |         +--    sql/ |                         CallableStatement.class |                         Connection.class |                         DatabaseMetaData.class |                         DataTruncation.class |                         Date.class |                         Driver.class |                         DriverInfo.class |                         DriverManager.class |                         DriverPropertyInfo.class |                         PreparedStatement.class |                         ResultSet.class |                         ResultSetMetaData.class |                         SQLException.class |                         SQLWarning.class |                         Statement.class |                         Time.class |                         Timestamp.class |                         Types.class +--      jdbc/              +--    odbc/                          JdbcOdbc.class                          JdbcOdbcBoundCol.class                          JdbcOdbcBoundParam.class                          ...                          ...

To make the JDBC classes available to the current class using the abbreviated name, you use the import statement in source programs. The code is more readable if you don’t use the fully qualified names. However, as explained later in this chapter, in the section "Specifying a JDBC driver," there is no need to import the packages of any specific JDBC driver implementation classes. So you should include the following line at the top of the Java program, applet, or servlet:

import java.sql.*;

The usual command line to compile Java source code will be used for all examples in this book:

% javac example.java

As usual, you can use the classpath parameter to specify additional Java classes that aren’t reachable through the CLASSPATH environment variable. In case the JDBC driver that you intend to use isn’t pure Java, it is mandatory to have the related native libraries available in either the LD_LIBRARY_PATH in UNIX (Solaris) or the PATH in Microsoft Windows. In UNIX, shared libraries end with .so, whereas in Windows, DLLs end with the well-known .dll. I advise you to look for those files when using an impure Java JDBC driver.