How to bind the unWrapped native connection object to Spring @Transactional platformTransactionManager

[vka255]

Tomcat 8 Web server with Bitronix Transaction Manager as Distributed Transaction Manager
Below are the properties for datasource :
resource.ds1.className=oracle.jdbc.xa.client.OracleXADataSource
resource.ds1.uniqueName=jdbc/oracledb
resource.ds1.minPoolSize=0
resource.ds1.maxPoolSize=10
resource.ds1.driverProperties.user=user1
resource.ds1.driverProperties.password=user2
resource.ds1.driverProperties.URL=jdbc:oracle:thin:@//URL:1521/SCHEMA
resource.ds1.allowLocalTransactions=true
resource.ds1.shareTransactionConnections=false
resource.ds1.localAutoCommit=true
resource.ds1.ignoreRecoveryFailures=false
resource.ds1.automaticEnlistingEnabled=true
resource.ds1.applyTransactionTimeout=true

Below is the bean configuration for datasource in Spring:
@bean(name = "dataSource", initMethod = "init", destroyMethod = "close")
public DataSource dataSource() throws Exception {
JndiDataSourceLookup dataSourceLookup = new JndiDataSourceLookup();
DataSource ds = dataSourceLookup.getDataSource("java:comp/env/jdbc/oracledb");
return ds;
}
@bean(name = "bitronixTransactionManager", destroyMethod = "shutdown")
public UserTransaction bitronixTransactionManager() throws NamingException {
JndiObjectFactoryBean jndiObjectFactoryBean = new JndiObjectFactoryBean();
return (UserTransaction) jndiObjectFactoryBean.getJndiTemplate().lookup("java:comp/UserTransaction");
}

@Bean(name = "platformTransactionManager")
@DependsOn({ "bitronixTransactionManager" })
public PlatformTransactionManager platformTransactionManager() throws Throwable {
	JtaTransactionManager jtaTransactionManager = new JtaTransactionManager(bitronixTransactionManager());
	jtaTransactionManager.setRollbackOnCommitFailure(true);
	jtaTransactionManager.setAllowCustomIsolationLevels(true);
	jtaTransactionManager.setGlobalRollbackOnParticipationFailure(true);
	return jtaTransactionManager;
}

Below is my method in one of the classes
publi class Test{

@Autowired
DataSource dataSource;
@transactional(value = "platformTransactionManager", propagation = Propagation.REQUIRED)
public void testTransaction() {
throws Exception {
Connection conn = DataSourceUtils.getConnection(dataSource);
Connection connectionToUse = conn.unwrap(java.sql.Connection.class);
//1. create a statement using connectionToUse and write a record to the database
//2. have some JMS related operations to publish which I dont have issue to publish. everything works fine
throw new RuntimeException();
//3. after throwing an exception the write operation performed by connectinToUse object should be rolled back but in this case, it's not getting rolled back,
}
}

not able to bind the unwrapped connection object to the existing transaction.

[lorban]

I do see a few things that do look suspicious to me:

1. Why do you need to unwrap the connection? The reason why the BTM pool wraps them is to intercept your statement creation calls to be able to enlist the connection into the transaction, i.e.: know what connection participates into what transaction. By using the unwrapped connection directly you bypassed this logic so BTM is unaware of what's going on. Don't do that.

2. Since you're using both JMS and JDBC in the same method together with a transaction manager I guess that you're willing to create a transaction that can either commit or rollback both atomically. If you don't create a BTM JMS connection pool, your JMS connections won't participate in the same transaction as your JDBC connections do, so rolling back the transaction will only rollback your JDBC work.

3. The Spring @transaction annotation does not rollback on exception by default, at least as far as I remember. You have to parameterize the annotation with rollbackFor=Throwable.class fr instance to make that happen.

[vka255]

I need the native connection to create an oracle array which is only available through oracle.jdbc.OracleConnection.
Example : Array array1= ((oracle.jdbc.OracleConnection) conn).createOracleArray("VARCHAR2", arrayObject);

[lorban]

Fine, create the array with the unwrapped connection object, but do everything else with the wrapped one.

[jonesde]

Agreed. This is true of any connection pool, especially in a transaction manager... ie the wrapped Connection should be used for everything possible, and standard JDBC API methods as much as possible too. It is pretty easy, and common practice, to unwrap the Connection only in very specific code blocks when non-standard JDBC functionality is needed.

[vka255]

Thanks @lorban and @jonesde this works fine. the only issue which i see now is, I need to call a Stored Procedure (SP) from java callablestatement passing the arrayobject as one parameter.

1. with Drivermanager.getconnection of oracl connection the SP returns success .
2. with poolingdatasource.getconnection() at runtime invoked from my server the SP returns failure.

Where exactly could the issue be? java or SP?

[lorban]

No idea, could be both.

You could try to unwrap the callable statement before executing it to see if that makes any difference. But it could very well be that your SP does something that is forbidden in a XA transaction context.

[vka255]

approach1:
OracleConnection nativeConnection = conn.unwrap(OracleConnection.class);
java.sql.Array array1= nativeConnection.createOracleArray("VARCHAR",arrayName);
CallableStatement stmt = conn.prepareCall({"call SP1(?,?)"}); // This is java.sql.Connection which is bound to BTM

In this approach, SP returns ERROR.

Approach2:
OracleConnection nativeConnection = conn.unwrap(OracleConnection.class);
java.sql.Array array1= nativeConnection.createOracleArray("VARCHAR",arrayName);
CallableStatement stmt = nativeConnection.prepareCall({"call SP1(?,?)"}); // This is nativeConnection

In this approach, SP returns Success.(But the problem with this approach is the callable statement is not bound to java.sql.connection which we got from poolingdatasource )

[lorban]

That doesn't invalidate what I've said: it could very well be that your SP does something that is forbidden in a XA transaction context.

As an experiment, you could try to force enlistment of the XAResource and work with the native connection. Simply execute SELECT 1 FROM DUAL using the wrapping connection right before using the unwrapped one should make sure the connection is bound to the XA transaction's context and should allow you to freely use the unwrapped connection.

[vka255]

Thanks @lorban , Issue fixed. COMMIT was inside the Procedure which shouldn't be

[vka255]

one last thing. do we need to close the connection which we unwrapped for performing the native connection operations?

[lorban]

Definitely not, but you do have to close the wrapping one.

[vka255]

got it!