mybatis连接双数据库

发表于 更新于 分类于 ，
本文字数： 10k 阅读时长 ≈ 9 分钟

背景：

  项目中需要从两个不同的数据库查询数据，之前实现方法是：springboot配置连接一个数据源，另一个使用jdbc代码连接。
  为了改进，现在使用SpringBoot配置连接两个数据源。

实现效果：

  一个SpringBoot项目，同时连接两个数据库。

注意：连接什么数据库，要引入对应数据库的包

实现

  实现多数据源均需要使用到mybatis plus。这里也引入了druid连接池。
  双数据源中需要将不同数据源的mapper和对应xml文件放在不同的包中。

  先准备两个数据源。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 数据源1
create schema mybatisdubboone;
create table user
(
    id          int auto_increment
        primary key,
    username    varchar(50) default ''                not null,
    mobile      varchar(15) default ''                null,
    create_time datetime    default CURRENT_TIMESTAMP not null
);

-- 数据源2
create schema mybatisdubbotwo;
create table mybatisdubbotwo.order_info
(
    id          int auto_increment
        primary key,
    order_id    varchar(20) default ''                not null,
    user_id     int         default 0                 not null,
    create_time datetime    default CURRENT_TIMESTAMP not null
);

静态双数据源

  静态数据源需要对不同的数据源单独配置，同时需要关闭掉springboot和druid的数据源自动配置。

1
2
3
4
5
6
7
8
9
10
11
12
13
spring:
  application:
    name: staticmybatisdubbomysql
  datasourceone:
    jdbc-url: jdbc:mysql://localhost:3306/mybatisdubboone?serverTimezone=GMT
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 1234
  datasourcetwo:
    jdbc-url: jdbc:mysql://localhost:3306/mybatisdubbotwo?serverTimezone=GMT
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 1234

  数据源1配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
@Configuration
@MapperScan(basePackages = "com.lin.mybatisdubbomysql.dal.mapper.databaseone", sqlSessionTemplateRef = "SqlSessionTemplateOne")
@EnableConfigurationProperties(DatabaseOneProperties.class)
public class DatabaseOneConfig {

    @Resource
    DatabaseOneProperties databaseOneProperties;

    @Bean("dataSourceOne")
    @Primary
    public DataSource dataSource() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(databaseOneProperties.getJdbcUrl());
        druidDataSource.setUsername(databaseOneProperties.getUsername());
        druidDataSource.setPassword(databaseOneProperties.getPassword());
        druidDataSource.setDriverClassName(databaseOneProperties.getDriverClassName());
        return druidDataSource;
    }

    @Bean("sqlSessionFactoryOne")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSourceOne") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mapper/databaseone/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean("SqlSessionTemplateOne")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryOne")SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean("Txone")
    @Primary
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dataSourceOne") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }
}

  数据源2配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
@Configuration
@MapperScan(basePackages = "com.lin.mybatisdubbomysql.dal.mapper.databasetwo", sqlSessionTemplateRef = "SqlSessionTemplateTwo")
@EnableConfigurationProperties(DatabaseTwoProperties.class)
public class DatabaseTwoConfig {

    @Resource
    DatabaseTwoProperties databaseTwoProperties;

    @Bean("dataSourceTwo")
    public DataSource dataSource() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(databaseTwoProperties.getJdbcUrl());
        druidDataSource.setUsername(databaseTwoProperties.getUsername());
        druidDataSource.setPassword(databaseTwoProperties.getPassword());
        druidDataSource.setDriverClassName(databaseTwoProperties.getDriverClassName());
        return druidDataSource;
    }

    @Bean("sqlSessionFactoryTwo")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSourceTwo") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mapper/databasetwo/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean("SqlSessionTemplateTwo")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryTwo") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean("Txtwo")
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dataSourceTwo") DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

}

  开启druid监控：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
