Basic JDBC Programming Concepts

Programming with the JDBC classes is, by design, not very different from programming with the usual Java platform classes: You build objects from the JDBC core classes, extending them by inheritance if need be. This section takes you through the details.

NOTE

The classes that you use for JDBC programming are contained in the java.sql and javax.sql packages.

Database URLs

When connecting to a database, you must specify the data source and you may need to specify additional parameters. For example, network protocol drivers may need a port, and ODBC drivers may need various attributes.

As you might expect, JDBC uses a syntax similar to that of ordinary URLs to describe data sources. Here are examples of the syntax:

 jdbc:mckoi://localhost/ jdbc:postgresql:COREJAVA 

These JDBC URLs specify a local McKoi database and a PostgreSQL database named COREJAVA. The general syntax is

jdbc:subprotocol:other stuff

where a subprotocol selects the specific driver for connecting to the database.

The format for the other stuff parameter depends on the subprotocol used. Look up your vendor's documentation for the specific format.

Making the Connection

Find out the names of the JDBC driver classes used by your vendor. Typical driver names are

 org.postgresql.Driver com.mckoi.JDBCDriver 

Next, find the library in which the driver is located, such as pg74jdbc3.jar or mkjdbc.jar. Use one of the following three mechanisms:

• Launch your database programs with the -classpath command-line argument.

• Modify the CLASSPATH environment variable.

• Copy the database library into the jre/lib/ext directory.

The DriverManager class selects database drivers and creates a new database connection. However, before the driver manager can activate a driver, the driver must be registered.

The jdbc.drivers property contains a list of class names for the drivers that the driver manager will register at startup. Two methods can set that property.

You can specify the property with a command-line argument, such as

 java -Djdbc.drivers=org.postgresql.Driver MyProg 

Or your application can set the system property with a call such as

 System.setProperty("jdbc.drivers", "org.postgresql.Driver"); 

You can also supply multiple drivers; separate them with colons, such as

 org.postgresql.Driver:com.mckoi.JDBCDriver 

NOTE

Alternatively, you can manually register a driver by loading its class. For example,

 Class.forName("org.postgresql.Driver"); // force registration of driver 

You may need to use this approach if the driver manager cannot load the driver. This can happen because of limitations in a particular driver or because your program executes inside a container such as a servlet engine.

After registering drivers, you open a database connection with code that is similar to the following example:

 String url = "jdbc:postgresql:COREJAVA"; String username = "dbuser"; String password = "secret"; Connection conn = DriverManager.getConnection(url, username, password); 

The driver manager iterates through the available drivers currently registered with it to find a driver that can use the subprotocol specified in the database URL.

For our example programs, we find it convenient to use a properties file to specify the URL, user name, and password in addition to the database driver. A typical properties file has the following contents:

 jdbc.drivers=org.postgresql.Driver jdbc.url=jdbc:postgresql:COREJAVA jdbc.username=dbuser jdbc.password=secret 

Here is the code for reading a properties file and opening the database connection.

 Properties props = new Properties(); FileInputStream in = new FileInputStream("database.properties"); props.load(in); in.close(); String drivers = props.getProperty("jdbc.drivers"); if (drivers != null) System.setProperty("jdbc.drivers", drivers); String url = props.getProperty("jdbc.url"); String username = props.getProperty("jdbc.username"); String password = props.getProperty("jdbc.password"); return DriverManager.getConnection(url, username, password); 

The getConnection method returns a Connection object. In the following sections, you will see how to use the Connection object to execute SQL statements.

TIP

A good way to debug JDBC-related problems is to enable JDBC tracing. Call the DriverManager.setLogWriter method to send trace messages to a PrintWriter. The trace output contains a detailed listing of the JDBC activity.

Testing Your Database Installation

Setting up JDBC for the first time can be a bit tricky. You need several pieces of vendor-specific information, and the slightest configuration error can lead to very bewildering error messages. You should first test your database setup without JDBC. Follow these instructions.

Step 1.	Start the database. With McKoi, run java -jar mckoidb.jar from the installation directory. With PostgreSQL, run postmaster -i -D /usr/share/pgsql/data
Step 2.	Set up a user and a database. With McKoi, call java -jar mckoidb.jar -create dbuser secret For PostgreSQL, issue the commands createuser -d -U dbuser createdb -U dbuser COREJAVA
Step 3.	Start the SQL interpreter for your database. With McKoi, call java -classpath mckoidb.jar com.mckoi.tools.JDBCQueryTool With PostgreSQL, call psql COREJAVA
Step 4.	Enter the following SQL commands: CREATE TABLE Greetings (Message CHAR(20)) INSERT INTO Greetings VALUES ('Hello, World!') SELECT * FROM Greetings At this point, you should see a display of the "Hello, World!" entry.
Step 5.	Clean up: DROP TABLE Greetings

