shardingJDBC分表

pom.xml

        <!-- 数据库分片 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.1</version>
        </dependency>

 

application.yml

spring:
  shardingsphere:
    props:
      sql-show: true
    datasource:
      names: db0
      db0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order?characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
    rules:
      sharding:
        key-generators:
          alg-snowflake:
            type: SNOWFLAKE
        sharding-algorithms:
          table-inline:
            type: CLASS_BASED
            props:
              strategy: STANDARD
              algorithmClassName: com.common.log.sharding.TableShardingAlgorithm
        tables:
          t_log:
            # 主键：分布式序列
            key-generate-strategy:
              column: id
              key-generator-name: alg-snowflake
            # 节点
            actual-data-nodes: db0.t_log_$->{2025..2026}
            # 分表策略
            table-strategy:
              standard:
                sharding-column: create_time
                sharding-algorithm-name: table-inline


mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

 

TableShardingAlgorithm.java

package com.order.log.sharding;

import cn.hutool.core.collection.ListUtil;
import com.google.common.collect.Range;
import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;
import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm;

import java.time.LocalDateTime;
import java.util.Collection;

public class TableShardingAlgorithm implements StandardShardingAlgorithm<LocalDateTime> {

    @Override
    public void init() {}

    @Override
    public String getType() {
        return "CLASS_BASED";
    }

    /**
     * 精确增删改查 create_time
     **/
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<LocalDateTime> shardingValue) {
        int year = shardingValue.getValue().getYear();
        return "t_log_" + year;
    }

    /**
     * 范围增删改查 create_time
     **/
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<LocalDateTime> shardingValue) {
        // 可选实现范围查询逻辑
        Range<LocalDateTime> valueRange = shardingValue.getValueRange();
        LocalDateTime startDate = valueRange.lowerEndpoint();
        LocalDateTime endDate = valueRange.upperEndpoint();
        return ListUtil.of("t_log_" + startDate.getYear(), "t_log_" + endDate.getYear());
    }
}

 

测试

LogEntity

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.time.LocalDateTime;

/**
 * 日志表
 **/
@Getter
@Setter
@Accessors(chain = true)
@TableName("t_log")
public class LogEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    //是否配置sharding-jdbc的分布式序列 ? 是:使用ShardingJDBC的分布式序列,否:自动依赖数据库的主键自增策略
    @TableId(value = "id",type = IdType.AUTO)
    private Long id;

    @TableField("log_param")
    private String logParam;

    @TableField("log_result")
    private String logResult;

    @TableField("create_time")
    private LocalDateTime createTime;

}

LogDTO

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Getter;
import lombok.Setter;

import java.time.LocalDateTime;

/**
 * 日志表-入参
 **/
@Getter
@Setter
public class LogDTO {
    private Long id;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime createTime;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime startTime;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime endTime;
}

LogController

import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.iyungu.log.entity.LogDTO;
import com.iyungu.log.entity.LogEntity;
import com.iyungu.log.service.LogService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.time.LocalDateTime;
import java.util.List;

@RequestMapping("logs")
@RestController
public class LogController {
    @Autowired
    private LogService logService;

    @PostMapping("save")
    public void save(@RequestBody LogEntity logEntity) {
        logEntity.setCreateTime(LocalDateTime.now());
        logService.save(logEntity);
    }

    @PostMapping("save2")
    public void save2(@RequestBody LogEntity logEntity) {
        logEntity.setCreateTime(LocalDateTime.now().plusYears(1));
        logService.save(logEntity);
    }

    @PostMapping("detail")
    public LogEntity detail(@RequestBody LogDTO req) {
        return logService.getById(req.getId());
    }

    @PostMapping("list")
    public List<LogEntity> list(@RequestBody LogDTO req) {
//        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
//        LocalDateTime createTime = LocalDateTime.parse(req.getCreateTime(), formatter);
        return logService.list(Wrappers.lambdaQuery(LogEntity.class)
//                .eq(LogEntity::getCreateTime, req.getCreateTime())
                .between(LogEntity::getCreateTime, req.getStartTime(), req.getEndTime())
        );
    }

}