ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

An Introduction to JDBC, Part 2
Pages: 1, 2, 3

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



NAME                             CUSTOMER_ID  PHONE
-------------------------------- ----------- -------------------
Jane Markham                      1           617 555-1212
Louis Smith                       2           617 555-1213
Woodrow Lang                      3           508 555-7171
Dr. John Smith                    4           (011) 42 323-1239

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.

A Java program might handle the previous query as follows:

Statement stmt = con.createStatement(  );
ResultSet rs = stmt.executeQuery(
 "SELECT NAME, CUSTOMER_ID, PHONE FROM CUSTOMERS");
 
while(rs.next(  )) {
 System.out.print("Customer #" + rs.getString("CUSTOMER_ID"));
 System.out.print(", " + rs.getString("NAME"));
 System.out.println(", is at " + rs.getString("PHONE");
}
rs.close(  );
stmt.close(  );

Here's the resulting output:

Customer #1, Jane Markham, is at 617 555-1212
Customer #2, Louis Smith, is at 617 555-1213
Customer #3, Woodrow Lang, is at 508 555-7171
Customer #4, Dr. John Smith, is at (011) 42 323-1239

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:

int numberInStock = rs.getInt("STOCK");
if(rs.wasNull(  ))
 System.out.println("Result was null");
else
 System.out.println("In Stock: " + numberInStock);

Alternately, you can call getObject( ) and test to see if the result is null (Some drivers, including early versions of Oracle's JDBC drivers, don't properly support this behavior.):

Object numberInStock = rs.getObject("STOCK");
if(numberInStock == null)
 System.out.println("Result 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):

ResultSet rs =
 stmt.executeQuery("SELECT IMAGE FROM PICTURES WHERE PID = " +
 req.getParameter("PID"));
 
if (rs.next(  )) {
 BufferedInputStream gifData =
 new BufferedInputStream(rs.getBinaryStream("IMAGE"));
 byte[] buf = new byte[4 * 1024]; // 4K buffer
 int len;
 while ((len = gifData.read(buf, 0, buf.length)) != -1) {
 out.write(buf, 0, len);
 }
}

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:

{d 'yyyy-mm-dd'}
{t 'hh:mm:ss'}
{ts 'yyyy-mm-dd hh:mm:ss.ms.microseconds.ns'}

A TIMESTAMP needs only to be specified up to seconds; the remaining values are optional. Here is an example that uses a date escape sequence (where dateSQL is a Statement of some sort):

dateSQL.execute("INSERT INTO FRIENDS(BIRTHDAY) VALUES ({d '1978-12-14'})");

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.

Pages: 1, 2, 3

Next Pagearrow