提示
实现一个查询同时获取多个数据源内相关视图或者表里的信息
新建一个maven model,配置基本项目内容
xml
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-typehandlers-jsr310</artifactId>
<version>1.0.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-config</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-bootstrap</artifactId>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>5.7.1</scope>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
yml
server:
port: 8230
spring:
application:
name: rec-multiple-data-sources
datasource:
dynamic:
druid:
# 连接池建立时创建的初始化连接数
initialSize: 3
# 连接池中最小的活跃连接数
minIdle: 5
# 连接池中最大的活跃连接数
maxActive: 20
# 获取连接等待超时的时间
maxWait: 30000
# 间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 一个连接在池中最小生存的时间，单位是毫秒
minEvictableIdleTimeMillis: 300000
maxEvictableIdleTimeMillis: 900000
validationQuery: SELECT 1 FROM DUAL
# 是否在连接空闲一段时间后检测其可用性
testWhileIdle: true
# 是否在获得连接后检测其可用性
testOnBorrow: false
# 是否在连接放回连接池后检测其可用性
testOnReturn: false
# 打开PSCache，并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
filters: stat
# 合并多个DruidDataSource的监控数据
useGlobalDataSourceStat: true
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
connection-properties:
druid:
stat: mergeSql=true;slowSqlMillis=5000
primary: master
datasource:
master:
url: jdbc:oracle:thin:@localhost:1521/master
username: root
password: 123456
driver-class-name: oracle.jdbc.driver.OracleDriver
mybatis-plus:
mapper-locations: classpath:mapper/*Mapper.xml
eureka:
client:
service-url:
defaultZone: http://localhost:7777/eureka
sql
-- Create table
create table DB_STORAGE
(
id VARCHAR2(64),
data_source VARCHAR2(32),
data_source_name VARCHAR2(32),
username VARCHAR2(32),
password VARCHAR2(32),
url VARCHAR2(128),
type NUMBER(1),
creator VARCHAR2(64),
gmt_created DATE,
modifier VARCHAR2(64),
gmt_modified DATE
)
tablespace MEDQC
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column DB_STORAGE.id
is '主键id';
comment on column DB_STORAGE.data_source
is '代码';
comment on column DB_STORAGE.data_source_name
is '名称';
comment on column DB_STORAGE.username
is '数据库用户名';
comment on column DB_STORAGE.password
is '数据库密码';
comment on column DB_STORAGE.url
is '数据库地址';
comment on column DB_STORAGE.type
is '数据库类型 1.oracle 2.sqlserver 3.mysql';
comment on column DB_STORAGE.creator
is '创建者';
comment on column DB_STORAGE.gmt_created
is '创建时间';
comment on column DB_STORAGE.modifier
is '修改者';
comment on column DB_STORAGE.gmt_modified
is '修改时间';
java
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.creator.DefaultDataSourceCreator;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.druid.DruidConfig;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.lhw.rec.data.sources.mapper.DataSourcesMapper;
import com.lhw.rec.entity.qc.RecTypeDictDbStorageEntity;
import com.lhw.rec.enums.DbDriverEnum;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.List;
@Component
public class DynamicDataSourcesConfig {
@Resource
private DataSourcesMapper dataSourcesMapper;
@Resource
private DataSource dataSource;
@Resource
private DefaultDataSourceCreator defaultDataSourceCreator;
/**
* 初始化所有数据源
*/
@PostConstruct
private void init() {
//查出所有数据源
List<RecTypeDictDbStorageEntity> recTypeDictDbStorageEntities = dataSourcesMapper.selectList(new QueryWrapper<>());
//获取当前主数据源的配置信息
DruidConfig druidConfig = new DruidConfig();
druidConfig.setInitConnectionSqls("select 1");
recTypeDictDbStorageEntities.forEach(recTypeDictDbStorageEntity -> {
DataSourceProperty dataSourceProperty = new DataSourceProperty()
.setUrl(recTypeDictDbStorageEntity.getUrl())
.setPoolName(recTypeDictDbStorageEntity.getDataSource())
.setUsername(recTypeDictDbStorageEntity.getUsername())
.setPassword(recTypeDictDbStorageEntity.getPassword())
.setDriverClassName(DbDriverEnum.instanceOf(recTypeDictDbStorageEntity.getType()).getDriverName())
.setDruid(druidConfig);
DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
ds.addDataSource(dataSourceProperty.getPoolName(), defaultDataSourceCreator.createDataSource(dataSourceProperty));
});
}
}
这是获取数据库里的数据源信息，装载在数据库配置中
javaimport com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import com.lhw.rec.common.ResultCodeEnum;
import com.lhw.rec.data.sources.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
/**
*用于实现一个service方法切换多个数据源查询目前只支持查询List
*/
@Slf4j
@Aspect
@Component
public class DataSourceAspect {
@Resource
private DynamicRoutingDataSource dataSource;
@Around("execution(* com.lhw.medqc.data.sources.service.impl.*.*(..))")
public Object around(ProceedingJoinPoint joinPoint) {
List<Object> result = new ArrayList<>();
try {
Map<String, DataSource> dataSources = dataSource.getDataSources();
for (String datasource : dataSources.keySet()) {
DynamicDataSourceContextHolder.push(datasource);
Object proceed = joinPoint.proceed();
if (Objects.isNull(proceed) || !(proceed instanceof List)) {
log.error("查询第三方为空");
return proceed;
} else {
result.addAll((List<Object>) proceed);
}
}
} catch (Throwable throwable) {
log.error("环绕异常通知！");
throw new BusinessException(ResultCodeEnum.ERR_0x1000);
} finally {
DynamicDataSourceContextHolder.clear();
}
return result;
}
}
监控impl目录下的方法，根据数据源名进行切换查询，将结果合并在list中
本文作者:Weee
本文链接:
版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！