shardingsphere自动分表，参数校验技术的实际应用

今天可能喝了点酒，兴致很足，就再写一篇博客。还是上一篇po+mq里面用到的报文日志表，自动分表技术的应用：

一、啥也不说了首先引入maven包

 <properties>
 <sharding-sphere.version>4.1.0</sharding-sphere.version>
 </properties>
 
 <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
      <version>${sharding-sphere.version}</version>
  </dependency>

<dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-namespace</artifactId>
      <version>${sharding-sphere.version}</version>
  </dependency>

二、本人喜欢用mybaits plus，maven包自己引，下面是shardingsphere配置文件，我默认配置十年用到的表 ：

spring:
  profiles: dev
  shardingsphere:
    datasource:
      master:
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/asd-po?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=false
        type: com.alibaba.druid.pool.DruidDataSource
        initialSize: 5   #连接池初始化连接数
        minIdle: 3       #连接池最小连接数
        maxActive: 200   #连接池最大连接数
        username: root
        password: 123456
      names: master
    props:
      sql:
        show: true
    sharding:
      tables:
        sys_log_ack:
          actual-data-nodes: master.sys_log_ack_$->{2021..2031}${(1..12).collect{t ->t.toString().padLeft(2,'0')}}
          table-strategy:
            standard:
              range-algorithm-class-name: com.asd.po.config.DefaultTableRangeShardingAlgorithm
              precise-algorithm-class-name: com.asd.po.config.DefaultTableShardingAlgorithm
              sharding-column: CREATE_TIME
mybatis-plus:
  type-aliases-package: com.asd.po.**.entity
  mapper-locations: classpath*:mapper/**/*Mapper.xml
  configuration:
    cache-enabled: false
    map-underscore-to-camel-case: true
    call-setters-on-nulls: true
    jdbc-type-for-null: 'null'
    #log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #打印sql语句,调试用
  global-config:
    id-type: 0  #0:数据库ID自增   1:用户输入id  2:全局唯一id(IdWorker)  3:全局唯一ID(uuid)
    db-column-underline: false
    refresh-mapper: true
    db-config:
      id-type: auto
      banner: false

三、自定义分表策略

package com.asd.po.config;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import java.util.Collection;
import java.util.LinkedHashSet;

/**
 * 分表的自定义规则类(范围)
 *
 * @author zhanqi
 */
public class DefaultTableRangeShardingAlgorithm implements RangeShardingAlgorithm<String> {

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames,
                                         RangeShardingValue<String> rangeShardingValue) {
        Range<String> ranges = rangeShardingValue.getValueRange();
        String start = ranges.lowerEndpoint().substring(0,7).replace("-","");
        String stop =ranges.upperEndpoint().substring(0,7).replace("-","");
        Collection<String> tables = new LinkedHashSet<>();
            for (String c : availableTargetNames) {
                String cMonth = c.substring(c.length() - 6);
                if (start.equals(cMonth)||stop.equals(cMonth)) {
                    tables.add(c);
                }
            }
        return tables;
    }
}

package com.asd.po.config;

import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

/**
 * @author zhanqi
 * 分表的自定义规则类(精确)
 */
public class DefaultTableShardingAlgorithm  implements PreciseShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        String createTime = preciseShardingValue.getValue().substring(0,7).replace("-","");
        String columnName = preciseShardingValue.getColumnName();

        // 需要分库的逻辑表
        String table = preciseShardingValue.getLogicTableName();
        if (StringUtils.isBlank(createTime)) {
            throw new UnsupportedOperationException(columnName + "分表精确分片值不存在");
        }
        for (String each : collection) {
            if (each.startsWith(table)) {
                return table + "_" + createTime;
            }
        }
        return table;
    }
}

三、下面是model

package com.asd.po.entity;

import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

/**
 * 日志表(SysLog)表实体类
 *
 * @author zhanqi
 * @since 2021-11-20 23:02:48
 */
