shardingsphere-jdbc之Mybatis volume range分片算法

介绍

基于shardingsphere-jdbc 5.1.0 进行单库分表.

基于分片容量的范围分片算法

类型：VOLUME_RANGE

1. maven项目依赖

<dependencies>
	<dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.0</version>
        </dependency>
	<dependency>
           <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
</dependencies>

2.application.yml配置

spring:
  application:
    name: jdbc-myabtis-volume
  profiles:
    include: jdbc

mybatis:
  mapper-locations: classpath*:/mappers/*-mapper.xml
  type-aliases-package: com.lance.sharding.volume.domain
  configuration:
    default-fetch-size: 20
    default-statement-timeout: 30
    map-underscore-to-camel-case: true
    use-generated-keys: true

logging:
  file:
    name: logs/${spring.application.name}.log
  level:
    org.springframework: info
    com.lance.sharding.volume: debug

4.测试Sql脚本

CREATE TABLE `t_order_0`
(
    `order_id`    bigint NOT NULL AUTO_INCREMENT,
    `price`       double(12,2) NOT 0 ,
    `user_id`     int    NOT NULL,
    `address_id`  bigint NOT NULL,
    `city`        varchar(32) NULL DEFAULT NULL,
    `status`      tinyint NULL DEFAULT NULL,
    `interval_time` datetime NULL DEFAULT NULL,
    `creator`     varchar(32) NULL DEFAULT NULL,
    `create_time` datetime NULL DEFAULT NULL,
    `updater`     varchar(32) NULL DEFAULT NULL,
    `update_time` datetime NULL DEFAULT NULL,
    PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic

5.单元测试Test

class OrderMapperTests {
  private final static String[] CITIES = {"shanghai", "beijing"};
  /**
   * -120000, 0, 18, 20000, 40000, 50000, 60000, 64000, 80000, 99000
   */
  private final static long[] PRICES = {-120000, 0, 18, 20000, 40000, 50000, 60000, 64000, 80000, 99000};
  @Autowired
  private OrderMapper orderMapper;

  @Test
  @Disabled
  void save() {
    ThreadLocalRandom random = ThreadLocalRandom.current();

    Date[] dates = create();
    IntStream.range(0, 20).forEach(i -> {
      Order order = new Order();
      order.setOrderId(System.nanoTime() + i);
      order.setPrice(PRICES[i % PRICES.length]);
      order.setAddressId(i);
      order.setCity(CITIES[i % 2]);
      order.setUserId(Math.abs(random.nextInt()));
      order.setCreator("user.0" + i);
      order.setIntervalTime(dates[i % 5]);
      order.setUpdater(order.getCreator());
      log.info("====>{}", order);
      orderMapper.save(order);
    });
  }

  @Test
  @Disabled
  void findAll() {
    List<Order> list = orderMapper.findAllAtPrice(60000);
    log.info("===>{}", list);
  }

  private Date[] create() {
    Date[] dates = new Date[6];

    try {
      Date date = DateUtils.parseDate("2022-02-01 00:00:00", Locale.CHINA, "yyyy-MM-dd HH:mm:ss");
      IntStream.range(0, 6).forEach(i -> dates[i] = DateUtils.addDays(date, i));
    } catch (ParseException e) {
      log.error("date parse fail: ", e);
    }
    return dates;
  }
}

6.项目完整地址

shardingsphere-jdbc之Mybatis volume range分片算法 Github 地址

shardingsphere-jdbc之Mybatis volume range分片算法 Gitee 地址