多数据源切换

功能

• 多个数据源的切换
• 事务内的数据源切换和事务的回滚

环境依赖

• springboot + mybatis + atomikos

<!--分布式事务 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>
<!-- 集成Mysql -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.16</version>
</dependency>
<!-- 集成Oracle -->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.3</version>
</dependency>
<!-- 集成MyBatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.2</version>
</dependency>

多数据源配置

• properties

## 多数据源配置  自己实现
# 数据源1 MySQL
spring.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.master.jdbc-url=jdbc:mysql://ip:port/test?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
spring.datasource.master.username=#####
spring.datasource.master.password=$$$$$
# 数据源2 oracle
spring.datasource.slave.driverClassName = oracle.jdbc.driver.OracleDriver
spring.datasource.slave.jdbc-url =jdbc:oracle:thin:@localhost:1521/orcl
spring.datasource.slave.username =####
spring.datasource.slave.password =$$$$

• 数据源配置类

@Configuration//定义为配置类
public class DataSourceConfiguration {

    @Value("${spring.datasource.master.jdbc-url}")
    private String master_url;
    @Value("${spring.datasource.master.username}")
    private String master_username;
    @Value("${spring.datasource.master.password}")
    private String master_password;
    // 注入数据源2配置项
    @Value("${spring.datasource.slave.jdbc-url}")
    private String slave_url;
    @Value("${spring.datasource.slave.username}")
    private String slave_username;
    @Value("${spring.datasource.slave.password}")
    private String slave_password;


    @Bean(name = "master")//将返回值注册为组件
//    @ConfigurationProperties("spring.datasource.master")//指定配置前缀
    public DataSource master(){
        MysqlXADataSource dataSource = new MysqlXADataSource();
        dataSource.setUrl(master_url);
        dataSource.setUser(master_username);
        dataSource.setPassword(master_password);
        AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
        atomikosDataSourceBean.setXaDataSource(dataSource);
        atomikosDataSourceBean.setUniqueResourceName("master");
        return atomikosDataSourceBean;
    }

    @Bean(name = "slave")
//    @ConfigurationProperties("spring.datasource.slave")
    public DataSource slave(){
        try{
            OracleXADataSource dataSource = new OracleXADataSource();
            dataSource.setURL(slave_url);
            dataSource.setUser(slave_username);
            dataSource.setPassword(slave_password);
            AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
            atomikosDataSourceBean.setXaDataSource(dataSource);
            atomikosDataSourceBean.setUniqueResourceName("slave");
            return atomikosDataSourceBean;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }

    }

    @Bean(name = "dynamicDataSource")
    public DynamicDataSource dataSource(DataSource master, DataSource slave) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", master);
        targetDataSources.put("slave", slave);
        return new DynamicDataSource(master, targetDataSources);
    }

    /**
     * 分布式事务管理器
     */
    @Bean(name = "transactionManager")
    public JtaTransactionManager jtaTransactionManager() {
        UserTransactionManager userTransactionManager = new UserTransactionManager();
        UserTransaction userTransaction = new UserTransactionImp();
        return new JtaTransactionManager(userTransaction, userTransactionManager);
    }

}

• 定义动态数据源

/**
 * @Description:
 * 动态切换数据源主要依靠 AbstractRoutingDataSource。
 * 创建一个 AbstractRoutingDataSource 的子类，重写 determineCurrentLookupKey 方法，
 * 用于决定使用哪一个数据源。这里主要用到 AbstractRoutingDataSource 的两个属
 * 性 defaultTargetDataSource和targetDataSources。defaultTargetDataSource 默认目标数据源，
 * targetDataSources（map类型）存放用来切换的数据源。
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSources);
        // afterPropertiesSet()方法调用时用来将targetDataSources的属性写入resolvedDataSources中的
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDataSource();
    }
}

• 定义数据源注解，用于动态切换

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DS {
    String value();
}

动态切换

• 定义线程绑定数据源

public class DynamicDataSourceContextHolder {
    /***
     * @description: 使用ThreadLocal维护变量，TThreadLocal为每个使用该变量的县城提供独立的变量副本，
     * 所以每一个线程都可以独立改变自己的副本，而不会影响其他线程所对应的副本
     */
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();


    /***
     * @description: 设置数据源变量
     */
    public static void setDataSource(String dataSource) {
//        System.out.println("切换到 "+ dataSource+"数据源");
        CONTEXT_HOLDER.set(dataSource);
    }

    /***
     * @description: 获取数据源变量
     */
    public static String getDataSource() {
        return CONTEXT_HOLDER.get();
    }

    /**
     * 清空数据源变量
     */
    public static void clearDataSource() {
        CONTEXT_HOLDER.remove();
    }
}

