spring boot 多数据源的XA事务(Druid+Atomikos)

介绍

在一个项目中,可能需要连接不同的数据库,那么就需要配置多数据源.
如果在一个操作中,需要请求不同的数据库来完成业务逻辑,那么就需要使用分布式事务来保证数据一致性

数据库配置

• 数据库1 : jtm

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET FOREIGN_KEY_CHECKS=1;

• 数据库2: jtm1

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET FOREIGN_KEY_CHECKS=1;

应用配置

pom.xml

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.6.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jta-atomikos</artifactId>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

XA数据源配置类

package com.example.demo.conf;

import com.alibaba.druid.pool.xa.DruidXADataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
import javax.sql.XADataSource;

@Configuration
public class MyDataSourceAutoConfiguration {
    @Primary
    @Bean(name = "dataSource1")
    public DataSource dataSource1() {
        AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
        ds.setXaDataSource(xaDataSource1());
        return ds;
    }

    @Bean(name = "dataSource2")
    public DataSource dataSource2() {
        AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
        ds.setXaDataSource(xaDataSource2());
        return ds;
    }

    XADataSource xaDataSource1() {
        DruidXADataSource xaDataSource = new DruidXADataSource();
        xaDataSource.setUrl("jdbc:mysql://localhost:3306/jtm?useUnicode=true&characterEncoding=UTF8&useSSL=false");
        xaDataSource.setUsername("root");
        xaDataSource.setPassword("root");
        return xaDataSource;
    }

    XADataSource xaDataSource2() {
        DruidXADataSource xaDataSource = new DruidXADataSource();
        xaDataSource.setUrl("jdbc:mysql://localhost:3306/jtm1?useUnicode=true&characterEncoding=UTF8&useSSL=false");
        xaDataSource.setUsername("root");
        xaDataSource.setPassword("root");
        return xaDataSource;
    }

    @Bean("first")
    JdbcTemplate first(@Qualifier("dataSource1") DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(dataSource);
        return jdbcTemplate;
    }

    @Bean("second")
    JdbcTemplate second(@Qualifier("dataSource2") DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(dataSource);
        return jdbcTemplate;
    }
}

测试

MyController

package com.example.demo.controller;

import com.example.demo.service.MyService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class MyController {

    @Autowired
    MyService myService;
    @GetMapping("/my/test")
    public Object myTest(@RequestParam("a1") Integer a1, @RequestParam("a2") Integer a2) {
        myService.myTest(a1, a2);
        return "ok";
    }
}

MyService

package com.example.demo.service;

public interface MyService {
    void myTest(Integer a1, Integer a2);
}

MyServiceImpl

package com.example.demo.service.impl;

import com.example.demo.service.MyService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class MyServiceImpl implements MyService {
    @Autowired
    @Qualifier("first")
    JdbcTemplate jdbcTemplate;

    @Autowired
    @Qualifier("second")
    JdbcTemplate jdbcTemplate1;

    @Transactional
    public void myTest(Integer a1, Integer a2) {
        jdbcTemplate.execute("INSERT INTO person(id, name, age) VALUES (" + a1 + ",'aaa', 18)");
        jdbcTemplate1.execute("INSERT INTO person(id, name, age) VALUES (" + a2 + ",'aaa', 18)");
    }
}

请求一

请求: http://localhost:8999/my/test?a1=1&a2=1
返回结果: ok
数据库结果:
- jtm

- jtm1

请求二

请求 : http://localhost:8999/my/test?a1=11&a2=1
返回结果:

{
    "timestamp": 1512632326397,
    "status": 500,
    "error": "Internal Server Error",
    "exception": "org.springframework.dao.DuplicateKeyException",
    "message": "StatementCallback; SQL [INSERT INTO person(id, name, age) VALUES (1,'aaa', 18)]; Duplicate entry '1' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'",
    "path": "/my/test"
}

数据库结果:
- jtm

- jtm1

Mybatis的XA实现

代码地址： Github