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;@Configurationpublic 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;@RestControllerpublic 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;@Servicepublic 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