• 定义AOP切面，切换数据源

/**
 * @Description: 通过拦截 @DS 注解，在其执行之前处理设置当前执行SQL的数据源的信息，
 * CONTEXT_HOLDER.set(dataSourceType)这里的数据源信息从我们设置的注解上面获取信息，
 * 如果没有设置就是用默认的数据源的信息。
 */
@Aspect
@Order(-1)
@Component
@EnableAspectJAutoProxy(exposeProxy = true,proxyTargetClass = true)
public class DataSourceAspect {

    //定义切点
    @Pointcut("@annotation(edson.MyTemplate.multiDataSource.DS)")
    public void dsPointCut() {

    }

    @Around("dsPointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Method method = signature.getMethod();
        DS dataSource = method.getAnnotation(DS.class);
        if (dataSource != null) {
            //通过注解值设置数据源
            DynamicDataSourceContextHolder.setDataSource(dataSource.value());
        }
        try {
            return point.proceed();
        } finally {
            // 销毁数据源 在执行方法之后
            DynamicDataSourceContextHolder.clearDataSource();
        }
    }
}

事务重写

• 重写transaction

public class MultiDataSourceTransaction implements Transaction {
    private static final Log LOGGER = LogFactory.getLog(MultiDataSourceTransaction.class);

    private final DataSource dataSource;

    private Connection mainConnection;

    private String mainDatabaseIdentification;

    private ConcurrentMap<String, Connection> otherConnectionMap;


    private boolean isConnectionTransactional;

    private boolean autoCommit;


    public MultiDataSourceTransaction(DataSource dataSource) {
        notNull(dataSource, "No DataSource specified");
        this.dataSource = dataSource;
        otherConnectionMap = new ConcurrentHashMap<>();
        mainDatabaseIdentification=DynamicDataSourceContextHolder.getDataSource();
    }


    /**
     * {@inheritDoc}
     */
    @Override
    public Connection getConnection() throws SQLException {
        String databaseIdentification = DynamicDataSourceContextHolder.getDataSource();
        if (databaseIdentification.equals(mainDatabaseIdentification)) {
            if (mainConnection != null) return mainConnection;
            else {
                openMainConnection();
                mainDatabaseIdentification =databaseIdentification;
                return mainConnection;
            }
        } else {
            if (!otherConnectionMap.containsKey(databaseIdentification)) {
                try {
                    Connection conn = dataSource.getConnection();
                    otherConnectionMap.put(databaseIdentification, conn);
                } catch (SQLException ex) {
                    throw new CannotGetJdbcConnectionException("Could not get JDBC Connection", ex);
                }
            }
            return otherConnectionMap.get(databaseIdentification);
        }

    }