@SuppressWarnings("serial")
@ApiModel
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("sys_log")
public class SysLog extends Model<SysLog> {
    //物理主键
    @TableId(value = "ID")
    @ApiModelProperty(value = "物理主键")
    private Long id;
    //认证配置主键
    @ApiModelProperty(value = "认证配置主键")
    private Long accessConfigId;
    //认证秘钥
    @ApiModelProperty(value = "认证秘钥")
    private String accessCode;
    //系统编码
    @ApiModelProperty(value = "系统编码")
    private String systemCode;
    //系统名称
    @ApiModelProperty(value = "系统名称")
    private String systemName;
    //公司编码
    @ApiModelProperty(value = "公司编码")
    private String companyCode;
    //公司名称
    @ApiModelProperty(value = "公司名称")
    private String companyName;
    //接口编码
    @ApiModelProperty(value = "接口编码")
    private String interfaceCode;
    //接口名称
    @ApiModelProperty(value = "接口名称")
    private String interfaceName;
    //是否开启EDI模式：Y-开启，N-停用
    @ApiModelProperty(value = "是否开启EDI模式：Y-开启，N-停用")
    private String isEdi;
    //抽象类bean名称
    @ApiModelProperty(value = "抽象类bean名称")
    private String serviceName;
    //接口URL
    @ApiModelProperty(value = "接口URL")
    private String url;
    //请求方法
    @ApiModelProperty(value = "请求方法")
    private String method;
    //是否回调确认：Y-是，N-否
    @ApiModelProperty(value = "是否回调确认：Y-是，N-否")
    private String isAck;
    //消息唯一索引
    @ApiModelProperty(value = "消息唯一索引")
    private String uuid;
    //行号
    @ApiModelProperty(value = "行号")
    private Long rowNum;
    //参考号
    @ApiModelProperty(value = "参考号")
    private String refNo;
    //报文
    @ApiModelProperty(value = "报文")
    private String messages;
    //更新时间
    @ApiModelProperty(value = "更新时间")
    private String updateTime;
    //创建时间
    @ApiModelProperty(value = "创建时间")
    private String createTime;
    //预留字段1
    @ApiModelProperty(value = "预留字段1")
    private String reservedOne;
    //预留字段2
    @ApiModelProperty(value = "预留字段2")
    private String reservedTwo;
    //预留字段3
    @ApiModelProperty(value = "预留字段3")
    private String reservedThree;
    //预留字段4
    @ApiModelProperty(value = "预留字段4")
    private String reservedFour;
    //处理时间
    @ApiModelProperty(value = "处理时间")
    private Long respondTime;
    //重试次数
    @ApiModelProperty(value = "重试次数")
    private Integer retryNum;
    //状态：Y-已消费，N-未消费
    @ApiModelProperty(value = "状态：Y-已消费，N-未消费")
    private String state;

}

四、Mapper

package com.asd.po.mapper;

import com.asd.po.entity.SysLog;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * 日志表(SysLog)表数据库访问层
 *
 * @author zhanqi
 * @since 2021-11-20 15:58:42
 */
public interface SysLogMapper extends BaseMapper<SysLog> {
}

五、接口类

package com.asd.po.service;

import com.asd.po.entity.SysLog;
import com.asd.po.entity.from.SysLogFrom;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import java.util.List;

/**
 * 日志表(SysLog)表服务接口
 *
 * @author zhanqi
 * @since 2021-11-20 15:58:44
 */
public interface SysLogService extends IService<SysLog> {
    IPage<SysLog> selectAll(SysLogFrom from);

    SysLog findByUuid(String uuid);
}

五、实现类

package com.asd.po.service.impl;

import cn.hutool.core.date.DateUtil;
import com.asd.po.entity.SysLog;
import com.asd.po.entity.SysLogAck;
import com.asd.po.entity.from.SysLogFrom;
import com.asd.po.mapper.SysLogMapper;
import com.asd.po.service.SysLogAckService;
import com.asd.po.service.SysLogService;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.List;

/**
 * 日志表(SysLog)表服务实现类
 *
 * @author zhanqi
 * @since 2021-11-20 15:58:44
 */
