O'Reilly logo

Database Programming with JDBC & Java, Second Edition by George Reese

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Advanced Datatypes

JDBC 1.x supported the SQL2 datatypes. JDBC 2.0 introduces support for more advanced datatypes, including the SQL3 “object” types and direct persistence of Java objects. Except for the BLOB and CLOB datatypes, few of these advanced datatype features are likely to be relevant to most programmers for a few years. While they are important features for bridging the gap between the object and relational paradigms, they are light years ahead of where database vendors are with relational technology and how people use relational technology today.

Blobs and Clobs

Stars of a bad horror film? No. These are the two most important datatypes introduced by JDBC 2.0. A blob is a B inary Large Object, and a clob is a C haracter Large Object. In other words, they are two datatypes designed to hold really large amounts of data. Blobs, represented by the BLOB datatype, hold large amounts of binary data. Similarly, clobs, represented by the CLOB datatype, hold large amounts of text data.

You may wonder why these two datatypes are so important when SQL2 already provides VARCHAR and VARBINARY datatypes. These two old datatypes have two important implementation problems that make them impractical for large amounts of data. First, they tend to have rather small maximum data sizes. Second, you retrieve them from the database all at once. While the first problem is more of a tactical issue (those maximum sizes are arbitrary), the second problem is more serious. Fields with sizes of 100 KB or more are better served through streaming than an all-at-once approach. In other words, instead of having your query wait to fetch the full data for each row in a result set containing a column of 1-MB data, it makes more sense to not send that data across the network until the instant you ask for it. The query runs faster using streaming, and your network will not be overburdened trying to shove 10 rows of 1 MB each at a client machine all at once. The BLOB and CLOB types support the streaming of large data elements.

JDBC 2.0 provides two Java types to correspond to SQL BLOB and CLOB types: java.sql.Blob and java.sql.Clob. You retrieve them from a result set in the same way you retrieve any other datatype, through a getter method:

Blob b = rs.getBlob(1);

Unlike other Java datatypes, when you call getBlob( ) or getClob( ) you are getting only an empty shell; the Blob or Clob instance contains none of the data from the database.[10] You can retrieve the actual data at your leisure using methods in the Blob and Clob interfaces as long as the transaction in which the value was retrieved is open. JDBC drivers can optionally implement alternate lifespans for Blob and Clob implementations to extend beyond the transaction.

The two interfaces enable your application to access the actual data either as a stream:

Blob b = rs.getBlob(1);
InputStream binstr = b.getBinaryStream( );
Clob c = rs.getClob(2);
Reader charstr = c.getCharacterStream( );

so you can read from the stream, or you can grab it in chunks:

Blob b = rs.getBlob(1);
byte[] data = b.getBytes(0, b.length( ));
Clob c = rs.getClob(2);
String text = c.getSubString(0, c.length( ));

The storage of blobs and clobs is a little different from their retrieval. While you can use the setBlob( ) and setClob( ) methods in the PreparedStatement and CallableStatement classes to bind Blob and Clob objects as parameters to a statement, the JDBC Blob and Clob interfaces provide no database-independent mechanism for constructing Blob and Clob instances.[11] You need to either write your own implementation or tie yourself to your driver vendor’s implementation.

A more database-independent approach is to use the setBinaryStream() or setObject( ) methods for binary data or the setAsciiStream( ), setUnicodeStream(), or setObject() methods for character data. Example 4.2 puts everything regarding blobs together into a program that looks for a binary file and either saves it to the database, if it exists, or retrieves it from the database and stores it in the named file if it does not exist.

Example 4-2. Storing and Retrieving Binary Data

import java.sql.*;
import java.io.*;

