数据库分库分表（七）shardingjdbc读写分离demo

一、读写分离demo：

1、数据库准备：

这里新建了两个数据库，一主二从

 每个数据库里面表结构都是一样的，（我这里mysql没有配置主从复制，所以后面的代码演示，从库数据是我手动从主库复制过去的）

CREATE TABLE `t_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_delete` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

2、配置文件：

server.context-path=/sharding
server.port=5555
mybatis.mapper-locations=classpath*:Mapper/*Mapper.xml

spring.shardingsphere.datasource.names = master,slave1,slave2
#配置master
spring.shardingsphere.datasource.master.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/sharding_demo_master?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=******
spring.shardingsphere.datasource.master.maxPoolSize = 100
spring.shardingsphere.datasource.master.minPoolSize = 5
#配置slave1
spring.shardingsphere.datasource.slave1.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.url=jdbc:mysql://localhost:3306/sharding_demo_slave1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=******
spring.shardingsphere.datasource.slave1.maxPoolSize = 100
spring.shardingsphere.datasource.slave1.minPoolSize = 5
#配置slave2
spring.shardingsphere.datasource.slave2.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.url=jdbc:mysql://localhost:3306/sharding_demo_slave2?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=******
spring.shardingsphere.datasource.slave2.maxPoolSize = 100
spring.shardingsphere.datasource.slave2.minPoolSize = 5
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

#配置读写分离
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave2,slave1
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
#默认
spring.shardingsphere.sharding.default-data-source-name=master
spring.shardingsphere.masterslave.name=ms

3、数据源配置

package com.demo.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {
    //指定当前对象作为bean
    @Bean(name = "master")
    @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.master")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }

    @Bean(name = "slave1")
    @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.slave1")
    public DataSource slave1DataSource(){
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }

    @Bean(name = "slave2")
    @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.slave2")
    public DataSource slave2DataSource(){
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }
}

4、测试：

（1）新增：执行localhost:5555/sharding/user/add?name=张三1&age=1

2021-12-24 14:36:48.242  INFO 5380 --- [nio-5555-exec-2] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 14:36:48.245  INFO 5380 --- [nio-5555-exec-2] ShardingSphere-SQL                       : SQL: insert into t_user(user_name,age) values (?,?) ::: DataSources: master

查看数据库，只有主库插入了数据

 （2）查询：多次执行localhost:5555/sharding/user/getById?id=1

通过日志可以看到两个从库在轮询

2021-12-24 14:37:55.661  INFO 5380 --- [nio-5555-exec-5] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 14:37:55.661  INFO 5380 --- [nio-5555-exec-5] ShardingSphere-SQL                       : SQL: select id,user_name userName,age from t_user where id = ? ::: DataSources: slave2
2021-12-24 14:38:01.974  INFO 5380 --- [nio-5555-exec-6] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 14:38:01.974  INFO 5380 --- [nio-5555-exec-6] ShardingSphere-SQL                       : SQL: select id,user_name userName,age from t_user where id = ? ::: DataSources: slave1
2021-12-24 14:38:02.699  INFO 5380 --- [nio-5555-exec-7] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 14:38:02.699  INFO 5380 --- [nio-5555-exec-7] ShardingSphere-SQL                       : SQL: select id,user_name userName,age from t_user where id = ? ::: DataSources: slave2
2021-12-24 14:38:03.416  INFO 5380 --- [nio-5555-exec-8] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 14:38:03.416  INFO 5380 --- [nio-5555-exec-8] ShardingSphere-SQL                       : SQL: select id,user_name userName,age from t_user where id = ? ::: DataSources: slave1

（3）更新

<update id="update" parameterType="com.demo.model.UserDTO">
        update t_user set user_name = #{userName} where id = #{id}
    </update>

访问localhost:5555/sharding/user/update?name=张三2&id=5，更新主表

2021-12-24 16:19:30.791  INFO 9536 --- [nio-5555-exec-2] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 16:19:30.798  INFO 9536 --- [nio-5555-exec-2] ShardingSphere-SQL                       : SQL: update t_user set user_name = ? where id = ? ::: DataSources: master

（4）删除

<delete id="deleteById">
        delete from t_user where id=#{id}
    </delete>

访问localhost:5555/sharding/user/deleteById?id=5

2021-12-24 16:20:47.539  INFO 9536 --- [nio-5555-exec-5] ShardingSphere-SQL                       : Rule Type: master-slave
2021-12-24 16:20:47.539  INFO 9536 --- [nio-5555-exec-5] ShardingSphere-SQL                       : SQL: delete from t_user where id=? ::: DataSources: master

二、垂直分库+水平分库+水平分表+读写分离：

1、数据库准备：

（1）公共库：

user库有一张数据表

CREATE TABLE `t_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_delete` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

（2）订单表水平分库+水平分表，按年分为2020和2021两个库，这两个库做读写分离再分别有一  主一从，总共是4个库，其中localhost为主库，32机器为从库

 每个库里面都是1到12共12张月表，表结构都是一样的

CREATE TABLE `t_order_1` (
  `id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_number` varchar(255) DEFAULT NULL COMMENT '订单号',
  `order_date` date DEFAULT NULL COMMENT '下单时间',
  `user_id` bigint(20) DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（我这里Mysql没有配置主从复制，从库的数据仍然是我手动从主库复制过去的；且每个库只建了      t_order_1和t_order_12两个表用来测试）

2、代码结构：

3、pom：

<?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>org.example</groupId>
    <artifactId>sharding-demo-5</artifactId>
    <version>1.0-SNAPSHOT</version>
    <description>垂直分库+水平分库+读写分离+水平分表</description>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.1.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

       <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>

        <!-- Druid连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.12</version>
        </dependency>

        <!-- Mysql驱动依赖 -->
        <!--<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.21</version>
        </dependency>-->

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.13</version>
        </dependency>

        <!-- Sharding-JDBC -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
    </dependencies>

    <repositories>
        <repository>
            <id>aliyunmaven</id>
            <url>http://maven.aliyun.com/nexus/content/groups/public/</url>
        </repository>
    </repositories>
</project>

4、application.properties：

server.context-path=/sharding
server.port=6666
mybatis.mapper-locations=classpath*:Mapper/*Mapper.xml

# 配置数据源
spring.shardingsphere.datasource.names=master0,master1,slave0,slave1,sharding_demo_user
#配置master0数据源,localhost的order_ms_2020库
spring.shardingsphere.datasource.master0.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master0.url=jdbc:mysql://localhost:3308/order_ms_2020?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=******
#配置slave0数据源，32的order_ms_2020库
spring.shardingsphere.datasource.slave0.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://xxx.xx.xxx.32:23306/order_ms_2020?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=******
#配置master1数据源,localhost的order_ms_2021库
spring.shardingsphere.datasource.master1.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master1.url=jdbc:mysql://localhost:3308/order_ms_2021?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=******
#配置slave1数据源，32的order_ms_2021库
spring.shardingsphere.datasource.slave1.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave1.url=jdbc:mysql://xxx.xx.xxx.32:23306/order_ms_2021?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=******
#配置sharding_demo_user数据源,localhost的sharding_demo_user库
spring.shardingsphere.datasource.sharding_demo_user.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding_demo_user.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.sharding_demo_user.url=jdbc:mysql://localhost:3308/sharding_demo_user?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.sharding_demo_user.username=root
spring.shardingsphere.datasource.sharding_demo_user.password=******
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

#配置t_order库、表的分布，配置主键id为雪花算法自增长
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=order_ms_$->{2020..2021}.t_order_$->{1..12}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
#配置t_order表的分库策略,按年分库
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.preciseAlgorithmClassName=com.demo.config.OrderDbPreciseShardingConfig
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.rangeAlgorithmClassName=com.demo.config.OrderDbRangeShardingConfig
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=order_date
#配置t_order表的分表策略,按月分表
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.preciseAlgorithmClassName=com.demo.config.OrderTablePreciseShardingConfig
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.rangeAlgorithmClassName=com.demo.config.OrderTableRangeShardingConfig
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_date

#配置垂直分库t_user的策略
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=sharding_demo_user.t_user
spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=id
#由于只有一张表，因此在此直接写表明，不需要像水平分多个表那样写策略
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user

#配置读写分离
spring.shardingsphere.sharding.master-slave-rules.order_ms_2020.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.order_ms_2020.slave-data-source-names=slave0
spring.shardingsphere.sharding.master-slave-rules.order_ms_2021.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.order_ms_2021.slave-data-source-names=slave1

 5、分库分表策略：

（1）精准分库：

package com.demo.config;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

/**
 * 分库
 * order_ms_yyyy
 */
