An Introduction to JDBC, Part 2

Editor's Note: This is Part Two of a book excerpt from Java Enterprise in a Nutshell. This excerpt focuses on database connection, statements and results.

Connecting to the Database

The java.sql.Connection object, which encapsulates a single connection to a particular database, forms the basis of all JDBC data-handling code. An application can maintain multiple connections, up to the limits imposed by the database system itself. A standard small office or web server Oracle installation can support 50 or so connections, while a major corporate database could host several thousand. The DriverManager.getConnection( ) method creates a connection:

You pass three arguments to getConnection( ): a JDBC URL, a database username, and a password. For databases that don't require explicit logins, the user and password strings should be left blank. When the method is called, the DriverManager queries each registered driver, asking if it understands the URL. If a driver recognizes the URL, it returns a Connection object. Because the getConnection( ) method checks each driver in turn, you should avoid loading more drivers than are necessary for your application.

The getConnection( ) method has two other variants that are less frequently used. One variant takes a single String argument and tries to create a connection to that JDBC URL without a username or password, or with a username and password embedded in the URL itself. The other version takes a JDBC URL and a java.util.Properties object that contains a set of name/value pairs. You generally need to provide at least username=value and password=value pairs.

When a Connection has outlived its usefulness, you should be sure to explicitly close it by calling its close( ) method. This frees up any memory being used by the object, and, more importantly, it releases any other database resources the connection may be holding on to. These resources (cursors, handles, and so on) can be much more valuable than a few bytes of memory, as they are often quite limited. This is particularly important in applications such as servlets that might need to create and destroy thousands of JDBC connections between restarts. Because of the way some JDBC drivers are designed, it is not safe to rely on Java's garbage collection to remove unneeded JDBC connections.

The JDBC 2.0 standard extension, discussed later in this chapter, provides a facility for connection pooling, whereby an application can maintain several open database connections and spread the load among them. This is often necessary for enterprise-level applications, such as servlets, that may be called upon to perform tens of thousands of database transactions a day.

Statements

Once you have created a Connection, you can begin using it to execute SQL statements. This is usually done via Statement objects. There are actually three kinds of statements in JDBC:

Statement

Represents a basic SQL statement

PreparedStatement

Represents a precompiled SQL statement, which can offer improved performance

We're just going to discuss the Statement object for now; PreparedStatement and CallableStatement are covered in detail later in this chapter.

To get a Statement object, call the createStatement( ) method of a Connection:

Statement stmt = con.createStatement( );

Once you have created a Statement, use it to execute SQL statements. A statement can either be a query that returns results or an operation that manipulates the database in some way. If you are performing a query, use the executeQuery( ) method of the Statement object:

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

Here we've used executeQuery( ) to run a SELECT statement. This call returns a ResultSet object that contains the results of the query (we'll take a closer look at ResultSet in the next section).

Statement also provides an executeUpdate( ) method, for running SQL statements that don't return results, such as the UPDATE and DELETE statements. executeUpdate( ) returns an integer that indicates the number of rows in the database that were altered.

If you don't know whether a SQL statement is going to return results (such as when the user is entering the statement in a form field), you can use the execute( ) method of Statement. This method returns true if there is a result associated with the statement. In this case, the ResultSet can be retrieved using the getResultSet( ) method and the number of updated rows can be retrieved using getUpdateCount( ):

It is important to remember that a Statement object represents a single SQL statement. A call to executeQuery( ), executeUpdate( ), or execute( ) implicitly closes any active ResultSet associated with the Statement. In other words, you need to be sure you are done with the results from a query before you execute another query with the same Statement object. If your application needs to execute more than one simultaneous query, you need to use multiple Statement objects. As a general rule, calling the close( ) method of any JDBC object also closes any dependent objects, such as a Statement generated by a Connection or a ResultSet generated by a Statement, but well-written JDBC code closes everything explicitly.

Multiple Result Sets

