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:

• SQL92 built-in types, which consist of the familiar SQL column types such as CHAR, FLOAT, and DATE
• SQL99 built-in types, which consist of types added by SQL99:
  • BOOLEAN: Boolean (true or false) value
  • BLOB: Binary large Bobject
  • CLOB: Character large object
• New built-in types added by SQL:2003:
  • XML: XML object
• User defined types:
  • Structured type: User-defined type; for example:
  • DISTINCT type: User-defined type based on a built-in type; for example:
• Constructed types: New types based on a given base type:
  • REF(structured-type): Pointer that persistently denotes an instance of a structured type that resides in the database
  • base-type ARRAY[n]: Array of n base-type elements
• Locators: Entities that are logical pointers to data that resides on the database server. A locator exists in the client computer and is a transient, logical pointer to data on the server. A locator typically refers to data that is too large to materialize on the client, such as images or audio. (Materialized views are query results that have been stored or "materialized" in advance as schema objects.) There are operators defined at the SQL level to retrieve randomly accessed pieces of the data denoted by the locator:
  • LOCATOR(structured-type): Locator to a structured instance in the server
  • LOCATOR(array): Locator to an array in the server
  • LOCATOR(blob): Locator to a binary large object in the server
  • LOCATOR(clob): Locator to a character large object in the server
• Datalink: Type for managing data external to the data source. Datalink values are part of SQL MED (Management of External Data), a part of the SQL ANSI/ISO standard specification.

o    CREATE TYPE PLANE_POINT

o    AS (X FLOAT, Y FLOAT) NOT FINAL

o    CREATE TYPE MONEY

o    AS NUMERIC(10,2) FINAL

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:

• BLOB: Blob interface
• CLOB: Clob interface
• NCLOB: NClob interface
• ARRAY: Array interface
• XML: SQLXML interface
• Structured types: Struct interface
• REF(structured type): Ref interface
• ROWID: RowId interface
• DISTINCT: Type to which the base type is mapped. For example, a DISTINCT value based on a SQL NUMERIC type maps to a java.math.BigDecimal type because NUMERIC maps to BigDecimal in the Java programming language.
• DATALINK: java.net.URL object

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:

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.

Using Large Objects

An important feature of Blob, Clob, and NClob Java objects is that you can manipulate them without having to bring all of their data from the database server to your client computer. Some implementations represent an instance of these types with a locator (logical pointer) to the object in the database that the instance represents. Because a BLOB, CLOB, or NCLOB SQL object may be very large, the use of locators can make performance significantly faster. However, other implementations fully materialize large objects on the client computer.

If you want to bring the data of a BLOB, CLOB, or NCLOB SQL value to the client computer, use methods in the Blob, Clob, and NClob Java interfaces that are provided for this purpose. These large object type objects materialize the data of the objects they represent as a stream.

The following topics are covered:

Adding Large Object Type Object to Database

The following excerpt from ClobSample.addRowToCoffeeDescriptions adds a CLOB SQL value to the table COFFEE_DESCRIPTIONS. The Clob Java object myClob contains the contents of the file specified by fileName.

public void addRowToCoffeeDescriptions(

    String coffeeName, String fileName)

    throws SQLException {

    PreparedStatement pstmt = null;

    try {

        Clob myClob = this.con.createClob();

        Writer clobWriter = myClob.setCharacterStream(1);

        String str = this.readFile(fileName, clobWriter);

        System.out.println("Wrote the following: " +

            clobWriter.toString());

        if (this.settings.dbms.equals("mysql")) {

            System.out.println(

                "MySQL, setting String in Clob " +

                "object with setString method");

            myClob.setString(1, str);

        }

        System.out.println("Length of Clob: " + myClob.length());

        String sql = "INSERT INTO COFFEE_DESCRIPTIONS " +

                     "VALUES(?,?)";

        pstmt = this.con.prepareStatement(sql);

        pstmt.setString(1, coffeeName);

        pstmt.setClob(2, myClob);

        pstmt.executeUpdate();

    } catch (SQLException sqlex) {

        JDBCTutorialUtilities.printSQLException(sqlex);

    } catch (Exception ex) {

      System.out.println("Unexpected exception: " + ex.toString());

    } finally {

        if (pstmt != null)pstmt.close();

    }

}

