非侵入式mybatis多数据库切换实践

本文链接：https://blog.csdn.net/a_c_c_a/article/details/106059976

环境：springboot+mybatis

数据库：mysql+oracle

需求：一个程序中，一部分数据从mysql中查，一部分数据从oracle中查。使用AOP来实现数据库动态切换

参考网上的mybatis动态数据源，并额外扩展使用AOP

步骤：

1.先定义不同的数据库连接类型，用来表示mysql数据库和oracle数据库，如果还有多个数据库，继续定义（这里cms表示oracle数据库，hms表示mysql数据库）

public enum  DataSourceTypeEnum {
    cms,hms
}

2.继承AbstractRoutingDataSource，实现我们自己的动态数据源类型

/**
 * 动态数据源
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    private static final  ThreadLocal<DataSourceTypeEnum> contextHolder = new ThreadLocal<>();

    public static void setDataSourceType(DataSourceTypeEnum type){
        contextHolder.set(type);
    }

    public static  DataSourceTypeEnum getDataSourceType(){
        return contextHolder.get();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return getDataSourceType();
    }
}

3.定义数据源配置类，在里面定义数据库连接1（cms），数据库连接2（hms），外加主数据源。

数据库连接1/2有两组实现，分别使用Druid连接池和Pooled连接池实现，由配置文件指定使用哪种。

主数据源传入数据库连接1和2 来完成构造，并设置默认数据库连接

/**
 * 数据库定义，通过配置项datasource.type，来切换数据库连接池
 */
@Configuration
public class DataSourceConfig {
    private static int initTimes = 0;

    @Autowired
    Environment environment;

    /**
     * 动态数据源，注入cms的数据库连接和hms的数据库连接
     */
    @Bean
    @Primary
    public DynamicDataSource dataSource(@Qualifier("cmsDataSource") DataSource cmsDataSource,
                                        @Qualifier("hmsDataSource") DataSource hmsDataSource) {
        assert initTimes==0;    //数据源只能初始化一次
        initTimes++;
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceTypeEnum.cms, cmsDataSource);
        targetDataSources.put(DataSourceTypeEnum.hms, hmsDataSource);

        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法
        dataSource.setDefaultTargetDataSource(cmsDataSource);// 默认的datasource设置为myTestDbDataSource

        return dataSource;
    }

    @Bean("cmsDataSource")
    @ConditionalOnProperty(value = "datasource.type", havingValue = "druid")
    public DataSource cmsDataSourceDruid() throws Exception {
        Properties props = new Properties();
        props.put("driverClassName", environment.getProperty("cms.db.driverClass"));
        props.put("url", environment.getProperty("cms.db.url"));
        props.put("username", environment.getProperty("cms.db.username"));
        props.put("password", environment.getProperty("cms.db.password"));
        props.put("initialPoolSize", environment.getProperty("cms.db.initialPoolSize"));
        props.put("initialMaxPoolSize", environment.getProperty("cms.db.initialMaxPoolSize"));
        props.put("initialMinPoolSize", environment.getProperty("cms.db.initialMinPoolSize"));
        return DruidDataSourceFactory.createDataSource(props);
    }
    @Bean("hmsDataSource")
    @ConditionalOnProperty(value = "datasource.type", havingValue = "druid")
    public DataSource hmsDataSourceDruid() throws Exception {
        Properties props = new Properties();
        props.put("driverClassName", environment.getProperty("hms.db.driverClass"));
        props.put("url", environment.getProperty("hms.db.url"));
        props.put("username", environment.getProperty("hms.db.username"));
        props.put("password", environment.getProperty("hms.db.password"));
        props.put("initialPoolSize", environment.getProperty("hms.db.initialPoolSize"));
        props.put("initialMaxPoolSize", environment.getProperty("hms.db.initialMaxPoolSize"));
        props.put("initialMinPoolSize", environment.getProperty("hms.db.initialMinPoolSize"));
        return DruidDataSourceFactory.createDataSource(props);
    }

    @Bean("cmsDataSource")
    @ConditionalOnProperty(value = "datasource.type", havingValue = "jdbc")
    public DataSource cmsDataSourceJdbc() throws Exception {
        PooledDataSource pooledDataSource = new PooledDataSource();
        pooledDataSource.setDriver(environment.getProperty("cms.db.driverClass"));
        pooledDataSource.setUrl(environment.getProperty("cms.db.url"));
        pooledDataSource.setUsername(environment.getProperty("cms.db.username"));
        pooledDataSource.setPassword(environment.getProperty("cms.db.password"));
        return pooledDataSource;
    }
    @Bean("hmsDataSource")
    @ConditionalOnProperty(value = "datasource.type", havingValue = "jdbc")
    public DataSource hmsDataSourceJdbc() throws Exception {
        PooledDataSource pooledDataSource = new PooledDataSource();
        pooledDataSource.setDriver(environment.getProperty("hms.db.driverClass"));
        pooledDataSource.setUrl(environment.getProperty("hms.db.url"));
        pooledDataSource.setUsername(environment.getProperty("hms.db.username"));
        pooledDataSource.setPassword(environment.getProperty("hms.db.password"));
        return pooledDataSource;
    }
}

4.编写mybatis配置类，传入我们定义的动态数据库

@Configuration
@MapperScan("demo.dao")
public class MybatisConfig {

