SpringBoot实现Mybatis多数据源方案
背景
目前报表导出需要多数据库的数据,因此我们需要做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

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
Dubbo 新编程模型之外部化配置
外部化配置(External Configuration) 在Dubbo 注解驱动例子中,无论是服务提供方,还是服务消费方,均需要转配相关配置Bean: @Bean publicApplicationConfigapplicationConfig(){ ApplicationConfigapplicationConfig=newApplicationConfig(); applicationConfig.setName("dubbo-annotation-consumer"); returnapplicationConfig; } 虽然实现类似于ProviderConfiguration和ConsumerConfiguration这样的 Spring@ConfigurationBean 成本并不高,不过通过 Java Code 的方式定义配置 Bean,或多或少是一种 Hard Code(硬编码)的行为,缺少弹性。 尽管在 Spring 应用中,可以通过@Value或者Environment的方式获取外部配置,其代码简洁性以及类型转换灵活性存在明显的不足。因此,Spring Boot ...
- 下一篇
9种高性能可用高并发的技术架构
1、分层 分层是企业应用系统中最常见的一种架构模式,将系统在横向维度上切分成几个部分,每个部分负责一部分相对简单并比较单一的职责,然后通过上层对下层的依赖和调度组成一个完整的系统。 在网站的分层架构中,常见的为3层,即应用层、服务层、数据层。应用层具体负责业务和视图的展示;服务层为应用层提供服务支持;数据库提供数据存储访问服务,如数据库、缓存、文件、搜索引擎等。 分层架构是逻辑上的,在物理部署上,三层架构可以部署在同一个物理机器上,但是随着网站业务的发展,必然需要对已经分层的模块分离部署,即三层结构分别部署在不同的服务器上,是网站拥有更多的计算资源以应对越来越多的用户访问。 所以虽然分层架构模式最初的目的是规划软件清晰的逻辑结构以便于开发维护,但在网站的发展过程中,分层结构对网站支持高并发向分布式方向的发展至关重要。 2、冗余 网站需要7×24小时连续运行,那么就得有相应的冗余机制,以防某台机器宕掉时无法访问,而冗余则可以通过部署至少两台服务器构成一个集群实现服务高可用。数据库除了定期备份还需要实现冷热备份。甚至可以在全球范围内部署灾备数据中心。 3、分隔 如果说分层是将软件在横向方面...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- SpringBoot2初体验,简单认识spring boot2并且搭建基础工程
- CentOS8,CentOS7,CentOS6编译安装Redis5.0.7
- Windows10,CentOS7,CentOS8安装Nodejs环境
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- Windows10,CentOS7,CentOS8安装MongoDB4.0.16
- CentOS6,CentOS7官方镜像安装Oracle11G
- Red5直播服务器,属于Java语言的直播服务器
- SpringBoot2更换Tomcat为Jetty,小型站点的福音
- SpringBoot2整合Thymeleaf,官方推荐html解决方案
- SpringBoot2整合Redis,开启缓存,提高访问速度