Clob

10.1 Clob Overview

A Clob object represents the Java programming language mapping of an SQL CLOB (Character Large Object). An SQL CLOB is a built-in type that stores a Character Large Object as a column value in a row of a database table. Methods in the interfaces ResultSet, CallableStatement, and PreparedStatement allow a programmer to access the SQL3 type CLOB in the same way that more basic SQL types are accessed. In other words, an application using the JDBC 2.0 API uses methods such as getClob and setClob for a CLOB value the same way it uses getInt and setInt for an INTEGER value or getString and setString for a CHAR or VARCHAR value.

The default is for a JDBC driver to implement the Clob interface using the SQL type LOCATOR(CLOB) behind the scenes. A LOCATOR(CLOB) designates an SQL CLOB residing on a database server, and operations on the locator achieve the same results as operations on the CLOB itself. This means that a client can operate on a Clob instance without ever having to materialize the CLOB data on the client machine. The driver uses LOCATOR(CLOB) behind the scenes, making its use completely transparent to the JDBC programmer.

The standard behavior for a Clob instance is to remain valid until the transaction in which it was created is either committed or rolled back.

The interface Clob provides methods for getting the length of an SQL CLOB value, for materializing the data in a CLOB value on the client, and for searching for a substring or CLOB object within a CLOB value.

10.1.1 Creating a Clob Object

The following code fragment illustrates creating a Clob object, where rs is a ResultSet object:

Clob clob = rs.getClob(1);

The variable clob can now be used to operate on the CLOB value that is stored in the first column of the result set rs.

10.1.2 Materializing Clob Data

Programmers can invoke methods in the JDBC API on a Clob object as if they were operating on the SQL CLOB value it designates. However, if they want to operate on a Clob object as an object in the Java programming language, they must first materialize the data of the CLOB object on the client. The Clob interface provides three methods for materializing a Clob object as an object in the Java programming language:

• getAsciiStream materializes the CLOB value as a byte stream containing Ascii bytes

Clob notes = rs.getClob("NOTES");
java.io.InputStream in = notes.getAsciiStream();
byte b = in.read();
// in contains the characters in the CLOB value designated by
// notes as Ascii bytes; b contains the first character as an Ascii 
// byte

• getCharacterStream materializes the CLOB value as a stream of Unicode characters

java.io.Reader reader = notes.getCharacterStream();
int c = Reader.read();
// c contains the first character in the CLOB that notes designates

• getSubString materializes all or part of the CLOB value as a String object

String substring = notes.getSubString(10, 5);
// substring contains five characters, starting with the tenth
// character of the CLOB value that notes designates
long len = notes.length();
String substring = notes.getSubString(1, len);
// substring contains all of the characters in the CLOB object that
// notes designates

10.1.3 Storing a Clob Object

To store a Clob object in the database, it is passed as a parameter to the PreparedStatement method setClob. For example, the following code fragment stores the Clob object notes by passing it as the first input parameter to the PreparedStatement object pstmt:

Clob notes = rs.getClob("NOTES");
PreparedStatement pstmt = con.prepareStatement(
		"UPDATE SALES_STATS SET COMMENTS = ? WHERE SALES > 500000");
pstmt.setClob(1, notes);
pstmt.executeUpdate();

The CLOB value designated by notes is now stored in the table SALES_STATS in column COMMENTS in every row where the value in the column SALES is greater than 500000.

10.2 Clob Interface Definition

package java.sql;
public interface java.sql.Clob {
	long length() throws SQLException;
	InputStream getAsciiStream() throws SQLException;
	Reader getCharacterStream() throws SQLException;
	String getSubString(long pos, int length) throws SQLException;
	long position(String searchstr, long start) throws SQLException;
	long position(Clob searchstr, long start) throws SQLException;
}

10.3 Clob Methods

getAsciiStream

InputStream getAsciiStream() throws SQLException

Materializes the CLOB value designated by this Clob object as a stream of Ascii bytes.

RETURNS:

an InputStream object with all the data in the CLOB value designated by this Clob object as Ascii bytes

EXAMPLE:

java.io.InputStream in = clob.getAsciiStream();
byte b = in.read();
// in has all of the characters in the CLOB value designated by
// clob as Ascii bytes; b designates the first character as an Ascii
// byte

getCharacterStream

Reader getCharacterStream() throws SQLException

Materializes the CLOB value designated by this Clob object as a stream of Unicode characters.

RETURNS:

a Reader object with all the data in the CLOB value designated by this Clob object as Unicode characters

EXAMPLE:

Reader read = clob.getCharacterStream();
// read has all the data in the CLOB value designated by clob
// as Unicode characters

getSubString

length getSubString(long pos, int length) throws SQLException

Returns a copy of the portion of the CLOB value represented by this Clob object that starts at position pos and has up to length consecutive characters.

PARAMETERS:

pos		the position of the first char to extract from the CLOB value designated by this Clob object; the initial position is 1
length		the number of consecutive characters to be copied

RETURNS:

a String object containing a copy of up to length consecutive characters from the CLOB value represented by this Clob object, starting with the char at position pos

EXAMPLE:

String substr = clob.getSubString(1, 100);
// substr contains the first 100 characters in the CLOB value
// designated by clob (those in positions 1 through 100, inclusive)

length

long length() throws SQLExceptions

Returns the number of characters in the CLOB value designated by this Clob object.

RETURNS:

the length in characters of the CLOB value that this Clob object represents

EXAMPLE:

Clob clob = rs.getClob(3);
long len = clob.length(); 
// len contains the number of characters in the CLOB value
// designated by clob

position

long position(Clob searchstr, long start) throws SQLException

Determines the character position at which the Clob object searchstr begins within the CLOB value that this Clob object represents. The search begins at position start.

PARAMETERS:

searchstr		the Clob object for which to search
start		the position at which to begin searching; the first character is at position 1

RETURNS:

the position at which the Clob object searchstr begins, which will be start or larger if the search, starting at position start, is successful; -1 otherwise

EXAMPLE:

Clob clob2 = rs.getClob(4);
long beginning = clob.position(clob2, 1024);
// if clob2 is contained in clob starting at position 1024 or later, 
// beginning will contain the position at which clob2 begins

position

long position(String searchstr, long start) throws SQLException

Determines the position at which the String searchstr begins within the CLOB value that this Clob object represents. The search begins at position start.

PARAMETERS:

searchstr		the string for which to search
start		the position at which to begin searching; the first character is at position 1

RETURNS:

the position at which the String object searchstr begins, which will be start or larger if the search, starting at position start, is successful; -1 otherwise

EXAMPLE:

String searchstr= clob.getSubString(5, 100);
long beginning = clob.position(searchstr, 1024);
// if searchstr is contained in clob from position 1024 on, beginning
// will contain the position at which searchstr begins