【后端】DynasticDataSource实现多数据源配置

学习视频地址

1. 使用场景

1.1 业务复杂（数据量大）

数据分布在不同的数据库,数据库拆了,应用没拆,一个公司多个子项目,各用各的数据库,设计数据共享.

1.2读写分离

为了解决数据库的读性能瓶颈,(读比写性能更高,写锁会影响读阻塞,从而影响读的性能)
很多数据库用有主从架构,也就是,一台

2.配置单一数据源

pg:

<dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.3.8</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=xxxx
spring.datasource.password=xxxxxx
spring.datasource.url=jdbc:postgresql://xxxxxx:5000/xxxx?currentSchema=xxxx
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.configuration.map-underscore-to-camel-case=true
#mybatis.type-aliases-package=com.example.multidatasource6.mapper
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
#logging.level.com.example.multidatasource6.mapper=debug

<select id="getStaffInfo" resultType="java.util.Map">
      select * from bs_staff_dict limit 1
</select>

@SpringBootTest
class MultiDatasource6ApplicationTests {
    @Resource
    PgTestMapper pgTestMapper;

    @Test
    void contextLoads() {
        Map<String, String> staffInfo = pgTestMapper.getStaffInfo();
        System.out.println(staffInfo);
    }

}

3. 配置多数据源

3.1 使用Datasource

引入druid依赖.
 <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

3.1.1 首先创建单一数据源配置

@Configuration
public class DataSourceConfig {

    //底层会自动拿到spring.datasource中的配置,创建一个DruidDataSource
    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource(){
        return DruidDataSourceBuilder.create().build();
    }
}

配置完后即可使用,效果等同于默认配置

3.1.2 多数据源配置(较粗糙版本)

@Configuration
public class DataSourceConfig {

    //底层会自动拿到spring.datasource中的配置,创建一个DruidDataSource
    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource1")
    public DataSource dataSource1(){
        return DruidDataSourceBuilder.create().build();
    }

    //底层会自动拿到spring.datasource中的配置,创建一个DruidDataSource
    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource2")
    public DataSource dataSource2(){
        return DruidDataSourceBuilder.create().build();
    }
}

重写getConnection

@Component
@Primary //主要数据源bean,重要,否则启动会失败;因为dataSource1 ,因为dataSource2,DynamicDataSource会是3个数据源
public class DynamicDataSource implements DataSource {

    //当前使用的数据源标识
    public static ThreadLocal<String> name = new ThreadLocal<>();


    @Resource(name = "dataSource1")
    DataSource dataSource1;

    @Resource(name = "dataSource2")
    DataSource dataSource2;

    @Override
    public Connection getConnection() throws SQLException {
        if (name.get().equals("W")) {
            return dataSource1.getConnection();
        } else {
            return dataSource2.getConnection();
        }
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return null;
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {

    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {

    }

    @Override
    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }
}

调用测试

@SpringBootTest
class MultiDatasource6ApplicationTests {
    @Resource
    PgTestMapper pgTestMapper;

    @Test
    void contextLoads() {
        DynamicDataSource.name.set("W");
        Map<String, String> staffInfo = pgTestMapper.getStaffInfo();
        System.out.println(staffInfo);
    }

}

问题:
1.只实现了getConnection(),其他方法没有实现,当框架调用其他方法时,可能报错
2.数据源标识设置代码耦合性过高,如将切换数据源放在了代码中,对后期维护造成很大麻烦.

3.2 使用AbstraceRoutingDataSource

targetDataSources 所有数据源
defaultTargetDataSource 默认数据源
resolvedDataSources=targetDataSources,负责最后的数据源
配置

@Component
@Primary //主要数据源bean,重要,否则启动会失败;因为dataSource1 ,因为dataSource2,DynamicDataSource会是3个数据源
public class DynamicDataSource2 extends AbstractRoutingDataSource {

    //当前使用的数据源标识
    public static ThreadLocal<String> name = new ThreadLocal<>();


    @Resource(name = "dataSource1")
    DataSource dataSource1;

