2021-09-18

weixin_46007090

于 2023-03-23 17:50:31 发布

阅读量227

点赞数

文章标签： mybatis java spring boot

本文链接：https://blog.csdn.net/weixin_46007090/article/details/120371661

版权

多数据源使用文档
介绍
部分项目中存在多数据源需求，现基于mybatisplus的dynamic-datasource做多数据源demo，需要进行多数据源引入的项目可参考以下步骤。
使用方法
Demo
📎dynamic-datasource-sample.zip
git@code.dayu.work:dynamic-datasource-demo/dynamic-datasource-demo.git
引入依赖
示例demo版本基于3.3.2，使用框架为springBoot+Druid+Mybatis

com.baomidou
dynamic-datasource-spring-boot-starter
${version}

配置数据源
数据源配置可以采用系统内yml文件，数据库存储形式或采用nacos存储形式，目前demo中采用nacos存储配置，使得配置可以动态更新，配置信息存储形式不可同时使用，如部分存储nacos，部分存储数据库。
单数据源配置
spring:
datasource:
druid:
stat-view-servlet:
enabled: true
login-username: root
login-password: root
dynamic:
p6spy: true
lazy: true
# 配置全局druid参数，请按需配置
druid:
initial-size: 5
max-active: 8
min-idle: 3
max-wait: 1000
validation-query: ‘select 1’
# time-between-eviction-runs-millis:
# time-between-log-stats-millis:
# stat-sqlmax-size:
# min-evictable-idle-time-millis:
# max-evictable-idle-time-millis:
# test-while-idle:
# test-on-borrow:
# test-on-return:
# validation-query-timeout:
# use-global-datasource-stat:
# async-init:
# clear-filters-enable:
# reset-stat-enable:
# not-full-timeout-retry-count:
# max-wait-thread-count:
# fail-fast:
# phyTimeout-millis:
# keep-alive:
# pool-prepared-statements:
# init-variants:
# init-global-variants:
# use-unfair-lock:
# kill-when-socket-read-timeout:
# connection-properties:
# max-pool-prepared-statement-per-connection-size:
# init-connection-sqls:
# share-prepared-statements:
# connection-errorretry-attempts:
# break-after-acquire-failure:
# filters: stat,wall # 注意这个值和druid原生不一致，默认启动了stat,wall
datasource:
# 名称可自定义
mysql:
username: root
password: root
url: jdbc:mysql://localhost:3306/dynamic_datasource_test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
schema: db/schema.sql
# 设置主数据源
primary: mysql

management:
endpoints:
web:
exposure:
include: ‘*’
logging:
level:
com.baomidou: debug
配置单数据源或者纯粹多库时，需要设置主数据源，primary属性。
主从数据源配置
spring:
datasource:
druid:
stat-view-servlet:
enabled: true
login-username: root
login-password: root
dynamic:
#设置默认的数据源或者数据源组,默认值即为master
primary: master
p6spy: true
lazy: true
# 配置全局druid参数，请按需配置
druid:
initial-size: 5
max-active: 8
min-idle: 3
max-wait: 1000
validation-query: ‘select 1’
# time-between-eviction-runs-millis:
# time-between-log-stats-millis:
# stat-sqlmax-size:
# min-evictable-idle-time-millis:
# max-evictable-idle-time-millis:
# test-while-idle:
# test-on-borrow:
# test-on-return:
# validation-query-timeout:
# use-global-datasource-stat:
# async-init:
# clear-filters-enable:
# reset-stat-enable:
# not-full-timeout-retry-count:
# max-wait-thread-count:
# fail-fast:
# phyTimeout-millis:
# keep-alive:
# pool-prepared-statements:
# init-variants:
# init-global-variants:
# use-unfair-lock:
# kill-when-socket-read-timeout:
# connection-properties:
# max-pool-prepared-statement-per-connection-size:
# init-connection-sqls:
# share-prepared-statements:
# connection-errorretry-attempts:
# break-after-acquire-failure:
# filters: stat,wall # 注意这个值和druid原生不一致，默认启动了stat,wall
datasource:
master:
username: root
password: root
url: jdbc:mysql://localhost:3306/dynamic_datasource_test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
schema: db/schema.sql
slave_1:
username: root
password: root
url: jdbc:mysql://localhost:3307/dynamic_datasource_test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
druid:
initial-size: 6
slave_2:
username: root
password: root
url: jdbc:mysql://localhost:3308/dynamic_datasource_test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
druid:
initial-size: 6
# strategy: com.baomidou.dynamic.datasource.strategy.LoadBalanceDynamicDataSourceStrategy

