数据库分库分表（五）shardingjdbc水平分库+水平分表demo

一、行表达式策略：

1、application.properties：

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


# 配置数据源,（db3以及t_user_3实际上均无数据，这里测试是否为全路由）
spring.shardingsphere.datasource.names=db1,db2,db3
#配置db1
spring.shardingsphere.datasource.db1.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding_demo_1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=******
#配置db2
spring.shardingsphere.datasource.db2.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://localhost:3306/sharding_demo_2?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=******
#配置db3
spring.shardingsphere.datasource.db3.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db3.url=jdbc:mysql://localhost:3306/sharding_demo_3?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db3.username=root
spring.shardingsphere.datasource.db3.password=******
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

#配置t_user表的分布，配置主键id为雪花算法自增长
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=db$->{1..3}.t_user_$->{1..3}
spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
#配置t_user表的分库策略,age为分片键，为偶数在db1库，奇数在db2库
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.sharding-column=age
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.algorithm-expression=db$->{age % 2 + 1}
#配置t_user表的分表策略,id为分片键，为偶数在db1库，奇数在db2库
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_$->{id % 2+1}

2、测试：

（1）批量新增：

    @Override
    public void batchInsert() {
        List<UserDTO> users = new ArrayList<>();
        for(int i=0;i<6;i++){
            UserDTO userDTO = new UserDTO();
            userDTO.setUserName("张三"+i);
            userDTO.setAge(i);
            users.add(userDTO);
        }
        userMapper.batchInsert(users);
    }

 <insert id="batchInsert">
         insert into t_user(user_name,age) values
         <foreach collection="users" item="item" separator=",">
             (#{item.userName},#{item.age})
         </foreach>
    </insert>

访问localhost:7777/sharding/user/batchInsert

后台日志打印

2021-12-22 14:51:21.800  INFO 12300 --- [nio-7777-exec-2] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 14:51:21.802  INFO 12300 --- [nio-7777-exec-2] ShardingSphere-SQL                       : Logic SQL: insert into t_user(user_name,age) values
           
             (?,?)
          , 
             (?,?)
          , 
             (?,?)
          , 
             (?,?)
          , 
             (?,?)
          , 
             (?,?)
2021-12-22 14:51:21.802  INFO 12300 --- [nio-7777-exec-2] 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=[Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=1})]), AndCondition(conditions=[Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=3})]), AndCondition(conditions=[Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=5})]), AndCondition(conditions=[Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=7})]), AndCondition(conditions=[Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=9})]), AndCondition(conditions=[Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=11})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=18)], parametersIndex=12, 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@e3c4100, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6785da2d]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@55999193, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@4d28a3b8]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@2170df5c, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@686d3ded]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@71612c7a, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@795c748d]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@5e71cec5, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@5ff202b3]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@2788e7cb, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@3dea17ab])])
2021-12-22 14:51:21.802  INFO 12300 --- [nio-7777-exec-2] ShardingSphere-SQL                       : Actual SQL: db1 ::: insert into t_user_2 (user_name, age, id) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) ::: [张三0, 0, 680426342226853889, 张三2, 2, 680426342226853891, 张三4, 4, 680426342226853893]
2021-12-22 14:51:21.803  INFO 12300 --- [nio-7777-exec-2] ShardingSphere-SQL                       : Actual SQL: db2 ::: insert into t_user_1 (user_name, age, id) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) ::: [张三1, 1, 680426342226853890, 张三3, 3, 680426342226853892, 张三5, 5, 680426342226853894]

查看数据库：分片成功

（2）查询：

 <select id="getById"  resultType="com.demo.model.UserDTO">
        select id,user_name userName,age from t_user where id = #{id}
    </select>

访问localhost:7777/sharding/user/getById?id=680428938429726720，返回：

因为查询条件只有分表键，所以是全库路由

2021-12-22 15:03:50.183  INFO 12300 --- [nio-7777-exec-9] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 15:03:50.183  INFO 12300 --- [nio-7777-exec-9] ShardingSphere-SQL                       : Logic SQL: select id,user_name userName,age from t_user where id = ?
2021-12-22 15:03:50.183  INFO 12300 --- [nio-7777-exec-9] 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-22 15:03:50.184  INFO 12300 --- [nio-7777-exec-9] ShardingSphere-SQL                       : Actual SQL: db1 ::: select id,user_name userName,age from t_user_1 where id = ? ::: [680428938429726720]
2021-12-22 15:03:50.184  INFO 12300 --- [nio-7777-exec-9] ShardingSphere-SQL                       : Actual SQL: db2 ::: select id,user_name userName,age from t_user_1 where id = ? ::: [680428938429726720]
2021-12-22 15:03:50.184  INFO 12300 --- [nio-7777-exec-9] ShardingSphere-SQL                       : Actual SQL: db3 ::: select id,user_name userName,age from t_user_1 where id = ? ::: [680428938429726720]

而

 <select id="getByIdAndAge" resultType="com.demo.model.UserDTO">
        select id,user_name userName,age from t_user where id = #{id}
        and age = #{age}
    </select>

