Friday, January 21, 2011

Converting a database CLOB into a String

I've seen code that converts database CLOBs to String by using CharacterStreams and BufferedReaders to read CLOBs in a loop until the CLOB is fully read. A simpler way is using the getSubString() method of the Clob object. While knowing the length or size of the CLOB, you can read the whole CLOB into a String like this:

DataSource ds = ...
Connection c = null;
PreparedStatement s = null;
ResultSet r = null;

try {
c = ds.getConnection();
s = c.prepareStatement("SELECT clobColumn FROM someTable");
r = s.executeQuery();

while (r.next()) {
Clob clob = r.getClob(1);

if (clob != null) {

if ((int) clob.length() > 0) {
String s = clob.getSubString(1, (int) clob.length());
// Do something with string.
}
}
}
} catch (SQLException e) {
// Handle exception.
} finally {
// Close Connection, PreparedStatement, and ResultSet.
}

Beware if the data in the CLOB is really large (larger than Integer.MAX_VALUE). In that case, you'll still need a streaming method.

No comments:

Post a Comment