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 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;
        }
    }

}

<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
);

create table T_ORACLE2 (
    id integer not null primary key,
    operation varchar(20),
    name varchar(80),
    audit_date timestamp
);