@Slf4j
@Service("sysLogService")
public class SysLogServiceImpl extends ServiceImpl<SysLogMapper, SysLog> implements SysLogService {
    @Autowired
    private RedisTemplate redisTemplate;

    @Autowired
    private SysLogAckService sysLogAckService;
    @Override
  public IPage<SysLog> selectAll(SysLogFrom from) {
        Page<SysLog> page = new Page<>(from.getPage(), from.getLimit());
        QueryWrapper<SysLog> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(StringUtils.isEmpty(from.getUuid()), SysLog::getUuid, from.getUuid())
                .eq(StringUtils.isEmpty(from.getRefNo()), SysLog::getRefNo, from.getRefNo())
                .eq(StringUtils.isEmpty(from.getSystemName()), SysLog::getSystemName, from.getSystemName())
                .eq(StringUtils.isEmpty(from.getSystemCode()), SysLog::getSystemCode, from.getSystemCode())
                .eq(StringUtils.isEmpty(from.getCompanyCode()), SysLog::getCompanyCode, from.getCompanyCode())
                .eq(StringUtils.isEmpty(from.getCompanyName()), SysLog::getCompanyName, from.getCompanyName())
                .eq(StringUtils.isEmpty(from.getInterfaceCode()), SysLog::getInterfaceCode, from.getInterfaceCode())
                .eq(StringUtils.isEmpty(from.getInterfaceName()), SysLog::getInterfaceName, from.getInterfaceName())
                .between(SysLog::getCreateTime, from.getCreateTimeStart(), from.getCreateTimeStop())
                .orderByDesc(SysLog::getCreateTime);
        IPage<SysLog> iPage = this.page(page, queryWrapper);
        return iPage;
    }

    @Override
    public SysLog findByUuid(String uuid) {
        QueryWrapper<SysLog> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(SysLog::getUuid,uuid)
                .eq(SysLog::getState,'N').last(" limit 1");
        return this.getOne(queryWrapper);
    }

}

六、控制器

package com.asd.po.controller;


import com.asd.po.entity.SysLog;
import com.asd.po.entity.from.SysLogFrom;
import com.asd.po.service.SysLogService;
import com.asd.po.util.HttpResult;
import com.baomidou.mybatisplus.core.metadata.IPage;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import java.io.Serializable;
import java.util.List;

/**
 * 日志表(SysLog)表控制层
 *
 * @author zhanqi
 * @since 2021-11-20 16:02:29
 */
@RestController
@RequestMapping("sysLog")
@Api("sysLog")
@Slf4j
public class SysLogController {
    /**
     * 服务对象
     */
    @Autowired
    private SysLogService sysLogService;

    /**
     * 分页查询所有数据
     *
     * @param from 查询实体
     * @return 所有数据
     */
    @ApiOperation(value = "分页查询所有数据")
    @ApiImplicitParam(paramType = "header", name = "Access-Key", value = "Access-Key", required = true)
    @GetMapping("selectAll")
    public IPage<SysLog> selectAll(@Validated @RequestBody @ApiParam(name = "queryForm", value = "查询表单", required = true) SysLogFrom from) {
        return sysLogService.selectAll(from);
    }

    /**
     * 通过主键查询单条数据
     *
     * @param id 主键
     * @return 单条数据
     */
    @ApiOperation(value = "通过主键查询单条数据")
    @ApiImplicitParam(paramType = "header", name = "Access-Key", value = "Access-Key", required = true)
    @PostMapping("selectOne")
    public HttpResult<SysLog> selectOne(@PathVariable Serializable id) {
        return HttpResult.ok(sysLogService.getById(id));
    }

    /**
     * 新增数据
     *
     * @param sysLog 实体对象
     * @return 新增结果
     */
    @ApiOperation(value = "新增数据")
    @ApiImplicitParam(paramType = "header", name = "Access-Key", value = "Access-Key", required = true)
    @PostMapping("save")
    public HttpResult<Boolean> save(@RequestBody SysLog sysLog) {
        return HttpResult.ok(sysLogService.save(sysLog));
    }

