Spring Boot(一)—— 多数据源事务处理

caroly 2020年07月05日 181次浏览

MyBatis配置文件中只有一个数据源的时候,按照正常的事务注解形式@Transaction是没有问题的。但是当配置文件中有多个数据源的时候,一个方法中需要操作两个数据源,单数据源的事务处理将不起作用。

多数据源事务处理

使用jta-atomikos进行事务管理。

『pom.xml』中引入依赖:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>

修改『application.properties』文件:

#mysql1
mysql1.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
mysql1.datasource.url=jdbc:mysql://127.0.0.1:3306/database1?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowMultiQueries=true
mysql1.datasource.username=root
mysql1.datasource.password=xxxx
mysql1.datasource.borrowConnectionTimeout=30
mysql1.datasource.loginTimeout=30
mysql1.datasource.maintenanceInterval=60
mysql1.datasource.maxIdleTime=60
mysql1.datasource.maxLifetime=20000
mysql1.datasource.maxPoolSize=25
mysql1.datasource.minPoolSize=3
mysql1.datasource.uniqueResourceName=mysql1DataSource

#mysql2
mysql2.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
mysql2.datasource.url=jdbc:mysql://127.0.0.1:3306/database2?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowMultiQueries=true
mysql2.datasource.username=root
mysql2.datasource.password=xxxx
mysql2.datasource.borrowConnectionTimeout=30
mysql2.datasource.loginTimeout=30
mysql2.datasource.maintenanceInterval=60
mysql2.datasource.maxIdleTime=60
mysql2.datasource.maxLifetime=20000
mysql2.datasource.maxPoolSize=25
mysql2.datasource.minPoolSize=3
mysql2.datasource.uniqueResourceName=mysql2DataSource

config/目录下添加『Mysql1Config』配置类,属性与『application.properties』中的数据源配置是一致的:

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;

@Data
@ConfigurationProperties(prefix = "mysql1.datasource")
public class Mysql1Config {
    private String url;
    private String username;
    private String password;
    private int minPoolSize;
    private int maxPoolSize;
    private int maxLifetime;
    private int borrowConnectionTimeout;
    private int loginTimeout;
    private int maintenanceInterval;
    private int maxIdleTime;
    private String testQuery;
    private String uniqueResourceName;
}

同理添加『Mysql2Config』配置类:

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;

@Data
@ConfigurationProperties(prefix = "mysql2.datasource")
public class Mysql2Config {
    private String url;
    private String username;
    private String password;
    private int minPoolSize;
    private int maxPoolSize;
    private int maxLifetime;
    private int borrowConnectionTimeout;
    private int loginTimeout;
    private int maintenanceInterval;
    private int maxIdleTime;
    private String testQuery;
    private String uniqueResourceName;
}

其中,@Data注解提供『get/set』方法,@ConfigurationProperties注解负责解析对应属性在『application.properties』文件中的前缀。


config/目录下添加『Mysql1DataSourceConfig』文件:

@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = Mysql1DataSourceConfig.PACKAGE, sqlSessionFactoryRef = "mysql1SqlSessionFactory")
public class Mysql1DataSourceConfig {
 
    // 精确到 mysql1 目录,以便跟其他数据源隔离
    static final String PACKAGE = "com.xxxx.xxxx.dao.mysql1";
    static final String MAPPER_LOCATION = "classpath*:mapper/mysql1/*.xml";
 
    @Value("${mysql1.datasource.url}")
    private String url;
 
    @Value("${mysql1.datasource.username}")
    private String user;
 
    @Value("${mysql1.datasource.password}")
    private String password;
 
    @Value("${mysql1.datasource.driver-class-name}")
    private String driverClass;