public class OrderDbPreciseShardingConfig implements PreciseShardingAlgorithm<Date> {

    //精准
    @Override
    public String doSharding(Collection<String> collection,
                             PreciseShardingValue<Date> preciseShardingValue) {
        Date value = preciseShardingValue.getValue();
        if(value == null){
            throw new UnsupportedOperationException("prec is null");
        }
        SimpleDateFormat format = new SimpleDateFormat("yyyy");
        String dbName=  "order_ms_"+format.format(value);
        System.out.println("dbName:"+dbName);
        for (String collectionDbName : collection) {//循环表名已确定使用哪张表
            if (collectionDbName.equals(dbName)){  //表示相等就返回
                System.out.println("库名为"+collectionDbName);
                return collectionDbName;
            }
        }
        return null;
    }
}

（2）范围分库：

package com.demo.config;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

public class OrderDbRangeShardingConfig implements RangeShardingAlgorithm<Date> {

    @Override
    public Collection<String> doSharding(Collection<String> collection,
                                         RangeShardingValue<Date> rangeShardingValue) {
        Range<Date> dates = rangeShardingValue.getValueRange();
        Date minDate = dates.lowerEndpoint();
        Date maxDate = dates.upperEndpoint();
        List<String> dbs = new ArrayList<>();
        for(String dbName : collection){
            SimpleDateFormat format = new SimpleDateFormat("yyyy");
            String dbName1 = "order_ms_"+format.format(minDate);
            String dbName2 = "order_ms_"+format.format(maxDate);
            if(dbName.equals(dbName1) || dbName.equals(dbName2)){
                dbs.add(dbName);
            }
        }
        return dbs;
    }
}

