012.SpringBoot+MyBatis+Postgre+Druid+Atomikos实现多库事物

实现多库事物+SQL监控

POM引用

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.java</groupId>
    <artifactId>core</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>core</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.github.ulisesbocchio</groupId>
            <artifactId>jasypt-spring-boot-starter</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-spring-boot-starter</artifactId>
            <version>2.0.4</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jta-atomikos</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>com.github.ulisesbocchio</groupId>
                <artifactId>jasypt-maven-plugin</artifactId>
                <version>3.0.3</version>
            </plugin>
        </plugins>
    </build>

</project>

多库和Druid配置

server.port=8080
#单数据源
#spring.datasource.url=jdbc:postgresql://localhost:5432/javacore
#spring.datasource.username=postgres
#spring.datasource.password=asdf-1234
#spring.datasource.driver-class-name=org.postgresql.Driver

#多数据源
#spring.datasource.javacore.jdbc-url=jdbc:postgresql://localhost:5432/javacore
#spring.datasource.javacore.username=postgres
#spring.datasource.javacore.password=asdf-1234
#spring.datasource.javacore.driver-class-name=org.postgresql.Driver
#
#spring.datasource.postgres.jdbc-url=jdbc:postgresql://localhost:5432/postgres
#spring.datasource.postgres.username=postgres
#spring.datasource.postgres.password=asdf-1234
#spring.datasource.postgres.driver-class-name=org.postgresql.Driver

#mybatis.mapper-locations=classpath:mapper/*.xml

#连接池druid的配置信息+多数据源
spring.datasource.druid.javacore.url=jdbc:postgresql://localhost:5432/javacore
spring.datasource.druid.javacore.username=postgres
spring.datasource.druid.javacore.password=asdf-1234
spring.datasource.druid.javacore.driver-class-name=org.postgresql.Driver

spring.datasource.druid.postgres.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.druid.postgres.username=postgres
spring.datasource.druid.postgres.password=asdf-1234
spring.datasource.druid.postgres.driver-class-name=org.postgresql.Driver

spring.datasource.druid.initialSize=5
spring.datasource.druid.minIdle=5
spring.datasource.druid.maxActive=20
spring.datasource.druid.maxWait=60000
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
spring.datasource.druid.minEvictableIdleTimeMillis=300000
#spring.datasource.druid.validationQuery=SELECT 1 FROM DUAL
spring.datasource.druid.testWhileIdle=true
spring.datasource.druid.testOnBorrow=false
spring.datasource.druid.testOnReturn=false
spring.datasource.druid.poolPreparedStatements=true
spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize=20
#SQL检测打开
#spring.datasource.druid.filter.stat.log-slow-sql=true
#spring.datasource.druid.filter.stat.enabled=true
#spring.datasource.druid.filter.merge-sql=true
#spring.datasource.druid.filter.stat.slow-sql-millis=2000

spring.datasource.druid.filters=stat
#,wall,log4j,config

#druid的监控站点
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=true
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=123

Druid配置类

package com.javacore.config.db;

import com.alibaba.druid.pool.xa.DruidXADataSource;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;

import java.sql.SQLException;

@Data
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.druid")
public class DruidConfig {
    private String filters;
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private int maxWait;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private Boolean testWhileIdle;
    private Boolean testOnBorrow;
    private Boolean testOnReturn;
    private Boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;

    public DruidXADataSource getDruidXADataSource() throws SQLException {
        DruidXADataSource xa=new DruidXADataSource();
        xa.setFilters(this.getFilters());
        xa.setInitialSize(this.getInitialSize());
        xa.setMinIdle(this.getMinIdle());
        xa.setMaxActive(this.getMaxActive());
        xa.setMaxWait(this.getMaxWait());
        xa.setTimeBetweenEvictionRunsMillis(this.getTimeBetweenEvictionRunsMillis());
        xa.setMinEvictableIdleTimeMillis(this.getMinEvictableIdleTimeMillis());
        xa.setTestWhileIdle(this.getTestWhileIdle());
        xa.setTestOnBorrow(this.getTestOnBorrow());
        xa.setTestOnReturn(this.getTestOnReturn());
        xa.setPoolPreparedStatements(this.getPoolPreparedStatements());
        xa.setMaxPoolPreparedStatementPerConnectionSize(this.getMaxPoolPreparedStatementPerConnectionSize());
        return xa;
    }

}

数据库1配置类，引入Atomikos事物

package com.javacore.config.db;

import com.alibaba.druid.pool.xa.DruidXADataSource;
import com.atomikos.jdbc.AtomikosDataSourceBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;