    @Resource(name = "dataSource2")
    DataSource dataSource2;


    //返回当前数据源标识
    @Override
    protected Object determineCurrentLookupKey() {
        return name.get();

    }
    @Override
    public void afterPropertiesSet() {

        //为targetDataSources 初始化所有数据源
        HashMap<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("W", dataSource1);
        targetDataSources.put("R", dataSource2);
        super.setTargetDataSources(targetDataSources);
        // 为defaultTargetDataSource 设置默认的数据源
        super.setDefaultTargetDataSource(dataSource1);
        super.afterPropertiesSet();
    }
}

3.2.1 测试（解决问题1）

@SpringBootTest
class MultiDatasource6ApplicationTests {
    @Resource
    PgTestMapper pgTestMapper;

    @Test
    void contextLoads() {
        DynamicDataSource2.name.set("R");
        Map<String, String> staffInfo = pgTestMapper.getStaffInfo();
        System.out.println(staffInfo);
    }

}

3.2.2 （解决问题2）

3.3.2.1 AOP方式 (不同业务）

@Aspect
@Component
public class SpringAop  {
    @Pointcut(value = "@annotation(com.example.multidatasource6.abstractRoutingDataSource.aop.PgDataSourceAnno)")
    public void pgAop() {
    }

    @Pointcut(value = "@annotation(com.example.multidatasource6.abstractRoutingDataSource.aop.MysqlDataSourceAnno)")
    public void mySqlAop() {
    }

    @Around("pgAop()")
    public Object pgAopAround(ProceedingJoinPoint point) throws Throwable {
        DynamicDataSource2.name.set("W");
        Object proceed = point.proceed();
        return proceed;
    }
    @Around("mySqlAop()")
    public Object mySqlAopAround(ProceedingJoinPoint point) throws Throwable {
        DynamicDataSource2.name.set("R");
        Object proceed = point.proceed();
        return proceed;
    }
}

测试

@SpringBootTest
class MultiDatasource6ApplicationTests {
    @Resource
    PgTestMapper pgTestMapper;

    @Resource
    SelectStaff selectStaff;
    @Test
    void contextLoads() {
//        DynamicDataSource2.name.set("R");
        System.out.println(selectStaff.getInfo());
        System.out.println(selectStaff.getInfo2());
    }

}

3.3.2.2 mybaits插件 （只适用于读写分离的方式）

@Component
@Intercepts(
        {
                @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
        }
)
public class DynamicDataSourcePlugin implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        // 拿到当前方法(update、query)所有参数
        Object[] objects = invocation.getArgs();

        // MappedStatement 封装CRUD所有的元素和SQL
        MappedStatement ms = (MappedStatement) objects[0];

        // 读方法
        if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
            DynamicDataSource2.name.set("R");
        } else {
            // 写方法
            DynamicDataSource2.name.set("W");
        }

        return invocation.proceed();
    }
}

测试

@SpringBootTest
class MultiDatasource6ApplicationTests {
    @Resource
    PgTestMapper pgTestMapper;

    @Test
    void contextLoads() {
//        DynamicDataSource2.name.set("R");
        Map<String, String> staffInfo = pgTestMapper.getStaffInfo();
        System.out.println(staffInfo);
    }

}

3.3 使用多个mybatis框架（无需切换数据源，会自动使用对应的数据源）

目录结构：

mapper类放在2个不同文件夹，否则会报错，mapper.xml也放在2个文件夹

@Configuration
@MapperScan(basePackages = "com.example.multdatasource6_multi_mybatis.pgmapper",
sqlSessionFactoryRef = "pgSqlSessionFactory")
public class PgDataSourceConfig {