    @Autowired
    DataSourceConfig dataSourceConfig;

    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("cmsDataSource") DataSource cmsDataSource,
                                               @Qualifier("hmsDataSource") DataSource hmsDataSource)
            throws Exception{
        SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
        fb.setDataSource(dataSourceConfig.dataSource(cmsDataSource,hmsDataSource));//多次调用dataSource，返回同一个bean
        fb.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:demo/dao/*.xml"));
        fb.setDatabaseIdProvider(getDatabaseIdProvider());
        return fb.getObject();
    }

    public DatabaseIdProvider getDatabaseIdProvider(){
        DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        properties.setProperty("Oracle","oracle");
        properties.setProperty("Mysql","mysql");
        properties.setProperty("Microsoft SQL Server","sqlserver");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }


}

5.到这里就已经可以切换数据库，正常使用了，例如：

@Service
public class IntegerateHelper {
    @Autowired
    IntegrateInfoMapper cmsMapper;    //mysql对应的sql语句
    @Autowired
    CtrlIntegrationInfoMapper hmsMapper;    //oracle对应的sql语句

    //默认的，这里是查询mysql库
    public IntegrateInfo queryIntegrateInfo(Short integrateId){
        IntegrateInfo integrateInfo = cmsMapper.selectByPrimaryKey(integrateId);
        return integrateInfo;
    }

    //进行数据库切换，查询oracle数据库，查询完，再切换回mysql数据库
    public CtrlIntegrationInfo queryCtrlVendorInfo(String integrateName){
        DynamicDataSource.setDataSourceType(DataSourceTypeEnum.hms);    
        CtrlIntegrationInfo integrationInfo = hmsMapper.selectByName(integrateName);
        DynamicDataSource.setDataSourceType(DataSourceTypeEnum.cms);    
        return integrationInfo;
    }
}

6.每次需要数据库切换，都要手动在代码前后添加两行代码，可以使用AOP来帮助我们完成切换

定义一个注解（注意：因为数据库切换不支持事务，所以需要声明成Propagation.NOT_SUPPORTED。如果不声明，在事务中进行数据库切换，会切换不成功，还是在默认数据库中执行）

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Transactional(propagation = Propagation.NOT_SUPPORTED)
public @interface ChooseHmsDatabase {
}

再将这个注解作为切面，定义一个around的AOP

@Aspect
@Component
public class ChangeDbAop {
    @Pointcut("@annotation(ChooseHmsDatabase)")
    public void pointcut(){}

    @Around("pointcut()")
    public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
        DynamicDataSource.setDataSourceType(DataSourceTypeEnum.hms);    //切换到hms的库
        try {
            return joinPoint.proceed();
        }finally {
            DynamicDataSource.setDataSourceType(DataSourceTypeEnum.cms);    //切换回cms的库
        }
    }
}

7.在需要数据库切换到oracle库的地方，注解上@ChooseHmsDatabase就行了

@Service
public class IntegerateHelper {
    @Autowired
    IntegrateInfoMapper cmsMapper;

    @Autowired
    CtrlIntegrationInfoMapper hmsMapper;

    public IntegrateInfo queryIntegrateInfo(Short integrateId){
        IntegrateInfo integrateInfo = cmsMapper.selectByPrimaryKey(integrateId);
        return integrateInfo;
    }

    //使用注解，就会自动在方法调用时切换到另一个数据库，方法调用后，自动切换会原来的数据库
    @ChooseHmsDatabase
    public CtrlIntegrationInfo queryCtrlVendorInfo(String integrateName){
        CtrlIntegrationInfo integrationInfo = hmsMapper.selectByName(integrateName);
        return integrationInfo;
    }

}

最后，附上gradle架包引用和数据库的配置

    compile("org.springframework.boot:spring-boot-starter")
    compile("org.springframework.boot:spring-boot-starter-aop")

    //mybatis相关架包
    compile("com.baomidou:mybatis-plus-boot-starter:2.3")
    compile group: 'org.mybatis.generator', name: 'mybatis-generator-core', version: '1.4.0'
    compile group: 'com.alibaba', name: 'druid', version: '1.1.22'
    compile group: 'com.github.pagehelper', name: 'pagehelper', version: '5.1.11'


    compile("com.microsoft.sqlserver:mssql-jdbc:6.4.0.jre8")
    compile group: 'com.oracle.ojdbc', name: 'ojdbc8', version: '19.3.0.0'
    compile group: 'mysql', name: 'mysql-connector-java', version: '5.1.18'

################## 选择数据库连接池实现
datasource.type=druid

#############   oracle的数据库连接配置     #############
cms.db.url=jdbc:oracle:thin:@127.0.0.1:1521:xe
cms.db.driverClass=oracle.jdbc.driver.OracleDriver
cms.db.username=user
cms.db.password=123456
cms.db.initialPoolSize=5
cms.db.initialMaxPoolSize=20
cms.db.initialMinPoolSize=5

##############   mysql的数据库连接配置   #####################
hms.db.url=jdbc:mysql://10.10.213.168:3306/test
hms.db.driverClass=com.mysql.jdbc.Driver
hms.db.username=root
hms.db.password=123456
hms.db.initialPoolSize=5
hms.db.initialMaxPoolSize=20
hms.db.initialMinPoolSize=5