CODEWITHSUNDEEP
javaoracle-databasecastingprepared-statement
BenjaminR
BenjaminR

Reputation: 53

Using OraclePreparedStatement with DBCP Connection

I'm trying to make a connection pool with the dbcp framework for my oracle server. I used this tutorial for the connection. The problem is to create a OraclePreparedStatement with this connection: 

Connection oracleCon;
OraclePreparedStatement o_stmt;
String sql = "INSERT INTO T002_metadata (T002_datacitexml,T002_version, T002_active)       VALUES (?,?,?) RETURNING T002_id INTO ?";


oracleCon = ConnectionManager.ds.getConnection();

o_stmt = ((OraclePreparedStatement) oracleCon.prepareStatement(sql));

After executing this, an exception is thrown.

org.apache.commons.dbcp.DelegatingPreparedStatement cannot be cast to oracle.jdbc.OraclePreparedStatement

Is there any possibility to cast the statement?

Upvotes: 2

Views: 3656

Answers (4)

Marian
Marian

Reputation: 140

I use an utility method

public static OraclePreparedStatement getOraclePreparedStatement(PreparedStatement stmt) {
        if (stmt instanceof OraclePreparedStatement) {
            return (OraclePreparedStatement) stmt;
        }
        if (stmt instanceof DelegatingPreparedStatement) {
            DelegatingPreparedStatement stmt1 = (DelegatingPreparedStatement) stmt;

            return (OraclePreparedStatement) stmt1.getInnermostDelegate();
        }
        System.out.println(stmt);
        throw new RuntimeException("unknown statement");
    }

Essentially it allows using the same code standalone (e.g. in tests) or in tomcat. I use it instead of casting, so the code does not get longer.

try (OraclePreparedStatement stmt = getOraclePreparedStatement(con.prepareStatement(SQL_INSERT_TREATMENT))) {
    ...
    }

Upvotes: 0

Maxx
Maxx

Reputation: 1453

I had the same problem today and solved it by changing Tomcat configuration as suggested here. I changed the context.xml file by changing the type and factory properties like this:

....
<Resource name="jdbc/dsName"
       auth="Container"
          type="oracle.jdbc.pool.OracleDataSource"
          factory="oracle.jdbc.pool.OracleDataSourceFactory"
       user="username"
       password="password"
       driverClassName="oracle.jdbc.driver.OracleDriver"
       url="jdbcurl"/>
....

also resource definition in web.xml file had to be changed

....
 <resource-ref>
    <description>Oracle datasource</description>
    <res-ref-name>jdbc/test</res-ref-name>
    <!-- <res-type>javax.sql.DataSource</res-type>-->
    <res-type>oracle.jdbc.pool.OracleDataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
....

no changes have been needed in my code.

Upvotes: 2

user1741202
user1741202

Reputation: 121

I f you're using Oracle, you can cast your connection to OracleConnection, and then cast your prepared statement to OraclePreparedStatement. What jdbc does is upcast the Oracle objects to standard sql objects, but they are still Oracle sql objects.

Upvotes: 0

Ryan Stewart
Ryan Stewart

Reputation: 128799

Not to an Oracle class, no. That's what JDBC is for. It's an API. Use java.sql.PreparedStatement only. By attempting to downcast, you violate polymorphism and break things like this, where a library is wrapping the real connection and statement to provide some additional services for you.

Upvotes: 2

Related Questions