SpringBoot整合MyatisPlus shardingjdbc分库分表分库分表

一、分库分表环境搭建

1、创建SpringBoot项目

2、引入相关maven依赖

<?xml version="1.0" encoding="UTF-8"?>

<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.0</modelVersion>

<groupId>com.roy</groupId>

<artifactId>ShardingSphereDemo</artifactId>

<version>1.0-SNAPSHOT</version>

<build>

<plugins>

<plugin>

<groupId>org.apache.maven.plugins</groupId>

<artifactId>maven-compiler-plugin</artifactId>

<configuration>

<source>8</source>

<target>8</target>

</configuration>

</plugin>

</plugins>

</build>

<dependencyManagement>

<dependencies>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-dependencies</artifactId>

<version>2.3.1.RELEASE</version>

<type>pom</type>

<scope>import</scope>

</dependency>

</dependencies>

</dependencyManagement>

<dependencies>

<dependency>

<groupId>org.apache.shardingsphere</groupId>

<artifactId>sharding-jdbc-spring-boot-starter</artifactId>

<version>4.1.1</version>

</dependency>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter</artifactId>

</dependency>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-test</artifactId>

</dependency>

<dependency>

<groupId>com.alibaba</groupId>

<artifactId>druid</artifactId>

<version>1.1.22</version>

</dependency>

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

<dependency>

<groupId>com.baomidou</groupId>

<artifactId>mybatis-plus-boot-starter</artifactId>

<version>3.0.5</version>

</dependency>

</dependencies>

</project>

二、创建数据库和数据库表

1、创建order_1，order_2两个数据库

2、分表在order_1和order_2库中创建 t_order_1和t_order_2两个表

3、按照创建时间create_date 进行分库分表 2020年的订单放到order_1库里面，2021年创建的订单放到order_2库里面，并且订单月份是1-6月的订单放到t_order_1表里面，7-12月份的订单放到t_order_2表里面

三、具体代码实现

1、实体类代码

import com.baomidou.mybatisplus.annotation.TableName;

@TableName("t_order")
public class Order {

    private Long id;
    private String orderSn;
    private String memberId;
    private String createDate;

    public Long getId() {
        return id;
    }

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

    public String getOrderSn() {
        return orderSn;
    }

    public void setOrderSn(String orderSn) {
        this.orderSn = orderSn;
    }

    public String getMemberId() {
        return memberId;
    }

    public void setMemberId(String memberId) {
        this.memberId = memberId;
    }

    public String getCreateDate() {
        return createDate;
    }

    public void setCreateDate(String createDate) {
        this.createDate = createDate;
    }
}           

2、数据库访问层

import com.roy.shardingDemo.entity.Order;

public interface OrderMapper extends BaseMapper<Order> {
}           

3.application.properties配置分片规则

#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/order_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=liujing@888

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/order_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=liujing@888
#真实表分布，分库，分表


spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
        
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m$->{1..2}.t_order_$->{1..2}

        
spring.shardingsphere.sharding.tables.t_order.table-strategy.complex.sharding-columns= create_date, member_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.complex.algorithm-class-name=com.xp.shardingDemo.algorithem.MemberScoreTableShardingAlgorithm
#
spring.shardingsphere.sharding.tables.t_order.database-strategy.complex.sharding-columns=create_date, member_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.complex.algorithm-class-name=com.xp.shardingDemo.algorithem.MemberScoreDSShardingAlgorithm
        
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true           

上面配置文件配置了m1,m2两个数据源，数据真实的存储在m1,m2两个数据源的t_order_1 和t_order_2两个表里面，也就是order_1和order_2两个库的t_order_1和t_order_2里面，这里使用的分片键是create_date 和member_id两个列，具体的分表逻辑和分库逻辑对应了OrderTableShardingAlgorithm,OrderDSShardingAlgorithm这两个实现类

public class OrderDSShardingAlgorithm implements ComplexKeysShardingAlgorithm<String> {
    @Override
    public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<String> complexKeysShardingValue) {
        Collection<String> createDateCol = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("create_date");
        Range<String> createRange = complexKeysShardingValue.getColumnNameAndRangeValuesMap().get("create_date");

        List<String> res = new ArrayList<>();
        if(createDateCol != null){
            for(String createDate: createDateCol){
                String str = createDate.substring(0,4);
                String target = "";
                if(str.equals("2020")){
                    target =  "1";
                }else {
                    target = "2";
                }
                res.add("m"+target);
            }
        }
        if(createRange != null){
            String start = createRange.lowerEndpoint();
            String end = createRange.upperEndpoint();
            return Arrays.asList("m1","m2");
        }
        return res;
    }
}           

public class OrderTablehardingAlgorithm implements ComplexKeysShardingAlgorithm<String> {
    @Override
    public Collection<String> doSharding(Collection<String> collection, ComplexKeysShardingValue<String> complexKeysShardingValue) {
        Collection<String> createDateCol = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("create_date");
        Range<String> createRange = complexKeysShardingValue.getColumnNameAndRangeValuesMap().get("create_date");

        List<String> res = new ArrayList<>();
        List<String> list1 = Arrays.asList("01","02","03","04","05","06");
        if(createDateCol != null){
            for(String createDate :createDateCol){
                String str = createDate.substring(5,7);
                String target = "";
                if(list1.contains(str)){
                    target = complexKeysShardingValue.getLogicTableName() + "_1";
                }else {
                    target = complexKeysShardingValue.getLogicTableName() + "_2";
                }
                res.add(target);
            }
        }
        if(createRange != null) {
            String taget = complexKeysShardingValue.getLogicTableName() + "_1";
            res.add(taget);
            return res;
        }


        return res;
    }

}           

四、执行单元测试

@RunWith(SpringRunner.class)

@SpringBootTest

public class ShardingTest {

@Resource private OrderMapper orderMapper;

@Test public void saveOrder() {

Order order1 = new Order(); order1.setMemberId("2344444");

order1.setOrderSn("20210412001");

order1.setCreateDate("2020-01-04 14:58:17");

orderMapper.insert(order1); Order order2 = new Order();

order2.setMemberId("2344444"); order2.setOrderSn("20210412001");

order2.setCreateDate("2020-10-04 14:58:23");

orderMapper.insert(order2); Order order3 = new Order();

order3.setMemberId("23444478"); order3.setOrderSn("20210412001");

order3.setCreateDate("2021-03-04 14:58:17");

orderMapper.insert(order3);

Order order4 = new Order();

order4.setMemberId("23444478"); order4.setOrderSn("20210412001");

order4.setCreateDate("2021-10-04 14:45:17");

orderMapper.insert(order4);

}

}

执行单元测试插入4条订单数据，按照我们的需求以及分片规则，应该是order1里面t_order_1,t_order_2各有一条订单数据，order_2库里面t_order_1,t_order_2各有一条订单数据

以上就是Sharding-JDBC自定义分片算法的操作