Getting a handle to native Oracle Connection in Hi

Question

This is a follow-up to Executing native query with Hibernate 4.1

I've tried the options in the listed answer but we get errors and are unable to unwrap the proxied connection into the native connection which Oracle needs

Option 2 code

public void execute(Connection connection) throws SQLException {
    OracleConnection oracleConnection = connection.unwrap( OracleConnection.class );
    ...
}

Error

java.lang.reflect.UndeclaredThrowableException
ERROR [stderr] (http-localhost/127.0.0.1:8080-3) at $Proxy64.unwrap(Unknown Source)

Option 3 code

public void execute(Connection connection) throws SQLException {
    Connection unproxiedConnection = ( (JdbcWrapper<Connection>) connection ).getWrappedObject();
    ...
}

Error

java.lang.ClassCastException: org.jboss.jca.adapters.jdbc.jdk6.WrappedConnectionJDK6 cannot be cast to oracle.jdbc.OracleConnection
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:158) [ojdbc6.jar:11.2.0.1.0]
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:126) [ojdbc6.jar:11.2.0.1.0]
at com.xxx.massBookingUpdateParam(HoldoversDAO.java:312) [Business-1.jar:]
at com.xxx.saveHoldovers(HoldoversServiceBean.java:86) [Business-1.jar:]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0]
at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0]
at org.jboss.as.ee.component.ManagedReferenceMethodInterceptorFactory$ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptorFactory.java:72) [jboss-as-ee-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:374) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:129) [jboss-as-weld-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
at org.jboss.as.weld.ejb.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:137) [jboss-as-weld-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:36) [jboss-as-ee-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:36) [jboss-as-ee-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43) [jboss-as-ejb3-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47) [jboss-as-jpa-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor.processInvocation(EjbRequestScopeActivationInterceptor.java:74) [jboss-as-weld-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:53) [jboss-as-ee-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51) [jboss-as-ejb3-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-1.jar:1.1.1.Final-redhat-1]
at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:227) [jboss-as-ejb3-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
... 71 more

any suggestions?

I've also tried this option SessionFactory.openSession(Connection) in Hibernate 4 but that class does not get instantiated at all

Update:

Option 2 code works correctly. The problem was we had 2 versions of ojbc.jar - one as a Jboss module and one within the EAR, hence the Exception.

Answer 1

Option 2 code works correctly.

ie.

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

The problem was we had 2 versions of ojbc.jar - one as a Jboss module and one within the EAR, hence the ClassCastException.

As mentioned in the comments, this code also works

oracle.jdbc.driver.OracleConnection oc = (oracle.jdbc.driver.OracleConnection) connection.getMetaData().getConnection();

Answer 2

Try the following

I had encountered the same issue. We were using spring and it has a class called NativeJdbcExtractor. It has many implementations and the following one works for TomCat. There is a specific implementation for Jboss called the JBossNativeJdbcExtractor

<bean id="jdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"></bean>

In your DAO you can inject the bean and use the following method

protected NativeJdbcExtractor jdbcExtractor;
Connection conn=jdbcExtractor.getNativeConnection(oracleConnection);