Once you know that your database installation is working and that you can log on to the database, you need to gather five pieces of information:

• The database user name and password

• The name of the database to use (such as COREJAVA)

• The JDBC URL format

• The JDBC driver name

• The location of the library files with the driver code

The first two depend on your database setup. The other three are supplied in the JDBC-specific documentation from your database vendor.

For McKoi, typical values are

• Database user name = dbuser, password = secret

• Database name = (none)

• JDBC URL format = jdbc:mckoi://localhost/

• JDBC driver = com.mckoi.JDBCDriver

• Library file = mkjdbc.jar

For PostgreSQL, you may have

• Database user name = dbuser, password = (none)

• Database name = COREJAVA

• JDBC URL format = jdbc:postgresql:COREJAVA

• JDBC driver = org.postgresql.Driver

• Library file = pg74jdbc3.jar

Example 4-1 is a small test program that you can use to test your JDBC setup. Prepare the database.properties file with the information that you collected. Then start the test program with the driver library on the class path, such as

java -classpath .:driverPath TestDB

(Remember to use a semicolon instead of a colon as the path separator on Windows.)

This program executes the same SQL instructions as the manual test. If you get an SQL error message, you need to keep working on your setup. It is extremely common to make one or more small errors with capitalization, path names, the JDBC URL format, or the database configuration. Once the test program displays "Hello, World!", everything is fine and you can move on to the next section.

Example 4-1. TestDB.java

  1. import java.sql.*;  2. import java.io.*;  3. import java.util.*;  4.  5. /**  6.    This program tests that the database and the JDBC  7.    driver are correctly configured.  8. */  9. class TestDB 10. { 11.    public static void main (String args[]) 12.    { 13.       try 14.       { 15.          runTest(); 16.       } 17.       catch (SQLException ex) 18.       { 19.          while (ex != null) 20.          { 21.             ex.printStackTrace(); 22.             ex = ex.getNextException(); 23.          } 24.       } 25.       catch (IOException ex) 26.       { 27.          ex.printStackTrace(); 28.       } 29.    } 30. 31.    /** 32.       Runs a test by creating a table, adding a value, showing the table contents, and 33.       removing the table. 34.    */ 35.    public static void runTest() 36.       throws SQLException, IOException 37.    { 38.       Connection conn = getConnection(); 39.       try 40.       { 41.          Statement stat = conn.createStatement(); 42. 43.          stat.execute("CREATE TABLE Greetings (Message CHAR(20))"); 44.          stat.execute("INSERT INTO Greetings VALUES ('Hello, World!')"); 45. 46.          ResultSet result = stat.executeQuery("SELECT * FROM Greetings"); 47.          result.next(); 48.          System.out.println(result.getString(1)); 49.          stat.execute("DROP TABLE Greetings"); 50.       } 51.       finally 52.       { 53.          conn.close(); 54.       } 55.    } 56. 57.    /** 58.       Gets a connection from the properties specified 59.       in the file database.properties 60.       @return the database connection 61.    */ 62.    public static Connection getConnection() 63.       throws SQLException, IOException 64.    { 65.       Properties props = new Properties(); 66.       FileInputStream in = new FileInputStream("database.properties"); 67.       props.load(in); 68.       in.close(); 69. 70.       String drivers = props.getProperty("jdbc.drivers"); 71.       if (drivers != null) 72.          System.setProperty("jdbc.drivers", drivers); 73.       String url = props.getProperty("jdbc.url"); 74.       String username = props.getProperty("jdbc.username"); 75.       String password = props.getProperty("jdbc.password"); 76. 77.       return DriverManager.getConnection(url, username, password); 78.    } 79. } 

Executing SQL Commands

To execute a SQL command, you first create a Statement object. To create statement objects, use the Connection object that you obtained from the call to DriverManager.getConnection.

 Statement stat = conn.createStatement(); 

Next, place the statement that you want to execute into a string, for example,

 String command = "UPDATE Books"    + " SET Price = Price - 5.00"    + " WHERE Title NOT LIKE '%Introduction%'"; 

Then call the executeUpdate method of the Statement class:

 stat.executeUpdate(command); 

The executeUpdate method returns a count of the rows that were affected by the SQL command. For example, the call to executeUpdate in the preceding example returns the number of book records whose price was lowered by $5.00.

