由于公司需要实现saas平台,保证各商家的数据隔离,需要通过商家登录的时候指定相关数据源,进入系统后只查相应的数据库,使用springboot+dynamic-datasource实现
一、配置了数据库DB1、DB2和redis如下:
#数据库db1
spring.datasource.dynamic.primary=db1
spring.datasource.dynamic.strict=true
spring.datasource.dynamic.datasource.db1.url: jdbc:mysql://192.168.0.152:3306/zyb1130?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
spring.datasource.dynamic.datasource.db1.username=root
spring.datasource.dynamic.datasource.db1.password=root1234
spring.datasource.dynamic.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
#数据库db2
spring.datasource.dynamic.datasource.db2.url: jdbc:mysql://192.168.0.152:3306/zyb?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
spring.datasource.dynamic.datasource.db2.username=root
spring.datasource.dynamic.datasource.db2.password=root1234
spring.datasource.dynamic.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
#
#redis配置
#是否开启redis缓存 true开启 false关闭
spring.redis.open=true
#Redis服务器地址
spring.redis.host=127.0.0.1
spring.redis.password=123456
#Redis服务器连接端口
spring.redis.port:6379
#Redis数据库索引(默认为0)
spring.redis.database:0
#连接池最大连接数(使用0表示没有限制)
spring.redis.jedis.pool.max-active=500
#连接池最大阻塞等待时间(使用0表示没有限制)
spring.redis.jedis.pool.max-wait:3000
#连接池中的最大空闲连接
spring.redis.jedis.pool.max-idle:100
#连接池中的最小空闲连接
spring.redis.jedis.pool.min-idle:50
#控制一个pool可分配多少个jedis实例,用来替换上面的redis.maxActive,如果是jedis 2.4以后用该属性
spring.redis.jedis.pool.maxTotal:500
#连接超时时间(毫秒)
spring.redis.timeout:3000
#在空闲时检查有效性, 默认false
spring.redis.testWhileIdle:true
#是否在从池中取出连接前进行检验,如果检验失败,则从池中去除连接并尝试取出另一个
spring.redis.testOnBorrow:true
二、请求时通过@DS("DB1")在controller或service上指定数据源代码如下:
1、本例子是在其它方法里面通过request.getSession().setAttribute("dbname",dto.getType()),往session里面添加数据库连接池名称之后,再通过@DS注解使用,如果看到此方法的朋友,请灵活使用哦
@RestController
@RequestMapping("/user")
#此处的参数可以直接写成DB1,我这里是获取session数据
@DS(value = "#session.dbname")
public class DoctorController {
@Resource
private DataSource dataSource;
@Resource
private DefaultDataSourceCreator dataSourceCreator;
@Resource
private DruidDataSourceCreator druidDataSourceCreator;
@Resource
private HikariDataSourceCreator hikariDataSourceCreator;
@GetMapping("getDataSource")
public String getDataSource(){
DynamicRoutingDataSource drds= (DynamicRoutingDataSource) dataSource;
return "动态数据源";
}
# 推荐此方法
@PostMapping("/add")
public Set<String> add(@Validated @RequestBody DataSourceDTO dto) {
DataSourceProperty dataSourceProperty = new DataSourceProperty();
BeanUtils.copyProperties(dto, dataSourceProperty);
DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
DataSource dataSource = dataSourceCreator.createDataSource(dataSourceProperty);
ds.addDataSource(dto.getPoolName(), dataSource);
return ds.getDataSources().keySet();
}
#通过DataSourceDTO上传参数后,动态添加数据源,也可以通过数据库查询到之后再添加
@PostMapping("/addDruid")
public String addDruid(@Validated @RequestBody DataSourceDTO dto, HttpServletRequest request) {
DataSourceProperty dataSourceProperty = new DataSourceProperty();
BeanUtils.copyProperties(dto, dataSourceProperty);
// 3.4.0版本以下如果有此属性,需手动设置,不然会空指针。
// dataSourceProperty.setLazy(true);
DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
#基础Druid数据源
DataSource dataSource = druidDataSourceCreator.createDataSource(dataSourceProperty);
#推荐此方法
#DataSource dataSource = dataSourceCreator.createDataSource(dataSourceProperty)
#基础HikariCP数据源
#DataSource dataSource = hikariDataSourceCreator.createDataSource(dataSourceProperty);
ds.addDataSource(dto.getPoolName(), dataSource);
//添加数据源之后动态切换
request.getSession().setAttribute("dbname",dto.getType());
System.out.println("获取用户数据"+dto.getType());
List<HmsDoctorBean> hmsDoctorBeanList=doctorService.getUserList("15173205615");
for (HmsDoctorBean hmsDoctorBean : hmsDoctorBeanList) {
JSONObject jsonObject = (JSONObject) JSONObject.toJSON(hmsDoctorBean);
System.out.println(jsonObject);
}
// return ds.getDataSources().keySet();
return "添加成功";
}
}
@Service
@DS("#session.dbname")
public class DoctorServiceImpl implements IDoctorService {
@Autowired
HmsDoctorBeanMapper doctorBeanMapper;
@Override
public List<HmsDoctorBean> getDoctorList(String phone) {
return doctorBeanMapper.selectByPhone(phone);
}
@Override
public List<HmsDoctorBean> getUserList(String phone) {
return doctorBeanMapper.selectByPhone(phone);
}
}
maven引入jar
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
动态添加数据源的请求参数/wd/db/addDruid
{
"poolName":"db5",
"driverClassName":"com.mysql.jdbc.Driver",
"url":"jdbc:mysql://192.168.0.152:3306/zyb?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false",
"username":"root",
"password":"root1234",
"type":"db5"
}