It is possible to write a SQL statement that returns more than one ResultSet or update count (exact methods of doing so vary depending on the database). The Statement object supports this functionality via the getMoreResults( ) method. Calling this method implicitly closes any existing ResultSet and moves to the next set of results for the statement. getMoreResults( ) returns true if there is another ResultSet available to be retrieved by getResultSet( ). However, the method returns false if the next statement is an update, even if there is another set of results waiting farther down the line. To be sure you've processed all the results for a Statement, you need to check that getMoreResults( ) returns false and that getUpdateCount( ) returns -1.

We can modify the previous execute( ) example to handle multiple results:

Statements that return multiple results are actually quite rare. They generally arise from stored procedures or SQL implementations that allow multiple statements to be executed in a batch. Under SyBase, for instance, multiple SELECT statements may be separated by newline (\n) characters.

Results

When an SQL query executes, the results form a pseudo-table that contains all rows that fit the query criteria. For instance, here's a textual representation of the results of the query string "SELECT NAME, CUSTOMER_ID, PHONE FROM CUSTOMERS":

This kind of textual representation is not very useful for Java programs. Instead, JDBC uses the java.sql.ResultSet interface to encapsulate the query results as Java primitive types and objects. You can think of a ResultSet as an object that represents an underlying table of query results, where you use method calls to navigate between rows and retrieve particular column values.

The code loops through each row of the ResultSet using the next( ) method. When you start working with a ResultSet, you are positioned before the first row of results. That means you have to call next( ) once just to access the first row. Each time you call next( ), you move to the next row. If there are no more rows to read, next( ) returns false. Note that with the JDBC 1.0 ResultSet, you can only move forward through the results and, since there is no way to go back to the beginning, you can read them only once. The JDBC 2.0 ResultSet, which we discuss later, overcomes these limitations.

Individual column values are read using the getString( ) method. getString( ) is one of a family of getXXX( ) methods, each of which returns data of a particular type. There are two versions of each getXXX( ) method: one that takes the case-insensitive String name of the column to be read (e.g., "PHONE", "CUSTOMER_ID") and one that takes a SQL-style column index. Note that column indexes run from 1 to n, unlike Java array indexes, which run from 0 to n-1, where n is the number of columns.

The most important getXXX( ) method is getObject( ), which can return any kind of data packaged in an object wrapper. For example, calling getObject( ) on an integer field returns an Integer object, while calling it on a date field yields a java.sql.Date object. Table 2-1 lists the different getXXX( ) methods, along with the corresponding SQL data type and Java data type. Where the return type for a getXXX( ) method is different from the Java type, the return type is shown in parentheses. Note that thejava.sql.Types class defines integer constants that represent the standard SQL data types.

Table 2-1: SQL Data Types, Java Types, and Default getXXX( ) Methods

SQL Data Type

Java Type

getXXX( ) Method

CHAR

String

getString( )

VARCHAR

String

getString( )

LONGVARCHAR

String

getString( )

NUMERIC

java.math.BigDecimal

getBigDecimal( )

DECIMAL

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[]

getBytes( )

VARBINARY

byte[]

getBytes( )

LONGVARBINARY

byte[]

getBytes( )

DATE

java.sql.Date

getDate( )

TIME

java.sql.Time

getTime( )

TIMESTAMP

java.sql.Timestamp

getTimestamp( )

BLOB

java.sql.Blob

getBlob( )

CLOB

java.sql.Clob

getClob( )

Note that this table merely lists the default mappings according to the JDBC specification, and some drivers don't follow these mappings exactly. Also, a certain amount of casting is permitted. For instance, the getString( ) method returns a String representation of just about any data type.

Handling Nulls

The driver can figure this out after reading the object, but since some driver implementations and database connection protocols allow you to reliably read a value from a column only once, implementing an isNull( ) method requires the ResultSet to cache the entire row in memory. While many programs do exactly this, it is not appropriate behavior for the lowest-level result handler.

Sometimes database columns contain null, or empty, values. However, because of the way certain database APIs are written, it is impossible for JDBC to provide a method to determine before the fact whether or not a column is null (see sidebar). Methods that don't return an object of some sort are especially vulnerable. getInt( ), for instance, resorts to returning a value of -1. JDBC deals with this problem via the wasNull( ) method, which indicates whether or not the last column read was null:

Large Data Types

