springboot整合ShardingSphere5.2.1（最新版）

内容包含分表配置文件类型和自定义类型两种方法
无法直接上次代码，疯狂贴图

config
MyBaseMapper

package cn.com.lyb.config;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import java.util.List;

public interface MyBaseMapper<T> extends BaseMapper<T> {

    /**
     * 批量插入,相当于insertList
     */
    int insertBatchSomeColumn(List<T> entityList);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

MybatisPlusConfig

package cn.com.lyb.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.List;

@Configuration
public class MybatisPlusConfig extends DefaultSqlInjector {
    /**
     * mybatisPlus分页配置
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        paginationInnerInterceptor.setOptimizeJoin(true);
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        paginationInnerInterceptor.setOverflow(true);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);
        OptimisticLockerInnerInterceptor optimisticLockerInnerInterceptor = new OptimisticLockerInnerInterceptor();
        interceptor.addInnerInterceptor(optimisticLockerInnerInterceptor);
        return interceptor;
    }
    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        // 注意：此SQL注入器继承了DefaultSqlInjector(默认注入器)，调用了DefaultSqlInjector的getMethodList方法，保留了mybatis-plus的自带方法
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        // 注入InsertBatchSomeColumn
        // 在!t.isLogicDelete()表示不要逻辑删除字段，!"update_time".equals(t.getColumn())表示不要字段名为 update_time 的字段
        methodList.add(new InsertBatchSomeColumn(t -> !t.isLogicDelete() && !"update_time".equals(t.getColumn())));
        return methodList;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

TableShardingAlgorithm

package cn.com.lyb.config;



import com.google.common.collect.Range;
import org.apache.commons.collections4.CollectionUtils;
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.util.*;
import java.util.stream.Collectors;

/**
 * 分表逻辑配置类
 * 此类生效必须配置/resource/META_INF.service/org.apache.shardingsphere.sharding.spi.ShardingAlgorithm
 * 不然启动报错：SPI-00001: No implementation class load from SPI `org.apache xxx
 */
public class TableShardingAlgorithm implements StandardShardingAlgorithm<Long>{

    private static final String TABLE_NAME = "base_";

    private final static String CUT = "_";

    private Properties props = new Properties();