@Configuration
public class DruidConfig {
    /**
     * 主要实现WEB监控的配置处理
     */
    @Bean
    public ServletRegistrationBean<StatViewServlet> druidServlet() {
        // 现在要进行druid监控的配置处理操作
        ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>(
                new StatViewServlet(), "/druid/*");
        // 白名单,多个用逗号分割， 如果allow没有配置或者为空，则允许所有访问
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
        // 黑名单,多个用逗号分割 (共同存在时，deny优先于allow)
        servletRegistrationBean.addInitParameter("deny", "192.168.1.110");
        // 控制台管理用户名
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        // 控制台管理密码
        servletRegistrationBean.addInitParameter("loginPassword", "admin");
        // 是否可以重置数据源，禁用HTML页面上的“Reset All”功能
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean<WebStatFilter> filterRegistrationBean() {
        FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>();
        filterRegistrationBean.setFilter(new WebStatFilter());
        //所有请求进行监控处理
        filterRegistrationBean.addUrlPatterns("/*");
        //添加不需要忽略的格式信息
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.css,/druid/*");
        return filterRegistrationBean;
    }

}

  启动类配置：

1
2
3
4
5
6
7
8
9
10
@EnableTransactionManagement
@ComponentScan(basePackages = "com.lin.mybatisdubbomysql.*")
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, DruidDataSourceAutoConfigure.class})
public class StaticMybatisDubboMysqlApplication {

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

}

动态双数据源

  动态数据源，通过MybatisPlus提供的AbstractRoutingDataSource的路由能力，配合AOP在操作数据库前进行数据源切换。同样也需要关闭SpringBoot和druid的自动数据源配置。

  数据源配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
@Configuration
@MapperScan(basePackages = "com.lin.mybatisdubbomysql.dal.mapper",sqlSessionFactoryRef = "sqlSessionFactory")
public class DBConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasourceone")
    public DataSource dataSourceOne() {
        return new DruidDataSource();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasourcetwo")
    public DataSource dataSourceTwo() {
        return new DruidDataSource();
    }

    @Bean
    @Primary
    public DynamicDataSource dataSource(DataSource dataSourceOne,DataSource dataSourceTwo){
        Map<Object, Object> targetDataSources = new HashMap<>(8);
        targetDataSources.put(DateBaseEnum.DataBaseOne.getValue(),dataSourceOne);
        targetDataSources.put(DateBaseEnum.DataBaseTwo.getValue(), dataSourceTwo);
        return new DynamicDataSource(dataSourceOne, targetDataSources);
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(DynamicDataSource dynamicDataSource) throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dynamicDataSource);
        return sqlSessionFactoryBean.getObject();
    }

}

  动态数据路由配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
public class DynamicDataSource extends AbstractRoutingDataSource {

    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();

    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

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

    public static void setDataSource(String dataSource) {
        CONTEXT_HOLDER.set(dataSource);
    }

    public static String getDataSource() {
        return CONTEXT_HOLDER.get();
    }

    public static void clearDataSource() {
        CONTEXT_HOLDER.remove();
    }
}

  数据类型枚举：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@AllArgsConstructor
@Getter
public enum DateBaseEnum {


    /**
     * 数据库one
     */
    DataBaseOne("one", "one"),
    /**
     * 数据库two
     */
    DataBaseTwo("two", "two");

    private final String name;
    private final String value;
}

  通过AOP在执行前切换数据源：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Component
@Aspect
public class DatasourceAspect {


    @Pointcut("execution(public * com.lin.mybatisdubbomysql.dal.mapper.databasetwo..*.*(..))")
    public void dataSourcePointCut() {

    }

    @Around("dataSourcePointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        DynamicDataSource.setDataSource(DateBaseEnum.DataBaseTwo.getValue());
        try {
            return point.proceed();
        } finally {
            DynamicDataSource.clearDataSource();
        }
    }
}

mybatisplus官方动态双数据源

  官方的动态数据源配置最为简单。
  yml文件的配置与其他的不同，使用mybatisplus的配置，yml文件配置后，使用自动注入的数据源即可。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
spring:
  application:
    name: plusmybatisdubbomysql
  datasource:
    dynamic:
      primary: master #设置默认的数据源或者数据源组,默认值即为master
      strict: false #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候会抛出异常,不启动则使用默认数据源.
      datasource:
        master:
          url: jdbc:mysql://localhost:3306/mybatisdubboone?serverTimezone=GMT
          username: root
          password: 1234
          driver-class-name: com.mysql.cj.jdbc.Driver # 3.2.0开始支持SPI可省略此配置
        slave:
          url: jdbc:mysql://localhost:3306/mybatisdubbotwo?serverTimezone=GMT
          username: root
          password: 1234
          driver-class-name: com.mysql.cj.jdbc.Driver

  官方的切换方式，采用@DS("")注解，指定类、方法、mapper（官方不推荐）进行数据源的切换。这里注解中的值要和yml文件配置的一样。

1
2
3
4
5
@Service
@DS("master")
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

}
相关文章