SpringBoot 里怎样整合多数据源,根据最近的研究发现了有两种配置方式,第一种是配置文件的方式(需要写配置类,从配置类里配置dataSource,sqlSessionFactory,sqlSessionTemplate,及包路径等配置);第二种是通过自定义注解的方式来实现,相比与第一种更灵活,不依赖于包路径,是类、方法级别的。
第一种:配置文件方式
- application.properites配置
##主数据库连接池 mysql
spring.datasource.primary.type= com.zaxxer.hikari.HikariDataSource
spring.datasource.primary.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.primary.url=jdbc:mysql://127.0.0.1:3306/xxx?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
spring.datasource.primary.username=
spring.datasource.primary.password=
spring.datasource.primary.hikari.pool-name=PrimaryPoolMysql
##第二数据库连接池 sqlserver
spring.datasource.secondary.type= com.zaxxer.hikari.HikariDataSource
spring.datasource.secondary.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.secondary.url=jdbc:sqlserver://xxx:1433;DatabaseName=xxx
spring.datasource.secondary.username=xxx
spring.datasource.secondary.password=xxx
java 后台配置类:
- 主数据源:
package com.wzw.config;
importcom.alibaba.druid.pool.DruidDataSource;importcom.github.pagehelper.PageInterceptor;importorg.apache.ibatis.plugin.Interceptor;importorg.apache.ibatis.session.SqlSessionFactory;importorg.mybatis.spring.SqlSessionFactoryBean;importorg.mybatis.spring.SqlSessionTemplate;importorg.mybatis.spring.annotation.MapperScan;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importorg.springframework.beans.factory.annotation.Qualifier;importorg.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;importorg.springframework.boot.context.properties.ConfigurationProperties;importorg.springframework.context.annotation.Bean;importorg.springframework.context.annotation.Configuration;importorg.springframework.context.annotation.Primary;importorg.springframework.core.io.DefaultResourceLoader;importorg.springframework.core.io.ResourceLoader;importorg.springframework.core.io.support.PathMatchingResourcePatternResolver;importorg.springframework.jdbc.datasource.DataSourceTransactionManager;importorg.springframework.transaction.annotation.EnableTransactionManagement;importjavax.sql.DataSource;importjava.util.Properties;/**
* 主数据源配置类,连接默认mysql数据库
*/@Configuration@EnableTransactionManagement@MapperScan(basePackages={"com.wzw.dao"}, sqlSessionFactoryRef="sqlSessionFactoryPrimary")publicclassPrimaryDataSourceConfig{privateLogger logger=LoggerFactory.getLogger(PrimaryDataSourceConfig.class);@Bean(name="primaryDataSource")@Primary@Qualifier("primaryDataSource")@ConfigurationProperties(prefix="spring.datasource.primary")publicDataSourceprimaryDataSource(){
logger.info("主数据库连接池创建中.......");DataSourceBuilder dsb=DataSourceBuilder.create();// 手动设置数据源连接池类型DruidDataSource ds=newDruidDataSource();
dsb.type(ds.getClass());return dsb.build();}@Bean@PrimarypublicDataSourceTransactionManagerdataSourceTransactionManager(@Qualifier("primaryDataSource")DataSource dataSource){returnnewDataSourceTransactionManager(dataSource);}@Bean(name="sqlSessionFactoryPrimary")// 表示这个数据源是默认数据源@PrimarypublicSqlSessionFactorysqlSessionFactoryPrimary(@Qualifier("primaryDataSource")DataSource datasource)throwsException{SqlSessionFactoryBean bean=newSqlSessionFactoryBean();
bean.setDataSource(datasource);
bean.setTypeAliasesPackage("com.wzw.entity");//设置config路径ResourceLoader resourceLoader=newDefaultResourceLoader();
bean.setConfigLocation(resourceLoader.getResource("classpath:mybatis-configuration.xml"));//设置mapper.xml路径
bean.setMapperLocations(// 设置mybatis的xml所在位置newPathMatchingResourcePatternResolver().getResources("classpath*:com/wzw/dao/mapper/*.xml"));//addmybatis分页插件PageInterceptor pageInterceptor=newPageInterceptor();Properties props=newProperties();
props.setProperty("helperDialect","mysql");
pageInterceptor.setProperties(props);
bean.setPlugins(newInterceptor[]{pageInterceptor});try{return bean.getObject();}catch(Exception e){
e.printStackTrace();thrownewRuntimeException(e);}}@Bean(name="sqlSessionTemplatePrimary")@PrimarypublicSqlSessionTemplatesqlSessionTemplatePrimary(@Qualifier("sqlSessionFactoryPrimary")SqlSessionFactory sessionFactory){returnnewSqlSessionTemplate(sessionFactory);}}
2.第二数据源配置:
packagecom.wzw.config;importcom.alibaba.druid.pool.DruidDataSource;importcom.github.pagehelper.PageInterceptor;importorg.apache.ibatis.plugin.Interceptor;importorg.apache.ibatis.session.SqlSessionFactory;importorg.mybatis.spring.SqlSessionFactoryBean;importorg.mybatis.spring.SqlSessionTemplate;importorg.mybatis.spring.annotation.MapperScan;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importorg.springframework.beans.factory.annotation.Qualifier;importorg.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;importorg.springframework.boot.context.properties.ConfigurationProperties;importorg.springframework.context.annotation.Bean;importorg.springframework.context.annotation.Configuration;importorg.springframework.core.io.DefaultResourceLoader;importorg.springframework.core.io.ResourceLoader;importorg.springframework.core.io.support.PathMatchingResourcePatternResolver;importorg.springframework.jdbc.datasource.DataSourceTransactionManager;importjavax.sql.DataSource;importjava.util.Properties;/**
* 第二 数据源配置类,连接能耗sqlserver数据库
*/@Configuration@MapperScan(basePackages={"com.wzw.ems.dao"}, sqlSessionFactoryRef="sqlSessionFactorySecondary")publicclassSecondaryDataSourceConfig{privateLogger logger=LoggerFactory.getLogger(SecondaryDataSourceConfig.class);@Bean(name="secondaryDataSource")@Qualifier("secondaryDataSource")@ConfigurationProperties(prefix="spring.datasource.secondary")publicDataSourcegetDataSource(){
logger.info("从数据库连接池创建中.......");DataSourceBuilder dsb=DataSourceBuilder.create();// 手动设置数据源连接池类型DruidDataSource ds=newDruidDataSource();
dsb.type(ds.getClass());return dsb.build();}@Bean(name="txManagerMSSQL")@Qualifier("txManagerMSSQL")publicDataSourceTransactionManagerdataSourceTransactionManager2(@Qualifier("secondaryDataSource")DataSource dataSource){returnnewDataSourceTransactionManager(dataSource);}@Bean(name="sqlSessionFactorySecondary")publicSqlSessionFactorysqlSessionFactory(@Qualifier("secondaryDataSource")DataSource datasource)throwsException{SqlSessionFactoryBean bean=newSqlSessionFactoryBean();
bean.setDataSource(datasource);
bean.setTypeAliasesPackage("com.wzw.ems.entity");//设置config路径ResourceLoader resourceLoader=newDefaultResourceLoader();
bean.setConfigLocation(resourceLoader.getResource("classpath:mybatis-configuration.xml"));//设置mapper.xml路径
bean.setMapperLocations(// 设置mybatis的xml所在位置newPathMatchingResourcePatternResolver().getResources("classpath*:com/wzw/ems/dao/mapper/*.xml"));//addmybatis分页插件PageInterceptor pageInterceptor=newPageInterceptor();Properties props=newProperties();
props.setProperty("helperDialect","sqlserver");
pageInterceptor.setProperties(props);
bean.setPlugins(newInterceptor[]{pageInterceptor});try{return bean.getObject();}catch(Exception e){
e.printStackTrace();thrownewRuntimeException(e);}}@Bean(name="sqlSessionTemplateSecondary")publicSqlSessionTemplatesqlSessionTemplate(@Qualifier("sqlSessionFactorySecondary")SqlSessionFactory sessionFactory){returnnewSqlSessionTemplate(sessionFactory);}}
总结:
1.不同的数据库通过不同的包来配置,第一个通过basePackages = {“com.wzw.dao”},第二个数据库通过basePackages = {“com.wzw.ems.dao”}来配置。
2.配置过程:先配置datasource ,再配置 sqlSessionFactory,最后配置sqlSessionTemplate
第二种方式:自定义注解方式
- application.yml 配置
# 数据源配置
spring:
datasource:
type:com.alibaba.druid.pool.DruidDataSource
driverClassName:com.mysql.cj.jdbc.Driver
druid:
# 主库数据源
master:
url: jdbc:mysql://localhost:3306/xxx?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password:123456
# 从库数据源
slave:
# 从数据源开关/默认关闭
enabled:false
url:
username:
password:
# 初始连接数
initialSize:5
# 最小连接池数量
minIdle:10
# 最大连接池数量
maxActive:20
# 配置获取连接等待超时的时间
maxWait:60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis:60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis:300000
# 配置一个连接在池中最大生存的时间,单位是毫秒
maxEvictableIdleTimeMillis:900000
# 配置检测连接是否有效
validationQuery: SELECT1 FROM DUAL
testWhileIdle:true
testOnBorrow:false
testOnReturn:false
webStatFilter:
enabled:true
statViewServlet:
enabled:true
# 设置白名单,不填则允许所有访问
allow:
url-pattern:/druid/*
# 控制台管理用户名和密码
login-username:
login-password:
filter:
stat:
enabled: true
# 慢SQL记录
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
- 自定义注解
/**
1. 自定义多数据源切换注解
2. 3. 优先级:先方法,后类,如果方法覆盖了类上的数据源类型,以方法的为准,否则以类上的为准
4. 5. @author ruoyi
*/@Target({ElementType.METHOD,ElementType.TYPE})@Retention(RetentionPolicy.RUNTIME)@Documented@Inheritedpublic@interfaceDataSource{/**
* 切换数据源名称
*/publicDataSourceTypevalue()defaultDataSourceType.MASTER;}
- 常量DataSourceType
/**
* 数据源
*
* @author ruoyi
*/publicenumDataSourceType{/**
* 主库
*/
MASTER,/**
* 从库
*/
SLAVE}
4 DruidConfig
packagecom.ruoyi.framework.config;importjava.io.IOException;importjava.util.HashMap;importjava.util.Map;importjavax.servlet.Filter;importjavax.servlet.FilterChain;importjavax.servlet.ServletException;importjavax.servlet.ServletRequest;importjavax.servlet.ServletResponse;importjavax.sql.DataSource;importorg.springframework.boot.autoconfigure.condition.ConditionalOnProperty;importorg.springframework.boot.context.properties.ConfigurationProperties;importorg.springframework.boot.web.servlet.FilterRegistrationBean;importorg.springframework.context.annotation.Bean;importorg.springframework.context.annotation.Configuration;importorg.springframework.context.annotation.Primary;importcom.alibaba.druid.pool.DruidDataSource;importcom.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;importcom.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;importcom.alibaba.druid.util.Utils;importcom.ruoyi.common.enums.DataSourceType;importcom.ruoyi.common.utils.spring.SpringUtils;importcom.ruoyi.framework.config.properties.DruidProperties;importcom.ruoyi.framework.datasource.DynamicDataSource;/**
1. druid 配置多数据源
2.
3. @author ruoyi
*/@ConfigurationpublicclassDruidConfig{@Bean@ConfigurationProperties("spring.datasource.druid.master")publicDataSourcemasterDataSource(DruidProperties druidProperties){DruidDataSource dataSource=DruidDataSourceBuilder.create().build();return druidProperties.dataSource(dataSource);}@Bean@ConfigurationProperties("spring.datasource.druid.slave")@ConditionalOnProperty(prefix="spring.datasource.druid.slave", name="enabled", havingValue="true")publicDataSourceslaveDataSource(DruidProperties druidProperties){DruidDataSource dataSource=DruidDataSourceBuilder.create().build();return druidProperties.dataSource(dataSource