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
[email protected]

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
[email protected]
#真实表分布，分库，分表


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各有一条订单数据

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

以上就是Sharding-JDBC自定义分片算法的操作。若有问题可留言评论

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

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

三、具体代码实现

继续阅读

nginx location中斜线的位置的重要性

nginx 安装错误信息解决

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的简单使用

neo4j之cypher使用文档

Ambari介绍和架构原理

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

spark/scala关于【资源文件】加载方法概述外部文件加载方案测试资源文件打包入jar包中小结

mybatis_入门程序Mybatis入门

AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文

Effective Java 8:通用程序设计

OOM三种类型

工厂模式-三种类型

【递归】高效率求2的n次幂

win10本地scala和spark安装安装scala安装spark

scala (3) Function 和 Method