    @Bean(name = "mysql1SqlSessionFactory")
    @Primary
    public SqlSessionFactory mysql1SqlSessionFactory(@Qualifier("mysql1DataSource") DataSource mysql1DataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(mysql1DataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(Mysql1DataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

    @Primary
    @Bean("mysql1DataSource")
    public DataSource mysql1DataSource(Mysql1Config memberConfig) throws SQLException {
        MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
        mysqlXaDataSource.setUrl(memberConfig.getUrl());
        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
        mysqlXaDataSource.setPassword(memberConfig.getPassword());
        mysqlXaDataSource.setUser(memberConfig.getUsername());
        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
        //注册到全局事务
        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(mysqlXaDataSource);
        xaDataSource.setUniqueResourceName(memberConfig.getUniqueResourceName());
        xaDataSource.setMinPoolSize(memberConfig.getMinPoolSize());
        xaDataSource.setMaxPoolSize(memberConfig.getMaxPoolSize());
        xaDataSource.setMaxLifetime(memberConfig.getMaxLifetime());
        xaDataSource.setBorrowConnectionTimeout(memberConfig.getBorrowConnectionTimeout());
        xaDataSource.setLoginTimeout(memberConfig.getLoginTimeout());
        xaDataSource.setMaintenanceInterval(memberConfig.getMaintenanceInterval());
        xaDataSource.setMaxIdleTime(memberConfig.getMaxIdleTime());
        xaDataSource.setTestQuery(memberConfig.getTestQuery());
        return xaDataSource;
    }
}

同理添加『Mysql2DataSourceConfig』文件:

@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = Mysql2DataSourceConfig.PACKAGE, sqlSessionFactoryRef = "mysql2SqlSessionFactory")
public class Mysql2DataSourceConfig {

    // 精确到 mysql2 目录,以便跟其他数据源隔离
    static final String PACKAGE = "com.xxxx.xxxx.dao.mysql2";
    static final String MAPPER_LOCATION = "classpath*:mapper/mysql2/*.xml";

    @Value("${mysql2.datasource.url}")
    private String url;

    @Value("${mysql2.datasource.username}")
    private String user;

    @Value("${mysql2.datasource.password}")
    private String password;

    @Value("${mysql2.datasource.driver-class-name}")
    private String driverClass;

    @Bean(name = "mysql2SqlSessionFactory")
    public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("mysql2DataSource") DataSource clusterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(clusterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(Mysql2DataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }

    @Bean("mysql2DataSource")
    public DataSource mysql2DataSource(Mysql2Config orderConfig) throws SQLException {
        MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
        mysqlXaDataSource.setUrl(orderConfig.getUrl());
        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
        mysqlXaDataSource.setPassword(orderConfig.getPassword());
        mysqlXaDataSource.setUser(orderConfig.getUsername());
        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);

        //注册到全局事务
        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(mysqlXaDataSource);
        xaDataSource.setUniqueResourceName(orderConfig.getUniqueResourceName());
        xaDataSource.setMinPoolSize(orderConfig.getMinPoolSize());
        xaDataSource.setMaxPoolSize(orderConfig.getMaxPoolSize());
        xaDataSource.setMaxLifetime(orderConfig.getMaxLifetime());
        xaDataSource.setBorrowConnectionTimeout(orderConfig.getBorrowConnectionTimeout());
        xaDataSource.setLoginTimeout(orderConfig.getLoginTimeout());
        xaDataSource.setMaintenanceInterval(orderConfig.getMaintenanceInterval());
        xaDataSource.setMaxIdleTime(orderConfig.getMaxIdleTime());
        xaDataSource.setTestQuery(orderConfig.getTestQuery());
        return xaDataSource;
    }
}

在启动类中添加@EnableConfigurationProperties注解,解决『DataSource』无法注入问题:

@EnableConfigurationProperties({Mysql1Config.class, Mysql2Config.class})
@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

在操作两个数据源的方法或接口上,添加@Transactional注解。如果有一条『SQL』语句有问题,那么对两个数据源的操作都会回滚。

错误解决

启动项目后,可能会出现这个问题:

Fatal error occurred in the transaction branch - check your data for consist

原因是『mysql』的操作账号缺少权限,执行下面命令解决:

GRANT XA_RECOVER_ADMIN ON *.* TO 'root'@'%';

注:root为操作数据库的用户名


在添加『Mysql1Config』和『Mysql2Config』文件的时候可能会出现如下问题:

Spring Boot Configuration Annotation Processor not configured

原因是Spring Boot配置注解执行器没有配置,在『pom.xml』文件中引入依赖:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-configuration-processor</artifactId>
    <optional>true</optional>
</dependency>