The executeUpdate method can execute actions such as INSERT, UPDATE, and DELETE as well as data definition commands such as CREATE TABLE and DROP TABLE. However, you need to use the executeQuery method to execute SELECT queries. There is also a catch-all execute statement to execute arbitrary SQL statements. It's commonly used only for queries that a user supplies interactively.

When you execute a query, you are interested in the result. The executeQuery object returns an object of type ResultSet that you use to walk through the result one row at a time.

 ResultSet rs = stat.executeQuery("SELECT * FROM Books") 

The basic loop for analyzing a result set looks like this:

while (rs.next())
{
   look at a row of the result set
}

CAUTION

The iteration protocol of the ResultSet class is subtly different from the protocol of the Iterator interface that we discussed in Chapter 2. Here, the iterator is initialized to a position before the first row. You must call the next method once to move the iterator to the first row.

NOTE

The order of the rows in a result set is completely arbitrary. Unless you specifically ordered the result with an ORDER BY clause, you should not attach any significance to the row order.

When inspecting an individual row, you will want to know the contents of the fields. A large number of accessor methods give you this information.

 String isbn = rs.getString(1); double price = rs.getDouble("Price"); 

There are accessors for various types, such as getString and getdouble. Each accessor has two forms, one that takes a numeric argument and one that takes a string argument. When you supply a numeric argument, you refer to the column with that number. For example, rs.getString(1) returns the value of the first column in the current row.

CAUTION

Unlike array indexes, database column numbers start at 1.

When you supply a string argument, you refer to the column in the result set with that name. For example, rs.getDouble("Price") returns the value of the column with name Price. Using the numeric argument is a bit more efficient, but the string arguments make the code easier to read and maintain.

Each get method makes reasonable type conversions when the type of the method doesn't match the type of the column. For example, the call rs.getString("Price") converts the floating-point value of the Price column to a string.

NOTE

SQL data types and Java data types are not exactly the same. See Table 4-6 for a listing of the basic SQL data types and their equivalents in the Java programming language.

Table 4-6. SQL Data Types and Their Corresponding Java Types

SQL Data Type	Java Data Type
INTEGER or INT	int
SMALLINT	short
NUMERIC(m,n), DECIMAL(m,n) or DEC(m,n)	java.math.BigDecimal
FLOAT(n)	double
REAL	float
DOUBLE	double
CHARACTER(n) or CHAR(n)	String
VARCHAR(n)	String
BOOLEAN	boolean
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp
BLOB	java.sql.Blob
CLOB	java.sql.Clob
ARRAY	java.sql.Array

Advanced SQL Types

In addition to numbers, strings, and dates, many databases can store large objects such as images or other data. In SQL, binary large objects are called BLOBs, and character large objects are called CLOBs. The getBlob and getClob methods return objects of type java.sql.Blob and java.sql.Clob. These classes have methods to fetch the bytes or characters in the large objects.

A SQL ARRAY is a sequence of values. For example, in a Student table, you can have a Scores column that is an ARRAY OF INTEGER. The getArray method returns an object of type java.sql.Array (which is different from the java.lang.reflect.Array class that we discussed in Volume 1). The java.sql.Array interface has methods to fetch the array values.

When you get a BLOB or an array from a database, the actual contents are fetched from the database only when you request individual values. This is a useful performance enhancement, since the data can be quite voluminous.

Some databases can store user-defined structured types. JDBC supports a mechanism for automatically mapping structured SQL types to Java objects. We do not discuss BLOBs, arrays, and user-defined types any further. You can find more information on these topics in the book JDBC(TM) API Tutorial and Reference: Universal Data Access for the Java 2 Platform (2nd Edition) by Seth White, Maydene Fisher, Rick Cattell, Graham Hamilton, and Mark Hapner [Addison-Wesley 1999].

 java.sql.DriverManager 1.1 

• static Connection getConnection(String url, String user, String password)

  establishes a connection to the given database and returns a Connection object.

 java.sql.Connection 1.1 

• Statement createStatement()

  creates a Statement object that can be used to execute SQL queries and updates without parameters.

• void close()

  immediately closes the current connection and the JDBC resources that it created.

 java.sql.Statement 1.1 

• ResultSet executeQuery(String sqlQuery)

  executes the SQL statement given in the string and returns a ResultSet object to view the query result.

• int executeUpdate(String sqlStatement)

  executes the SQL INSERT, UPDATE, or DELETE statement specified by the string. Also executes Data Definition Language (DDL) statements such as CREATE TABLE. Returns the number of records affected, or -1 for a statement without an update count.