    //底层会自动拿到spring.datasource中的配置,创建一个DruidDataSource
    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource1")
    public DataSource dataSource1(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
//    @Primary
    public SqlSessionFactory pgSqlSessionFactory(@Qualifier("dataSource1") DataSource dataSource1) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource1);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:pgMapper/*.xml"));
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setLogImpl(StdOutImpl.class);
        sqlSessionFactoryBean.setConfiguration(configuration);
        return sqlSessionFactoryBean.getObject();
    }

}

@Configuration
@MapperScan(basePackages = "com.example.multdatasource6_multi_mybatis.mysqlmapper",
        sqlSessionFactoryRef = "mySqlSqlSessionFactory")
public class MySqlDataSourceConfig {


    //底层会自动拿到spring.datasource中的配置,创建一个DruidDataSource
    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource2")
    public DataSource dataSource2(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
//    @Primary
    public SqlSessionFactory mySqlSqlSessionFactory(@Qualifier("dataSource2") DataSource dataSource2) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource2);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mySqlMapper/*.xml"));
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setLogImpl(StdOutImpl.class);
        sqlSessionFactoryBean.setConfiguration(configuration);
        return sqlSessionFactoryBean.getObject();
    }
}

@SpringBootApplication
public class MultDatasource6MultiMybatisApplication {

    public static void main(String[] args) {
        SpringApplication.run(MultDatasource6MultiMybatisApplication.class, args);
    }

}

测试：

@SpringBootTest
class MultDatasource6MultiMybatisApplicationTests {

    @Resource
    PgTestMapper pgTestMapper;

    @Resource
    MySqlTestMapper mySqlTestMapper;
    @Test
    void contextLoads() {
//        DynamicDataSource2.name.set("w");
        System.out.println(pgTestMapper.getStaffInfo());
        System.out.println(mySqlTestMapper.getStaffInfo());
    }

}

4.多数据源事务控制 （基于多个mybatis的形式，其他形式比较复杂，请查询其他资料）

在多数据源下，由于涉及到数据库的多个读写，一旦发生异常，就可能会导致数据不一致的情况，在这种情况希望使用事务进行回退。
但是Spring的声明式事务在一次请求线程中，只能使用一个数据源进行控制。

但是对于多数据源库：
1.单一事务管理器（TransactionManager)无法切换数据源，需要配置多个TransactionManager
2.@Transactionnal 是无法管理多个数据源的，如果向真正实现多数据源事务控制，肯定是需要分布式事务。这里学习多源数据库事务控制的一种变通方式。

开启事务注解：

@EnableTransactionManagement

4.1 使用单一事务管理器(配置多个事务管理器)

配置事务管理器：

@Configuration
@MapperScan(basePackages = "com.example.multdatasource6_multi_mybatis.pgmapper",
        sqlSessionFactoryRef = "pgSqlSessionFactory")
public class PgDataSourceConfig {

    //底层会自动拿到spring.datasource中的配置,创建一个DruidDataSource
    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource1")
    public DataSource dataSource1() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
//    @Primary
    public SqlSessionFactory pgSqlSessionFactory(@Qualifier("dataSource1") DataSource dataSource1) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource1);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:pgMapper/*.xml"));
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setLogImpl(StdOutImpl.class);
        sqlSessionFactoryBean.setConfiguration(configuration);
        return sqlSessionFactoryBean.getObject();
    }

    //配置事务管理器
    @Bean
    public DataSourceTransactionManager transactionManager1(@Qualifier("dataSource1") DataSource dataSource1) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource1);
        return dataSourceTransactionManager;
    }

}

@Configuration
@MapperScan(basePackages = "com.example.multdatasource6_multi_mybatis.mysqlmapper",
        sqlSessionFactoryRef = "mySqlSqlSessionFactory")
public class MySqlDataSourceConfig {


    //底层会自动拿到spring.datasource中的配置,创建一个DruidDataSource
    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource2")
    public DataSource dataSource2(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
//    @Primary
    public SqlSessionFactory mySqlSqlSessionFactory(@Qualifier("dataSource2") DataSource dataSource2) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource2);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mySqlMapper/*.xml"));
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setLogImpl(StdOutImpl.class);
        sqlSessionFactoryBean.setConfiguration(configuration);
        return sqlSessionFactoryBean.getObject();
    }

    //配置事务管理器
    @Bean
    public DataSourceTransactionManager transactionManager2(@Qualifier("dataSource2") DataSource dataSource2) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource2);
        return dataSourceTransactionManager;
    }
}

测试

 @Transactional(value = "transactionManager2")//只对mysql库生效,而且必须指定,否则报错,因为有多个事务管理器
 //这种方式只能针对一个事务管理器进行控制,无法完成多数据源控制
    @Test
    void contextLoads() {
		pgTestMapper.updateStaffInfo("");
        mySqlTestMapper.updateStaffInfo("");
        int a=1/0;
    }

4.2 使用多个事务管理器(配置多个事务管理器)

4.2.1 spring编程式事务

通过TransactionTemplate实现

@Bean
    public TransactionTemplate transactionTemplate1(@Qualifier("transactionManager1") DataSourceTransactionManager transactionManager) {
        return new TransactionTemplate(transactionManager);
    }

 @Bean
    public TransactionTemplate transactionTemplate1(@Qualifier("transactionManager2") DataSourceTransactionManager transactionManager) {
        return new TransactionTemplate(transactionManager);
    }

@Resource(name = "transactionTemplate1")
    TransactionTemplate transactionTemplate1;
    @Resource(name = "transactionTemplate2")
    TransactionTemplate transactionTemplate2;

    @Test
    void transactionTemplate() {
        transactionTemplate1.execute(status -> {
            transactionTemplate2.execute(status2 -> {
                try {
                    pgTestMapper.updateStaffInfo("");
                    mySqlTestMapper.updateStaffInfo("");
                    int a = 1 / 0;
                } catch (Exception e) {
                    status.setRollbackOnly();
                    status2.setRollbackOnly();
                }
                return true;
            });
            return true;
        });
    }

4.2.2 spring声明式事务

2个事务方法嵌套,间接实现

5.dynamic-datasource多数据源组件

– Dynamic-Datasource是基于SpringBoot的多数据源组件,功能强悍,支持Seata分布式事务(基于AOP(注解))

引入依赖:

 <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>4.1.3</version>
        </dependency>

properties

mybatis.mapper-locations=classpath*:*apper/*.xml
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

#设置默认的数据源或者数据源组,默认值即为master ,[master,slave_1名字命名随意即可,这里为默认数据源,下方配置名称随意]
spring.datasource.dynamic.primary=master
#严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
spring.datasource.dynamic.strict=false
spring.datasource.dynamic.datasource.master.url=jdbc:postgresql://bbbb:5000/yyyy?currentSchema=xxxx
spring.datasource.dynamic.datasource.master.username=dsfadf
spring.datasource.dynamic.datasource.master.password=sdafdaf
# 3.2.0开始支持SPI可省略此配置
spring.datasource.dynamic.datasource.master.driver-class-name=org.postgresql.Driver
#内置加密,使用请查看详细文档

spring.datasource.dynamic.datasource.slave_1.url=jdbc:mysql://aaaaa:8166/bbbbb
spring.datasource.dynamic.datasource.slave_1.username=aaaa
spring.datasource.dynamic.datasource.slave_1.password=aaaeee
spring.datasource.dynamic.datasource.slave_1.driver-class-name=com.mysql.cj.jdbc.Driver

使用:

@Service
public class TestService {
    @Resource
    PgTestMapper pgTestMapper;
    @DS("slave_1")//多个下划线分割的数据源,可以只写slave,系统会自动负载
    public Map<String,String> getInfo(){
        return pgTestMapper.getStaffInfo();
    }
}

@SpringBootTest
class MultiDatasource6DanamicDatasourceApplicationTests {
    @Resource
    TestService testService;

    @Test
    void contextLoads() {
        System.out.println(testService.getInfo());
    }

}

注意:

不能直接在直接调用方法上添加注释,会不生效.如下所示
@SpringBootTest
class MultiDatasource6DanamicDatasourceApplicationTests {

    @Resource
    PgTestMapper pgTestMapper;
    @Test
    @DS("slave_1")//不会生效
    void contextLoads() {
        System.out.println(pgTestMapper.getStaffInfo());
    }

}

本地事务:

使用@DSTransactional即可,不能和Spirng @Transactional混用! (使用没有生效)