HikariCP with Oracle com.zaxxer.hikari.pool.HikariProxyCallableStatement cannot be cast to oracle.jdbc.OracleCallableStatement

Question

I using Hikari Pool and OracleCallableStatement:

My datasource:

<bean id="myDataSource" class="com.zaxxer.hikari.HikariDataSource">
      <property name="jdbcUrl" value="${my.oracle.url}"/>
      <property name="driverClassName" value="oracle.jdbc.pool.OracleDataSource"/>
      <property name="username" value="${my.oracle.user}"/>
      <property name="password" value="${my.oracle.password}"/>
</bean>

And I try make a request to Oracle: public List getProducts(int numbersMonths, Long initServiceId,

List<Long> serviceIds) throws SQLException {
        Connection cnn = null;
        OracleCallableStatement stm = null;
        ResultSet rs = null;
        List<ProductLink> res = new ArrayList<>();
        final String sql = sqlCust(sqlProducts);
        try {
            cnn = custDataSource.getConnection();
            stm = (OracleCallableStatement) cnn.prepareCall(sql);
            stm.setPlsqlIndexTable(1, serviceIds.toArray(), serviceIds.size(), serviceIds.size(), OracleTypes.BIGINT, 0);
            stm.registerOutParameter(2, OracleTypes.CURSOR);
            stm.setLong(3, initServiceId);
            stm.setInt(4, numbersMonths);
            stm.execute();
            rs = stm.getCursor(2);
            // do stuff
} catch (SQLException ex) {
            DbUtils.closeQuietly(cnn, stm, rs);
            throw ex;
        } finally {
            DbUtils.closeQuietly(cnn, stm, rs);
        }

But I have error om this line: stm = (OracleCallableStatement) cnn.prepareCall(sql);

java.lang.ClassCastException: com.zaxxer.hikari.pool.HikariProxyCallableStatement cannot be cast to oracle.jdbc.OracleCallableStatement

Also I have a error:

Failed to create instance of driver class oracle.jdbc.pool.OracleDataSource, trying jdbcUrl resolution
java.lang.ClassCastException: oracle.jdbc.pool.OracleDataSource cannot be cast to java.sql.Driver

on this line: cnn = custDataSource.getConnection();

What is wrong? Why I getting this errors?

the second error message (classcastexception) is due to a mis-configured driver class name: Please use `oracle.jdbc.OracleDriver` instead of `oracle.jdbc.pool.OracleDataSource` (it is not/an outdated driver class name) — xerx593, May 15 '19 at 13:50

score 3 · Accepted Answer · answered Aug 31 '18 at 18:32

3

In fact, since you are using a connection pool, the CallableStatement implementation is not from the driver, but from that connection pool instead.

Either you should find a way to access the core implementation (which I think is risky), or you should try to use the CallableStatement only, and not depend on Oracle implementation.

answered Aug 31 '18 at 18:32

Bruno Paulino

56
1
2

But, `pool` don't have method `setPlsqlIndexTable ` – All_Safe Aug 31 '18 at 18:43
2

I see. Well, in that case, you should unwrap your CallableStatement for that implementation. That may work. https://docs.oracle.com/javase/7/docs/api/java/sql/Wrapper.html#unwrap(java.lang.Class) Try `cnn.prepareCall(sql).unwrap(OracleCallableStatement.class)` – Bruno Paulino Sep 01 '18 at 18:14
How true this is? Will there be a drop in performance from using the pool because of this? – All_Safe Sep 02 '18 at 14:18
This is very true, dude. You see, the `unwrap` method is an official method for the Statement interface (from `java.sql`), which is a super interface for `CallableStatement`. If you look the Hikari CP's implementation, you will find out there is a recursion to find if the inner instance can be cast to the required interface. Meaning you should not have problem using this approach. – Bruno Paulino Sep 03 '18 at 13:03

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

Use CallableStatement instead

The interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs.

This is used by prepareCall method

Creates a CallableStatement object

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 31 '18 at 18:30

Ori Marko

56,308
23
131
233

How can I use CallableStatement if it doesn't have a method `setPlsqlIndexTable` – All_Safe Aug 31 '18 at 18:41

score 1 · Answer 3 · answered Aug 31 '18 at 19:10

1

Try replacing the line

            stm = (OracleCallableStatement) cnn.prepareCall(sql);

with

            stm = cnn.prepareCall(sql).unwrap(OracleCallableStatement.class);

answered Aug 31 '18 at 19:10

Luke Woodward

63,336
16
89
104

How true this is? Will there be a drop in performance from using the pool because of this? – All_Safe Sep 02 '18 at 14:18

HikariCP with Oracle com.zaxxer.hikari.pool.HikariProxyCallableStatement cannot be cast to oracle.jdbc.OracleCallableStatement

3 Answers3