springboot项目关联多个mysql数据源

1 背景描述

业务设计中需要对数据库进行拆分，日志数据、过程数据及基础数据等进行拆分，根据不同的业务场景进行不同的运维等。

本文基于以下环境进行分库框架设计。

项目环境：springboot、mybatis、mysql8.0

目标数据库

• business：基础业务数据库，基础数据及系统数据存放在该数据库
  • 测试表，表名：business
  • 应用过程中自定义表字段即可
• log：日志数据库，运行时日志、操作日志等存储在日志数据库
  • 测试表，表名：log
  • 应用过程中自定义表字段即可

2 目录描述

本文架构的核型是通过指定不同的数据源并绑定不同的映射目录进行自动数据源切换。

3 实现方式

3.1 yaml配置文件配置

yaml配置文件需要配置指定的数据源信息及mybatis扫描信息，如下所示

spring:
  datasource:
    business:
      url: jdbc:mysql://127.0.0.1:3306/business
      username: test
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
    log:
      url: jdbc:mysql://127.0.0.1:3306/log
      username: test
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource

mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  # 针对多级目录扫描
  mapper-locations: classpath*:mappers/**/*.xml
  type-aliases-package: com.demo.model.entity

3.2 数据源配置

通过声明不同的数据源实例，并配置不同的mapper和entity扫描目录，实现数据源基础配置。

BusinessDataSourceConfig

@Configuration
@MapperScan(basePackages = {"com.demo.dao.business"}, sqlSessionTemplateRef = "businessSqlSessionTemplate")
public class BusinessDataSourceConfig {
    @Bean(name = "businessDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.business")
    public DruidDataSource businessDataSource() {
        return new DruidDataSource();
    }

    @Bean(name = "businessSqlSessionFactory")
    public SqlSessionFactory businessSqlSessionFactory(@Qualifier("businessDataSource") DataSource businessDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(businessDataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mappers/business/*.xml"));
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setLogImpl(StdOutImpl.class);
        bean.setConfiguration(configuration);
        bean.setTypeAliasesPackage("com.demo.model.entity.business.**");
        return bean.getObject();
    }

    // 声明事务管理器
    @Bean(name = "business")
    public DataSourceTransactionManager businessTransactionManager(@Qualifier("businessDataSource") DataSource businessDataSource) {
        return new DataSourceTransactionManager(businessDataSource);
    }

    @Bean(name = "businessSqlSessionTemplate")
    public SqlSessionTemplate SqlSessionTemplate(@Qualifier("businessSqlSessionFactory") SqlSessionFactory businessSqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(businessSqlSessionFactory);
    }
}

LogDataSourceConfig

@Configuration
@MapperScan(basePackages = {"com.demo.dao.log"}, sqlSessionTemplateRef = "logSqlSessionTemplate")
public class LogDataSourceConfig {
    @Bean(name = "logDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.log")
    public DruidDataSource logDataSource() {
        return new DruidDataSource();
    }

    @Bean(name = "logSqlSessionFactory")
    public SqlSessionFactory logSqlSessionFactory(@Qualifier("logDataSource") DataSource logDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(logDataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mappers/log/*.xml"));
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setLogImpl(StdOutImpl.class);
        bean.setConfiguration(configuration);
        bean.setTypeAliasesPackage("com.demo.model.entity.log.**");
        return bean.getObject();
    }

    // 声明事务管理器
    @Bean(name = "log")
    public DataSourceTransactionManager logTransactionManager(@Qualifier("logDataSource") DataSource logDataSource) {
        return new DataSourceTransactionManager(logDataSource);
    }

    @Bean(name = "logSqlSessionTemplate")
    public SqlSessionTemplate logSqlSessionTemplate(@Qualifier("logSqlSessionFactory") SqlSessionFactory logSqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(logSqlSessionFactory);
    }
}

3.3 应用举例

使用单元测试进行分表测试，代码如下

@SpringBootTest
@Slf4j
public class DataSourceTest {

    @Resource
    private BusinessMapper businessMapper;
    @Resource
    private LogMapper logMapper;

    @Test
    public void selectTest() {
        // business数据库
        Business business = businessMapper.selectById(1);
        log.info("business {}",business);
        // 日志数据库
        Log log = logMapper.selectById(1);
        log.info("log {}",log);
    }
}

4 mybatis generator配置

mybatis 自动生成映射文件及对象文件配置如下,对

<!-- business数据库驱动-->
    <context id="Business_DB2Tables" targetRuntime="MyBatis3">

        <!--数据库链接URL，用户名、密码 -->
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver" connectionURL="jdbc:mysql://127.0.0.1:3306/business" userId="test" password="123456"></jdbcConnection>

        <!-- 生成模型的包名和位置 指定business目录-->
        <javaModelGenerator targetPackage="com.demo.model.entity.business" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>

        <!-- 生成映射文件的包名和位置 指定business目录-->
        <sqlMapGenerator targetPackage="mappers/business" targetProject="src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>

        <!-- 生成DAO的包名和位置 注意：不能删除下面的表，每次都需要重新全部生成 指定business目录-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.demo.dao.business" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
    </context>

    <!-- log数据库驱动-->
    <context id="Log_DB2Tables" targetRuntime="MyBatis3">

        <!--数据库链接URL，用户名、密码 -->
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver" connectionURL="jdbc:mysql://127.0.0.1:3306/log" userId="test" password="123456"></jdbcConnection>

        <!-- 生成模型的包名和位置 指定business目录-->
        <javaModelGenerator targetPackage="com.demo.model.entity.log" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>

        <!-- 生成映射文件的包名和位置 指定business目录-->
        <sqlMapGenerator targetPackage="mappers/log" targetProject="src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>

        <!-- 生成DAO的包名和位置 注意：不能删除下面的表，每次都需要重新全部生成 指定business目录-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.demo.dao.log" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
    </context>