The following line creates a Clob Java object:

Clob myClob = this.con.createClob();

The following line retrieves a stream (in this case a Writer object named clobWriter) that is used to write a stream of characters to the Clob Java object myClob. The method ClobSample.readFile writes this stream of characters; the stream is from the file specified by the String fileName. The method argument 1 indicates that the Writer object will start writing the stream of characters at the beginning of the Clob value:

Writer clobWriter = myClob.setCharacterStream(1);

The ClobSample.readFile method reads the file line-by-line specified by the file fileName and writes it to the Writer object specified by writerArg:

private String readFile(String fileName, Writer writerArg)

        throws FileNotFoundException, IOException {

    BufferedReader br = new BufferedReader(new FileReader(fileName));

    String nextLine = "";

    StringBuffer sb = new StringBuffer();

    while ((nextLine = br.readLine()) != null) {

        System.out.println("Writing: " + nextLine);

        writerArg.write(nextLine);

        sb.append(nextLine);

    }

    // Convert the content into to a string

    String clobData = sb.toString();

    // Return the data.

    return clobData;

}

The following excerpt creates a PreparedStatement object pstmt that inserts the Clob Java object myClob into COFFEE_DESCRIPTIONS:

PreparedStatement pstmt = null;

// ...

String sql = "INSERT INTO COFFEE_DESCRIPTIONS VALUES(?,?)";

pstmt = this.con.prepareStatement(sql);

pstmt.setString(1, coffeeName);

pstmt.setClob(2, myClob);

pstmt.executeUpdate();

Retrieving CLOB Values

The method ClobSample.retrieveExcerpt retrieves the CLOB SQL value stored in the COF_DESC column of COFFEE_DESCRIPTIONS from the row whose column value COF_NAME is equal to the String value specified by the coffeeName parameter:

public String retrieveExcerpt(String coffeeName, int numChar)

    throws SQLException {

    String description = null;

    Clob myClob = null;

    PreparedStatement pstmt = null;

    try {

        String sql =

            "select COF_DESC " +

            "from COFFEE_DESCRIPTIONS " +

            "where COF_NAME = ?";

        pstmt = this.con.prepareStatement(sql);

        pstmt.setString(1, coffeeName);

        ResultSet rs = pstmt.executeQuery();

        if (rs.next()) {

            myClob = rs.getClob(1);

            System.out.println("Length of retrieved Clob: " +

                myClob.length());

        }

        description = myClob.getSubString(1, numChar);

    } catch (SQLException sqlex) {

        JDBCTutorialUtilities.printSQLException(sqlex);

    } catch (Exception ex) {

        System.out.println("Unexpected exception: " + ex.toString());

    } finally {

        if (pstmt != null) pstmt.close();

    }

    return description;

}

The following line retrieves the Clob Java value from the ResultSet object rs:

myClob = rs.getClob(1);

The following line retrieves a substring from the myClob object. The substring begins at the first character of the value of myClob and has up to the number of consecutive characters specified in numChar, where numChar is an integer.

description = myClob.getSubString(1, numChar);

Adding and Retrieving BLOB Objects

Adding and retrieving BLOB SQL objects is similar to adding and retrieving CLOB SQL objects. Use the Blob.setBinaryStream method to retrieve an OutputStream object to write the BLOB SQL value that the Blob Java object (which called the method) represents.

Releasing Resources Held by Large Objects

Blob, Clob, and NClob Java objects remain valid for at least the duration of the transaction in which they are created. This could potentially result in an application running out of resources during a long running transaction. Applications may release Blob, Clob, and NClob resources by invoking their free method.

In the following excerpt, the method Clob.free is called to release the resources held for a previously created Clob object:

Clob aClob = con.createClob();

int numWritten = aClob.setString(1, val);

aClob.free();