This section describes how to retrieve CLOB values with the ResultSet.getClob() method.
If you like to work with java.sql.Clob objects, you can retrieve CLOB values with the getClob() method on ResultSet objects.
The Clob object offers some interesting methods:
length() - Returns the number of characters in the Clob object. The return value has a type of "long".
You may need to convert it to "int" to be used in other motheds.
getSubString(long pos, int length) - Returns a substring of characters from the Clob object with a specified starting position and length.
Note the start position is "long" value, but the length is "int" value.
getCharacterStream() - Returns a Reader object from the Clob object so that you can read the content as a stream.
free() - Releases the resources that the Clob object holds. This was added in JDBC 4.0 (Java 1.6).
Here is my test program on getClob() method:
/**
* OracleClobGetClob.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.io.*;
import java.sql.*;
public class OracleClobGetClob {
public static void main(String [] args) {
Connection con = null;
try {
oracle.jdbc.pool.OracleDataSource ds
= new oracle.jdbc.pool.OracleDataSource();
ds.setDriverType("thin");
ds.setServerName("localhost");
ds.setPortNumber(1521);
ds.setDatabaseName("XE");
ds.setUser("Herong");
ds.setPassword("TopSecret");
con = ds.getConnection();
// Retrieving CLOB value with getClob()
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery("SELECT * FROM Article");
int i = 0;
while (res.next() && i<3) {
i++;
System.out.println("Record ID: "+res.getInt("ID"));
System.out.println(" Subject = "+res.getString("Subject"));
Clob bodyOut = res.getClob("Body");
int length = (int) bodyOut.length();
System.out.println(" Body Size = "+length);
String body = bodyOut.getSubString(1, length);
if (body.length() > 100) body = body.substring(0,100);
System.out.println(" Body = "+body+"...");
// bodyOut.free(); // new in JDBC 4.0
}
res.close();
sta.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
e.printStackTrace();
}
}
}
The output confirms that the getClob() method and Clob objects are not hard to use:
C:\>java -cp .;\local\lib\ojdbc14.jar OracleClobGetClob
Record ID: 1
Subject = Test on INSERT statement
Body Size = 84
Body = A BLOB (Binary Large OBject) is a large chunk of data
which is stored in a database....
Record ID: 2
Subject = Test of the setString() method
Body Size = 304
Body = He is wonderful and strange and who knows how old he is,
he thought. Never have I had such a strong...
Record ID: 3
Subject = Test of setCharacterStream() methods
Body Size = 2232
Body = /**
* OracleClobSetCharacterStream.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserv...