management:
endpoints:
web:
exposure:
include: ‘*’
logging:
level:
com.baomidou: debug
主从数据库名称建议命名master，slave，也可以自定义命名。
数据源切换
使用@DS切换数据源，注解可以使用在方法或者类上，同时存在就近原则方法上注解优先于类上注解。
注解结果
没有@DS 默认数据源
@DS(“dsName”) dsName可以为组名也可以为具体某个库的名称
@Service
@DS(“master”)
public class UserServiceImpl implements UserService {

@Resource
private UserMapper userMapper;

@Override
public List<User> selectMasterUsers() {
    return userMapper.selectUsers(1);
}

@DS("slave_1")
@Override
public List<User> selectSlaveUsers() {
    return userMapper.selectUsers(1);
}

@Override
public void addUser(User user) {
    userMapper.addUser(user.getName(), user.getAge());
}

@Override
public void deleteUserById(Long id) {
    userMapper.deleteUserById(id);
}

@Override
@DS("#dataSource")
public List<User> getDatasourceData(String dataSource) {
    return userMapper.selectUsers(1);
}

}
如果想要不通过写注解进行自动读写分离（主库增删改，从库只读），可通过以下配置实现
@Bean
public MasterSlaveAutoRoutingPlugin masterSlaveAutoRoutingPlugin(){
return new MasterSlaveAutoRoutingPlugin();
}
默认主库名称master,从库名称slave。
动态创建删除数据源
dynamic-datasource提供接口可实现动态添加删除数据源，接口动态添加的数据源重启项目后会失效，在生产上谨慎操作。如果集群部署，建议使用一个监听器或者观察者之类模式，统一向一个地方发消息，其他机器监听自动加载创建新数据源。

package com.aliyun.gts.bpass.dynamic.datasource.controller;

import com.aliyun.gts.bpass.dynamic.datasource.dto.DataSourceDTO;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.creator.;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import lombok.AllArgsConstructor;
import org.springframework.beans.BeanUtils;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.;

import javax.sql.DataSource;
import java.util.Set;

/**

Describe class function detail.
@author Tara
@version v1.0
@date 2021 -03-18 17:14:37
@since jdk1.8
*/
@RestController
@AllArgsConstructor
@RequestMapping(“/datasources”)
public class DataSourceController {

/**
- Data source
  /
  private final DataSource dataSource;
  /*
- Data source creator
  /
  private final DefaultDataSourceCreator dataSourceCreator;
  /*
- Basic data source creator
  /
  private final BasicDataSourceCreator basicDataSourceCreator;
  /*
- Jndi data source creator
  /
  private final JndiDataSourceCreator jndiDataSourceCreator;
  /*
- Druid data source creator
  /
  private final DruidDataSourceCreator druidDataSourceCreator;
  /*
- Hikari data source creator
  */
  private final HikariDataSourceCreator hikariDataSourceCreator;
/**
- 获取当前所有数据源
- @return set set
  */
  @GetMapping
  public Set now() {
  DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
  return ds.getCurrentDataSources().keySet();
  }
/**
- 通用添加数据源（推荐）
- 测试数据配合查询方法 com.aliyun.gts.bpass.dynamic.datasource.controller.UserController#getDatasourceData(java.lang.String)
- {
- ```
"pollName": "Druid",
```
- ```
"driverClassName": "com.mysql.cj.jdbc.Driver",
```
- ```
"url": "jdbc:mysql://localhost:3306/dynamic_datasource_test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false",
```
- ```
"username": "root",
```
- ```
"password": "root"
```
- }
- @param dto dto
- @return set set
  */
  @PostMapping(“/add”)
  public Set 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.getPollName(), dataSource);
  return ds.getCurrentDataSources().keySet();
  }
