shardingJdbc专题系列(二)之环境准备

阳光穿透心脏的1/2处 2022-12-25 08:57 164阅读 0赞

上篇文章中，我们提到了shardingJdbc中的五种分片策略和四种分片算法。 其中最简单的分片策略就是inline行内表达式分片策略。只需要在配置文件中通过几行简单的配置就可以实现分库分表的操作。这边文章我们就来介绍如何使用分片表达式实现分库分表。

同时要注意，使用inline行内表达式的分片策略使用groovy的表达式，提供对sql语句中的 = 和 in的分片操作支持，只支持单分片。使用简单的分片算法，可以通过简单的配置使用，从而避免繁琐的java代码开发，如 t\_user$->\{u\_id%8\}表示t\_user表根据u\_id模8，而分成8张表，表名为 t\_user\_0 到 t\_user\_7

# 环境准备： #

本次案例采用springboot和mybatis-plus进行演示，具体版本如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzcWluZ2Zlbmc_size_16_color_FFFFFF_t_70][]

首先我们执行一下初始化的sql,创建几张表，然后使用mybatis-plus自动生成所需实体和mapper

### 初始化sql： ###

CREATE database ds0;
    use ds0;
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for t_address
    -- ----------------------------
    DROP TABLE IF EXISTS `t_address`;
    CREATE TABLE `t_address` (
      `address_id` bigint(20) NOT NULL,
      `address_name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`address_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_order
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order`;
    CREATE TABLE `t_order` (
      `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `order_code` varchar(255) DEFAULT NULL,
      `address_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=532651227377807361 DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_order_1
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order_1`;
    CREATE TABLE `t_order_1` (
      `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `order_code` varchar(255) DEFAULT NULL,
      `address_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_order_2
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order_2`;
    CREATE TABLE `t_order_2` (
      `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `order_code` varchar(255) DEFAULT NULL,
      `address_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_order_3
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order_3`;
    CREATE TABLE `t_order_3` (
      `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `order_code` varchar(255) DEFAULT NULL,
      `address_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_order_4
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order_4`;
    CREATE TABLE `t_order_4` (
      `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `order_code` varchar(255) DEFAULT NULL,
      `address_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_order_item
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order_item`;
    CREATE TABLE `t_order_item` (
      `order_item_id` bigint(20) NOT NULL,
      `order_id` bigint(20) DEFAULT NULL,
      `user_id` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`order_item_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_user
    -- ----------------------------
    DROP TABLE IF EXISTS `t_user`;
    CREATE TABLE `t_user` (
      `user_id` bigint(20) NOT NULL,
      `user_name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    SET FOREIGN_KEY_CHECKS = 1;
    
    -- ------------第二个库------------------
    CREATE database ds1;
    use ds1;
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for t_address
    -- ----------------------------
    DROP TABLE IF EXISTS `t_address`;
    CREATE TABLE `t_address` (
      `address_id` bigint(20) NOT NULL,
      `address_name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`address_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_order
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order`;
    CREATE TABLE `t_order` (
      `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `order_code` varchar(255) DEFAULT NULL,
      `address_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=532651227377807361 DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_order_1
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order_1`;
    CREATE TABLE `t_order_1` (
      `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `order_code` varchar(255) DEFAULT NULL,
      `address_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_order_2
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order_2`;
    CREATE TABLE `t_order_2` (
      `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `order_code` varchar(255) DEFAULT NULL,
      `address_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_order_3
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order_3`;
    CREATE TABLE `t_order_3` (
      `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `order_code` varchar(255) DEFAULT NULL,
      `address_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_order_4
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order_4`;
    CREATE TABLE `t_order_4` (
      `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) DEFAULT NULL,
      `order_code` varchar(255) DEFAULT NULL,
      `address_id` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_order_item
    -- ----------------------------
    DROP TABLE IF EXISTS `t_order_item`;
    CREATE TABLE `t_order_item` (
      `order_item_id` bigint(20) NOT NULL,
      `order_id` bigint(20) DEFAULT NULL,
      `user_id` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`order_item_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- ----------------------------
    -- Table structure for t_user
    -- ----------------------------
    DROP TABLE IF EXISTS `t_user`;
    CREATE TABLE `t_user` (
      `user_id` bigint(20) NOT NULL,
      `user_name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    SET FOREIGN_KEY_CHECKS = 1;

### 项目结构如下： ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzcWluZ2Zlbmc_size_16_color_FFFFFF_t_70 1][]

其中 bz模块，是用来存放mybatis-plus生成的实体，mapper等文件的， parent模块是管理依赖的，用于其他模块继承的，剩下的几个模块，是分表演示inline策略模式和standard策略模式实现分库分表的。 两个策略都分别给出了yml配置方式和java config配置方式。

### parent依赖如下： ###

<?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">
        <parent>
            <artifactId>shardingjdbc-sample</artifactId>
            <groupId>cn.cestc</groupId>
            <version>1.0</version>
        </parent>
        <modelVersion>4.0.0</modelVersion>
    
        <artifactId>sharding-parent</artifactId>
        <packaging>pom</packaging>
    
    
        <properties>
            <java.version>1.8</java.version>
            <spring.boot.version>2.2.5.RELEASE</spring.boot.version>
            <action.version>1.0.0</action.version>
    
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    
            <lombok.version>1.18.8</lombok.version>
            <mybatis-plus.version>3.3.1.tmp</mybatis-plus.version>
            <mysql.connector.version>8.0.17</mysql.connector.version>
            <modelmapper.version>2.3.0</modelmapper.version>
            <shardingsphere.version>4.1.1</shardingsphere.version>
        </properties>
    
    
        <dependencyManagement>
            <dependencies>
                <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-dependencies</artifactId>
                    <version>${spring.boot.version}</version>
                    <scope>import</scope>
                    <type>pom</type>
                </dependency>
            </dependencies>
        </dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>${lombok.version}</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>${mysql.connector.version}</version>
            </dependency>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>${mybatis-plus.version}</version>
            </dependency>
            
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>${shardingsphere.version}</version>
            </dependency>
            
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-namespace</artifactId>
                <version>${shardingsphere.version}</version>
            </dependency>
        </dependencies>
    
        <build>
            <pluginManagement>
                <plugins>
                    
                    <plugin>
                        <groupId>org.apache.maven.plugins</groupId>
                        <artifactId>maven-jar-plugin</artifactId>
                        <version>3.1.2</version>
                    </plugin>
                    <plugin>
                        <groupId>org.apache.maven.plugins</groupId>
                        <artifactId>maven-compiler-plugin</artifactId>
                        <version>3.8.1</version>
                        <configuration>
                            <source>${java.version}</source>
                            <target>${java.version}</target>
                            <encoding>${project.build.sourceEncoding}</encoding>
                        </configuration>
                    </plugin>
                    <plugin>
                        <groupId>org.apache.maven.plugins</groupId>
                        <artifactId>maven-resources-plugin</artifactId>
                        <version>3.1.0</version>
                        <configuration>
                            <encoding>${project.build.sourceEncoding}</encoding>
                        </configuration>
                    </plugin>
    
                    
                    <plugin>
                        <groupId>org.springframework.boot</groupId>
                        <artifactId>spring-boot-maven-plugin</artifactId>
                        <version>${spring.boot.version}</version>
                        <executions>
                            <execution>
                                <goals>
                                    <goal>repackage</goal>
                                </goals>
                            </execution>
                        </executions>
                    </plugin>
                </plugins>
            </pluginManagement>
        </build>
    </project>

项目脚手架代码已上传到git: https://github.com/lsqingfeng/sharding-staging.git

这里边只是一个架子，还没有分库分表的实现

好了这几步准备好之后，就可以开始我们的实战了，这一步大家只需要准备好依赖，parent,biz模块即可。后边我们会逐个演示 inline-yml, inline-java, stardard-yml, stard-java 两个策略，两个配置方式的实现。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzcWluZ2Zlbmc_size_16_color_FFFFFF_t_70]: /images/20221120/9cf495ee3caf4a8cac825010df5903a0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xzcWluZ2Zlbmc_size_16_color_FFFFFF_t_70 1]: /images/20221120/bbf4806008cb446d8d21f0c7b7fe5b19.png