Reading a CLOB from Oracle DB after the DB connection is closed
In one of the Java classes I am reviewing I see the following code
private oracle.sql.CLOB getCLOB() {
oracle.sql.CLOB xmlDocument = null;
CallableStatement cstmt = null;
ResultSet resultSet = null;
Connection connection = null;
try {
connection = Persistence.getConnection();
cstmt = connection.prepareCall("{call pkg.proc(?,?)}");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.setString(2, id);
cstmt.execute();
resultSet = (ResultSe开发者_StackOverflow社区t)cstmt.getObject(1);
if (resultSet.next()) {
xmlDocument = ((OracleResultSet) resultSet).getCLOB(1);
}
} finally {
Persistence.closeAll(resultSet, cstmt, connection);
}
return xmlDocument;
}
The oracle.sql.CLOB that is returned by getCLOB() is read in another method:
private void anotherMethod() {
...
oracle.sql.CLOB xmlDocument = getCLOB();
clobLength = xmlDocument.length();
chunkSize = xmlDocument.getChunkSize();
textBuffer = new char[chunkSize];
for (int position = 1; position <= clobLength; position += chunkSize) {
charsRead = xmlDocument.getChars(position, chunkSize, textBuffer);
outputBufferedWriter.write(textBuffer, 0, charsRead);
}
...
}
I am new to this project and the folks here say this code is working. I don't understand how we can read a CLOB (which, in my understanding, is a reference) after the underlying database connection is closed. What am I missing?
EDIT: Another point to note is that this code is running in an app server. Persistence.getConnection() gets the connection from a data source (most probably with a connection pool). I wonder if the database connection is used after it is returned to the connection pool.
EDIT2: Using the connection after it was returned to the pool might not be the cause. The app server is Oracle's Glassfish server Websphere and I am hoping they would guard against such usage.
JDBC driver prefetches LOBs selected into a result set. Read API can use prefetch buffers without connection. Buffer size specified by oracle.jdbc.defaultLobPrefetchSize parameter with default 4000.
You should be able to simply use getString() on that column.
The current drivers do not require the use of the CLOB interface anymore.
(at least it works for me with regular SELECT statements)
精彩评论