• boolean execute(String sqlStatement)

  executes the SQL statement specified by the string. Returns true if the statement returns a result set, false otherwise. Use the getresultSet or getUpdateCount method to obtain the statement outcome.

• int getUpdateCount()

  returns the number of records affected by the preceding update statement, or -1 if the preceding statement was a statement without an update count. Call this method only once per executed statement.

• ResultSet getResultSet()

  returns the result set of the preceding query statement, or null if the preceding statement did not have a result set. Call this method only once per executed statement.

• void close()

  closes this statement object and its associated result set.

 java.sql.ResultSet 1.1 

• boolean next()

  makes the current row in the result set move forward by one. Returns false after the last row. Note that you must call this method to advance to the first row.

• Xxx getXxx(int columnNumber)

• Xxx getXxx(String columnName)

  (Xxx is a type such as int, double, String, Date, etc.) return the value of the column with the given column number or name, converted to the specified type. Not all type conversions are legal. See documentation for details.

• int findColumn(String columnName)

  gives the column index associated with a column name.

• void close()

  immediately closes the current result set.

 java.sql.SQLException 1.1 

• String getSQLState()

  gets the "SQL state," a five-digit error code associated with the error.

• int getErrorCode()

  gets the vendor-specific exception code.

• SQLException getNextException()

  gets the exception chained to this one. It may contain more information about the error.

Managing Connections, Statements, and Result Sets

Every Connection object can create one or more Statement objects. You can use the same Statement object for multiple, unrelated commands and queries. However, a statement has at most one open result set. If you issue multiple queries whose results you analyze concurrently, then you need multiple Statement objects.

Be forewarned, though, that at least one commonly used database (Microsoft SQL Server) has a JDBC driver that allows only one active statement at a time. Use the getMaxStatements method of the DatabaseMetaData class to find out the number of concurrently open statements that your JDBC driver supports.

This sounds restrictive, but in practice, you should probably not fuss with multiple concurrent result sets. If the result sets are related, then you should be able to issue a combined query and analyze a single result. It is much more efficient to let the database combine queries than it is for a Java program to iterate through multiple result sets.

When you are done using a ResultSet, Statement, or Connection, you should call the close method immediately. These objects use large data structures, and you don't want to wait for the garbage collector to deal with them.

The close method of a Statement object automatically closes the associated result set if the statement has an open result set. Similarly, the close method of the Connection class closes all statements of the connection.

If your connections are short-lived, you don't have to worry about closing statements and result sets. Just make absolutely sure that a connection object cannot possibly remain open, by placing the close statement in a finally block:

Connection conn = . . .;
try
{
   Statement stat = conn.createStatement();
   ResultSet result = stat.executeQuery(queryString);
   process query result
}
finally
{
   conn.close();
}

TIP

Use the TRy/finally block just to close the connection, and use a separate try/catch block to deal with exceptions. Separating the try blocks makes your code easier to read.

Populating a Database

We now want to write our first, real, JDBC program. Of course, it would be nice if we could execute some of the fancy queries that we discussed earlier. Unfortunately, we have a problem: Right now, there are no data in the database. And you won't find a database file on the CD-ROM that you can simply copy onto your hard disk for the database program to read, because no database file format lets you interchange SQL relational databases from one vendor to another. SQL does not have anything to do with files. It is a language to issue queries and updates to a database. How the database executes these statements most efficiently and what file formats it uses toward that goal are entirely up to the implementation of the database. Database vendors try hard to come up with clever strategies for query optimization and data storage, and different vendors arrive at different mechanisms. Thus, although SQL statements are portable, the underlying data representation is not.

To get around our problem, we provide a small set of data in a series of text files that contain the raw SQL instructions to create the tables and insert the values. We also give you a program that reads a file with SQL instructions, one instruction per line, and executes them.

Specifically, the program reads data from a text file in a format such as

 CREATE TABLE Publisher (Publisher_Id CHAR(6), Name CHAR(30), URL CHAR(80)) INSERT INTO Publishers VALUES ('0201', 'Addison-Wesley', 'www.aw-bc.com') INSERT INTO Publishers VALUES ('0471', 'John Wiley & Sons', 'www.wiley.com') . . . 

At the end of this section, you can see the code for the program that reads the SQL statement file and executes the statements. Even if you are not interested in looking at the implementation, you must run this program if you want to execute the more interesting examples in the remainder of this chapter. Run the program as follows:

