CLOB Datatype in Java

Posted in: CLOB,JDBC
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 andsetString 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 SQLCLOB 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 aClob 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.


Creating a Clob ObjectThe 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.

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

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.

Clob Interface Definition

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;