    /*
     *availableTargetNames:配置文件中定义的表名称集合
     *shardingValue：insert,select,update,delate时，pid字段值
     *函数返回值：返回函数值shardingValue对应的表名。比如 pid%10=，返回表名base_3
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        if (CollectionUtils.isEmpty(availableTargetNames) || (shardingValue == null || shardingValue.getValue() == null)) {
            throw new IllegalArgumentException("sharding jdbc not find logic table,please check config");
        }
        String shard = null;
        if (shardingValue != null) {
            Long value = shardingValue.getValue();
            shard = String.valueOf(value % 10);
            return TABLE_NAME + shard;
        } else {
            throw new UnsupportedOperationException("传递参数有误");
        }
    }

    /*
     *availableTargetNames:配置文件中定义的表名称集合
     *函数返回值：返回函数值rangeShardingValue 对应的表名集合，比如获取到的pid集合取模为1，2，3，就返回base_1,base_2,base_3
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> rangeShardingValue) {
        Collection<String> resList = new ArrayList<>();
        // 获取所有表的后缀数字
        List<Integer> intList = availableTargetNames.stream()
                .map(table -> Integer.valueOf(table.substring(table.lastIndexOf(CUT) + 1)))
                .collect(Collectors.toList());
        Range<Long> valueRange = rangeShardingValue.getValueRange();
        Long startLong = valueRange.lowerEndpoint();
        int startInteger = (int) (startLong % 10);
        Long endLong = valueRange.upperEndpoint();
        int endInteger = (int) (endLong % 10);
        if (!intList.contains(startInteger) || !intList.contains(endInteger)) {
            throw new RuntimeException("存在无效参数");
        }
        for (String tableName : availableTargetNames) {
            for (int i = startInteger; i <= endInteger; i++) {
                String tag = String.valueOf(i);
                if (tableName.substring(tableName.lastIndexOf(CUT)+1).equals(tag)) {
                    resList.add(tableName);
                }
            }
        }
        if (CollectionUtils.isEmpty(resList)) {
            throw new RuntimeException("参数分表结果为空");
        }
        return resList;
    }

    @Override
    public Properties getProps() {
        return props;
    }

    @Override
    public void init(Properties properties) {

    }


}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

controller

package cn.com.lyb.controller;

import cn.com.lyb.entity.BaseEntity;
import cn.com.lyb.service.BaseService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class BaseController {

    @Autowired
    private BaseService baseService;

    @GetMapping("/batchSave")
    private void save(){
        baseService.save();
    }

    @PostMapping("/selectByIdAndPid")
    private List<BaseEntity> selectByIdAndPid(Long id, Long pid){
        List<BaseEntity> baseEntities = baseService.selectByIdandPid(id, pid);
        return baseEntities;
    }

    @PostMapping("/selectByPis")
    private List<BaseEntity> selectByPis(String pids){
        List<BaseEntity> baseEntities = baseService.selectByPids(pids);
        return baseEntities;
    }

    @PostMapping("/selectAll")
    private List<BaseEntity> selectAll(){
        List<BaseEntity> baseEntities = baseService.selectAll();
        return baseEntities;
    }

    @PostMapping("/selectRangPid")
    private List<BaseEntity> selectRangPid(Long startPid, Long endPid){
        List<BaseEntity> baseEntities = baseService.selectRangPid(startPid, endPid);
        return baseEntities;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

entity

package cn.com.lyb.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

@TableName(value = "base")
public class BaseEntity {
    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private Long id;

    private Integer divideId;

    private String type;

    private Long pid;
    private String pids;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Integer getDivideId() {
        return divideId;
    }

    public void setDivideId(Integer divideId) {
        this.divideId = divideId;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public Long getPid() {
        return pid;
    }

    public void setPid(Long pid) {
        this.pid = pid;
    }

    public String getPids() {
        return pids;
    }

    public void setPids(String pids) {
        this.pids = pids;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

dao

package cn.com.lyb.dao;

import cn.com.lyb.config.MyBaseMapper;
import cn.com.lyb.entity.BaseEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface BaseDao extends MyBaseMapper<BaseEntity> {

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

service

package cn.com.lyb.service;

import cn.com.lyb.entity.BaseEntity;

import java.util.List;

public interface BaseService {
    void save();

    List<BaseEntity> selectByIdandPid(Long id, Long pid);

    List<BaseEntity> selectByPids(String pids);

    List<BaseEntity> selectAll();

    List<BaseEntity> selectRangPid(Long startPid, Long endPid);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

impl

package cn.com.lyb.service.impl;

import cn.com.lyb.dao.BaseDao;
import cn.com.lyb.entity.BaseEntity;
import cn.com.lyb.service.BaseService;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

@Service
public class BaseServiceImpl implements BaseService {

    @Autowired
    private BaseDao baseDao;

    @Override
    @Async
    @Transactional(rollbackFor = Exception.class)
    public void save() {
        for (int i = 0;i<10;i++){
            BaseEntity baseEntity = new BaseEntity();
            baseEntity.setPid(0L);
            baseEntity.setType("schema");
            baseEntity.setDivideId((int) (baseEntity.getPid()%10));
            baseEntity.setPids("0");
            baseDao.insert(baseEntity);
            this.saveTable(baseEntity.getId(), baseEntity.getPids());
        }
    }

    @Override
    public List<BaseEntity> selectByIdandPid(Long id, Long pid) {
        return baseDao.selectList(new QueryWrapper<BaseEntity>().eq("id",id)
                .eq("pid",pid));
    }

    @Override
    public List<BaseEntity> selectByPids(String pids) {
        List<Long> pidList = Arrays.stream(pids.split(","))
                .map(Long::parseLong)
                .collect(Collectors.toList());
        return baseDao.selectList(new QueryWrapper<BaseEntity>()
                .in("pid",pidList));
    }

    @Override
    public List<BaseEntity> selectAll() {
        return baseDao.selectList(new QueryWrapper<BaseEntity>());
    }

    @Override
    public List<BaseEntity> selectRangPid(Long startPid, Long endPid) {
        return baseDao.selectList(new QueryWrapper<BaseEntity>()
                .gt("pid",startPid)
                .lt("pid",endPid));
    }

    public void saveTable(Long id, String pids) {
        for (int i = 0; i < 10; i++) {
            BaseEntity baseEntity = new BaseEntity();
            baseEntity.setPid(id);
            baseEntity.setType("table");
            baseEntity.setDivideId((int) (baseEntity.getPid()%10));
            baseEntity.setPids(pids + "," +id);
            baseDao.insert(baseEntity);
            this.saveColumn(baseEntity.getId(), baseEntity.getPids());
        }
    }

    public void saveColumn(Long id, String pids) {
        List<BaseEntity> insertList = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            BaseEntity baseEntity = new BaseEntity();
            baseEntity.setPid(id);
            baseEntity.setType("column");
            baseEntity.setDivideId((int) (baseEntity.getPid()%10));
            baseEntity.setPids(pids + "," +id);
            insertList.add(baseEntity);
        }
        baseDao.insertBatchSomeColumn(insertList);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

Main

package cn.com.lyb;


import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.transaction.jta.JtaAutoConfiguration;
import org.springframework.scheduling.annotation.EnableAsync;

@EnableAsync
@SpringBootApplication
@MapperScan("cn.com.lyb.**.dao")
public class Main {
    public static void main(String[] args) {
        try{
            SpringApplication.run(Main.class, args);
        }catch (Exception e){
            System.out.println("报错原因：" + e);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

配置文件

server:
  port: 9090
spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://17x.xx.xx.xx:3367/lyb-base?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false
    username: root
    password: 123456
  sharding-sphere:
    # 是否启用 Sharding
    enabled: true
    # 打印sql
    props:
      sql-show: true
    datasource:
      names: ds
      ds:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: ${spring.datasource.driver-class-name}
        url: ${spring.datasource.url}
        username: ${spring.datasource.username}
        password: ${spring.datasource.password}
    rules:
      sharding:
        defaultDataSourceName: ds
        # 表策略配置
        tables:
          # base 是逻辑表，数据库存在的是base_0,base_1,...
          base:
            actualDataNodes: ds.base_$->{0..9}
            tableStrategy:
              # 使用标准分片策略
              standard:
                # 配置分片字段
                shardingColumn: pid
                # 分片算法名称，不支持大写字母和下划线，否则启动就会报错
                shardingAlgorithmName: sharding-altorithm
        # 分片算法配置
        shardingAlgorithms:
          # 分片算法名称，不支持大写字母和下划线，否则启动就会报错
          sharding-altorithm:
            # 类型：配置文件处理，简单的可采用此类型
#            type: INLINE
#            props:
#                #分片表达式，用pid对10取模，然后分散到10个表中
#                algorithm-expression: base_$->{pid % 10}
#                allow-range-query-with-inline-sharding: true
            # 类型：自定义策略
            type: CLASS_BASED
            props:
              # 分片策略
              strategy: standard
              # 分片算法类
              algorithmClassName: cn.com.lyb.config.TableShardingAlgorithm

#mybatis-plus配置控制台打印完整带参数SQL语句
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0modelVersion>
    <parent>
        <groupId>org.springframework.bootgroupId>
        <artifactId>spring-boot-starter-parentartifactId>
        <version>2.2.5.RELEASEversion>
        <relativePath/> 
    parent>
    <groupId>cn.com.lybgroupId>
    <artifactId>springboot-shard-jdbc-5.2.1artifactId>
    <version>1.0-SNAPSHOTversion>

    <properties>
        <snakeyaml.version>1.33snakeyaml.version>
        <maven.compiler.source>8maven.compiler.source>
        <maven.compiler.target>8maven.compiler.target>
        <project.build.sourceEncoding>UTF-8project.build.sourceEncoding>
    properties>
    <dependencies>

        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>
        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-data-jdbcartifactId>
        dependency>

        
        <dependency>
            <groupId>com.baomidougroupId>
            <artifactId>mybatis-plus-boot-starterartifactId>
            <version>3.4.3version>
        dependency>
        
        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <version>5.1.49version>
        dependency>

        <dependency>
            <groupId>org.apache.shardingspheregroupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starterartifactId>
            <version>5.2.1version>
        dependency>



        <dependency>
            <groupId>org.apache.tomcatgroupId>
            <artifactId>tomcat-dbcpartifactId>
            <version>10.0.16version>
        dependency>

        <dependency>
            <groupId>cn.hutoolgroupId>
            <artifactId>hutool-allartifactId>
            <version>5.6.3version>
        dependency>

        <dependency>
            <groupId>org.projectlombokgroupId>
            <artifactId>lombokartifactId>
        dependency>

    dependencies>

project>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74