背景
目前报表导出需要多数据库的数据,因此我们需要做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注册为第二个数据源
我们如下文件结构
这样就可以完成多数据源的配置