You can retrieve large chunks of data from a ResultSet as a stream. This can be useful when reading images from a database or loading large documents from a data store, for example. The relevant ResultSet methods are getAsciiStream( ), getBinaryStream( ), and getUnicodeStream( ), where each method has column name and column index variants, just like the other getXXX( ) methods. Each of these methods returns an InputStream. Here's a code sample that retrieves an image from a PICTURES table and writes the image to an OutputStream of some kind (this might be a ServletOutputStream for a Java servlet that produces a GIF from a database):

The JDBC 2.0 API includes Blob and Clob objects to handle large data types; we discuss these objects later in this chapter.

Dates and Times

JDBC defines three classes devoted to storing date and time information: java.sql.Date, java.sql.Time, and java.sql.Timestamp. These correspond to the SQL DATE, TIME, and TIMESTAMP types. The java.util.Date class is not suitable for any of them, so JDBC defines a new set of wrapper classes that extend (or limit) the standard Date class to fit the JDBC mold.

The SQL DATE type contains only a date, so the java.sql.Date class contains only a day, month, and year. SQL TIME (java.sql.Time) includes only a time of day, without date information. SQL TIMESTAMP (java.sql.Timestamp) includes both, but at nanosecond precision (the standard Date class is incapable of handling more than milliseconds).

Since different DBMS packages have different methods of encoding date and time information, JDBC supports the ISO date escape sequences, and individual drivers must translate these sequences into whatever form the underlying DBMS requires. The syntax for dates, times, and timestamps is:

Advanced Results Handling

With JDBC 1.0, the functionality provided by the ResultSet interface is rather limited. There is no support for updates of any kind, and access to rows is limited to a single, sequential read (i.e., first row, second row, third row, etc., and no going back). JDBC 2.0 supports scrollable and updateable result sets, which allows for advanced record navigation and in-place data manipulation.

With scrolling, you can move forward and backward through the results of a query, rather than just using the next( ) method to move to the next row. In terms of scrolling, there are now three distinct types of ResultSet objects: forward-only (as in JDBC 1.0), scroll-insensitive, and scroll-sensitive. A scroll-insensitive result set generally doesn't reflect changes to the underlying data, while scroll-sensitive ones do. In fact, the number of rows in a sensitive result set doesn't even need to be fixed.

As of JDBC 2.0, result sets are also updateable. From this perspective, there are two different kinds of result sets: read-only result sets that don't allow changes to the underlying data and updateable result sets that allow such changes, subject to transaction limitations and so on.

To create an updateable, scroll-sensitive result set, we pass two extra arguments to the createStatement( ) method.

If you don't pass any arguments to createStatement( ), you get a forward-only, read-only result set, just as you would using JDBC 1.0. Note that if you specify a scrollable result set (either sensitive or insensitive), you must also specify whether or not the result set is updateable. After you have created a scrollable ResultSet, use the methods listed in Table 2-2 to navigate through it. As with JDBC 1.0, when you start working with a ResultSet, you are positioned before the first row of results.

Table 2-2: JDBC 2.0 Record Scrolling Functions

Method

Function

first( )

Move to the first record.

last( )

Move to the last record.

next( )

Move to the next record.

previous( )

Move to the previous record.

beforeFirst( )

Move to immediately before the first record.

afterLast( )

Move to immediately after the last record.

absolute(int)

Move to an absolute row number. Takes a positive or negative argument.

relative(int)

Move backward or forward a specified number of rows. Takes a positive or negative argument.

The JDBC 2.0 API also includes a number of methods that tell you where you are in a ResultSet. You can think of your position in a ResultSet as the location of a cursor in the results. The isFirst( ) and isLast( ) methods return true if the cursor is located on the first or last record, respectively. isAfterLast( ) returns true if the cursor is after the last row in the result set, while isBeforeFirst( ) returns true if the cursor is before the first row.

With an updateable ResultSet, you can change data in an existing row, insert an entirely new row, or delete an existing row. To change data in an existing row, use the newupdateXXX( ) methods of ResultSet. Let's assume we want to update the CUSTOMER_ID field of the first row we retrieve (okay, it's a contrived example, but bear with us):

