当涉及到分库分表和数据源切换时，可以采用
dynamic-datasource与ShardingSphere的集成方案来满足这一需求。
微服务架构：在微服务架构中，每个服务可以使用自己的数据库，确保服务之间的数据隔离和独立性，避免对同一数据库的竞争。
读写分离：通过将写操作指向主数据库，将读操作分配到一个或多个从数据库上，以提高系统的读取性能和负载均衡。
灾难恢复和备份：将数据分散到多个数据源中，可以在主数据库发生故障时快速切换到备份数据库，确保业务的连续性。
不同地域的数据存储：在跨地域的应用中，可以将数据存储在离用户更近的数据库中，以减少延迟和提高访问速度。
多租户架构：在多租户应用中，每个租户可以有独立的数据源，以确保数据的安全性和隐私。
兼容旧系统：在进行系统迁移时，可以暂时使用多数据源，逐步将数据从旧系统迁移到新系统中，而不影响业务的正常运行。
数据审计和监控：使用专门的数据源记录审计日志和监控信息，确保主业务逻辑不受干扰。
<!-- ShardingSphere 读写分离/分库分表 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.2</version>
</dependency>
# spring配置
spring:
redis:
host: localhost
port: 6379
password:
# 分库分表配置
shardingsphere:
datasource:
ds0:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
username: root
password: password
jdbc-url: jdbc:mysql://localhost:3306/ry-log?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
names: ds0
props:
sql-show: true
rules:
sharding:
sharding-algorithms:
table-inline:
props:
algorithm-expression: sys_oper_log_$->{request_method}
type: INLINE
tables:
sys_oper_log:
actual-data-nodes: ds0.sys_oper_log_GET,ds0.sys_oper_log_POST,ds0.sys_oper_log_PUT,ds0.sys_oper_log_DELETE
table-strategy:
standard:
sharding-algorithm-name: table-inline
sharding-column: request_method
# 动态数据源配置
datasource:
dynamic:
datasource:
master:
username: root
password: password
url: jdbc:mysql://localhost:3306/ry-cloud?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
driver-class-name: com.mysql.cj.jdbc.Driver
order:
username: root
password: password
url: jdbc:mysql://localhost:3306/ry-order?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
driver-class-name: com.mysql.cj.jdbc.Driver
# mybatis配置
mybatis:
# 搜索指定包别名
typeAliasesPackage: com.ruoyi.system
# 配置mapper的扫描，找到所有的mapper.xml映射文件
mapperLocations: classpath:mapper/**/*.xml
# seata配置
seata:
enabled: false
DROP DATABASE IF EXISTS `ry-order`;
CREATE DATABASE `ry-order` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
USE `ry-order`;
-- ----------------------------
-- 订单信息表sys_order
-- ----------------------------
DROP TABLE IF EXISTS sys_order;
CREATE TABLE sys_order
(
order_id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
user_id BIGINT(64) NOT NULL COMMENT '用户编号',
STATUS CHAR(1) NOT NULL COMMENT '状态（0交易成功 1交易失败）',
order_no VARCHAR(64) DEFAULT NULL COMMENT '订单流水',
PRIMARY KEY (order_id)
) ENGINE=INNODB COMMENT = '订单信息表';
package com.ruoyi.system.domain;
import com.ruoyi.common.core.web.domain.BaseEntity;
/**
* 订单对象 tb_order
*
*/
public class SysOrder extends BaseEntity
{
private static final long serialVersionUID = 1L;
/** 订单编号 */
private Long orderId;
/** 用户编号 */
private Long userId;
/** 状态 */
private String status;
/** 订单编号 */
private String orderNo;
public void setOrderId(Long orderId)
{
this.orderId = orderId;
}
public Long getOrderId()
{
return orderId;
}
public void setUserId(Long userId)
{
this.userId = userId;
}
public Long getUserId()
{
return userId;
}
public void setStatus(String status)
{
this.status = status;
}
public String getStatus()
{
return status;
}
public void setOrderNo(String orderNo)
{
this.orderNo = orderNo;
}
public String getOrderNo()
{
return orderNo;
}
}
package com.ruoyi.system.mapper;
import java.util.List;
import com.ruoyi.system.domain.SysOrder;
/**
* 订单Mapper接口
*
*/
public interface SysOrderMapper
{
/**
* 查询订单
*
* @param orderId 订单编号
* @return 订单信息
*/
public SysOrder selectSysOrderById(Long orderId);
/**
* 查询订单列表
*
* @param sysOrder 订单信息
* @return 订单列表
*/
public List<SysOrder> selectSysOrderList(SysOrder sysOrder);
/**
* 新增订单
*
* @param sysOrder 订单
* @return 结果
*/
public int insertSysOrder(SysOrder sysOrder);
}
package com.ruoyi.system.service;
import java.util.List;
import com.ruoyi.system.domain.SysOrder;
/**
* 订单Service接口
*
*/
public interface ISysOrderService
{
/**
* 查询订单
*
* @param orderId 订单编号
* @return 订单信息
*/
public SysOrder selectSysOrderById(Long orderId);
/**
* 查询订单列表
*
* @param sysOrder 订单信息
* @return 订单列表
*/
public List<SysOrder> selectSysOrderList(SysOrder sysOrder);
/**
* 新增订单
*
* @param sysOrder 订单
* @return 结果
*/
public int insertSysOrder(SysOrder sysOrder);
}
package com.ruoyi.system.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.ruoyi.system.domain.SysOrder;
import com.ruoyi.system.mapper.SysOrderMapper;
import com.ruoyi.system.service.ISysOrderService;
/**
* 订单Service业务层处理
*
*/
@Service
public class SysOrderServiceImpl implements ISysOrderService
{
@Autowired
private SysOrderMapper myShardingMapper;
/**
* 查询订单
*
* @param orderId 订单编号
* @return 订单信息
*/
@Override
@DS("order")
public SysOrder selectSysOrderById(Long orderId)
{
return myShardingMapper.selectSysOrderById(orderId);
}
/**
* 查询订单列表
*
* @param sysOrder 订单信息
* @return 订单列表
*/
@Override
@DS("order")
public List<SysOrder> selectSysOrderList(SysOrder sysOrder)
{
return myShardingMapper.selectSysOrderList(sysOrder);
}
/**
* 新增订单
*
* @param sysOrder 订单
* @return 结果
*/
@Override
@DS("order")
public int insertSysOrder(SysOrder sysOrder)
{
return myShardingMapper.insertSysOrder(sysOrder);
}
}
package com.ruoyi.system.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.ruoyi.common.core.utils.uuid.IdUtils;
import com.ruoyi.common.core.web.controller.BaseController;
import com.ruoyi.common.core.web.domain.AjaxResult;
import com.ruoyi.system.domain.SysOrder;
import com.ruoyi.system.service.ISysOrderService;
/**
* 订单 Controller
*
*/
@RestController
@RequestMapping("/order")
public class SysOrderController extends BaseController
{
@Autowired
private ISysOrderService sysOrderService;
@GetMapping("/add/{userId}")
public AjaxResult add(@PathVariable("userId") Long userId)
{
SysOrder sysOrder = new SysOrder();
sysOrder.setUserId(userId);
sysOrder.setStatus("0");
sysOrder.setOrderNo(IdUtils.fastSimpleUUID());
return AjaxResult.success(sysOrderService.insertSysOrder(sysOrder));
}
@GetMapping("/list")
public AjaxResult list(SysOrder sysOrder)
{
return AjaxResult.success(sysOrderService.selectSysOrderList(sysOrder));
}
@GetMapping("/query/{orderId}")
public AjaxResult query(@PathVariable("orderId") Long orderId)
{
return AjaxResult.success(sysOrderService.selectSysOrderById(orderId));
}
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.system.mapper.SysOrderMapper">
<resultMap type="SysOrder" id="SysOrderResult">
<result property="orderId" column="order_id" />
<result property="userId" column="user_id" />
<result property="status" column="status" />
<result property="orderNo" column="order_no" />
</resultMap>
<sql id="selectSysOrderVo">
select order_id, user_id, status, order_no from sys_order
</sql>
<select id="selectSysOrderList" parameterType="SysOrder" resultMap="SysOrderResult">
<include refid="selectSysOrderVo"/>
</select>
<select id="selectSysOrderById" parameterType="Long" resultMap="SysOrderResult">
<include refid="selectSysOrderVo"/>
where order_id = #{orderId}
</select>
<insert id="insertSysOrder" parameterType="SysOrder">
insert into sys_order
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="orderId != null">order_id,</if>
<if test="userId != null">user_id,</if>
<if test="status != null">status,</if>
<if test="orderNo != null">order_no,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="orderId != null">#{orderId},</if>
<if test="userId != null">#{userId},</if>
<if test="status != null">#{status},</if>
<if test="orderNo != null">#{orderNo},</if>
</trim>
</insert>
</mapper>
package com.ruoyi.system.config;
import java.util.Map;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
/**
* 动态数据源配置
*
*/
@Configuration
public class DataSourceConfiguration
{
/**
* 分表数据源名称
*/
private static final String SHARDING_DATA_SOURCE_NAME = "sharding";
/**
* 动态数据源配置项
*/
@Autowired
private DynamicDataSourceProperties properties;
/**
* shardingjdbc有四种数据源，需要根据业务注入不同的数据源
*
* <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
* <p>2. 主从数据源: masterSlaveDataSource;
* <p>3. 脱敏数据源：encryptDataSource;
* <p>4. 影子数据源：shadowDataSource
*
*/
@Resource
private DataSource shardingSphereDataSource;
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider()
{
Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
return new AbstractDataSourceProvider()
{
@Override
public Map<String, DataSource> loadDataSources()
{
Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
// 将 shardingjdbc 管理的数据源也交给动态数据源管理
dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingSphereDataSource);
return dataSourceMap;
}
};
}
/**
* 将动态数据源设置为首选的
* 当spring存在多个数据源时, 自动注入的是首选的对象
* 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
*/
@Primary
@Bean
public DataSource dataSource()
{
DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
dataSource.setPrimary(properties.getPrimary());
dataSource.setStrict(properties.getStrict());
dataSource.setStrategy(properties.getStrategy());
dataSource.setP6spy(properties.getP6spy());
dataSource.setSeata(properties.getSeata());
return dataSource;
}
}
DROP DATABASE IF EXISTS `ry-log`;
CREATE DATABASE `ry-log` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
USE `ry-log`;
DROP TABLE IF EXISTS sys_oper_log_get;
CREATE TABLE sys_oper_log_get (
oper_id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '日志主键',
title VARCHAR(50) DEFAULT '' COMMENT '模块标题',
business_type INT(2) DEFAULT 0 COMMENT '业务类型（0其它 1新增 2修改 3删除）',
method VARCHAR(100) DEFAULT '' COMMENT '方法名称',
request_method VARCHAR(10) DEFAULT '' COMMENT '请求方式',
operator_type INT(1) DEFAULT 0 COMMENT '操作类别（0其它 1后台用户 2手机端用户）',
oper_name VARCHAR(50) DEFAULT '' COMMENT '操作人员',
dept_name VARCHAR(50) DEFAULT '' COMMENT '部门名称',
oper_url VARCHAR(255) DEFAULT '' COMMENT '请求URL',
oper_ip VARCHAR(128) DEFAULT '' COMMENT '主机地址',
oper_location VARCHAR(255) DEFAULT '' COMMENT '操作地点',
oper_param VARCHAR(2000) DEFAULT '' COMMENT '请求参数',
json_result VARCHAR(2000) DEFAULT '' COMMENT '返回参数',
STATUS INT(1) DEFAULT 0 COMMENT '操作状态（0正常 1异常）',
error_msg VARCHAR(2000) DEFAULT '' COMMENT '错误消息',
oper_time DATETIME COMMENT '操作时间',
PRIMARY KEY (oper_id)
) ENGINE=INNODB AUTO_INCREMENT=100 COMMENT = 'get操作日志记录';
DROP TABLE IF EXISTS sys_oper_log_post;
CREATE TABLE sys_oper_log_post (
oper_id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '日志主键',
title VARCHAR(50) DEFAULT '' COMMENT '模块标题',
business_type INT(2) DEFAULT 0 COMMENT '业务类型（0其它 1新增 2修改 3删除）',
method VARCHAR(100) DEFAULT '' COMMENT '方法名称',
request_method VARCHAR(10) DEFAULT '' COMMENT '请求方式',
operator_type INT(1) DEFAULT 0 COMMENT '操作类别（0其它 1后台用户 2手机端用户）',
oper_name VARCHAR(50) DEFAULT '' COMMENT '操作人员',
dept_name VARCHAR(50) DEFAULT '' COMMENT '部门名称',
oper_url VARCHAR(255) DEFAULT '' COMMENT '请求URL',
oper_ip VARCHAR(128) DEFAULT '' COMMENT '主机地址',
oper_location VARCHAR(255) DEFAULT '' COMMENT '操作地点',
oper_param VARCHAR(2000) DEFAULT '' COMMENT '请求参数',
json_result VARCHAR(2000) DEFAULT '' COMMENT '返回参数',
STATUS INT(1) DEFAULT 0 COMMENT '操作状态（0正常 1异常）',
error_msg VARCHAR(2000) DEFAULT '' COMMENT '错误消息',
oper_time DATETIME COMMENT '操作时间',
PRIMARY KEY (oper_id)
) ENGINE=INNODB AUTO_INCREMENT=100 COMMENT = 'post操作日志记录';
DROP TABLE IF EXISTS sys_oper_log_put;
CREATE TABLE sys_oper_log_put (
oper_id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '日志主键',
title VARCHAR(50) DEFAULT '' COMMENT '模块标题',
business_type INT(2) DEFAULT 0 COMMENT '业务类型（0其它 1新增 2修改 3删除）',
method VARCHAR(100) DEFAULT '' COMMENT '方法名称',
request_method VARCHAR(10) DEFAULT '' COMMENT '请求方式',
operator_type INT(1) DEFAULT 0 COMMENT '操作类别（0其它 1后台用户 2手机端用户）',
oper_name VARCHAR(50) DEFAULT '' COMMENT '操作人员',
dept_name VARCHAR(50) DEFAULT '' COMMENT '部门名称',
oper_url VARCHAR(255) DEFAULT '' COMMENT '请求URL',
oper_ip VARCHAR(128) DEFAULT '' COMMENT '主机地址',
oper_location VARCHAR(255) DEFAULT '' COMMENT '操作地点',
oper_param VARCHAR(2000) DEFAULT '' COMMENT '请求参数',
json_result VARCHAR(2000) DEFAULT '' COMMENT '返回参数',
STATUS INT(1) DEFAULT 0 COMMENT '操作状态（0正常 1异常）',
error_msg VARCHAR(2000) DEFAULT '' COMMENT '错误消息',
oper_time DATETIME COMMENT '操作时间',
PRIMARY KEY (oper_id)
) ENGINE=INNODB AUTO_INCREMENT=100 COMMENT = 'put操作日志记录';
DROP TABLE IF EXISTS sys_oper_log_delete;
CREATE TABLE sys_oper_log_delete (
oper_id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '日志主键',
title VARCHAR(50) DEFAULT '' COMMENT '模块标题',
business_type INT(2) DEFAULT 0 COMMENT '业务类型（0其它 1新增 2修改 3删除）',
method VARCHAR(100) DEFAULT '' COMMENT '方法名称',
request_method VARCHAR(10) DEFAULT '' COMMENT '请求方式',
operator_type INT(1) DEFAULT 0 COMMENT '操作类别（0其它 1后台用户 2手机端用户）',
oper_name VARCHAR(50) DEFAULT '' COMMENT '操作人员',
dept_name VARCHAR(50) DEFAULT '' COMMENT '部门名称',
oper_url VARCHAR(255) DEFAULT '' COMMENT '请求URL',
oper_ip VARCHAR(128) DEFAULT '' COMMENT '主机地址',
oper_location VARCHAR(255) DEFAULT '' COMMENT '操作地点',
oper_param VARCHAR(2000) DEFAULT '' COMMENT '请求参数',
json_result VARCHAR(2000) DEFAULT '' COMMENT '返回参数',
STATUS INT(1) DEFAULT 0 COMMENT '操作状态（0正常 1异常）',
error_msg VARCHAR(2000) DEFAULT '' COMMENT '错误消息',
oper_time DATETIME COMMENT '操作时间',
PRIMARY KEY (oper_id)
) ENGINE=INNODB AUTO_INCREMENT=100 COMMENT = 'delete操作日志记录';
package com.ruoyi.system.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.ruoyi.system.api.domain.SysOperLog;
import com.ruoyi.system.mapper.SysOperLogMapper;
import com.ruoyi.system.service.ISysOperLogService;
/**
* 操作日志 服务层处理
*
*/
@Service
public class SysOperLogServiceImpl implements ISysOperLogService
{
@Autowired
private SysOperLogMapper operLogMapper;
/**
* 新增操作日志
*
* @param operLog 操作日志对象
* @return 结果
*/
@Override
@DS("sharding")
public int insertOperlog(SysOperLog operLog)
{
return operLogMapper.insertOperlog(operLog);
}
/**
* 查询系统操作日志集合
*
* @param operLog 操作日志对象
* @return 操作日志集合
*/
@Override
@DS("sharding")
public List<SysOperLog> selectOperLogList(SysOperLog operLog)
{
return operLogMapper.selectOperLogList(operLog);
}
/**
* 批量删除系统操作日志
*
* @param operIds 需要删除的操作日志ID
* @return 结果
*/
@Override
@DS("sharding")
public int deleteOperLogByIds(Long[] operIds)
{
return operLogMapper.deleteOperLogByIds(operIds);
}
/**
* 查询操作日志详细
*
* @param operId 操作ID
* @return 操作日志对象
*/
@Override
@DS("sharding")
public SysOperLog selectOperLogById(Long operId)
{
return operLogMapper.selectOperLogById(operId);
}
/**
* 清空操作日志
*/
@Override
@DS("sharding")
public void cleanOperLog()
{
operLogMapper.cleanOperLog();
}
}
package com.ruoyi.system.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.ruoyi.common.core.web.controller.BaseController;
import com.ruoyi.common.core.web.domain.AjaxResult;
import com.ruoyi.system.api.domain.SysOperLog;
import com.ruoyi.system.service.ISysOperLogService;
@RestController
@RequestMapping("/test/operlog")
public class TestOperlogController extends BaseController
{
@Autowired
private ISysOperLogService operLogService;
@GetMapping("/{method}")
public AjaxResult operlog(@PathVariable("method") String method)
{
SysOperLog operLog = new SysOperLog();
operLog.setTitle("测试数据");
operLog.setOperName("admin");
operLog.setRequestMethod(method);
return toAjax(operLogService.insertOperlog(operLog));
}
@GetMapping("/list")
public AjaxResult list()
{
return AjaxResult.success(operLogService.selectOperLogList(new SysOperLog()));
}
}
订单数据库测试
访问
http://localhost:8080/order/add/1到ry-order库sys_order表查询
http://localhost:8080/order/list查询
http://localhost:8080/order/query/1分库分表测试
访问
http://localhost:8080/test/operlog/GET到ry-log库sys_oper_log_get表访问
http://localhost:8080/test/operlog/POST到ry-log库sys_oper_log_post表访问
http://localhost:8080/test/operlog/PUT到ry-log库sys_oper_log_put表访问
http://localhost:8080/test/operlog/DELETE到ry-log库sys_oper_log_delete表查询
http://localhost:8080/test/operlog/list
powered by kaifamiao