分库分表 springboot+dubbo+mybatisPlus+shardingSphere

CREATE TABLE `student_analysis_0` (
  `ID` bigint(20) NOT NULL,
  `SCHOOL_ID` bigint(20) NOT NULL COMMENT '学校id',
  `CREATE_TIME` datetime NOT NULL COMMENT '创建时间',
  `UPDATE_TIME` datetime NOT NULL COMMENT '更新时间',
  `CREATE_USER_ID` bigint(20) NOT NULL COMMENT '创建用户主键',
  `UPDATE_USER_ID` bigint(20) NOT NULL COMMENT '更新用户主键',
  `STATUS` int(1) NOT NULL COMMENT '状态 -1:删除,0:停用，1-启用',
  PRIMARY KEY (`ID`)
) ;

        <!-- Dubbo Spring Boot Starter -->        <dependency>            <groupId>${dubbo.groupId}</groupId>            <artifactId>dubbo-spring-boot-starter</artifactId>        </dependency>        <dependency>            <groupId>${dubbo.groupId}</groupId>            <artifactId>dubbo</artifactId>        </dependency>        <dependency>            <groupId>org.apache.zookeeper</groupId>            <artifactId>zookeeper</artifactId>        </dependency>        <dependency>            <groupId>${dubbo.groupId}</groupId>            <artifactId>dubbo-configcenter-zookeeper</artifactId>            <exclusions>                <exclusion>                    <artifactId>objenesis</artifactId>                    <groupId>org.objenesis</groupId>                </exclusion>            </exclusions>        </dependency>
        <dependency>            <groupId>${dubbo.groupId}</groupId>            <artifactId>dubbo-rpc-rest</artifactId>            <exclusions>                <exclusion>                    <artifactId>jetty-server</artifactId>                    <groupId>org.eclipse.jetty</groupId>                </exclusion>                <exclusion>                    <artifactId>jetty-servlet</artifactId>                    <groupId>org.eclipse.jetty</groupId>                </exclusion>            </exclusions>        </dependency>
        <dependency>            <groupId>org.apache.tomcat.embed</groupId>            <artifactId>tomcat-embed-logging-juli</artifactId>        </dependency>
        <dependency>            <groupId>javax.el</groupId>            <artifactId>javax.el-api</artifactId>            <version>3.0.0</version>        </dependency>        <dependency>            <groupId>org.glassfish.web</groupId>            <artifactId>javax.el</artifactId>            <version>2.2.6</version>            <exclusions>                <exclusion>                    <artifactId>javax.el-api</artifactId>                    <groupId>javax.el</groupId>                </exclusion>            </exclusions>        </dependency>
        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-log4j2</artifactId>            <version>2.1.4.RELEASE</version>        </dependency>
        <!-- MyBatis Plus -->        <dependency>            <groupId>com.baomidou</groupId>            <artifactId>mybatis-plus-boot-starter</artifactId>            <version>3.1.0</version>        </dependency>
        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <version>6.0.6</version>        </dependency>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-test</artifactId>            <version>2.1.5.RELEASE</version>            <scope>test</scope>        </dependency>               <!--sharding jdbc springboot-->        <dependency>            <groupId>org.apache.shardingsphere</groupId>            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>            <version>4.1.0</version>            <!--<version>4.0.0-RC2</version>-->        </dependency>
        <dependency>            <groupId>org.apache.shardingsphere</groupId>            <artifactId>sharding-jdbc-spring-namespace</artifactId>            <version>4.1.0</version>            <!--<version>4.0.0-RC2</version>-->        </dependency>

spring:  shardingsphere:    datasource:      # 数据库名称，多个以逗号隔开      names: ds2020,ds2021      ds2020:        type: com.zaxxer.hikari.HikariDataSource        driver-class-name: com.mysql.cj.jdbc.Driver        jdbc-url: jdbc:mysql://${database.mysql.ip}:${database.mysql.port}/big-data?useUnicode=true&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&useSSL=false&nullNamePatternMatchesAll=true        username: ${database.mysql.username}        password: ${database.mysql.password}        minimum-idle: 1        maximum-pool-size: 5        connection-test-query: SELECT 1        connection-timeout: 6000      ds2021:        type: com.zaxxer.hikari.HikariDataSource        driver-class-name: com.mysql.cj.jdbc.Driver        jdbc-url: jdbc:mysql://${database.mysql.ip}:${database.mysql.port}/big-data-2021?useUnicode=true&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&useSSL=false&nullNamePatternMatchesAll=true        username: ${database.mysql.username}        password: ${database.mysql.password}        minimum-idle: 1        maximum-pool-size: 5        connection-test-query: SELECT 1        connection-timeout: 6000
    sharding:      #默认库      default-data-source-name: ds2020      tables:        student_analysis:          #物理表的结点,下面代表的是ds2020.student_analysis_0..10、ds2021.student_analysis_0..1          actual-data-nodes: ds$->{2020..2021}.student_analysis_$->{0..4}          #分库策略，按照创建时间的年份分库，如果不用分库的，直接注释掉分库相关的代码          database-strategy:            standard:              sharding-column: create_time              precise-algorithm-class-name: com.auge.big.data.sharding.CreateTimeShardingDatabaseAlgorithm          table-strategy:            #分表策略，根据school_id字段的值模10            inline:              sharding-column: school_id              algorithm-expression: student_analysis_$->{school_id % 5}    props:      #是否打印逻辑SQL语句和实际SQL语句，建议调试时打印，在生产环境关闭      sql:        show: true

/** * 分库策略自定义算法。 */public class CreateTimeShardingDatabaseAlgorithm implements PreciseShardingAlgorithm<Date> {    /**     * 按创建时间分库     */    @Override    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {        Date value = preciseShardingValue.getValue();        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");        String dataSource = "ds" + sdf.format(value);        System.out.println("switch datasource:" + dataSource);        return dataSource;    }}