Here we use first( ) to navigate to the first row of the result set and then call updateInt( ) to change the value of the customer ID column in the result set. After making the change, call updateRow( ) to actually make the change in the database. If you forget to call updateRow( ) before moving to another row in the result set, any changes you made are lost. If you need to make a number of changes in a single row, do so with multiple calls to updateXXX( ) methods and then a single call to updateRow( ). Just be sure you call updateRow( ) before moving on to another row.

The technique for inserting a row is similar to updating data in an existing row, with a few important differences. The first step is to move to what is called the insert row, using the moveToInsertRow( ) method. The insert row is a blank row associated with the ResultSet that contains all the fields, but no data; you can think of it as a pseudo-row in which you can compose a new row. After you have moved to the insert row, use updateXXX( ) methods to load new data into the insert row and then call insertRow( ) to append the new row to the ResultSet and the underlying database. Here's an example that adds a new customer to the database:

Note that you don't have to supply a value for every column, as long as the columns you omit can accept null values. If you don't specify a value for a column that can't be null, you'll get a SQLException. After you call insertRow( ), you can create another new row, or you can move back to the ResultSet using the various navigation methods shown in Table 2-2. One final navigation method that isn't listed in the table is moveToCurrentRow( ). This method takes you back to where you were before you called moveToInsertRow( ); it can only be called while you are in the insert row.

Deleting a row from an updateable result set is easy. Simply move to the row you want to delete and call the deleteRow( ) method. Here's how to delete the last record in a ResultSet:

rs.last( );
rs.deleteRow( );

Calling deleteRow( ) also deletes the row from the underlying database.

Note that not all ResultSet objects are updateable. In general, the query must reference only a single table without any joins. Due to differences in database implementations, there is no single set of requirements for what makes an updateable ResultSet.

As useful as scrollable and updateable result sets are, the JDBC 2.0 specification doesn't require driver vendors to support them. If you are building middleware or some other kind of system that requires interaction with a wide range of database drivers, you should avoid this functionality for the time being. The extended JDBC 2.0 DatabaseMetaData object can provide information about scrolling and concurrency support.

Java-Aware Databases

This is Sun's term. We have yet to see any packages actually marketed as Java-relational databases, but many newer packages, including Oracle 8i, are capable of storing Java classes. A number of these products also use Java as a trigger language, generally in a JDBC structure.

Java is object-oriented; relational databases aren't. As a result, it's decidedly difficult to shoehorn a Java object into a stubbornly primitive-oriented database table. Luckily, the wind is changing, and newer database systems, including object-oriented database management systems (OODBMS) and Java-relational database management systems (see sidebar), provide direct support for storing and manipulating objects. While a regular relational database can store only a limited number of primitive types, a JDBMS system can store entire, arbitrary Java objects.

Say we want to store a customized Java Account object in the ACCOUNTS table in a database. With a standard DBMS and JDBC 1.0, we have to pull each piece of data (account number, account holder, balance, etc.) out of the Account object and write it to a complicated database table. To get data out, we reverse the process. Short of serializing the Account object and writing it to a binary field (a rather complex operation), we're stuck with this clumsy approach. (Various commercial products, such as Sun's Forte developer tool, automatically handle mapping objects to database records and vice versa. Check this site for more information.)

With JDBC 2.0, the getObject( ) method has been extended to support these new Java-aware databases. Provided that the database supports a Java-object type, we can read the Account object just like any primitive type:

A column that stores a Java object has a type of Types.JAVA_OBJECT. The JDBC API doesn't take any special steps to locate the bytecodes associated with any particular class, so you should make sure that any necessary objects can be instantiated with a call to Class.forName( ).

Next time, learn about errors and more.

William Crawford, Jim Farley
is a coauthor of Java Enterprise in a Nutshell, 2nd Edition, and has been developing web-based enterprise applications since 1995. He is currently the Director of the Informatics Solutions Group at Children's Hospital, Boston, where he and his team are building open source Personally Controlled Health Record systems and tools for managing agile development projects in healthcare and regulated industries.

David Flanagan
is the author of a number of O'Reilly books, including
Java in a Nutshell, Java Examples in a Nutshell, Java Foundation Classes in a
Nutshell, JavaScript: The
Definitive Guide, and JavaScript Pocket Reference.