（3）精准分表：

package com.demo.config;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

/**
 * 分表
 * t_order_M
 */
public class OrderTablePreciseShardingConfig implements PreciseShardingAlgorithm<Date> {

    //精准
    @Override
    public String doSharding(Collection<String> collection,
                             PreciseShardingValue<Date> preciseShardingValue) {
        Date value = preciseShardingValue.getValue();
        if(value == null){
            throw new UnsupportedOperationException("prec is null");
        }
        SimpleDateFormat format = new SimpleDateFormat("M");
        String tableName=  "t_order_"+format.format(value);
        System.out.println("tableName:"+tableName);
        for (String collectionTableName : collection) {//循环表名已确定使用哪张表
            if (collectionTableName.equals(tableName)){  //表示相等就返回
                System.out.println("表名为"+tableName);
                return tableName;
            }
        }
        return null;
    }
}

（4）范围分表：

package com.demo.config;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

public class OrderTableRangeShardingConfig implements RangeShardingAlgorithm<Date> {

    @Override
    public Collection<String> doSharding(Collection<String> collection,
                                         RangeShardingValue<Date> rangeShardingValue) {
        Range<Date> dates = rangeShardingValue.getValueRange();
        Date minDate = dates.lowerEndpoint();
        Date maxDate = dates.upperEndpoint();
        List<String> tables = new ArrayList<>();
        for(String tableName : collection){
            SimpleDateFormat format = new SimpleDateFormat("M");
            String tableName1 = "t_order_"+format.format(minDate);
            String tableName2 = "t_order_"+format.format(maxDate);
            if(tableName.equals(tableName1) || tableName.equals(tableName2)){
                tables.add(tableName);
            }
        }
        return tables;
    }
}

6、测试：

