Using OraclePreparedStatement with DBCP Connection

Question

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?

See related question: stackoverflow.com/questions/43985601/… — user46688
– user46688, Commented May 15, 2017 at 18:49

Ryan Stewart · Accepted Answer · 2011-09-20 14:17:57Z

2

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.

answered Sep 20, 2011 at 14:17

Ryan Stewart

130k22 gold badges183 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Maxx · Accepted Answer · 2014-09-23 15:54:13Z

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.

Marian · Accepted Answer · 2020-06-01 16:46:31Z

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))) {
    ...
    }

user1741202 · Accepted Answer · 2013-04-16 21:07:27Z

0

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.

answered Apr 16, 2013 at 21:07

user1741202

1211 gold badge1 silver badge6 bronze badges

Collectives™ on Stack Overflow

Using OraclePreparedStatement with DBCP Connection

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related