What is the Spring 5 JDBC approach when nativeJdbcExtractor is needed?

Question

I have just upgraded Spring/SpringBoot dependencies and noticed that class JdbcTemplate does not have property "nativeJdbcExtractor" any more.

I was able to find the details and background: https://jira.spring.io/browse/SPR-14670

However I was not able to find the replacement configuration. I use commons-dbcp library and Spring classes like SimpleJdbcCall etc. I never deal with low level JDBC API, however if the vendor code needs its real Connection type (Oracle) the nativeJdbcExtractor settings ensured it will get it somewhere deep in Spring JDBC code (not my app. code). I´m not sure how I can address this by calling connection.unwrap() if I need Spring API to handle this automatically as it did it in past.

java.lang.ClassCastException: org.apache.commons.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection

Is that hidden somewhere in DataSource configuration? I have upgraded from commons-dbcp 1.4 to commons-dbcp2 but cannot find anything useful so far (BasicDataSource).

Update: Following thread is relevant but I cannot digest the answer I´m looking for since the Connection object is obtained within JdbcTemplate class and thus out of my control.

replacement for jdbc.support.nativejdbc remove in Spring 5

Update #2 - Stack Trace

Caused by: java.lang.ClassCastException: org.apache.commons.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection
at oracle.sql.TypeDescriptor.setPhysicalConnectionOf(TypeDescriptor.java:832)
at oracle.sql.TypeDescriptor.<init>(TypeDescriptor.java:586)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:224)
at org.springframework.data.jdbc.support.oracle.SqlArrayValue.createTypeValue(SqlArrayValue.java:90)
at org.springframework.jdbc.core.support.AbstractSqlTypeValue.setTypeValue(AbstractSqlTypeValue.java:60)
at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:293)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:232)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:147)
at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:200)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1048)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1104)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:414)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:397)
at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:193)

Update #3 - the code doing the cast (Oracle JDBC)

    public void setPhysicalConnectionOf(Connection var1) {
    this.connection = ((oracle.jdbc.OracleConnection)var1).physicalConnectionWithin();
}

Bourguiba

Answer 1

New:

It looks like you're using org.springframework.data.jdbc.support.oracle.SqlArrayValue from spring-data-jdbc-ext. The bug is there and the unwrapping should happen there.

Something like the following (untested)

protected Object createTypeValue(Connection conn, int sqlType, String typeName)
        throws SQLException { 
    return conn.unwrap(OracleConnection.class).createArray(typeName, values);
}

Regarding the JDBC driver:

You are using Java 8 or later since Spring 5 requires Java 8. Therefore you should use ojdbc8 (8 means Java 8). I strongly recommend using the latest 12.2.0.1 driver from http://www.oracle.com/technetwork/database/application-development/jdbc/downloads/index.html.

If you check the driver interoperability matrix from http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#01_02 you will see that this diver also works with older databases.

Old/Outdated:

In your code that performs the cast instead of casting call #unwrap. So instead of

OracleConnection oracleConnection = (OracleConnection) connection;

call

OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);

In the stack trace you see who is doing the cast, this will be in your code because SimpleJdbcCall and friends don't case to OracleConnection. The issue is not in JdbcTemplate but your code doing the cast.

Cakewalk

Thanks - But that is what is confusing for me - I have done that and I checked the stacktrace and saw the casting is NOT in my code, which I was not surprised as I never deal with raw Connection type. Please take a look at the stacktrace - you can see it is in JDBC driver code called by Spring code ... and this works well if "nativeJdbcExtractor" property can be used, but does not work with Spring 5+. – Bourguiba

Checking if there is JDBC driver update as I use ojdbc6 (11.2.0.4) which looks like pretty up-to-date. – Bourguiba

Upgrade to 12.2.0.1 did not help - this version has the same casting code. That is why I think Spring removal of the "nativeJdbcExtractor" feature was premature - the Oracle JDBC drivers are certified for Java 8 but their code does not use Connection.unwrap() ... not framework/pool library friendly – Bourguiba

Checking the Spring dependency (upgrade). – Bourguiba

Upgraded to spring-data-oracle 1.2.1-RELEASE and it also did not help as Spring is not unwrapping ... :( – Bourguiba

Of course, the bug is still there in the source spring-data-jdbc-ext you need to write a custom version of SqlArrayValue. – Cakewalk

I may report this bug and use the custom impl. as workaround in the meantime. Thanks Philippe for your help! – Bourguiba

@PhilippeMarschall Where does the method createTypeValue() belong to? I did not get how to define it. – Cainozoic

@LeosLiterak op was using org.springframework.data.jdbc.support.oracle.SqlArrayValue#createTypeValue Spring 6.1 will introduce a generic SqlArrayValue, if you use Oracle you will need something like github.com/ferstl/spring-jdbc-oracle/blob/master/src/main/java/… – Cakewalk