（1）新增：

 <insert id="add" parameterType="com.demo.model.OrderDTO">
        insert into t_order(order_number,order_date) values (#{orderNumber},#{orderDate})
    </insert>

访问 

 

 后台日志打印：

dbName:order_ms_2020
库名为order_ms_2020
tableName:t_order_12
表名为t_order_12
2021-12-29 14:01:59.061  INFO 15096 --- [nio-6666-exec-2] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-29 14:01:59.064  INFO 15096 --- [nio-6666-exec-2] ShardingSphere-SQL                       : Logic SQL: insert into t_order(order_number,order_date) values (?,?)
2021-12-29 14:01:59.064  INFO 15096 --- [nio-6666-exec-2] ShardingSphere-SQL                       : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_order, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=order_date, tableName=t_order), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_order, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=19)], parametersIndex=2, logicSQL=insert into t_order(order_number,order_date) values (?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[order_number, order_date], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@4607ed1a, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@19eb5708])])
2021-12-29 14:01:59.065  INFO 15096 --- [nio-6666-exec-2] ShardingSphere-SQL                       : Actual SQL: master0 ::: insert into t_order_12 (order_number, order_date, id) VALUES (?, ?, ?) ::: [aaa, 2020-12-21 00:00:10.0, 682950630939033601]

验证主库插入成功，从库没有插入数据

（2）批量新增：

<insert id="batchInsert" parameterType="com.demo.model.OrderDTO">
        insert into t_order(order_number,order_date) values
        <foreach collection="orderDTOs" separator="," item="item">
            (#{item.orderNumber},#{item.orderDate})
        </foreach>
    </insert>

 

2021-12-29 14:05:23.751  INFO 15096 --- [nio-6666-exec-5] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-29 14:05:23.751  INFO 15096 --- [nio-6666-exec-5] ShardingSphere-SQL                       : Logic SQL: insert into t_order(order_number,order_date) values
          
            (?,?)
         , 
            (?,?)
         , 
            (?,?)
2021-12-29 14:05:23.751  INFO 15096 --- [nio-6666-exec-5] ShardingSphere-SQL                       : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_order, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=order_date, tableName=t_order), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=1})]), AndCondition(conditions=[Condition(column=Column(name=order_date, tableName=t_order), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=3})]), AndCondition(conditions=[Condition(column=Column(name=order_date, tableName=t_order), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=5})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_order, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=19)], parametersIndex=6, logicSQL=insert into t_order(order_number,order_date) values
          
            (?,?)
         , 
            (?,?)
         , 
            (?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[order_number, order_date], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@2d8c7268, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@495bb305]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@2f263787, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@3fbc97b7]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@71edbd1a, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@653aebb9])])
2021-12-29 14:05:23.752  INFO 15096 --- [nio-6666-exec-5] ShardingSphere-SQL                       : Actual SQL: master1 ::: insert into t_order_1 (order_number, order_date, id) VALUES (?, ?, ?) ::: [aaa1, 2021-01-21 00:00:10.0, 682951489534033920]
2021-12-29 14:05:23.752  INFO 15096 --- [nio-6666-exec-5] ShardingSphere-SQL                       : Actual SQL: master0 ::: insert into t_order_1 (order_number, order_date, id) VALUES (?, ?, ?) ::: [bbb1, 2020-01-21 00:00:10.0, 682951489534033921]
2021-12-29 14:05:23.753  INFO 15096 --- [nio-6666-exec-5] ShardingSphere-SQL                       : Actual SQL: master0 ::: insert into t_order_12 (order_number, order_date, id) VALUES (?, ?, ?) ::: [ccc1, 2020-12-21 00:00:10.0, 682951489534033922]

（3）between and查询：

访问localhost:6666/sharding/order/betweenAndQuery?minDate=2020/1/20 00:00:00&maxDate=2021/12/22 00:00:10

后台日志打印：

2021-12-29 14:09:28.710  INFO 15096 --- [nio-6666-exec-8] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-29 14:09:28.710  INFO 15096 --- [nio-6666-exec-8] ShardingSphere-SQL                       : Logic SQL: select id,order_number orderNumber,order_date orderDate
       from t_order where  order_date between ?
        and  ?
