shardingsphere-jdbc基础配置(4.1.1)

maven配置

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>  
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.0</version>
        </dependency>
        <dependency>
            <groupId> com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.58</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.3</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>29.0-jre</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
        <resources>
            <!--将Java代码下的xml编译到class下面去-->
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>

            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.*</include>
                </includes>
            </resource>

            <resource>
                <directory>src/main/webapp</directory>
                <targetPath>META-INF/resources</targetPath>
                <includes>
                    <include>**/*.*</include>
                </includes>
            </resource>
        </resources>
    </build>

yml

server:
  port: 8081
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: "com.mysql.cj.jdbc.Driver"
        url: jdbc:mysql://localhost:3306/order?characterEncoding=UTF-8&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=Asia/Shanghai
        username: "root"
        password: "xxx"
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: "com.mysql.cj.jdbc.Driver"
        url: jdbc:mysql://localhost:3306/kms?characterEncoding=UTF-8&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=Asia/Shanghai
        username: "root"
        password: "xxxx"
    sharding:
      tables:
        t_order:
          actualDataNodes: ds0.t_order$->{0..1}
          table-strategy:
            inline:
              sharding-column: order_id
              #根据字段值求模
              algorithm-expression: t_order$->{order_id % 2}
              #字段的hashcode求模
              #algorithm-expression: t_order$->{Math.abs(order_id.hashCode()) % 2}
          key-generator:
            column: order_id
            type: SNOWFLAKE
            props:
              worker:
                id: ${workerId}
              max:
                vibration:
                  offset: 1
        t_order_item:
          actualDataNodes: ds0.t_order_item$->{0..1}
          table-strategy:
            inline:
              sharding-column: order_id
              #根据字段值求模
              algorithm-expression: t_order_item$->{order_id % 2}
              #字段的hashcode求模
              #algorithm-expression: t_order$->{Math.abs(order_id.hashCode()) % 2}
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id: ${workerId}
              max:
                vibration:
                  offset: 1
        t_area:
          actualDataNodes: ds0.t_area_$->{['shanghai','tianjin']}
          table-strategy:
            inline:
              sharding-column: area
              #枚举算法
              algorithm-expression: t_area_$->{area}
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id:456
        t_order_record:
          actualDataNodes: ds0.t_order_record_$->{['2022_01','2022_02','2022_03','2022_04','2022_05','2022_06','2022_07','2022_08','2022_09','2022_10','2022_11','2022_12']}
          tableStrategy:
            standard:
              shardingColumn: create_date
              #根据月份分片->每月一个分片
              preciseAlgorithmClassName: com.example.skywalkingtest.config.sharding.SingleMonthPreciseShardingAlgorithm
#              sharding-algorithm-name: order_record_date_interval
#              rangeAlgorithmClassName:
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id: 135
        t_user_bill:
          actualDataNodes: ds0.t_user_bill_$->{['2022_01_03','2022_04_06','2022_07_09','2022_10_12']}
          tableStrategy:
            standard:
              shardingColumn: create_date
              #根据月份分片->每三个月一个分片
              preciseAlgorithmClassName: com.example.skywalkingtest.config.sharding.RangeMonthPreciseShardingAlgorithm
              #范围查找时路由指定分片使用，不指定就会全表查询。 例：>、<、between···and、>=、<=
              rangeAlgorithmClassName: com.example.skywalkingtest.config.sharding.MonthRangeShardingAlgorithm
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id: 666
        t_user_activity:
          actualDataNodes: ds0.t_user_activity_south_$->{['2022_0103','2022_0406','2022_0709','2022_1012']},ds0.t_user_activity_north_$->{['2022_0103','2022_0406','2022_0709','2022_1012']}
          tableStrategy:
            #复合条件分片--》多字段分片，字段之间以逗号隔开，PS：不止两个字段，多个也可以
            complex:
              shardingColumns: area,create_date
              algorithmClassName: com.example.skywalkingtest.config.sharding.AreaAndMonthComplexKeysShardingAlgorithm
          keyGenerator:
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id: ${workerId}
              max:
                vibration:
                  offset: 1
        t_log:
          actualDataNodes: ds0.t_log_${0..1}
          tableStrategy:
            hint: #自定义使用，跳过sql解析，理解尚不透彻
              algorithmClassName: com.example.skywalkingtest.config.sharding.ModuloHintShardingAlgorithm
          keyGenerator:
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id: ${workerId}
              max:
                vibration:
                  offset: 1
        t_adress:
          actualDataNodes: ds$->{0..1}.t_adress
          keyGenerator:
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id: ${workerId}
              max:
                vibration:
                  offset: 1
      defaultTableStrategy:
        none:
      bindingTables:
        - t_order,t_order_item
      broadcastTables:
        - t_adress
    props:
      sql:
        show: true

mybatis-plus:
  typeAliasesPackage: com.example.skywalkingtest.po
  mapper-locations: classpath*:com/example/skywalkingtest/mapper/xml/*Mapper.xml
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

ServerApplication

@MapperScan("com.example.shardingsphere.mapper")
@SpringBootApplication
public class ShardingsphereApplication {

    public static void main(String[] args) {
      SpringApplication.run(SkywalkingTestApplication.class, args);
    }

    @PostConstruct
    void started() {
        System.setProperty("user.timezone", "Asia/Shanghai");
        TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai"));
    }

}

上述是本次demo中涉及到相关依赖和数据分片需要使用的所有配置，下一章逐步拆解各个分片。

分片：

• inline表达式分片
• standard标准分片
• complex复合分片
• hint自定义分片