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