Remove Oracle CLOB ClassCastException
Written by coregps on Friday, January 5th, 2007 in Java, SQL Server, Oracle.
If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!
I recently migrated from SQL Server 2000 to Oracle 8.1.7 and now I have to handle Oracle CLOB data. To demonstrate my problem, I will create the following table:
CREATE TABLE test(testid number, content clob);
The following code snippet is used to write CLOB data into database:
String sql = "INSERT INTO test(testid, content) VALUES(?, EMPTY_CLOB())";
PreparedStatement pst = conn.prepareStatement(sql);
pst.setInt(1, maxid);
pst.executeUpdate();
sql = "SELECT content FROM test WHERE testid=? FOR UPDATE";
pst = conn.prepareStatement(sql);
pst.setInt(1, maxid);
ResultSet rs = pst.executeQuery();
if (rs.next()) {
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
clob.putString(1, "Some content to write into database");
}
And the code used to convert CLOB data to String:
public static String clobToString(Object obj) {
StringBuffer content= new StringBuffer("");
if (obj != null) {
CLOB clob = (CLOB)obj;
Reader is;
try {
is = clob.getCharacterStream();
BufferedReader br = new BufferedReader(is);
String s = br.readLine();
while (s != null) {
content.append(s);
s = br.readLine();
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
return content.toString();
}
I was able to successfully run the code above when I use the connection obtained from normal DriverManager.getConnection method.
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
String url = “jdbc:oracle:thin:@ubuntu:1521:oradb”;
String userName = “scott”;
String password = “tiger”;
conn = DriverManager.getConnection(url, userName, password);
String url = “jdbc:oracle:thin:@ubuntu:1521:oradb”;
String userName = “scott”;
String password = “tiger”;
conn = DriverManager.getConnection(url, userName, password);
But got some trouble when I use a datasource lookup.
<Resource name="jdbc/oraDB" auth="Container" type="javax.sql.DataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
url="jdbc:oracle:thin:@server:1521:oradb"
username="scott"
password="tiger"
maxWait="-1" removeAbandoned="true"
maxActive="20" maxIdle="10"
removeAbandonedTimeout="180" logAbandoned="true" accessToUnderlyingConnectionAllowed="true" />
public class DBConn {
private static Context initCtx = null;
private static DataSource ds = null;
private static String resource = "java:comp/env/jdbc/oraDB";
/**
* Get a connection from pool
*
* @return a Connection
*/
public static Connection getConnection() {
Connection conn = null;
try {
if (initCtx == null) {
initCtx = new InitialContext();
}
if (ds == null) {
ds = (DataSource) initCtx.lookup(resource);
}
try {
conn = ds.getConnection();
} catch (SQLException e1) {
}
} catch (NamingException e) {
}
return conn;
}
}
The following line will throw a ClassCastException “java.lang.ClassCastException: oracle.sql.CLOB”.
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
I first found the following section in Oracle forum(http://forums.oracle.com/forums/message.jspa?messageID=855366):
It ONLY works if the Connection is an oracle.jdbc.driver.OracleConnection object. If it's not an oracle.jdbc.driver.OracleConnection, you get a ClassCastException.
The problem happens because when I run my code in Tomcat and get the database connection via a DataSource object that I lookup via JNDI, the connection is not a real OracleConnection object, but an object that wraps the real OracleConnection.
In more detail:
Tomcat uses the Apache Commons DBCP package (see http://jakarta.apache.org/commons/dbcp/) for database connection pooling. When you lookup a DataSource object using JNDI, like this:
Context context = new InitialContext();
DataSource ds = (DataSource)context.lookup("java:comp/env/jdbc/ari");
the object that you get is an instance of class org.apache.commons.dbcp.BasicDataSource. When you call getConnection on this object, you get an instance of class org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper (which obviously implements java.sql.Connection, and wraps the OracleConnection object).
To be able to call CLOB.createTemporary successfully, we need to find the wrapped OracleConnection object. A number of steps are necessary to reach this:
First of all, by default the PoolingDataSource$PoolGuardConnectionWrapper does not allow access to the wrapped Connection object (don't ask me why, but that's how the Apache developers decided it should be). You must enable access by configuring it in the <context> of the web application (in Tomcat's server.xml configuration file), for example:
<context path="/ariweb" docBase="ariweb.war">
<resource name="jdbc/ari" auth="Container" type="javax.sql.DataSource"/>
<resourceparams name="jdbc/ari">
<parameter>
<name>driverClassName</name>
<value>oracle.jdbc.driver.OracleDriver</value>
</parameter>
<parameter>
<!-- NOTE: This is necessary to enable access to the Oracle connection object -->
<name>accessToUnderlyingConnectionAllowed</name>
<value>true</value>
</parameter>
<!-- Other configuration parameters -->
...
</resourceparams>
</context>
The PoolingDataSource$PoolGuardConnectionWrapper extends class org.apache.commons.dbcp.DelegatingConnection, which has a getDelegate method. We can call this method to get the wrapped connection.
Unfortunately, the story is not yet finished. The getDelegate method of the PoolingDataSource$PoolGuardConnectionWrapper does not return the OracleConnection object - it returns an instance of class org.apache.commons.dbcp.PoolableConnection, which is a second wrapper around the OracleConnection object.
The PoolableConnection class also extends DelegatingConnection, so we can call getDelegate again and finally we have the OracleConnection object.
So here is the final code:
// conn is the Connection I got from the DataSource
Connection oracleConnection = conn;
if (conn instanceof org.apache.commons.dbcp.DelegatingConnection) {
// This returns a org.apache.commons.dbcp.PoolableConnection
Connection pc = ((org.apache.commons.dbcp.DelegatingConnection)conn).getDelegate();
// The PoolableConnection is a DelegatingConnection itself - get the delegate (the Oracle connection)
oracleConnection = ((org.apache.commons.dbcp.DelegatingConnection)pc).getDelegate();
}
CLOB clob = CLOB.createTemporary(oracleConnection, true, CLOB.DURATION_SESSION);
clob.open(CLOB.MODE_READWRITE);
...
However, after trying the solution above as following:
conn = DBConn.getConnection();
if (conn instanceof DelegatingConnection) {
// This returns a org.apache.commons.dbcp.PoolableConnection
Connection pc = ((DelegatingConnection)conn).getDelegate();
// The PoolableConnection is a DelegatingConnection itself - get the delegate (the Oracle connection)
conn = ((DelegatingConnection)pc).getDelegate();
}
I still got the same problem. Finally I found the right answer:
The two classes are the same (oracle.sql.CLOB), but they are loaded by different classloader. This usually happens when you put your JDBC driver jars both in server’s and webapp’s libraries and use the jndi datasource; the db objects (like CLOB) are created by the server (using its classloader) so when the webapp tries to cast those objects it gets that error, even if everything works fine when not using casts; you probably would have the same issue if you try to cast the ResultSet to OracleResultSet. I would recommend to put the JDBC driver(s) jars ONLY in the server lib dir; In Tomcat it would be the commons directory.
So, It is not necessary to get the wrapped OracleConnection object. Just put the Oracle jdbc driver ONLY in the Tomcat\Common\lib directory.
Hope this helps if you meet the same trouble.
