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.
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]");
return;
}
try {
Class.forName(args[0]).newInstance( );
con = DriverManager.getConnection(args[1],
args[2],
args[3]);
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().
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,
CREATE TYPE FRUIT AS VARCHAR(10)
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.
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);
custs.add(cust);
}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
.
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);
conn.setTypeMap(tm);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 {
os.writeString(name);
}
}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.
Get Database Programming with JDBC & Java, Second Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