/**
- 添加基础数据源
- 调用Springboot内置方法创建数据源，兼容1,2
- @param dto dto
- @return the set
  */
  @PostMapping(“/addBasic”)
  public Set addBasic(@Validated @RequestBody DataSourceDTO dto) {
  DataSourceProperty dataSourceProperty = new DataSourceProperty();
  BeanUtils.copyProperties(dto, dataSourceProperty);
  DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
  DataSource dataSource = basicDataSourceCreator.createDataSource(dataSourceProperty);
  ds.addDataSource(dto.getPollName(), dataSource);
  return ds.getCurrentDataSources().keySet();
  }
/**
- 添加JNDI数据源
- @param pollName poll name
- @param jndiName jndi name
- @return the set
  */
  @PostMapping(“/addJndi”)
  public Set addJndi(String pollName, String jndiName) {
  DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
  DataSource dataSource = jndiDataSourceCreator.createDataSource(jndiName);
  ds.addDataSource(pollName, dataSource);
  return ds.getCurrentDataSources().keySet();
  }
/**
- 基础Druid数据源
- @param dto dto
- @return the set
  */
  @PostMapping(“/addDruid”)
  public Set addDruid(@Validated @RequestBody DataSourceDTO dto) {
  DataSourceProperty dataSourceProperty = new DataSourceProperty();
  BeanUtils.copyProperties(dto, dataSourceProperty);
  DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
  DataSource dataSource = druidDataSourceCreator.createDataSource(dataSourceProperty);
  ds.addDataSource(dto.getPollName(), dataSource);
  return ds.getCurrentDataSources().keySet();
  }
/**
- 基础HikariCP数据源
- @param dto dto
- @return the set
  */
  @PostMapping(“/addHikariCP”)
  public Set addHikariCP(@Validated @RequestBody DataSourceDTO dto) {
  DataSourceProperty dataSourceProperty = new DataSourceProperty();
  BeanUtils.copyProperties(dto, dataSourceProperty);
  DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
  DataSource dataSource = hikariDataSourceCreator.createDataSource(dataSourceProperty);
  ds.addDataSource(dto.getPollName(), dataSource);
  return ds.getCurrentDataSources().keySet();
  }
/**
- 删除数据源
- @param name name
- @return the string
  */
  @DeleteMapping
  public String remove(String name) {
  DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
  ds.removeDataSource(name);
  return “删除成功”;
  }
  }
  动态解析数据源
  动态添加的数据源，如果想使用，可通过动态解析方式使用，目前dynamic-datasource默认支持三个职责链来处理动态参数解析器 header->session->spel 所有以 # 开头的参数都会从参数中获取数据源。
  测试过程中发现，如果系统配置了自动读写分离，新增数据源不符合从库命名规则，如从库命名slave_1，新增数据源test，在查询时，无法动态使用数据源test，如果命名为slave_2，则可以正常使用。
  @DS(“#session.tenantName”)//从session获取
  public List selectSpelBySession() {
  return userMapper.selectUsers();
  }

@DS(“#header.tenantName”)//从header获取
public List selectSpelByHeader() {
return userMapper.selectUsers();
}

@DS(“#tenantName”)//使用spel从参数获取
public List selectSpelByKey(String tenantName) {
return userMapper.selectUsers();
}

@DS(“#user.tenantName”)//使用spel从复杂参数获取
public List selecSpelByTenant(User user) {
return userMapper.selectUsers();
}
事务相关
目前dynamic-datasource的多库不支持原生spring事务，单库下（即service方法下不做数据源切换）可以使用spring事务。
解决方案：
目前官方提供了两种解决方案供参考，一种是引入seata进行集成，但需要维护seata server，另外一种是使用组件中支持的本地多数据源事务，但目前还不成熟，生产慎重考虑。
参考链接
dynamic-datasource官方文档
dynamic-datasource-samples
Docker搭建主从数据库

weixin_46007090

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
2021-09-18

多数据源使用文档介绍部分项目中存在多数据源需求，现基于mybatisplus的dynamic-datasource做多数据源demo，需要进行多数据源引入的项目可参考以下步骤。使用方法Demo????dynamic-datasource-sample.zipgit@code.dayu.work:dynamic-datasource-demo/dynamic-datasource-demo.git引入依赖示例demo版本基于3.3.2，使用框架为springBoot+Druid+Mybatis
复制链接

扫一扫