springboot 多数据源的2种实现
这里介绍2种多数据源：
- 固定数据源
数据源是已知且相对固定的，直接在配置文件里写入各个数据库的连接字符串、用域名、密码等 - 动态数据源
数据源是动态的，在数据库或其他可动态存储的地方保存数据源，可按需调整（增删改）
文章目录
1 固定数据源实现
业务场景：项目涉及多个数据库，比如本项目数据库、财务系统数据库、物资系统数据库。系统需要操作多个数据库。
1.1 分别定义各个数据源
第1个数据源： dataSourceMySql
@ConfigurationpublicclassDataSourceMySqlConfig{@Value("${spring.datasource.url}")privateString url;@Value("${spring.datasource.username}")privateString username;@Value("${spring.datasource.password}")privateString password;@Value("${spring.datasource.driverClassName}")privateString driverClassName;@Bean("dataSourceMySql")publicDataSourcedataSourceMySql()throwsException{Properties props =newProperties();
props.put("driverClassName", driverClassName);
props.put("url", url);
props.put("username", username);
props.put("password", password);returnDruidDataSourceFactory.createDataSource(props);}@Bean("jdbcTemplate")publicJdbcTemplatejdbcTemplate(@AutowiredDataSource dataSourceMySql){returnnewJdbcTemplate(dataSourceMySql);}}
第2个数据源： dataSource2
@ConfigurationpublicclassDataSource2Config{@Value("${spring.datasource.2.url}")privateString url;@Value("${spring.datasource.2.username}")privateString username;@Value("${spring.datasource.2.password}")privateString password;@Value("${spring.datasource.2.driverClassName}")privateString driverClassName;@Bean("dataSource2")publicDataSourcedataSource2()throwsException{Properties props =newProperties();
props.put("driverClassName", driverClassName);
props.put("url", url);
props.put("username", username);
props.put("password", password);returnDruidDataSourceFactory.createDataSource(props);}@Bean("jdbcTemplate2")publicJdbcTemplatejdbcTemplate(@AutowiredDataSource dataSource2){returnnewJdbcTemplate(dataSource2);}}
第3个数据源： dataSource3
@ConfigurationpublicclassDataSource3Config{@Value("${spring.datasource.3.url}")privateString url;@Value("${spring.datasource.3.username}")privateString username;@Value("${spring.datasource.3.password}")privateString password;@Value("${spring.datasource.3.driverClassName}")privateString driverClassName;@Bean("dataSource3")publicDataSourcedataSource3()throwsException{Properties props =newProperties();
props.put("driverClassName", driverClassName);
props.put("url", url);
props.put("username", username);
props.put("password", password);returnDruidDataSourceFactory.createDataSource(props);}@Bean("jdbcTemplate3")publicJdbcTemplatejdbcTemplate(@AutowiredDataSource dataSource3){returnnewJdbcTemplate(dataSource3);}}
1.2 动态数据源
publicclassDataSourceContextHolder{publicstaticfinalLogger log =LoggerFactory.getLogger(DataSourceContextHolder.class);privatestaticfinalThreadLocal<String> contextHolder =newThreadLocal<String>();publicstaticvoidsetDBType(String dbType){
contextHolder.set(dbType);}publicstaticStringgetDBType(){return((String) contextHolder.get());}publicstaticvoidclearDBType(){
contextHolder.remove();}}
publicclassDataSourceDynamicextendsAbstractRoutingDataSource{@OverrideprotectedObjectdetermineCurrentLookupKey(){returnDataSourceContextHolder.getDBType();}}
1.3 MyBatis配置
在Mybatis配置文件里注入和配置数据源
@Configuration@EnableTransactionManagementpublicclassMyBatisConfig{privateLogger logger =LoggerFactory.getLogger(this.getClass());@Bean(name ="sqlSessionFactory")publicSqlSessionFactorysqlSessionFactoryBean(@AutowiredDataSourceDynamic dataSource){MybatisSqlSessionFactoryBean bean =newMybatisSqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setPlugins(mybatisPlusInterceptor());//添加XML目录ResourcePatternResolver resolver =newPathMatchingResourcePatternResolver();try{
bean.setMapperLocations(resolver.getResources("classpath*:com/master/*/*/mapping/*.xml"));return bean.getObject();}catch(Exception e){
logger.error("发生错误！", e);thrownewRuntimeException(e);}}/**
* Mybatis-plus分页插件
*/@BeanpublicMybatisPlusInterceptormybatisPlusInterceptor(){MybatisPlusInterceptor interceptor =newMybatisPlusInterceptor();
interceptor.addInnerInterceptor(newPaginationInnerInterceptor(DbType.MYSQL));return interceptor;}@Bean@PrimarypublicDataSourceDynamicdataSource(@AutowiredDataSource dataSourceMySql
,@AutowiredDataSource dataSource2
,@AutowiredDataSource dataSource3
){Map<Object,Object> targetDataSources =newHashMap<>();//不建议下面这样直接写字符串，可以考虑用枚举类型或其他方式来配置
targetDataSources.put("dataSourceMySql", dataSourceMySql);
targetDataSources.put("dataSource2", dataSource2);
targetDataSources.put("dataSource3", dataSource3);DataSourceDynamic dataSource =newDataSourceDynamic();
dataSource.setTargetDataSources(targetDataSources);//可用DataSource
dataSource.setDefaultTargetDataSource(dataSourceMySql);// 默认datasourcereturn dataSource;}@BeanpublicSqlSessionTemplatesqlSessionTemplate(SqlSessionFactory sqlSessionFactory){returnnewSqlSessionTemplate(sqlSessionFactory);}@Bean("transactionManager")publicDataSourceTransactionManagerdataSourceTransactionManager(@AutowiredDataSourceDynamic dataSource){returnnewDataSourceTransactionManager(dataSource);}}
1.4 通过切面切换数据源
@Aspect@Order(1)@ComponentpublicclassDataSourceAspect{@Pointcut("@annotation(com.master.app.aspectj.lang.annotation.DataSource) || @within(com.master.app.aspectj.lang.annotation.DataSource)")publicvoiddsPointCut(){}@Around("dsPointCut()")publicObjectaround(ProceedingJoinPoint point)throwsThrowable{DataSource dataSource =getDataSource(point);if(Objects.nonNull(dataSource)){DataSourceContextHolder.setDBType(dataSource.value().name());}try{return point.proceed();}finally{//还原数据源DataSourceContextHolder.clearDBType();}}/**
* 切换数据源
*/publicDataSourcegetDataSource(ProceedingJoinPoint point){MethodSignature signature =(MethodSignature) point.getSignature();Class<?extendsObject> targetClass = point.getTarget().getClass();DataSource targetDataSource = targetClass.getAnnotation(DataSource.class);if(Objects.nonNull(targetDataSource)){return targetDataSource;}else{Method method = signature.getMethod();return method.getAnnotation(DataSource.class);}}}
1.5 使用
代码里未指定时，默认数据源是dataSourceMySql，当需要指定时
有2种使用方法：
- 直接用jdbcTemplate：
@ResourceprivateJdbcTemplate jdbcTemplate3;//直接jdbcTemplate3执行sql
- 在mapper使用
@DataSource注释
publicinterfaceSynExpenditureMapper{不建议下面这样直接写字符串，可以考虑用枚举类型或其他方式来配置@DataSource("dataSource3")List<SomeEntity>doSomeThing(Map<String,Object> param);}
2 动态数据源实现
数据源通过数据库来配置，系统先读取配置的数据源，再初始化它们。
我的业务场景：多租户模式。因为各种原因，我这个项目多租户模式最终做成了多数据库模式，每个租户一个库。初始化租户时，程序通过模板库为租户创建独立的数据库。下面介绍处理多数据源的方式。
租户访问采用
域名/上下文路径
的方式，域名固定，每个租户上下文路径不同。在nginx反向代理映射时，在header增加数据源的参数，然后在spring里增加拦截器，读取这个参数并写入session的datasource属性。DynamicDataSource再通过session存的值判断要使用的数据源。
2.1 数据源相关配置文件
DBContextHolder(和上文的一样)
publicclassDBContextHolder{privatefinalstaticLogger log =LoggerFactory.getLogger(DBContextHolder.class);privatestaticfinalThreadLocal<String> contextHolder =newThreadLocal<String>();// 调用此方法，切换数据源publicstaticvoidsetDataSource(String dataSource){
contextHolder.set(dataSource);
log.info("已切换到数据源:{}",dataSource);}// 获取数据源publicstaticStringgetDataSource(){return contextHolder.get();}// 删除数据源publicstaticvoidclearDataSource(){
contextHolder.remove();
log.info("clear数据源");}}
DataSourceUtil 用于获取当前数据源
publicclassDataSourceUtil{publicstaticStringgetDataSource(){String datasource ="";Subject subject;Session session;try{
subject =SecurityUtils.getSubject();
session = subject.getSession();//当前数据源通过session保存
datasource =(String)session.getAttribute(SessionConst.DATASOURCE);}catch(Exception e){}if(StringUtils.isEmpty(datasource)){//如果没有就返回base数据源
datasource =MasterConst.BASE_DB_KEY;}return datasource;}}
DynamicDataSource 动态数据源的实现。代码里的Partner对应租户数数据库信息，这里省略
publicclassDynamicDataSourceextendsAbstractRoutingDataSource{privateboolean debug =true;privatefinalLogger log =LoggerFactory.getLogger(getClass());privateMap<Object,Object> dynamicTargetDataSources;privateObject dynamicDefaultTargetDataSource;@OverrideprotectedObjectdetermineCurrentLookupKey(){String datasource =DBContextHolder.getDataSource();if(StringUtils.isEmpty(datasource)){try{
datasource =DataSourceUtil.getDataSource();}catch(Exception e){
e.printStackTrace();}}if(StringUtils.isEmpty(datasource)){
datasource =MasterConst.BASE_DB_KEY;}Map<Object,Object> dynamicTargetDataSources2 =this.dynamicTargetDataSources;if(!dynamicTargetDataSources2.containsKey(datasource)){
log.error("不存在的数据源："+ datasource);returnnull;}return datasource;}@OverridepublicvoidsetTargetDataSources(Map<Object,Object> targetDataSources){super.setTargetDataSources(targetDataSources);this.dynamicTargetDataSources = targetDataSources;}// 创建数据源publicbooleancreateDataSource(String key,String driveClass,String url,String username,String password,String databasetype)throwsException{Class.forName(driveClass);DriverManager.getConnection(url, username, password);// 相当于连接数据库DruidDataSource druidDataSource =newDruidDataSource();
druidDataSource.setName(key);
druidDataSource.setDriverClassName(driveClass);
druidDataSource.setUrl(url);
druidDataSource.setUsername(username);
druidDataSource.setPassword(password);
druidDataSource.init();this.dynamicTargetDataSources.put(key, druidDataSource);setTargetDataSources(this.dynamicTargetDataSources);// 将map赋值给父类的TargetDataSourcessuper.afterPropertiesSet();// 将TargetDataSources中的连接信息放入resolvedDataSources管理
log.info(key+"数据源初始化成功");returntrue;}// 删除数据源publicbooleandelDatasources(String datasourceid){Map<Object,Object> dynamicTargetDataSources2 =this.dynamicTargetDataSources;if(dynamicTargetDataSources2.containsKey(datasourceid)){Set<DruidDataSource> druidDataSourceInstances =DruidDataSourceStatManager.getDruidDataSourceInstances();for(DruidDataSource l : druidDataSourceInstances){if(datasourceid.equals(l.getName())){
dynamicTargetDataSources2.remove(datasourceid);DruidDataSourceStatManager.removeDataSource(l);setTargetDataSources(dynamicTargetDataSources2);// 将map赋值给父类的TargetDataSourcessuper.afterPropertiesSet();// 将TargetDataSources中的连接信息放入resolvedDataSources管理returntrue;}}returnfalse;}else{returnfalse;}}// 测试数据源连接是否有效publicbooleantestDatasource(String key,String driveClass,String url,String username,String password){try{Class.forName(driveClass);DriverManager.getConnection(url, username, password);returntrue;}catch(Exception e){returnfalse;}}@OverridepublicvoidsetDefaultTargetDataSource(Object defaultTargetDataSource){this.setDynamicDefaultTargetDataSource(defaultTargetDataSource);this.dynamicDefaultTargetDataSource = defaultTargetDataSource;}publicvoidsetDebug(boolean debug){this.debug = debug;}publicbooleanisDebug(){return debug;}publicMap<Object,Object>getDynamicTargetDataSources(){return dynamicTargetDataSources;}publicvoidsetDynamicTargetDataSources(Map<Object,Object> dynamicTargetDataSources){this.dynamicTargetDataSources = dynamicTargetDataSources;}publicObjectgetDynamicDefaultTargetDataSource(){return dynamicDefaultTargetDataSource;}publicvoidsetDynamicDefaultTargetDataSource(Object dynamicDefaultTargetDataSource){this.dynamicDefaultTargetDataSource = dynamicDefaultTargetDataSource;}//创建数据连接publicvoidcreateDataSourceWithCheck(Partner dataSource)throwsException{String datasourceId = dataSource.getCode();
log.info("正在检查数据源："+datasourceId);Map<Object,Object> dynamicTargetDataSources2 =this.dynamicTargetDataSources;if(dynamicTargetDataSources2.containsKey(datasourceId)){
log.info("数据源"+datasourceId+"之前已经创建，准备测试数据源是否正常...");DruidDataSource druidDataSource =(DruidDataSource) dynamicTargetDataSources2.get(datasourceId);boolean rightFlag =true;Connection connection =null;try{
log.info(datasourceId+"数据源的概况->当前闲置连接数："+druidDataSource.getPoolingCount());long activeCount = druidDataSource.getActiveCount();
log.info(datasourceId+"数据源的概况->当前活动连接数："+activeCount);if(activeCount >0){
log.info(datasourceId+"数据源的概况->活跃连接堆栈信息："+druidDataSource.getActiveConnectionStackTrace());}
log.info("准备获取数据库连接...");
connection = druidDataSource.getConnection();
log.info("数据源"+datasourceId+"正常");}catch(Exception e){
log.error(e.getMessage(),e);//把异常信息打印到日志文件
rightFlag =false;
log.info("缓存数据源"+datasourceId+"已失效，准备删除...");if(delDatasources(datasourceId)){
log.info("缓存数据源删除成功");}else{
log.info("缓存数据源删除失败");}}finally{if(null!= connection){
connection.close();}}if(rightFlag){
log.info("不需要重新创建数据源");return;}else{
log.info("准备重新创建数据源...");createDataSource(dataSource);
log.info("重新创建数据源完成");}}else{createDataSource(dataSource);}}privatevoidcreateDataSource(Partner dataSource)throwsException{String datasourceId = dataSource.getCode();
log.info("准备创建数据源"+datasourceId);String databasetype = dataSource.getDbType();String username = dataSource.getDbUser();String password = dataSource.getDbPwd();String url = dataSource.getDbUrl();String driveClass ="com.mysql.jdbc.Driver";if(testDatasource(datasourceId,driveClass,url,username,password)){boolean result =this.createDataSource(datasourceId, driveClass, url, username, password, databasetype);if(!result){
log.error("数据源"+datasourceId+"配置正确，但是创建失败");thrownewException("数据源"+datasourceId+"配置正确，但是创建失败");}}else{
log.error("数据源配置有错误");thrownewException("数据源配置有错误");}}}
BaseDataSourceConfig 基础数据连接配置
@Configuration@EnableTransactionManagementpublicclassBaseDataSourceConfig{@Value("${spring.datasource.url}")privateString url;@Value("${spring.datasource.username}")privateString username;@Value("${spring.datasource.password}")privateString password;@Value("${spring.datasource.driverClassName}")privateString driverClassName;@Bean("dataSourceBase")publicDataSourcedataSourceBase()throwsException{Properties props =newProperties();
props.put("driverClassName", driverClassName);
props.put("url", url);
props.put("username", username);
props.put("password", password);returnDruidDataSourceFactory.createDataSource(props);}@Bean@PrimarypublicDynamicDataSourcedataSource(@AutowiredDataSource dataSourceBase){DynamicDataSource dataSource =newDynamicDataSource();
dataSource.setDefaultTargetDataSource(dataSourceBase);Map<Object,Object> targetDataSources =newHashMap<Object,Object>();
targetDataSources.put("dataSourceBase", dataSourceBase);
dataSource.setTargetDataSources(targetDataSources);return dataSource;}@Bean("transactionManager")publicDataSourceTransactionManagerdataSourceTransactionManager(@AutowiredDynamicDataSource dataSource){returnnewDataSourceTransactionManager(dataSource);}@Bean("jdbcTemplate")publicJdbcTemplatejdbcTemplate(@AutowiredDynamicDataSource dataSource){returnnewJdbcTemplate(dataSource);}@BeanpublicSqlSessionFactorysqlSessionFactory(@AutowiredDynamicDataSource dataSource)throwsException{SqlSessionFactoryBean sqlSessionFactoryBean =newSqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);ResourcePatternResolver resolver =newPathMatchingResourcePatternResolver();
sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:com/master/*/*/mapping/*.xml"));return sqlSessionFactoryBean.getObject();}@BeanpublicSqlSessionTemplatesqlSessionTemplate(SqlSessionFactory sqlSessionFactory){returnnewSqlSessionTemplate(sqlSessionFactory);}@BeanpublicMapperScannerConfigurermapperScannerConfigurer(){MapperScannerConfigurer mapperScannerConfigurer =newMapperScannerConfigurer();
mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
mapperScannerConfigurer.setBasePackage("com.master.*.*.mapper");return mapperScannerConfigurer;}}
2.2 初始化各动态数据源
PartnerDataSourceService 主要2个地方是用：
- 项目启动时，调用initAllPartnerDataSource() 初始化所有租户的连接
- 租户有变动时，更新连接
@ServicepublicclassPartnerDataSourceService{@ResourceprivateIPartnerService partnerService;@ResourceprivateDynamicDataSource dynamicDataSource;@ResourceprivateMongoContext mongoContext;@ResourceprivateMongoClient mongoClientBase;@ResourceprivateMongoDbFactory mongoDbFactoryBase;@Value("${spring.data.mongodb.database}")privateString baseMongoDb;publicvoidinitAllPartnerDataSource(){List<Partner> list = partnerService.getAllPartner("");
mongoContext.createMongoFactory(MasterConst.BASE_DB_KEY, mongoClientBase, mongoDbFactoryBase, baseMongoDb);for(Partner partner : list){try{initDataSource(partner);}catch(Exception e){
e.printStackTrace();}}}publicvoidinitDataSource(Partner partner)throwsException{String code = partner.getCode();
dynamicDataSource.delDatasources(code);
dynamicDataSource.createDataSource(
code,"com.mysql.jdbc.Driver",
partner.getDbUrl(),
partner.getDbUser(),
partner.getDbPwd(),
partner.getDbType());//附件保存在mongodb，也在这里动态连接//mongoContext.createMongoFactory(partner);}}
2.3 nginx配置（主要部分）
访问系统时，在header记录datasource
location /base/ {
proxy_set_header datasource base;
proxy_pass http://localhost:88/app/;
proxy_cookie_path /app /base;}
location /a1/ {
proxy_set_header datasource a1;
proxy_pass http://localhost:88/app/;
proxy_cookie_path /app /a1;}
location /a2/ {
proxy_set_header datasource a2;
proxy_pass http://localhost:88/app/;
proxy_cookie_path /app /a2;}
2.4 spring拦截器处理
DataSourceInterceptor。 每次请求时，都把header里的datasource记录到session
@ComponentpublicclassDataSourceInterceptorimplementsHandlerInterceptor{/*
* 用于设置数据源。
*/@OverridepublicbooleanpreHandle(HttpServletRequest request,HttpServletResponse response,Object handler)throwsException{String datasourceHeader = request.getHeader("datasource");Subject subject =SecurityUtils.getSubject();Session session = subject.getSession();if(!StringUtil.isEmpty(datasourceHeader)){
session.setAttribute(SessionConst.DATASOURCE, datasourceHeader);
request.setAttribute("ctx","/"+ datasourceHeader);}returntrue;}}
2.5 非web访问场景的处理
部分场景不是web访问触发，比如定时任务。我在这里的处理是手动切换，程序执行完以后再清空：
DBContextHolder.setDataSource(目标源);//do somethingDBContextHolder.clearDataSource();
3. 其他
进行了复合场景测试：
- 后台不定期执行定时任务，触发切换
- web随机访问
记录定时任务和web访问的执行结果，都符合预期。
欢迎讨论
本文转载自: https://blog.csdn.net/fantoutou1/article/details/131779389
版权归原作者 fabulousme 所有， 如有侵权，请联系我们删除。
版权归原作者 fabulousme 所有， 如有侵权，请联系我们删除。