I have two datasource with two schema in oracle, I am performing unittest but it is failing. I want if the second transaction failed then it should rollback the first trasaction. Below is my code.
package com.test.db;
import static org.junit.Assert.assertEquals;
import java.util.Date;
import javax.sql.DataSource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.AfterTransaction;
import org.springframework.test.context.transaction.BeforeTransaction;
import org.springframework.transaction.annotation.Transactional;
//@EnableTransactionManagement
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations="/META-INF/spring/data-source-context.xml")
public class MultipleDatasourceTests {
private JdbcTemplate jdbcTemplate;
private JdbcTemplate otherJdbcTemplate;
@Autowired
public void setDataSources(@Qualifier("dataSource") DataSource dataSource,
@Qualifier("otherDataSource") DataSource otherDataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
this.otherJdbcTemplate = new JdbcTemplate(otherDataSource);
}
@BeforeTransaction
public void clearData() {
jdbcTemplate.update("delete from T_ORACLE1");
otherJdbcTemplate.update("delete from T_ORACLE1");
}
@AfterTransaction
public void checkPostConditions() {
int count = jdbcTemplate.queryForInt("select count(*) from T_ORACLE1");
// This change was rolled back by the test framework
assertEquals(0, count);
count = otherJdbcTemplate.queryForInt("select count(*) from T_ORACLE1");
// This rolls back as well if the connections are managed together
assertEquals(0, count);
}
/**
* Vanilla test case for two inserts into two data sources. Both should roll
* back.
*
* @throws Exception
*/
@Transactional
@Test
public void testInsertIntoTwoDataSources() throws Exception {
jdbcTemplate.update("delete from T_ORACLE1");
otherJdbcTemplate.update("delete from T_ORACLE2");
int count = jdbcTemplate.update(
"INSERT into T_ORACLE1 (id,name,foo_date) values (?,?,null)", 0,
"foo");
assertEquals(1, count);
count = otherJdbcTemplate
.update(
"INSERT into T_ORACLE2 (id,operation,name,audit_date) values (?,?,?,?)",
1, "INSERT", "foo", new Date());
assertEquals(1, count);
}
/**
* Shows how to check the operation on the inner data source to see if it
* has already been committed, and if it has do something different, instead
* of just hitting a {@link DataIntegrityViolationException}.
*
* @throws Exception
*/
@Transactional
@Test
public void testInsertWithCheckForDuplicates() throws Exception {
int count = jdbcTemplate.update(
"INSERT into T_ORACLE1 (id,name,foo_date) values (?,?,null)", 0,
"foo");
assertEquals(1, count);
count = otherJdbcTemplate.update(
"UPDATE T_ORACLE2 set operation=?, name=?, audit_date=? where id=?",
"UPDATE", "foo", new Date(), 0);
if (count == 0) {
count = otherJdbcTemplate.update(
"INSERT into T_ORACLE2 (id,operation,name,audit_date) values (?,?,?,?)",
0, "INSERT", "foo", new Date());
}
assertEquals(1, count);
}
}
XML
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-2.5.xsd">
<context:annotation-config />
<context:component-scan base-package="com.test.*"/>
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@//localhost:1521/TESTDB1" />
<property name="username" value="ORACLE1"/>
<property name="password" value="ORACLE1"/>
</bean>
<bean id="otherDataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@//localhost:1521/TESTDB2" />
<property name="username" value="ORACLE2"/>
<property name="password" value="ORACLE2"/>
</bean>
<bean id="transactionManager" class="com.test.db.MultiTransactionManager">
<property name="transactionManagers">
<list>
<bean
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<bean
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="otherDataSource" />
</bean>
</list>
</property>
</bean>
</beans>
package com.test.db;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionException;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.AbstractPlatformTransactionManager;
import org.springframework.transaction.support.DefaultTransactionStatus;
public class MultiTransactionManager extends
AbstractPlatformTransactionManager {
private List<PlatformTransactionManager> transactionManagers = new ArrayList<PlatformTransactionManager>();
private ArrayList<PlatformTransactionManager> reversed;
public void setTransactionManagers(
List<PlatformTransactionManager> transactionManagers) {
this.transactionManagers = transactionManagers;
reversed = new ArrayList<PlatformTransactionManager>(
transactionManagers);
Collections.reverse(reversed);
}
@Override
protected void doBegin(Object transaction, TransactionDefinition definition)
throws TransactionException {
@SuppressWarnings("unchecked")
List<DefaultTransactionStatus> list = (List<DefaultTransactionStatus>) transaction;
for (PlatformTransactionManager transactionManager : transactionManagers) {
DefaultTransactionStatus element = (DefaultTransactionStatus) transactionManager
.getTransaction(definition);
list.add(0, element);
}
}
@Override
protected void doCommit(DefaultTransactionStatus status)
throws TransactionException {
@SuppressWarnings("unchecked")
List<DefaultTransactionStatus> list = (List<DefaultTransactionStatus>) status
.getTransaction();
int i = 0;
for (PlatformTransactionManager transactionManager : reversed) {
TransactionStatus local = list.get(i++);
try {
transactionManager.commit(local);
} catch (TransactionException e) {
logger.error("Error in commit", e);
// Rollback will ensue as long as rollbackOnCommitFailure=true
throw e;
}
}
}
@Override
protected Object doGetTransaction() throws TransactionException {
return new ArrayList<DefaultTransactionStatus>();
}
@Override
protected void doRollback(DefaultTransactionStatus status)
throws TransactionException {
@SuppressWarnings("unchecked")
List<DefaultTransactionStatus> list = (List<DefaultTransactionStatus>) status
.getTransaction();
int i = 0;
TransactionException lastException = null;
for (PlatformTransactionManager transactionManager : reversed) {
TransactionStatus local = list.get(i++);
try {
transactionManager.rollback(local);
} catch (TransactionException e) {
// Log exception and try to complete rollback
lastException = e;
logger.error("Error in rollback", e);
}
}
if (lastException!=null) {
throw lastException;
}
}
}
My pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.springsource.open</groupId>
<artifactId>spring-best-db-db</artifactId>
<version>2.0.0.CI-SNAPSHOT</version>
<packaging>jar</packaging>
<name>Spring Best Efforts DB-DB</name>
<description><![CDATA[Sample project showing multi DataSource transaction
processing with Spring using best efforts 1PC.
]]> </description>
<properties>
<maven.test.failure.ignore>true</maven.test.failure.ignore>
<spring.framework.version>4.1.4.RELEASE</spring.framework.version>
</properties>
<profiles>
<profile>
<id>strict</id>
<properties>
<maven.test.failure.ignore>false</maven.test.failure.ignore>
</properties>
</profile>
<profile>
<id>fast</id>
<properties>
<maven.test.skip>true</maven.test.skip>
</properties>
</profile>
</profiles>
<dependencies>
<dependency>
<groupId>hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>1.8.0.7</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>1.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.1</version>
<exclusions>
<exclusion>
<groupId>avalon-framework</groupId>
<artifactId>avalon-framework</artifactId>
</exclusion>
<exclusion>
<groupId>logkit</groupId>
<artifactId>logkit</artifactId>
</exclusion>
<exclusion>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>10.2.1.6</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.9</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.easymock</groupId>
<artifactId>easymock</artifactId>
<version>2.4</version>
<scope>test</scope>
</dependency>
<!-- Spring Dependencies -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.framework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.framework.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.framework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${spring.framework.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.framework.version}</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc8</artifactId>
<version>12.1.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.github.marcus-nl.btm/btm-spring -->
<dependency>
<groupId>com.github.marcus-nl.btm</groupId>
<artifactId>btm-spring</artifactId>
<version>3.0.0-mk1</version>
</dependency>
<dependency>
<groupId>javax</groupId>
<artifactId>javaee-api</artifactId>
<version>6.0</version>
</dependency>
</dependencies>
</project>
TABLE IN ORACLE1 database
create table T_ORACLE1 (
id integer not null primary key,
name varchar(80),
foo_date timestamp
);
Table in ORACLE2 database
create table T_ORACLE2 (
id integer not null primary key,
operation varchar(20),
name varchar(80),
audit_date timestamp
);
I googled to use BitronixTransactionManager but, did not get any clue how to configure for two datasource.
It seems we can use hibernate but, I don't want to use hibernate. I want either jdbctemplate with sql query or simple jdbc.
The error I am getting is
java.lang.IllegalStateException: Cannot activate transaction synchronization - already active at org.springframework.transaction.support.TransactionSynchronizationManager.initSynchronization(TransactionSynchronizationManager.java:270)
I am new to distributed transaction. Could you please help me on this? Is it possible to use two datasource but one transactionmanager?