    /**
     * 修改数据
     *
     * @param sysLog 实体对象
     * @return 修改结果
     */
    @ApiOperation(value = "修改数据")
    @ApiImplicitParam(paramType = "header", name = "Access-Key", value = "Access-Key", required = true)
    @PostMapping("update")
    public HttpResult<Boolean> update(@RequestBody SysLog sysLog) {
        return HttpResult.ok(sysLogService.updateById(sysLog));
    }

    /**
     * 删除数据
     *
     * @param idList 主键结合
     * @return 删除结果
     */
    @ApiOperation(value = "删除数据")
    @ApiImplicitParam(paramType = "header", name = "Access-Key", value = "Access-Key", required = true)
    @PostMapping("del")
    public HttpResult<Boolean> del(@RequestBody @RequestParam("idList") List<Long> idList) {
        return HttpResult.ok(sysLogService.removeByIds(idList));
    }
}

七、请求实体类

package com.asd.po.entity.from;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.validation.constraints.NotNull;
import java.util.Date;

/**
 * 日志表(SysLog)查询Form
 *
 * @author zhanqi
 * @since 2021-11-20 15:58:44
 */
@ApiModel
@Data
public class SysLogFrom {
    @ApiModelProperty(value = "页数", required = true)
    @NotNull(message = "页数不能为空")
    int page;

    @ApiModelProperty(value = "每一页条数", required = true)
    @NotNull(message = "页每一页条数不能为空")
    int limit;


    @ApiModelProperty(value = "物理主键")
    private Long id;


    @ApiModelProperty(value = "认证配置主键")
    private Long accessConfigId;


    @ApiModelProperty(value = "认证秘钥")
    private String accessCode;


    @ApiModelProperty(value = "系统编码")
    private String systemCode;


    @ApiModelProperty(value = "系统名称")
    private String systemName;


    @ApiModelProperty(value = "公司编码")
    private String companyCode;


    @ApiModelProperty(value = "公司名称")
    private String companyName;


    @ApiModelProperty(value = "接口编码")
    private String interfaceCode;


    @ApiModelProperty(value = "接口名称")
    private String interfaceName;


    @ApiModelProperty(value = "消息唯一索引")
    private String uuid;


    @ApiModelProperty(value = "参考号")
    private String refNo;


    @ApiModelProperty(value = "报文")
    private String messages;


    @ApiModelProperty(value = "更新时间")
    private Date updateTime;


    @ApiModelProperty(value = "创建时间开始", required = true)
    private Date createTimeStart;

    @ApiModelProperty(value = "创建时间结束", required = true)
    private Date createTimeStop;

    @ApiModelProperty(value = "状态：Y-已消费，N-未消费")
    private String state;


    @ApiModelProperty(value = "预留字段1")
    private String reservedOne;


    @ApiModelProperty(value = "预留字段2")
    private String reservedTwo;


    @ApiModelProperty(value = "预留字段3")
    private String reservedThree;


    @ApiModelProperty(value = "预留字段4")
    private String reservedFour;
}

八、全局异常，校验异常的处理，前期自己在大物流中央库存服务里面写的，直接就复制过来了

package com.asd.po.handler;

import com.asd.po.util.HttpResult;
import org.springframework.http.HttpStatus;
import org.springframework.validation.BindException;
import org.springframework.validation.BindingResult;
import org.springframework.validation.FieldError;
import org.springframework.web.bind.MethodArgumentNotValidException;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.validation.ConstraintViolation;
import javax.validation.ConstraintViolationException;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * @author zhanqi
 * @since 2021/8/6 13:29
 */
@ControllerAdvice
@ResponseBody
public class GlobalExceptionHandler {
    @ExceptionHandler(BindException.class)
    public HttpResult bindExceptionHandler(BindException e) {
        List<FieldError> fieldErrors = e.getBindingResult().getFieldErrors();
        List<String> collect = fieldErrors.stream()
                .map(o -> o.getDefaultMessage())
                .collect(Collectors.toList());
        return HttpResult.error(HttpStatus.BAD_REQUEST.value(), collect.toString());
    }

