Springboot Mybatis 多数据源配置以及使用

在Spring Boot中配置MyBatis的多数据源是一个常见需求，尤其是在需要连接多个数据库时，下面是详细的步骤指南。

引入依赖

首先，在你的pom.xml文件中添加Spring Boot、MyBatis和数据库连接的相关依赖。例如，如果你使用的是MySQL数据库：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.4.1</version>
</dependency>
<dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <scope>runtime</scope>
 </dependency>

本文使用的是springboot 2.6.6版本

配置数据源

在application.yml或application.properties中配置多个数据源。假设我们有两个数据源：primary和secondary。

spring:
  datasource:
    primary:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/db-ocean?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
      username: root
      password: 123456
    secondary:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/db-log?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
      username: root
      password: 123456

注意

是driver-class-name 不是driverClassName
是jdbc-url 不是url

配置数据源和SqlSessionFactory

接下来，创建配置类来定义每个数据源及其相关的SqlSessionFactory。

主数据源配置

package com.angel.ocean.config;

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.angel.ocean.mapper", sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryMyBatisConfig {

    @Primary
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "primarySqlSessionFactory")
    public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return sessionFactoryBean.getObject();
    }

    @Primary
    @Bean(name = "primaryTransactionManager")
    public DataSourceTransactionManager primaryTransactionManager(@Qualifier("primaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

次数据源配置

package com.angel.ocean.config;

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.angel.ocean.log.mapper", sqlSessionFactoryRef = "secondarySqlSessionFactory")
public class SecondaryMyBatisConfig {

    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondarySqlSessionFactory")
    public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/log/*.xml"));
        return sessionFactoryBean.getObject();
    }

    @Bean(name = "secondaryTransactionManager")
    public DataSourceTransactionManager secondaryTransactionManager(@Qualifier("secondaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

注意

1. 主数据源对应的Mapper目录要与次数据源对应的Mapper目录不能相同，如本文主数据源Mapper：com.angel.ocean.mapper，次数据源Mapper：com.angel.ocean.log.mapper；
2. 要指定下主次数据源的Mapper映射路径。
   主数据源Mapper:
   sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/.xml"))*
   次数据源Mapper:
   sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(“classpath:mapper/log/*.xml”)).

启动类

package com.angel.ocean;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.transaction.annotation.EnableTransactionManagement;

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

实例验证

可以将服务的业务分为关键业务和其他业务，本文将关键业务数据放入db-ocean库中，其他业务数据放入db-log库中，如操作日志数据放入db-log库（通过AOP来实现该功能），用户相关的业务数据放入db-ocean库。

代码结构如下：

接口代码实例：

@ApiOperation(value = "保存角色信息表", notes = "保存角色信息表", consumes = "application/json", produces = "application/json", httpMethod = "POST")
@ApiResponses({@ApiResponse(code = 200, message = "OK"),
        @ApiResponse(code = 50000, message = "service exception")
})
@SyslogAnno("添加角色")
@PostMapping("/save")
public ApiResult<Long> save(@RequestBody SysRoleDTO dto) {
    return service.save(dto);
}

接口调用截图：

db-ocean库数据截图：

db-log库数据截图：