public class Blobs {
    public static void main(String args[]) {
        Connection con = null;

        if( args.length < 5 ) {
            System.err.println("Syntax: <java Blobs [driver] [url] " +
                               "[uid] [pass] [file]");
        try {
            Class.forName(args[0]).newInstance( );
            con = DriverManager.getConnection(args[1],
            File f = new File(args[4]);
            PreparedStatement stmt;

            if( !f.exists( ) ) {
                // if the file does not exist
                // retrieve it from the database and write it 
                // to the named file
                ResultSet rs;
                stmt = con.prepareStatement("SELECT blobData " +
                                              "FROM BlobTest " +
                                              "WHERE fileName = ?");

                stmt.setString(1, args[4]);
                rs = stmt.executeQuery( );
                if( !rs.next( ) ) {
                    System.out.println("No such file stored.");
                else {
                    Blob b = rs.getBlob(1);
                    BufferedOutputStream os;

                    os = new BufferedOutputStream(new FileOutputStream(f));
                    os.write(b.getBytes(0, (int)b.length( )), 0,
                             (int)b.length( ));
                    os.flush( );
                    os.close( );
            else {
                // otherwise read it and save it to the database
                FileInputStream fis = new FileInputStream(f);
                byte[] tmp = new byte[1024]; // arbitrary size
                byte[] data = null;
                int sz, len = 0;
                while( (sz = fis.read(tmp)) != -1 ) {
                    if( data == null ) {
                        len = sz;
                        data = tmp;
                    else {
                        byte[] narr;
                        int nlen;
                        nlen = len + sz;
                        narr = new byte[nlen];
                        System.arraycopy(data, 0, narr, 0, len);
                        System.arraycopy(tmp, 0, narr, len, sz);
                        data = narr;
                        len = nlen;
                if( len != data.length ) {
                    byte[] narr = new byte[len];

                    System.arraycopy(data, 0, narr, 0, len);
                    data = narr;
                stmt = con.prepareStatement(
                   "INSERT INTO BlobTest " + (fileName, " +
                   "blobData) VALUES(?, ?)");
                stmt.setString(1, args[4]);
                stmt.setObject(2, data);
                stmt.executeUpdate( );
                f.delete( );
            con.close( );
        catch( Exception e ) {
            e.printStackTrace( );
        finally {
            if( con != null ) {
                try { con.close( ); }
                catch( Exception e ) { }


SQL arrays are much simpler and much less frequently used than blobs and clobs. JDBC represents a SQL array through the java.sql.Array interface. This interface provides the getArray() method to turn an Array object into a normal Java array. It also provides a getResultSet() method to treat the SQL array instead as a JDBC result set. If, for example, your database has a column that is an array of string values, your code to retrieve that data might look like this:

Array col = rs.getArray(1);
String[] data = (String[])col.getArray( );

The default SQL to Java type mapping you saw in Chapter 3 determines the datatype of the array elements. You can, however, customize mapping of these values using something called a type mapping. You will cover more on type mappings later in the chapter.

Array storage faces the same difficulty as blob and clob storage: there is no driver-independent way to construct an Array instance for the setArray( ) methods. As an alternative, you can use setObject().

Other SQL3 Types

JDBC 2.0 supports a few other SQL3 types that behave in much the same way as types you have already seen. These types include the SQL REF , DISTINCT , and STRUCT types. Support for the REF type works in exactly the same way as support for the ARRAY type. JDBC provides a java.sql.Ref class with a getRef() method in ResultSet and setRef( ) methods in PreparedStatement and CallableStatement. The Ref interface only enables your application to reference its associated object; it does not provide a dereferencing mechanism. Using a DISTINCT type works in exactly the same way as using its underlying datatype. For example,


should be treated by JDBC code just as if the SQL type were VARCHAR(10). You would thus use getString( ) and setString( ) to retrieve and store the data for any column of this type.

Structured types work through getObject() and setObject() even though JDBC provides a special interface—java.sql.Struct —to support them. The JDBC driver fetches the underlying data in the Struct before returning it to you. The result is that a Struct reference is valid beyond the transaction until it is removed by the garbage collector.

Java Types

Sun is pushing the concept of a “Java-relational DBMS” that extends the basic type system of the DBMS with Java object types. What a Java-relational DBMS will ultimately look like is unclear, and the success of this effort remains to be seen. JDBC 2.0 nevertheless introduces features necessary to support it. These features are optional for JDBC drivers, and it is very likely that the driver you are using does not support them at this time.[12]

Returning to the example of a bank application, you might have customer and account tables in a traditional database. The idea behind a Java-relational database is that you have Customer and Account types that correspond directly to Java Customer and Account classes. You could therefore issue the following SQL:

SELECT Customer FROM Account

This SQL would give you all the data associated with all customers who have accounts. Your Java code might look like this:

ResultSet rs = stmt.executeQuery("SELECT Customer " +
                                 "FROM Account");
ArrayList custs = new ArrayList( );

while( rs.next( ) ) {
    Customer cust = (Customer)rs.getObject(1);


All the types I have mentioned so far in this book have a corresponding value in the java.sql.Types class. All Java object types, however, use a single value in java.sql.Types: JAVA_OBJECT .

Type Mapping

The new type support in JDBC 2.0 blurs the fine type mappings mentioned in Chapter 3. To help give the programmer more control over this type mapping, JDBC 2.0 introduces a type mapping system that lets you customize how you want SQL types mapped to Java objects. The central character of this new feature is a class from the Java Collections API, java.util.Map. You can pass JDBC an instance of this class that contains information on how to perform type mapping for user-defined types. This object is called the type-map object. The keys of the type-map object are strings that represent the name of the SQL type to be mapped. The values are the corresponding java.lang.Class objects. For example, you may have a Account string as a key that maps to a bank.Account class in your type map.

There are several levels of type mapping. The first is the default mapping. Until now, you have been working with the default type mapping. The default type mapping is used unless you provide an alternate type mapping. You can specify an alternate type mapping at the connection level by calling setTypeMap() in your Connection instance. For example, you might have the following code to handle your DISTINCT FRUIT type:

HashMap tm = new HashMap( );

tm.put("FRUIT", Fruit.class);

JDBC also provides you with a tool for more fine-grained type mapping. Many getXXX() methods such as getObject() have signatures that accept a type map as an argument. If, for example, you wanted to retrieve FRUIT data in most cases as the underlying String type, but in one specific instance wanted to retrieve it as an instance of a Java Fruit class, you would leave the default type map in place and instead use the following call to handle the special case:

HashMap tm = new HashMap( );

tm.put("FRUIT", Fruit.class);
rs.getObject(1, tm);

Of course, this type map provides no information on how to turn the String “orange” to an instance of the Fruit class that represents an Orange. Any class that appears in a type map must therefore implement the java.sql.SQLData interface. This interface prescribes methods that enable a driver to pass it the String “orange” from the database and initialize its data. These methods are readSQL(), writeSQL(), and getSQLTypeName( ). Example 4.3 shows a full implementation for the Fruit class.

Example 4-3. Mapping a SQL DISTINCT Type to a Java Class

import java.sql.*;

public class Fruit implements SQLData {
    private String name;
    private String sqlTypeName;

    public Fruit( ) {
        super( );

    public Fruit(String nom) {
        super( );
        name = nom;

    public String getName( ) {
        return name;

    public String getSQLTypeName( ) {
        return sqlTypeName;

    public void readSQL(SQLInput is, String type) throws SQLException {
        sqlTypeName = type;
        name = is.readString( );

    public void writeSQL(SQLOutput os) throws SQLException {

The readSQL() method reads the Fruit’s data from the database. The writeSQL() method, conversely, writes the Fruit’s state to the object stream. Finally, the getSQLTypeName() method says what SQL type represents this Java type. When using custom SQL3 object types that have an inheritance structure, your Java classes should call super.readSQL() and super.writeSQL( ) as the first order of business when implementing the readSQL() and writeSQL() methods in subclasses.

[10] Some database engines may actually fudge Blob and Clob support because they cannot truly support blob or clob functionality. In other words, the JDBC driver for the database may support Blob and Clob types even though the database it supports does not. More often than not, it fudges this support by loading the data from the database into these objects in the same way that VARCHAR and VARBINARY are implemented.

[11] This topic should be addressed by JDBC 3.0.

[12] My discussion of the topic is very cursory because it is still unclear how this feature will play itself out.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required