java -classpath .:driverPath ExecSQL Books.sql
java -classpath .:driverPath ExecSQL Authors.sql
java -classpath .:driverPath ExecSQL Publishers.sql
java -classpath .:driverPath ExecSQL BooksAuthors.sql

Before running the program, check that the file database.properties is set up properly for your environmentsee page 195.

The following steps briefly describe the ExecSQL program:

1.	Connect to the database. The getConnection method reads the properties in the file database.properties and adds the jdbc.drivers property to the system properties. The driver manager uses the jdbc.drivers property to load the appropriate database driver. The getConnection method uses the jdbc.url, jdbc.username, and jdbc.password properties to open the database connection.
2.	Open the file with the SQL commands. If no file name was supplied, then prompt the user to enter the commands on the console.
3.	Execute each command with the generic execute method. If it returns TRue, the command had a result set. The four SQL files that we provide for the book database all end in a SELECT * statement so that you can see that the data were successfully inserted.
4.	If there was a result set, print out the result. Because this is a generic result set, we need to use metadata to find out how many columns the result has. You will learn more about metadata on page 221.
5.	If there is any SQL exception, print the exception and any chained exceptions that may be contained in it.
6.	Close the connection to the database.

Example 4-2 lists the code for the program.

Example 4-2. ExecSQL.java

[View full width]

   1. import java.io.*;   2. import java.util.*;   3. import java.sql.*;   4.   5. /**   6.    Executes all SQL statements in a file.   7.    Call this program as   8.    java -classpath driverPath:. ExecSQL commandFile   9. */  10. class ExecSQL  11. {  12.    public static void main (String args[])  13.    {  14.       try  15.       {  16.          Scanner in;  17.          if (args.length == 0)  18.             in = new Scanner(System.in);  19.          else  20.             in = new Scanner(new File(args[0]));  21.  22.          Connection conn = getConnection();  23.          try  24.          {  25.             Statement stat = conn.createStatement();  26.  27.             while (true)  28.             {  29.                if (args.length == 0) System.out.println("Enter command or EXIT to  exit:");  30.  31.                if (!in.hasNextLine()) return;  32.  33.                String line = in.nextLine();  34.                if (line.equalsIgnoreCase("EXIT")) return;  35.                try  36.                {  37.                   boolean hasResultSet = stat.execute(line);  38.                   if (hasResultSet)  39.                      showResultSet(stat);  40.                }  41.                catch (SQLException e)  42.                {  43.                   while (e != null)  44.                   {  45.                      e.printStackTrace();  46.                      e = e.getNextException();  47.                   }  48.                }  49.             }  50.          }  51.          finally  52.          {  53.             conn.close();  54.          }  55.       }  56.       catch (SQLException e)  57.       {  58.          while (e != null)  59.          {  60.             e.printStackTrace();  61.             e = e.getNextException();  62.          }  63.       }  64.       catch (IOException e)  65.       {  66.          e.printStackTrace();  67.       }  68.    }  69.  70.    /**  71.       Gets a connection from the properties specified  72.       in the file database.properties  73.       @return the database connection  74.    */  75.    public static Connection getConnection()  76.       throws SQLException, IOException  77.    {  78.       Properties props = new Properties();  79.       FileInputStream in = new FileInputStream("database.properties");  80.       props.load(in);  81.       in.close();  82.  83.       String drivers = props.getProperty("jdbc.drivers");  84.       if (drivers != null) System.setProperty("jdbc.drivers", drivers);  85.  86.       String url = props.getProperty("jdbc.url");  87.       String username = props.getProperty("jdbc.username");  88.       String password = props.getProperty("jdbc.password");  89.  90.       return DriverManager.getConnection(url, username, password);  91.    }  92.  93.    /**  94.       Prints a result set.  95.       @param stat the statement whose result set should be  96.       printed  97.    */  98.    public static void showResultSet(Statement stat)  99.       throws SQLException 100.    { 101.       ResultSet result = stat.getResultSet(); 102.       ResultSetMetaData metaData = result.getMetaData(); 103.       int columnCount = metaData.getColumnCount(); 104. 105.       for (int i = 1; i <= columnCount; i++) 106.       { 107.          if (i > 1) System.out.print(", "); 108.          System.out.print(metaData.getColumnLabel(i)); 109.       } 110.       System.out.println(); 111. 112.       while (result.next()) 113.       { 114.          for (int i = 1; i <= columnCount; i++) 115.          { 116.             if (i > 1) System.out.print(", "); 117.             System.out.print(result.getString(i)); 118.          } 119.          System.out.println(); 120.       } 121.       result.close(); 122.    } 123. }