今天可能喝了点酒,兴致很足,就再写一篇博客。还是上一篇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;
}
}