系列文章目录
- MySQL分表分库基础
- 分库分表-ShardingSphere基础
- 分库分表ShardingSphere-ShardingJDBC数据分片实战1
- 分库分表ShardingSphere-ShardingJDBC数据分片实战2
- 分库分表ShardingSphere-ShardingJDBC源码解析
文章目录
前言
前文介绍了ShardingJDBC框架的基础使用以及一些核心配置介绍,本文通过一个示例讲解ShardingJDBC的使用
一、项目背景
聚合支付,用户通过不同的支付方式支付完订单之后入库到各个商户(微信支付,支付宝,银联支付)的库中
二、项目使用的技术
框架名称 | 版本号 |
---|---|
spring-boot | 2.3.4.RELEASE |
druid | 1.1.22 |
mysql-connector-java | 8.0.21 |
mybatis-plus-boot-starter | 3.0.5 |
spring-boot-starter-web | 2.3.4.RELEASE |
spring-boot-starter-aop | 2.3.4.RELEASE |
sharding-jdbc | 4.1.1 |
1.引入Maven库
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.4.RELEASE</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<!-- for spring namespace -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
2.基础代码介绍
SpringBoot启动类,扫描mybatis Mapper文件
@MapperScan("com.jany.sharding.mapper")
@SpringBootApplication
public class ShardingApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingApplication.class,args);
}
}
实体类
商户实体
/**
* 商户
*/
@Data
@Builder
public class Merchant {
/**
* 商户名称
*/
private String name;
/**
* 商户code
*/
private String code;
}
订单实体
/**
* 订单
*/
@Data
@TableName("`order`")
public class Order {
@TableId(type = IdType.AUTO)
private Integer id;
/**
* 用户ID
*/
private Integer userId;
/**
* 订单编号
*/
private String orderSn;
/**
* 订单状态
*/
private Integer payStatus;
/**
* 支付时间
*/
private Date payTime;
/**
* 金额
*/
private BigDecimal price;
/**
* 店铺Code
*/
private String merchantCode;
}
枚举类
支付状态枚举
public enum PayStatusEnum {
STATUS_CREATE("等待买家付款",101),
STATUS_CANCEL("已取消",102),
STATUS_AUTO_CANCEL("已取消(系统)",103),
STATUS_PAY("已付款",201),
STATUS_COMPLETE("完成",301),
;
private String text;
private Integer value;
PayStatusEnum(String text, Integer value) {
this.text = text;
this.value = value;
}
public Integer getValue() {
return value;
}
public String getText() {
return text;
}
}
分库分表类型枚举
public enum SharingTypeEnum {
ORDER("订单","order")
;
private String text;
private String value;
SharingTypeEnum(String text, String value) {
this.text = text;
this.value = value;
}
public String getValue() {
return value;
}
public String getText() {
return text;
}
}
自定义注解类
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@Documented
public @interface ShardingAop {
String value() default "";
}
基础查询方法
public interface MerchantService {
/**
* 获取商户列表
* @return
*/
List<Merchant> getList();
/**
* 通过用户查询商户
* @param userId
* @return
*/
String getByUser(Integer userId);
}
@Service
public class MerchantServiceImpl implements MerchantService {
private List<Merchant> merchants = new ArrayList<>();
@PostConstruct
void initData(){
merchants.add(Merchant.builder().code("1").name("微信支付").build());
merchants.add(Merchant.builder().code("2").name("阿里支付").build());
merchants.add(Merchant.builder().code("3").name("银联支付").build());
}
@Override
public List<Merchant> getList() {
return merchants;
}
@Override
public String getByUser(Integer userId) {
// 通过用户查询商户,实际应从关联表查询
return merchants.get(userId % 3).getCode();
}
}
3.核心代码介绍
配置文件介绍
相关配置可参考
分库分表-(ShardingSphere)ShardingJDBC数据分片实战1
spring:
shardingsphere:
datasource:
# 真实数据源名称,多个数据源用逗号区分
names: pay1,pay2,pay3
# 配置 数据源
pay1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/pay_wx?useUnicode=true&characterEncoding=utf8
username: root
password: root
pay2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3307/pay_ali?useUnicode=true&characterEncoding=utf8
username: root
password: root
pay3:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3308/pay_union?useUnicode=true&characterEncoding=utf8
username: root
password: root
sharding:
tables:
order:
# 标准分片表配置,由数据源名 + 表名组成,以小数点分隔
actual-data-nodes: pay1.order,pay2.order,pay3.order
database-strategy:
standard:
## 分片列名称,多个列以逗号分隔
sharding-column: merchant_code
## 分片算法名称
precise-algorithm-class-name: com.jany.sharding.algorithm.OrderAlgorithm
props:
sql:
show: true
main:
allow-bean-definition-overriding: true
# mybatis 配置
mybatis-plus:
mapper-locations: classpath:mapper/*.xml
type-aliases-package: com.jany.sharding.domain
订单分库算法
/**
* 订单分库算法
*/
public class OrderAlgorithm implements PreciseShardingAlgorithm<String> {
private static final String DB_NAME_PREFIX = "pay";
@Override
public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<String> shardingValue) {
// 库名前缀
String targetTable = DB_NAME_PREFIX + shardingValue.getValue();
// 判断是否匹配
if (availableTargetNames.contains(targetTable)){
return targetTable;
}
// 匹配不到抛出异常
throw new UnsupportedOperationException("无法判定的值: " + shardingValue.getValue());
}
}
单元测试类
测试用例:模拟用户支付场景
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = OrderTests.class)
@SpringBootApplication
public class OrderTests {
@Resource
private OrderService orderService;
@Test
public void toPay(){
for (int i = 1; i < 5; i++) {
Order order = new Order();
order.setUserId(i);
order.setOrderSn(UUID.randomUUID().toString().replace("-",""));
order.setPayStatus(PayStatusEnum.STATUS_PAY.getValue());
order.setPayTime(new Date());
order.setPrice(BigDecimal.valueOf(new Random().nextDouble()));
orderService.pay(order);
}
}
}
public interface OrderService {
/**
* 支付
* @param order
*/
void pay(Order order);
}
这里通过Aop实现,定义了aop类型,可通过切换不同类型实现不同逻辑
@Service
public class OrderServiceImpl implements OrderService {
@Resource
private OrderMapper orderMapper;
@Override
@ShardingAop(ShardingConstant.SHARDING_TYPE_ORDER)
public void pay(Order order) {
orderMapper.insert(order);
}
}
ShardingAop 的切面
/**
* ShardingAop 的切面
*/
@Aspect //定义一个切面
@Configuration
@Slf4j
public class ShardingAspect {
@Resource
private MerchantService merchantService;
//这里需要注意了,这个是将自己自定义注解作为切点的根据,路径一定要写正确了
@Pointcut(value = "@annotation(com.jany.sharding.annotation.ShardingAop)")
public void sharding() {
}
@Around("sharding()")
public Object doAround(ProceedingJoinPoint joinPoint) throws Throwable {
//1. 通过连接点获取方法签名 被切入方法的所有信息
MethodSignature signature = (MethodSignature) joinPoint.getSignature();
//2.获取被切入方法对象
Method method = signature.getMethod();
//3.获取方法上的注解
ShardingAop shardingAop = method.getAnnotation(ShardingAop.class);
String value = shardingAop.value();
Object[] args = joinPoint.getArgs();
if (ShardingConstant.SHARDING_TYPE_ORDER.equals(value)){
for (Object argItem : joinPoint.getArgs()) {
if (argItem instanceof Order){
Order order = (Order) argItem;
// 通过用户查询商户
String merchantCode = merchantService.getByUser(order.getUserId());
order.setMerchantCode(merchantCode);
}
}
}
return joinPoint.proceed(args);
}
}
触发单元测试,日志输出
Logic SQL 为逻辑SQL
Actual SQL 为实际执行SQL
2022-03-23 11:34:22.703 INFO 28880 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO `order` ( user_id,
order_sn,
pay_status,
pay_time,
price,
merchant_code ) VALUES ( ?,
?,
?,
?,
?,
? )
2022-03-23 11:34:22.704 INFO 28880 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6a7ea7c, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3ee200ae), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3ee200ae, columnNames=[user_id, order_sn, pay_status, pay_time, price, merchant_code], insertValueContexts=[InsertValueContext(parametersCount=6, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=98, stopIndex=98, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=101, stopIndex=101, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=104, stopIndex=104, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=107, stopIndex=107, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=110, stopIndex=110, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=113, stopIndex=113, parameterMarkerIndex=5)], parameters=[1, 0a721982a6f1407d8d4443e843250894, 201, 2022-03-23 11:34:22.124, 0.8074365584626553, 2])], generatedKeyContext=Optional.empty)
2022-03-23 11:34:22.704 INFO 28880 --- [ main] ShardingSphere-SQL : Actual SQL: pay2 ::: INSERT INTO `order` ( user_id,
order_sn,
pay_status,
pay_time,
price,
merchant_code ) VALUES (?, ?, ?, ?, ?, ?) ::: [1, 0a721982a6f1407d8d4443e843250894, 201, 2022-03-23 11:34:22.124, 0.8074365584626553, 2]
2022-03-23 11:34:22.829 INFO 28880 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO `order` ( user_id,
order_sn,
pay_status,
pay_time,
price,
merchant_code ) VALUES ( ?,
?,
?,
?,
?,
? )
2022-03-23 11:34:22.829 INFO 28880 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6a7ea7c, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@472dbaf5), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@472dbaf5, columnNames=[user_id, order_sn, pay_status, pay_time, price, merchant_code], insertValueContexts=[InsertValueContext(parametersCount=6, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=98, stopIndex=98, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=101, stopIndex=101, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=104, stopIndex=104, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=107, stopIndex=107, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=110, stopIndex=110, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=113, stopIndex=113, parameterMarkerIndex=5)], parameters=[2, 5b57ba8f42bc46f0852445119183c58c, 201, 2022-03-23 11:34:22.827, 0.07052991729011016, 3])], generatedKeyContext=Optional.empty)
2022-03-23 11:34:22.829 INFO 28880 --- [ main] ShardingSphere-SQL : Actual SQL: pay3 ::: INSERT INTO `order` ( user_id,
order_sn,
pay_status,
pay_time,
price,
merchant_code ) VALUES (?, ?, ?, ?, ?, ?) ::: [2, 5b57ba8f42bc46f0852445119183c58c, 201, 2022-03-23 11:34:22.827, 0.07052991729011016, 3]
2022-03-23 11:34:22.877 INFO 28880 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO `order` ( user_id,
order_sn,
pay_status,
pay_time,
price,
merchant_code ) VALUES ( ?,
?,
?,
?,
?,
? )
2022-03-23 11:34:22.877 INFO 28880 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6a7ea7c, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6ed87ccf), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6ed87ccf, columnNames=[user_id, order_sn, pay_status, pay_time, price, merchant_code], insertValueContexts=[InsertValueContext(parametersCount=6, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=98, stopIndex=98, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=101, stopIndex=101, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=104, stopIndex=104, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=107, stopIndex=107, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=110, stopIndex=110, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=113, stopIndex=113, parameterMarkerIndex=5)], parameters=[3, fda2ece36d5c48b390f834da27783513, 201, 2022-03-23 11:34:22.875, 0.8487772735728076, 1])], generatedKeyContext=Optional.empty)
2022-03-23 11:34:22.877 INFO 28880 --- [ main] ShardingSphere-SQL : Actual SQL: pay1 ::: INSERT INTO `order` ( user_id,
order_sn,
pay_status,
pay_time,
price,
merchant_code ) VALUES (?, ?, ?, ?, ?, ?) ::: [3, fda2ece36d5c48b390f834da27783513, 201, 2022-03-23 11:34:22.875, 0.8487772735728076, 1]
2022-03-23 11:34:23.164 INFO 28880 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO `order` ( user_id,
order_sn,
pay_status,
pay_time,
price,
merchant_code ) VALUES ( ?,
?,
?,
?,
?,
? )
2022-03-23 11:34:23.165 INFO 28880 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@6a7ea7c, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6292c63e), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6292c63e, columnNames=[user_id, order_sn, pay_status, pay_time, price, merchant_code], insertValueContexts=[InsertValueContext(parametersCount=6, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=98, stopIndex=98, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=101, stopIndex=101, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=104, stopIndex=104, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=107, stopIndex=107, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=110, stopIndex=110, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=113, stopIndex=113, parameterMarkerIndex=5)], parameters=[4, cd93e5f8683e408b9d5e6782f0f56b74, 201, 2022-03-23 11:34:23.163, 0.6337988359605945, 2])], generatedKeyContext=Optional.empty)
2022-03-23 11:34:23.165 INFO 28880 --- [ main] ShardingSphere-SQL : Actual SQL: pay2 ::: INSERT INTO `order` ( user_id,
order_sn,
pay_status,
pay_time,
price,
merchant_code ) VALUES (?, ?, ?, ?, ?, ?) ::: [4, cd93e5f8683e408b9d5e6782f0f56b74, 201, 2022-03-23 11:34:23.163, 0.6337988359605945, 2]
总结
本文通过ShardingJDBC框架实现了实际场景的分库,ShardingJDBC作为Jdbc增强 让我们实际开发中便捷实现分表分库操作