    @ExceptionHandler(ConstraintViolationException.class)
    public HttpResult constraintViolationExceptionHandler(ConstraintViolationException e) {
        Set<ConstraintViolation<?>> constraintViolations = e.getConstraintViolations();
        List<String> collect = constraintViolations.stream()
                .map(o -> o.getMessage())
                .collect(Collectors.toList());
        return HttpResult.error(HttpStatus.BAD_REQUEST.value(), collect.toString());
    }

    /**
     * NullPointerException
     */
    @ExceptionHandler(NullPointerException.class)
    public HttpResult<?> parameterExceptionHandler(NullPointerException e) {
        return HttpResult.error(HttpStatus.BAD_REQUEST.value(), "NullPointerException");
    }


    @ExceptionHandler(MethodArgumentNotValidException.class)
    public HttpResult<?> allExceptionHandler(MethodArgumentNotValidException e) {
        MethodArgumentNotValidException ex = e;
        BindingResult bindingResult = ex.getBindingResult();
        StringBuilder errMsg = new StringBuilder(bindingResult.getFieldErrors().size() * 16);
        for (int i = 0; i < bindingResult.getFieldErrors().size(); i++) {
            if (i > 0) {
                errMsg.append(",");
            }
            FieldError error = bindingResult.getFieldErrors().get(i);
            errMsg.append(error.getField() + ":" + error.getDefaultMessage());
        }
        return HttpResult.error(HttpStatus.BAD_REQUEST.value(), errMsg.toString());
    }


}

九、加入list校验，需要进行重写

package com.asd.po.handler;

import javax.validation.Valid;
import java.util.*;

/**
 * 加入list校验
 *
 * @author zhanqi
 * @since 2021/8/13 18:25
 */

public class ValidList<E> implements List<E> {
    @Valid
    private List<E> list = new LinkedList<>();

    @Override
    public int size() {
        return list.size();
    }

    @Override
    public boolean isEmpty() {
        return list.isEmpty();
    }

    @Override
    public boolean contains(Object o) {
        return list.contains(o);
    }

    @Override
    public Iterator<E> iterator() {
        return list.iterator();
    }

    @Override
    public Object[] toArray() {
        return list.toArray();
    }

    @Override
    public <T> T[] toArray(T[] a) {
        return list.toArray(a);
    }

    @Override
    public boolean add(E e) {
        return list.add(e);
    }

    @Override
    public boolean remove(Object o) {
        return list.remove(o);
    }

    @Override
    public boolean containsAll(Collection<?> c) {
        return list.containsAll(c);
    }

    @Override
    public boolean addAll(Collection<? extends E> c) {
        return list.addAll(c);
    }

    @Override
    public boolean addAll(int index, Collection<? extends E> c) {
        return list.addAll(index, c);
    }

    @Override
    public boolean removeAll(Collection<?> c) {
        return list.removeAll(c);
    }

    @Override
    public boolean retainAll(Collection<?> c) {
        return list.retainAll(c);
    }

    @Override
    public void clear() {
        list.clear();
    }

    @Override
    public E get(int index) {
        return list.get(index);
    }

    @Override
    public E set(int index, E element) {
        return list.set(index, element);
    }

    @Override
    public void add(int index, E element) {
        list.add(index, element);
    }

    @Override
    public E remove(int index) {
        return list.remove(index);
    }

    @Override
    public int indexOf(Object o) {
        return list.indexOf(o);
    }

    @Override
    public int lastIndexOf(Object o) {
        return list.lastIndexOf(o);
    }

    @Override
    public ListIterator<E> listIterator() {
        return list.listIterator();
    }

    @Override
    public ListIterator<E> listIterator(int index) {
        return list.listIterator(index);
    }

    @Override
    public List<E> subList(int fromIndex, int toIndex) {
        return list.subList(fromIndex, toIndex);
    }

    public List<E> getList() {
        return list;
    }

    public void setList(List<E> list) {
        this.list = list;
    }

}