    private void openMainConnection() throws SQLException {
        this.mainConnection = DataSourceUtils.getConnection(this.dataSource);
        this.autoCommit = this.mainConnection.getAutoCommit();
        this.isConnectionTransactional = DataSourceUtils.isConnectionTransactional(this.mainConnection, this.dataSource);

        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(
                    "JDBC Connection ["
                            + this.mainConnection
                            + "] will"
                            + (this.isConnectionTransactional ? " " : " not ")
                            + "be managed by Spring");
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void commit() throws SQLException {
        if (this.mainConnection != null && !this.isConnectionTransactional && !this.autoCommit) {
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug("Committing JDBC Connection [" + this.mainConnection + "]");
            }
            this.mainConnection.commit();
            for (Connection connection : otherConnectionMap.values()) {
                connection.commit();
            }
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void rollback() throws SQLException {
        if (this.mainConnection != null && !this.isConnectionTransactional && !this.autoCommit) {
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug("Rolling back JDBC Connection [" + this.mainConnection + "]");
            }
            this.mainConnection.rollback();
            for (Connection connection : otherConnectionMap.values()) {
                connection.rollback();
            }
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void close() throws SQLException {
        DataSourceUtils.releaseConnection(this.mainConnection, this.dataSource);
        for (Connection connection : otherConnectionMap.values()) {
            DataSourceUtils.releaseConnection(connection, this.dataSource);
        }
    }

    @Override
    public Integer getTimeout() throws SQLException {
        return null;
    }

}

• 重写TransactionFactory

@Component
public class MultiDataSourceTransactionFactory extends SpringManagedTransactionFactory {
    @Override
    public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {
        return new MultiDataSourceTransaction(dataSource);
    }
}

mybatis配置类

• 设置为动态数据源
• 设置事务工厂为重写的TransactionFactory

@Configuration
@MapperScan(value = "edson.MyTemplate.dao", sqlSessionFactoryRef = "sqlSessionFactory")
public class MybatisConfiguration {

    @Autowired  //自动装配
    @Qualifier("dynamicDataSource") // 指定装配的名称
    private DataSource db;
    @Autowired
    private MultiDataSourceTransactionFactory multiDataSourceTransactionFactory;

    @Bean
    @Primary
    public SqlSessionFactory sqlSessionFactory() throws Exception{
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(db);//设置动态数据源
        //设置事务（重写）
        sqlSessionFactoryBean.setTransactionFactory(multiDataSourceTransactionFactory);
        sqlSessionFactoryBean//指定mapper文件路径
                .setMapperLocations(
                        new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

}

示例

@Service
public class UserService {

    @Autowired
    private UserDao userDao;

    @Transactional(rollbackFor = Exception.class)
    public void save(){


        //动态代理  解决service内部方法调用aop失效问题
        UserService aopUserService =  ((UserService)AopContext.currentProxy());

        User master = aopUserService.selectMasterUserById("1");
        master.setName("cha012210000");
        aopUserService.updateMaster(master);


        User slave = master;
        slave.setId(1);
        slave.setName("change failfffffvvvds");
        aopUserService.updateSlave(slave);


    }

    @DS("slave")
    public User selectSlaveUserById(String id){
        return userDao.selectById(id);
    }

    @DS("master")
    public User selectMasterUserById(String id){
        return userDao.selectById(id);
    }

    @DS("master")
    public void updateMaster(User user){
         userDao.update(user);
    }

    @DS("slave")
    public void updateSlave(User user){
        System.out.println("---开始updateSlave----");
        userDao.update(user);
        System.out.println("---结束updateSlave----");
    }

//    @DS("slave")
    public void insertSlave(User user){
        Integer res = userDao.insert(user);
    }


}

问题及解决方案

同一service中A方法内调用B、C方法导致数据源切换失败

• 问题重现

public void save(){//方法A
    User master = selectMasterUserById("1");//方法B
    master.setName("cha012210000");
    updateMaster(master);
    
    
    User slave = master;
    slave.setId(1);
    slave.setName("change failfffffvvvds");
    updateSlave(slave);//方法C
}

• 解决办法

1. 在AOP切面类暴露代理类：@EnableAspectJAutoProxy(exposeProxy = true,proxyTargetClass = true)
2. 获取到当前的代理类AopContext.currentProxy()，调用代理类的方法；此时数据源AOP才会切入

//动态代理  解决service内部方法调用aop失效问题
UserService aopUserService =  ((UserService)AopContext.currentProxy());

User master = aopUserService.selectMasterUserById("1");
master.setName("cha012210000");
aopUserService.updateMaster(master);


User slave = master;
slave.setId(1);
slave.setName("change failfffffvvvds");
aopUserService.updateSlave(slave);

使用@Transactional后数据源切换失败

• 解决办法

重写transaction和transactionFactory(参考事务重写和mybatis配置类)