执行localhost:7777/sharding/user/getByIdAndAge?id=680428938429726720&age=1，返回结果相同，因为查询条件包含了库分片键和表分片键，所以是精准路由

2021-12-22 15:08:51.626  INFO 1688 --- [nio-7777-exec-3] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 15:08:51.626  INFO 1688 --- [nio-7777-exec-3] ShardingSphere-SQL                       : Logic SQL: select id,user_name userName,age from t_user where id = ?
        and age = ?
2021-12-22 15:08:51.626  INFO 1688 --- [nio-7777-exec-3] 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}), Condition(column=Column(name=age, tableName=t_user), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=2, logicSQL=select id,user_name userName,age from t_user where id = ?
        and age = ?)), 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-22 15:08:51.626  INFO 1688 --- [nio-7777-exec-3] ShardingSphere-SQL                       : Actual SQL: db2 ::: select id,user_name userName,age from t_user_1 where id = ?
        and age = ? ::: [680428938429726720, 1]

（3）in查询：

 <select id="inQuery" resultType="com.demo.model.UserDTO">
        select id,user_name userName,age from t_user where id in
        <foreach collection="ids" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
        and age in
        <foreach collection="ages" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </select>

访问localhost:7777/sharding/user/inQuery?ids=680426342226853893,680426342226853894,680426342226853889&ages=0,1,2,3

后台日志打印

2021-12-22 15:48:49.494  INFO 13152 --- [nio-7777-exec-7] ShardingSphere-SQL                       : Actual SQL: db1 ::: select id,user_name userName,age from t_user_1 where id in
         (  
            ?
         , 
            ?
         , 
            ?
         ) 
        and age in
         (  
            ?
         , 
            ?
         , 
            ?
         , 
            ?
         ) ::: [680426342226853893, 680426342226853894, 680426342226853889, 0, 1, 2, 3]
2021-12-22 15:48:49.494  INFO 13152 --- [nio-7777-exec-7] ShardingSphere-SQL                       : Actual SQL: db1 ::: select id,user_name userName,age from t_user_2 where id in
         (  
            ?
         , 
            ?
         , 
            ?
         ) 
        and age in
         (  
            ?
         , 
            ?
         , 
            ?
         , 
            ?
         ) ::: [680426342226853893, 680426342226853894, 680426342226853889, 0, 1, 2, 3]
2021-12-22 15:48:49.494  INFO 13152 --- [nio-7777-exec-7] ShardingSphere-SQL                       : Actual SQL: db2 ::: select id,user_name userName,age from t_user_1 where id in
         (  
            ?
         , 
            ?
         , 
            ?
         ) 
        and age in
         (  
            ?
         , 
            ?
         , 
            ?
         , 
            ?
         ) ::: [680426342226853893, 680426342226853894, 680426342226853889, 0, 1, 2, 3]
2021-12-22 15:48:49.495  INFO 13152 --- [nio-7777-exec-7] ShardingSphere-SQL                       : Actual SQL: db2 ::: select id,user_name userName,age from t_user_2 where id in
         (  
            ?
         , 
            ?
         , 
            ?
         ) 
        and age in
         (  
            ?
         , 
            ?
         , 
            ?
         , 
            ?
         ) ::: [680426342226853893, 680426342226853894, 680426342226853889, 0, 1, 2, 3]

其他以此类推。

二、标准分片策略&按年分库+按月分表：

建立sharding_demo_2020、sharding_demo_2021两个库，每个库建立

t_order_1到t_order_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;

（我这里只建了1和12两个月表）

1、application.properties：

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


# 配置数据源
spring.shardingsphere.datasource.names=sharding_demo_2020,sharding_demo_2021
#配置db1
spring.shardingsphere.datasource.sharding_demo_2020.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding_demo_2020.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding_demo_2020.url=jdbc:mysql://localhost:3306/sharding_demo_2020?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.sharding_demo_2020.username=root
spring.shardingsphere.datasource.sharding_demo_2020.password=******
#配置db2
spring.shardingsphere.datasource.sharding_demo_2021.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding_demo_2021.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding_demo_2021.url=jdbc:mysql://localhost:3306/sharding_demo_2021?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.sharding_demo_2021.username=root
spring.shardingsphere.datasource.sharding_demo_2021.password=******
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

