Documentation

Using Advanced Data Types
Trail: JDBC(TM) Database Access
Lesson: JDBC Basics

Using Advanced Data Types

The advanced data types introduced in this section give a relational database more flexibility in what can be used as a value for a table column. For example, a column can be used to store BLOB (binary large object) values, which can store very large amounts of data as raw bytes. A column can also be of type CLOB (character large object), which is capable of storing very large amounts of data in character format.

The latest version of the ANSI/ISO SQL standard is commonly referred to as SQL:2003. This standard specifies the following data types:

Mapping Advanced Data Types

The JDBC API provides default mappings for advanced data types specified by the SQL:2003 standard. The following list gives the data types and the interfaces or classes to which they are mapped:

Using Advanced Data Types

You retrieve, store, and update advanced data types the same way you handle other data types. You use either ResultSet.getDataType or CallableStatement.getDataType methods to retrieve them, PreparedStatement.setDataType methods to store them, and ResultSet.updateDataType methods to update them. (The variable DataType is the name of a Java interface or class mapped to an advanced data type.) Probably 90 percent of the operations performed on advanced data types involve using the getDataType, setDataType, and updateDataType methods. The following table shows which methods to use:

Advanced Data Type getDataType Method setDataType method updateDataType Method
BLOB getBlob setBlob updateBlob
CLOB getClob setClob updateClob
NCLOB getNClob setNClob updateNClob
ARRAY getArray setArray updateArray
XML getSQLXML setSQLXML updateSQLXML
Structured type getObject setObject updateObject
REF(structured type) getRef setRef updateRef
ROWID getRowId setRowId updateRowId
DISTINCT getBigDecimal setBigDecimal updateBigDecimal
DATALINK getURL setURL updateURL

Note: The DISTINCT data type behaves differently from other advanced SQL data types. Being a user-defined type that is based on an already existing built-in types, it has no interface as its mapping in the Java programming language. Consequently, you use the method that corresponds to the Java type on which the DISTINCT data type is based. See Using DISTINCT Data Type for more information.

For example, the following code fragment retrieves a SQL ARRAY value. For this example, suppose that the column SCORES in the table STUDENTS contains values of type ARRAY. The variable stmt is a Statement object.

ResultSet rs = stmt.executeQuery(
    "SELECT SCORES FROM STUDENTS " +
    "WHERE ID = 002238");
rs.next();
Array scores = rs.getArray("SCORES");

The variable scores is a logical pointer to the SQL ARRAY object stored in the table STUDENTS in the row for student 002238.

If you want to store a value in the database, you use the appropriate set method. For example, the following code fragment, in which rs is a ResultSet object, stores a Clob object:

Clob notes = rs.getClob("NOTES");
PreparedStatement pstmt =
    con.prepareStatement(
        "UPDATE MARKETS SET COMMENTS = ? " +
        "WHERE SALES < 1000000");
pstmt.setClob(1, notes);
pstmt.executeUpdate();

This code sets notes as the first parameter in the update statement being sent to the database. The Clob value designated by notes will be stored in the table MARKETS in column COMMENTS in every row where the value in the column SALES is less than one million.


Previous page: Using WebRowSet Objects
Next page: Using Large Objects