/**
 * 主数据源配置类
 */
@Configuration
@MapperScan(basePackages = DataSourceJavacoreConfig.PACKAGE, sqlSessionFactoryRef = "javacoreSqlSessionFactory")
public class DataSourceJavacoreConfig {

    /**
     * mapper操作类扫描路径
     */
    static final String PACKAGE =  "com.javacore.mybatis.javacore.mapper";

    /**
     * mybatis-mapper 扫描路径
     */
    private static final String MAPPER_LOCATION = "classpath:mapper/javacore/*.xml";

    @Autowired
    private  DruidConfig druidConfig;
    /**
     * 创建名为 javacoreDataSource 的数据源
     */
    @Bean(name = "javacoreXADataSource")
    @Primary    //该注解的作用是，当有多个相同的Bean的时候，优先选择有该注解的Bean 配置多数据源，必须有一个主数据源
    @ConfigurationProperties(prefix = "spring.datasource.druid.javacore")
    public DruidXADataSource javacoreXADataSource() throws SQLException {
        DruidXADataSource xa=new DruidXADataSource();
        return druidConfig.getDruidXADataSource();
    }
    @Bean(name = "javacoreDataSource")
    @Primary
    public DataSource javacoreDataSource(@Qualifier("javacoreXADataSource") DruidXADataSource javacoreXADataSource) {
        //将本地事务注册到Atomikos全局事务
        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(javacoreXADataSource);
        xaDataSource.setUniqueResourceName("javacore");
        return xaDataSource;
    }

    @Bean(name = "javacoreTransactionManager")
    @Primary
    public DataSourceTransactionManager javacoreTransactionManager(
            @Qualifier("javacoreDataSource") DataSource javacoreDataSource
    ) {
        return new DataSourceTransactionManager(javacoreDataSource);
    }

    /**
     * 将名为 javacoreDataSource 的数据源注入到 SqlSessionFactory
     */
    @Bean(name = "javacoreSqlSessionFactory")
    @Primary
    public SqlSessionFactory javacoreSqlSessionFactory(@Qualifier("javacoreDataSource") DataSource javacoreDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(javacoreDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(DataSourceJavacoreConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

}

数据库2配置类，引入Atomikos事物

package com.javacore.config.db;

import com.alibaba.druid.pool.xa.DruidXADataSource;
import com.atomikos.jdbc.AtomikosDataSourceBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;

/**
 * 主数据源配置类
 */
@Configuration
@MapperScan(basePackages = DataSourcePostgresConfig.PACKAGE, sqlSessionFactoryRef = "postgresSqlSessionFactory")
public class DataSourcePostgresConfig {

    /**
     * mapper操作类扫描路径
     */
    static final String PACKAGE =  "com.javacore.mybatis.postgres.mapper";

    /**
     * mybatis-mapper 扫描路径
     */
    private static final String MAPPER_LOCATION = "classpath:mapper/postgres/*.xml";

    @Autowired
    private  DruidConfig druidConfig;
    /**
     * 创建名为 postgresDataSource 的数据源
     */
    @Bean(name = "postgresXADataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.postgres")
    public DruidXADataSource postgresXADataSource() throws SQLException {
        return druidConfig.getDruidXADataSource();
    }
    @Bean(name = "postgresDataSource")
    public DataSource postgresDataSource(@Qualifier("postgresXADataSource") DruidXADataSource postgresXADataSource)  {
        //将本地事务注册到Atomikos全局事务
        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(postgresXADataSource);
        xaDataSource.setUniqueResourceName("postgres");
        return xaDataSource;
    }

    @Bean(name = "postgresTransactionManager")
    public DataSourceTransactionManager postgresTransactionManager(
            @Qualifier("postgresDataSource") DataSource postgresDataSource
    ) {
        return new DataSourceTransactionManager(postgresDataSource);
    }

    /**
     * 将名为 postgresDataSource 的数据源注入到 SqlSessionFactory
     */
    @Bean(name = "postgresSqlSessionFactory")
    public SqlSessionFactory postgresSqlSessionFactory(@Qualifier("postgresDataSource") DataSource postgresDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(postgresDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(DataSourcePostgresConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

}

Service层测试

@Transactional
//模拟插入4条数据
public boolean Insert4Data(){
    //数据库1
    userMapperPrimary.insert("A", 1);
    userMapperPrimary.insert("B", 2);
    //手动报错
    int a = Integer.parseInt("aaaa");
    //数据库2
    userMapperSecondary.insert("C", 3);
    userMapperSecondary.insert("D", 4);
    return true;
}

测试结果就是，以上代码都不会入库，因为加了多库事物