2021-12-29 14:09:28.710  INFO 15096 --- [nio-6666-exec-8] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_order, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=order_date, tableName=t_order), operator=BETWEEN, compareOperator=null, positionValueMap={}, positionIndexMap={0=0, 1=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_order, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=2, logicSQL=select id,order_number orderNumber,order_date orderDate
       from t_order where  order_date between ?
        and  ?)), containStar=false, firstSelectItemStartIndex=7, selectListStopIndex=54, groupByLastIndex=0, items=[CommonSelectItem(expression=id, alias=Optional.absent()), CommonSelectItem(expression=order_number, alias=Optional.of(orderNumber)), CommonSelectItem(expression=order_date, alias=Optional.of(orderDate))], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2021-12-29 14:09:28.711  INFO 15096 --- [nio-6666-exec-8] ShardingSphere-SQL                       : Actual SQL: slave1 ::: select id,order_number orderNumber,order_date orderDate
       from t_order_1 where  order_date between ?
        and  ? ::: [2020-01-20 00:00:00.0, 2021-12-22 00:00:10.0]
2021-12-29 14:09:28.711  INFO 15096 --- [nio-6666-exec-8] ShardingSphere-SQL                       : Actual SQL: slave1 ::: select id,order_number orderNumber,order_date orderDate
       from t_order_12 where  order_date between ?
        and  ? ::: [2020-01-20 00:00:00.0, 2021-12-22 00:00:10.0]
2021-12-29 14:09:28.711  INFO 15096 --- [nio-6666-exec-8] ShardingSphere-SQL                       : Actual SQL: slave0 ::: select id,order_number orderNumber,order_date orderDate
       from t_order_1 where  order_date between ?
        and  ? ::: [2020-01-20 00:00:00.0, 2021-12-22 00:00:10.0]
2021-12-29 14:09:28.711  INFO 15096 --- [nio-6666-exec-8] ShardingSphere-SQL                       : Actual SQL: slave0 ::: select id,order_number orderNumber,order_date orderDate
       from t_order_12 where  order_date between ?
        and  ? ::: [2020-01-20 00:00:00.0, 2021-12-22 00:00:10.0]

（4）公共库插入：访问localhost:6666/sharding/user/add?name=张三1&age=1

后台日志打印：只插入user库

2021-12-29 14:11:24.085  INFO 15096 --- [nio-6666-exec-1] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-29 14:11:24.085  INFO 15096 --- [nio-6666-exec-1] ShardingSphere-SQL                       : Logic SQL: insert into t_user(user_name,age) values (?,?)
2021-12-29 14:11:24.085  INFO 15096 --- [nio-6666-exec-1] ShardingSphere-SQL                       : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=t_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=18)], parametersIndex=2, logicSQL=insert into t_user(user_name,age) values (?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[user_name, age], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@4f44bc84, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6dfa8ac6])])
2021-12-29 14:11:24.085  INFO 15096 --- [nio-6666-exec-1] ShardingSphere-SQL                       : Actual SQL: sharding_demo_user ::: insert into t_user (user_name, age, id) VALUES (?, ?, ?) ::: [张三1, 1, 682953000913731585]

（5）公共库查询：访问localhost:6666/sharding/user/getById?id=1

 后台日志打印：只查询user库

2021-12-29 14:12:37.351  INFO 15096 --- [nio-6666-exec-4] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-29 14:12:37.352  INFO 15096 --- [nio-6666-exec-4] ShardingSphere-SQL                       : Logic SQL: select id,user_name userName,age from t_user where id = ?
2021-12-29 14:12:37.352  INFO 15096 --- [nio-6666-exec-4] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=id, tableName=t_user), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=0})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=select id,user_name userName,age from t_user where id = ?)), containStar=false, firstSelectItemStartIndex=7, selectListStopIndex=31, groupByLastIndex=0, items=[CommonSelectItem(expression=id, alias=Optional.absent()), CommonSelectItem(expression=user_name, alias=Optional.of(userName)), CommonSelectItem(expression=age, alias=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2021-12-29 14:12:37.352  INFO 15096 --- [nio-6666-exec-4] ShardingSphere-SQL                       : Actual SQL: sharding_demo_user ::: select id,user_name userName,age from t_user where id = ? ::: [1]