#配置t_order库、表的分布，配置主键id为雪花算法自增长
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=sharding_demo_$->{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

2、分片策略：

（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;

/**
 * 分库
 * sharding_demo_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=  "sharding_demo_"+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 = "sharding_demo_"+format.format(minDate);
            String dbName2 = "sharding_demo_"+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;
    }
}

3、测试：

（1）批量新增

<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>

后台日志打印：

dbName:sharding_demo_2021
库名为sharding_demo_2021
tableName:t_order_12
表名为t_order_12
dbName:sharding_demo_2020
库名为sharding_demo_2020
tableName:t_order_1
表名为t_order_1
dbName:sharding_demo_2020
库名为sharding_demo_2020
tableName:t_order_12
表名为t_order_12
2021-12-23 10:58:18.769  INFO 10632 --- [nio-6666-exec-5] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-23 10:58:18.769  INFO 10632 --- [nio-6666-exec-5] ShardingSphere-SQL                       : Logic SQL: insert into t_order(order_number,order_date) values
          
            (?,?)
         , 
            (?,?)
         , 
            (?,?)
2021-12-23 10:58:18.769  INFO 10632 --- [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@5a99b219, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@2c4576d]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@172b3af1, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@649b583b]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@738537cb, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@535a69e9])])
2021-12-23 10:58:18.770  INFO 10632 --- [nio-6666-exec-5] ShardingSphere-SQL                       : Actual SQL: sharding_demo_2021 ::: insert into t_order_12 (order_number, order_date, id) VALUES (?, ?, ?) ::: [aaa, 2021-12-21 00:00:10.0, 680730081433223168]
2021-12-23 10:58:18.770  INFO 10632 --- [nio-6666-exec-5] ShardingSphere-SQL                       : Actual SQL: sharding_demo_2020 ::: insert into t_order_1 (order_number, order_date, id) VALUES (?, ?, ?) ::: [bbb, 2020-01-21 00:00:10.0, 680730081433223169]
2021-12-23 10:58:18.770  INFO 10632 --- [nio-6666-exec-5] ShardingSphere-SQL                       : Actual SQL: sharding_demo_2020 ::: insert into t_order_12 (order_number, order_date, id) VALUES (?, ?, ?) ::: [ccc, 2020-12-21 00:00:10.0, 680730081433223170]

验证分库分表成功：

（2）between and

 <select id="betweenAndQuery" resultType="com.demo.model.OrderDTO">
       select id,order_number orderNumber,order_date orderDate
       from t_order where  order_date between #{minDate}
        and  #{maxDate}
    </select>

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

后台日志打印：

2021-12-23 11:07:13.749  INFO 3588 --- [nio-6666-exec-2] ShardingSphere-SQL                       : Logic SQL: select id,order_number orderNumber,order_date orderDate
       from t_order where  order_date between ?
        and  ?
2021-12-23 11:07:13.750  INFO 3588 --- [nio-6666-exec-2] 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-23 11:07:13.751  INFO 3588 --- [nio-6666-exec-2] ShardingSphere-SQL                       : Actual SQL: sharding_demo_2020 ::: 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-23 11:07:13.751  INFO 3588 --- [nio-6666-exec-2] ShardingSphere-SQL                       : Actual SQL: sharding_demo_2020 ::: 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-23 11:07:13.751  INFO 3588 --- [nio-6666-exec-2] ShardingSphere-SQL                       : Actual SQL: sharding_demo_2021 ::: 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-23 11:07:13.751  INFO 3588 --- [nio-6666-exec-2] ShardingSphere-SQL                       : Actual SQL: sharding_demo_2021 ::: 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]

三、操作公共表：在所有库中有一张公共表system_config，操作公共表时，需要操作所有数据库中的这张表。以第一个demo为例：

1、加上配置

#配置system_config表
spring.shardingsphere.sharding.tables.system_config.actual-data-nodes=db$->{1..3}.system_config

2、测试：

（1）新增：

 <insert id="add" parameterType="com.demo.model.SystemConfigDTO">
        insert into system_config(config_key,config_value) values (#{configKey},#{configValue})
    </insert>

后台日志打印

2021-12-23 14:08:28.558  INFO 14224 --- [nio-7777-exec-2] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-23 14:08:28.559  INFO 14224 --- [nio-7777-exec-2] ShardingSphere-SQL                       : Logic SQL: insert into system_config(config_key,config_value) values (?,?)
2021-12-23 14:08:28.559  INFO 14224 --- [nio-7777-exec-2] ShardingSphere-SQL                       : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=system_config, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=system_config, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=25)], parametersIndex=2, logicSQL=insert into system_config(config_key,config_value) values (?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[config_key, config_value], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@3ef32e26, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@69e1d51])])
2021-12-23 14:08:28.560  INFO 14224 --- [nio-7777-exec-2] ShardingSphere-SQL                       : Actual SQL: db1 ::: insert into system_config (config_key, config_value) VALUES (?, ?) ::: [aaa, aaa值]
2021-12-23 14:08:28.560  INFO 14224 --- [nio-7777-exec-2] ShardingSphere-SQL                       : Actual SQL: db2 ::: insert into system_config (config_key, config_value) VALUES (?, ?) ::: [aaa, aaa值]
2021-12-23 14:08:28.560  INFO 14224 --- [nio-7777-exec-2] ShardingSphere-SQL                       : Actual SQL: db3 ::: insert into system_config (config_key, config_value) VALUES (?, ?) ::: [aaa, aaa值]

查看三个表均新增成功：

原文链接：https://blog.csdn.net/w_t_y_y/article/details/122085660

你可能也喜欢