多数据源切换
多数据源解决方案
mybatis-plus动态切换数据源
- 该方法简单便捷，直接通过注解@DS("xxx")就可以切换数据源
- 但是这边官方建议只添加在方法上或类上，所以在同一个方法中只能使用一种数据源
依赖配置
1.添加依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.6.0</version>
</dependency>
2.yml文件
spring:
datasource:
dynamic:
primary: master #设置默认的数据源或者数据源组,默认值即为master
strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
datasource:
master:
url: jdbc:mysql://localhost:3306/test?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8&serverTimezone=GMT
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver # 3.2.0开始支持SPI可省略此配置
slave_1:
url: jdbc:mysql://xxxx:3306/test?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8&serverTimezone=GMT
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
controller
package com.wpc.springbootdynamicsourceswtich.controller;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.wpc.springbootdynamicsourceswtich.service.IndexServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
/**
* @ClassName IndexController
* @Description TODO
* @Author wpc
* @Date 2023/6/7 14:06
*/
@RestController
@RequestMapping("/index")
public class IndexController {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private IndexServiceImpl indexService;
@PostMapping("/master")
public List selectAllByMaster(){
return jdbcTemplate.queryForList("select * from sys_user");
}
@PostMapping("/slave")
public List selectAllBySlaver(){
List list = indexService.getList();
List list2 = indexService.getList2();
System.out.println(list);
System.out.println(list2);
return indexService.getList();
}
}
mapper
@Mapper
public interface IndexMapper extends BaseMapper<SysUser> {
//这里直接使用mybatis-plus的方法
}
service
@Service
public class IndexServiceImpl {
@Autowired
private IndexMapper indexMapper;
@Autowired
private JdbcTemplate jdbcTemplate;
@DS("slave_1")
public List getList() {
return indexMapper.selectList(null);
}
@DS("master")
public List getList2() {
return jdbcTemplate.queryForList("select * from sys_user");
}
}
AbstractRoutingDataSource手动切换数据源
- 在程序运行时通过AOP切面动态切换当前线程绑定的数据源对象，即数据库事物上下文来实现的
源码
@Nullable
private Map<Object, Object> targetDataSources; // 存放所有数据源
@Nullable
private Object defaultTargetDataSource; //存放默认数据源
@Nullable
private Map<Object, DataSource> resolvedDataSources; //targetDataSources 数据源集合的解析后的key-value对象
@Nullable
private DataSource resolvedDefaultDataSource; // 解析后的默认数据源对象
//由于该类实现了InitializingBean，在bean属性初始化之后执行该方法，spring的扩展点
//解析targetDataSources放入resolvedDataSources
public void afterPropertiesSet() {
if (this.targetDataSources == null) {
throw new IllegalArgumentException("Property 'targetDataSources' is required");
} else {
this.resolvedDataSources = CollectionUtils.newHashMap(this.targetDataSources.size());
this.targetDataSources.forEach((key, value) -> {
Object lookupKey = this.resolveSpecifiedLookupKey(key);
DataSource dataSource = this.resolveSpecifiedDataSource(value);
this.resolvedDataSources.put(lookupKey, dataSource);
});
if (this.defaultTargetDataSource != null) {
this.resolvedDefaultDataSource = this.resolveSpecifiedDataSource(this.defaultTargetDataSource);
}
}
}
//this.determineTargetDataSource()获取数据源，然后不同的数据源获取自己的连接
public Connection getConnection(String username, String password) throws SQLException {
return this.determineTargetDataSource().getConnection(username, password);
}
// 这里根据key值获取对应的数据源
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
Object lookupKey = this.determineCurrentLookupKey();
DataSource dataSource = (DataSource)this.resolvedDataSources.get(lookupKey);
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource;
}
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
} else {
return dataSource;
}
}
// 重写该方法，设置数据源对应的key
@Nullable
protected abstract Object determineCurrentLookupKey();
配置文件
// 这里的连接参数必须要和自己选择的数据源连接对象一致，比如MysqlDatasource使用的时user DruidDataSource使用的是username
spring:
datasource:
order:
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=true&serverTimezone=UTC
user: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
storage:
url: jdbc:mysql://xxx:3306/test?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
user: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
启动类
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
@MapperScan(basePackages = {"com.wpc.springbootmanuswitch.mapper"})
public class SpringbootManuSwitchApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootManuSwitchApplication.class, args);
}
}
配置类
@Configuration
public class DataSourceProxyConfig {
/**
* 这里使用MysqlDataSource数据源则，yml里面的用户名为user，要与 protected String user = null; 对应
* 使用 DruidDataSource 数据源 ，yml里面则使用username
* @return
*/
@Bean("originOrder")
@ConfigurationProperties(prefix = "spring.datasource.order")
public DataSource dataSourceMaster() {
return new MysqlDataSource();
}
@Bean("originStorage")
@ConfigurationProperties(prefix = "spring.datasource.storage")
public DataSource dataSourceStorage() {
return new MysqlDataSource();
}
@Bean(name = "order")
public DataSource masterDataSourceProxy(@Qualifier("originOrder") DataSource dataSource) {
return dataSource;
}
@Bean(name = "storage")
public DataSource storageDataSourceProxy(@Qualifier("originStorage") DataSource dataSource) {
return dataSource;
}
@Bean("dynamicDataSource")
public DataSource dynamicDataSource(@Qualifier("order") DataSource order,
@Qualifier("storage") DataSource storage) {
DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
// 数据源的集合
Map<Object, Object> dataSourceMap = new HashMap<>(3);
dataSourceMap.put("order", order);
dataSourceMap.put("storage", storage);
dynamicRoutingDataSource.setDefaultTargetDataSource(order);
dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);
return dynamicRoutingDataSource;
}
@Bean
@ConfigurationProperties(prefix = "mybatis")
public SqlSessionFactoryBean sqlSessionFactoryBean(@Qualifier("dynamicDataSource") DataSource dataSource) {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
org.apache.ibatis.session.Configuration configuration=new org.apache.ibatis.session.Configuration();
//使用jdbc的getGeneratedKeys获取数据库自增主键值
configuration.setUseGeneratedKeys(true);
//使用列别名替换列名
configuration.setUseColumnLabel(true);
//自动使用驼峰命名属性映射字段，如userId ---> user_id
configuration.setMapUnderscoreToCamelCase(true);
sqlSessionFactoryBean.setConfiguration(configuration);
return sqlSessionFactoryBean;
}
}
public class DynamicDataSourceContextHolder {
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
public static void setDataSourceKey(String key) {
CONTEXT_HOLDER.set(key);
}
public static String getDataSourceKey() {
return CONTEXT_HOLDER.get();
}
public static void clearDataSourceKey() {
CONTEXT_HOLDER.remove();
}
}
@Slf4j
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
log.info("当前数据源 [{}]", DynamicDataSourceContextHolder.getDataSourceKey());
return DynamicDataSourceContextHolder.getDataSourceKey();
}
}
controller
@RestController
@RequestMapping("/order")
@Slf4j
public class OrderController {
@Autowired
private OrderService orderService;
@PostMapping("/createOrder")
public String createOrder() throws Exception {
Order order = new Order();
order.setCount(1);
order.setMoney(100);
order.setUserId(10000l);
order.setProductId(1l);
orderService.saveOrder(order);
return "ok";
}
}
实体
@Data
public class Order {
@TableField("id")
private Long id;
@TableField("userId")
private Long userId;
@TableField("productId")
private Long productId;
@TableField("count")
private Integer count;
@TableField("money")
private Integer money;
@TableField("status")
private Integer status;
}
@Data
public class Storage {
@TableField("id")
private Long id;
@TableField("productId")
private Long productId;
@TableField("count")
private Integer count;
}
mapper
@Mapper
public interface OrderMapper {
@Insert("INSERT INTO `order`(userId, productId, count, status, money) VALUES (#{userId}, #{productId}, #{count}, #{status}, #{money})")
@Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
int insert(Order record);
/**
* 更新订单状态
* @param id
* @param status
* @return
*/
@Update("UPDATE `order` SET status = #{status} WHERE id = #{id}")
int updateOrderStatus(@Param("id") Long id, @Param("status") int status);
}
@Mapper
public interface StorageMapper {
/**
* 获取库存
* @param productId 商品编号
* @return
*/
@Select("SELECT id,productId,count FROM storage WHERE productId = #{productId}")
Storage findByProductId(@Param("productId") Long productId);
/**
* 扣减库存
* @param productId 商品编号
* @param count 要扣减的库存
* @return
*/
@Update("UPDATE storage SET count = count - #{count} WHERE productId = #{productId}")
int reduceStorage(@Param("productId") Long productId, @Param("count") Integer count);
}
service
@Service
@Slf4j
public class OrderServiceImpl implements OrderService {
@Autowired
private OrderMapper orderMapper;
@Autowired
private StorageService storageService;
@Override
@Transactional
public void saveOrder(Order order) {
log.info("=============切换数据源=================");
DynamicDataSourceContextHolder.setDataSourceKey("order");
log.info("=============插入订单=================");
orderMapper.insert(order);
log.info("=============扣减库存=================");
storageService.reduce(order);
log.info("=============更新订单状态=================");
//切换数据源
log.info("=============切换数据源=================");
DynamicDataSourceContextHolder.setDataSourceKey("order");
//更新订单
Integer updateOrderRecord = orderMapper.updateOrderStatus(order.getId(),1);
log.info("更新订单id:{} {}", order.getId(), updateOrderRecord > 0 ? "成功" : "失败");
}
}
@Service
@Slf4j
public class StorageServiceImpl implements StorageService {
@Autowired
private StorageMapper storageMapper;
@Override
@Transactional
public void reduce(Order order) {
log.info("切换数据源");
DynamicDataSourceContextHolder.setDataSourceKey("storage");
log.info("检查库存");
Storage storage = storageMapper.findByProductId(order.getProductId());
if(storage == null){
throw new RuntimeException("未找到该商品库存");
}
if (storage.getCount() < order.getCount()) {
log.warn("{} 库存不足，当前库存:{}", order.getProductId(), order.getCount());
throw new RuntimeException("库存不足");
}
log.info("开始扣减库存");
storageMapper.reduceStorage(order.getProductId(), order.getCount());
}
}
