背景

目前报表导出需要多数据库的数据，因此我们需要做Mybatis多数据源的配置

我们之前使用Spring的AbstractRoutingDataSource

做资源隔离redis限制请求频率及资源隔离

但是事实上我们确实存在两个数据源【非读写分离】

两个数据源完全不同 换言之在业务上完全不等价【即A数据源的数据和B数据源的数据不同】

而读写分离是A数据源和B数据源的数据相同【至少逻辑等同，比如分片比如读写分离】

当然利用上述方法依然是可以完成多数据源，只是需要做动态切换

本次我们使用另一种实现方式

在SpringBoot+MyBatis实现多个SqlSessionFactory

步骤

由于我们系统使用多数据源我们需要定义两个数据源

在application.properties中需要定义两个数据源

spring.datasource.url=jdbc:mysql://192.168.1.7:3306/f6dms_20160522?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.username=root spring.datasource.password=root spring.datasource2.url=jdbc:mysql://192.168.1.7:3306/f6dms_1116_prod_backup?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true spring.datasource2.driver-class-name=com.mysql.jdbc.Driver spring.datasource2.username=root spring.datasource2.password=root

我们使用spring.dataSource2作为第二个数据源的prefix

 

当只有一个数据源的时候由于druid-starter会自动注册

@Configuration @ConditionalOnClass(com.alibaba.druid.pool.DruidDataSource.class) @AutoConfigureBefore(DataSourceAutoConfiguration.class) @EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class}) @Import({DruidSpringAopConfiguration.class,         DruidStatViewServletConfiguration.class,         DruidWebStatFilterConfiguration.class,         DruidFilterConfiguration.class}) public class DruidDataSourceAutoConfigure {       @Bean     @ConditionalOnMissingBean     public DataSource dataSource() {         return new DruidDataSourceWrapper();     } }   @ConfigurationProperties("spring.datasource.druid") class DruidDataSourceWrapper extends DruidDataSource implements InitializingBean {     @Autowired     private DataSourceProperties basicProperties;       @Override     public void afterPropertiesSet() throws Exception {         //if not found prefix 'spring.datasource.druid' jdbc properties ,'spring.datasource' prefix jdbc properties will be used.         if (super.getUsername() == null) {             super.setUsername(basicProperties.determineUsername());         }         if (super.getPassword() == null) {             super.setPassword(basicProperties.determinePassword());         }         if (super.getUrl() == null) {             super.setUrl(basicProperties.determineUrl());         }         if (super.getDriverClassName() == null) {             super.setDriverClassName(basicProperties.determineDriverClassName());         }     }       @Autowired(required = false)     public void addStatFilter(StatFilter statFilter) {         super.filters.add(statFilter);     }       @Autowired(required = false)     public void addConfigFilter(ConfigFilter configFilter) {         super.filters.add(configFilter);     }       @Autowired(required = false)     public void addEncodingConvertFilter(EncodingConvertFilter encodingConvertFilter) {         super.filters.add(encodingConvertFilter);     }       @Autowired(required = false)     public void addSlf4jLogFilter(Slf4jLogFilter slf4jLogFilter) {         super.filters.add(slf4jLogFilter);     }       @Autowired(required = false)     public void addLog4jFilter(Log4jFilter log4jFilter) {         super.filters.add(log4jFilter);     }       @Autowired(required = false)     public void addLog4j2Filter(Log4j2Filter log4j2Filter) {         super.filters.add(log4j2Filter);     }       @Autowired(required = false)     public void addCommonsLogFilter(CommonsLogFilter commonsLogFilter) {         super.filters.add(commonsLogFilter);     }       @Autowired(required = false)     public void addWallFilter(WallFilter wallFilter) {         super.filters.add(wallFilter);     }     }

当DataSource未注册时会自动注册DruidWrapper

但是我们需要两个数据源因此必须自己注册

我们定义一个抽象DataSource

public abstract class AbstractDataSourceConfig {     private String driverClassName;       /**      * JDBC url of the database.      */     private String url;       /**      * Login user of the database.      */     private String username;       /**      * Login password of the database.      */     private String password;       public String getDriverClassName() {         return driverClassName;     }       public void setDriverClassName(String driverClassName) {         this.driverClassName = driverClassName;     }       public String getUrl() {         return url;     }       public void setUrl(String url) {         this.url = url;     }       public String getUsername() {         return username;     }       public void setUsername(String username) {         this.username = username;     }       public String getPassword() {         return password;     }       public void setPassword(String password) {         this.password = password;     }       protected DataSource getDatasource() {         DruidDataSource dataSource = new DruidDataSource();         dataSource.setUrl(this.getUrl());         dataSource.setUsername(this.getUsername());         dataSource.setPassword(this.getPassword());         dataSource.setDriverClassName(this.getDriverClassName());         return dataSource;     }   }

定义数据源1【注意使用了Primary】 primary的作用是当按照类型注册的时候当容器中存在多个将会注入这个Bean

/**  * @author qixiaobo  */ @Configuration @ConfigurationProperties("spring.datasource") public class DataSourceConfig1 extends AbstractDataSourceConfig {     @Bean(PRIMARY_DATA_SOURCE_NAME)     @Primary     public DataSource dataSource1() {         DataSource datasource = getDatasource();         return datasource;     } }

定义数据源2【注意ConditionalOnProperty会监控系统中存在该property才会注册该Bean】

/**  * @author qixiaobo  */ @Configuration @ConfigurationProperties("spring.datasource2") @ConditionalOnProperty(name = "spring.datasource2.url", matchIfMissing = false) public class DataSourceConfig2 extends AbstractDataSourceConfig {     @Bean(SECOND_DATA_SOURCE_NAME)     public DataSource dataSource2() {         DataSource datasource = getDatasource();         return datasource;     } }

如下我们注册MybatisConfiguar

public class AbstractMyBatisConfigurer {     protected static final String SQL_SESSION_FACTORY_NAME = "SqlSessionFactoryBean";     protected static final String TRANSACTION_MANAGER_NAME = "TransactionManager";     protected static final String DATA_SOURCE_NAME = "DataSource";       protected SqlSessionFactoryBean getSqlSessionFactoryBean(DataSource dataSource) {         SqlSessionFactoryBean factory = new SqlSessionFactoryBean();         factory.setDataSource(dataSource);         factory.setTypeAliasesPackage(MODEL_PACKAGE);           //配置分页插件，详情请查阅官方文档         PageHelper pageHelper = new PageHelper();         Properties properties = new Properties();         properties.setProperty("pageSizeZero", "true");         //分页尺寸为0时查询所有纪录不再执行分页         properties.setProperty("reasonable", "true");         //页码<=0 查询第一页，页码>=总页数查询最后一页         properties.setProperty("supportMethodsArguments", "false");         //支持通过 Mapper 接口参数来传递分页参数         pageHelper.setProperties(properties);           //添加插件         factory.setPlugins(new Interceptor[]{pageHelper, new SoInterceptor(), new MybatisTransactionTimeoutInterceptor()});           org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration();         config.setDefaultStatementTimeout(5);         config.setDefaultFetchSize(10000);         config.setDefaultExecutorType(ExecutorType.REUSE);         config.setLogImpl(Slf4jImpl.class);         config.setLogPrefix("dao.");         factory.setConfiguration(config);         return factory;     }       protected MapperScannerConfigurer getMapperScannerConfigurer() {         MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();         //配置通用Mapper，详情请查阅官方文档         Properties properties = new Properties();         properties.setProperty("mappers", MAPPER_INTERFACE_REFERENCE);         properties.setProperty("notEmpty", "false");         //insert、update是否判断字符串类型!='' 即 test="str != null"表达式内是否追加 and str != ''         properties.setProperty("IDENTITY", "MYSQL");         mapperScannerConfigurer.setProperties(properties);         return mapperScannerConfigurer;     } }

@Configuration public class MybatisConfigurer extends AbstractMyBatisConfigurer {       public static final String PRIMARY_SQL_SESSION_FACTORY_NAME = Constants.LEVEL_PRIMARY + SQL_SESSION_FACTORY_NAME;     public static final String PRIMARY_TRANSACTION_MANAGER_NAME = Constants.LEVEL_PRIMARY + TRANSACTION_MANAGER_NAME;     public static final String PRIMARY_DATA_SOURCE_NAME = Constants.LEVEL_PRIMARY + DATA_SOURCE_NAME;         @Bean     @Primary     public SqlSessionFactory sqlSessionFactoryBean(@Autowired DataSource dataSource) throws Exception {         SqlSessionFactoryBean sqlSessionFactoryBean = getSqlSessionFactoryBean(dataSource);         ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();         sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mapper/**/*.xml"));         return sqlSessionFactoryBean.getObject();     }         @Bean     public MapperScannerConfigurer mapperScannerConfigurer() {         MapperScannerConfigurer mapperScannerConfigurer = getMapperScannerConfigurer();         mapperScannerConfigurer.setBasePackage(MAPPER_PACKAGE);         return mapperScannerConfigurer;     }         @Bean     @Primary     public DataSourceTransactionManager transactionManager1(@Autowired DataSource dataSource) {         return new DataSourceTransactionManager(dataSource);       }     }

/*  * Copyright (c) 2017. Lorem ipsum dolor sit amet, consectetur adipiscing elit.  * Morbi non lorem porttitor neque feugiat blandit. Ut vitae ipsum eget quam lacinia accumsan.  * Etiam sed turpis ac ipsum condimentum fringilla. Maecenas magna.  * Proin dapibus sapien vel ante. Aliquam erat volutpat. Pellentesque sagittis ligula eget metus.  * Vestibulum commodo. Ut rhoncus gravida arcu.  */   package com.f6car.base.config;   import com.f6car.base.constant.Constants; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.autoconfigure.condition.ConditionalOnBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.core.io.support.ResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import tk.mybatis.spring.mapper.MapperScannerConfigurer;   import javax.sql.DataSource;   import static com.f6car.base.config.MyBatisConfigurer2.SECOND_DATA_SOURCE_NAME; import static com.f6car.base.constant.Constants.MAPPER_PACKAGE;   /**  * @author qixiaobo  */ @Configuration @ConditionalOnBean(name = SECOND_DATA_SOURCE_NAME) public class MyBatisConfigurer2 extends AbstractMyBatisConfigurer {     public static final String SECOND_TRANSACTION_MANAGER_NAME = Constants.LEVEL_SECOND + TRANSACTION_MANAGER_NAME;     public static final String SECOND_SQL_SESSION_FACTORY_NAME = Constants.LEVEL_SECOND + SQL_SESSION_FACTORY_NAME;     public static final String SECOND_DATA_SOURCE_NAME = Constants.LEVEL_SECOND + DATA_SOURCE_NAME;         @Bean     public MapperScannerConfigurer mapperScannerConfigurer2() {         MapperScannerConfigurer mapperScannerConfigurer = getMapperScannerConfigurer();         mapperScannerConfigurer.setSqlSessionFactoryBeanName(SECOND_SQL_SESSION_FACTORY_NAME);         mapperScannerConfigurer.setBasePackage(MAPPER_PACKAGE + "2");         return mapperScannerConfigurer;     }       @Bean(name = SECOND_TRANSACTION_MANAGER_NAME)     public DataSourceTransactionManager transactionManager2(@Autowired @Qualifier(SECOND_DATA_SOURCE_NAME) DataSource dataSource) {         return new DataSourceTransactionManager(dataSource);       }       @Bean(name = SECOND_SQL_SESSION_FACTORY_NAME)     public SqlSessionFactory sqlSessionFactoryBean2(@Autowired @Qualifier(SECOND_DATA_SOURCE_NAME) DataSource dataSource) throws Exception {         SqlSessionFactoryBean sqlSessionFactoryBean = getSqlSessionFactoryBean(dataSource);         ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();         sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mapper2/**/*.xml"));         return sqlSessionFactoryBean.getObject();     } }

mapper注册为第一个数据源

mapper2注册为第二个数据源

我们如下文件结构

这样就可以完成多数据源的配置。

本文地址为https://my.oschina.net/qixiaobo025/blog/1584810