分库分表ShardingSphere-ShardingJDBC数据分片实战2

系列文章目录


前言

前文介绍了ShardingJDBC框架的基础使用以及一些核心配置介绍,本文通过一个示例讲解ShardingJDBC的使用


一、项目背景

聚合支付,用户通过不同的支付方式支付完订单之后入库到各个商户(微信支付,支付宝,银联支付)的库中
项目背景

二、项目使用的技术

框架名称版本号
spring-boot2.3.4.RELEASE
druid1.1.22
mysql-connector-java8.0.21
mybatis-plus-boot-starter3.0.5
spring-boot-starter-web2.3.4.RELEASE
spring-boot-starter-aop2.3.4.RELEASE
sharding-jdbc4.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增强 让我们实际开发中便捷实现分表分库操作

  • 4
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 6
    评论
评论 6
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

janyxe

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值