数据库分库分表（四）shardingjdbc水平分表demo

已于 2023-07-27 10:31:20 修改
阅读量1.7k 收藏 6
点赞数 1
分类专栏： # ORM框架Mybatis 文章标签： 数据库 eureka java
于 2021-12-16 17:00:37 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/w_t_y_y/article/details/121978284
版权

 一、行表达式分片策略&自增长主键分表：代码示例：

数据库/表创建：system_config只有一张，t_user表进行分表，分别建了t_user_1、t_user_2表、t_user_3表（3表实际并无数据，主要是验证SQL的路由情况）

CREATE TABLE `system_config` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `config_key` varchar(255) DEFAULT NULL,
  `cocnfig_value` varchar(255) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


CREATE TABLE `t_user_1` (
  `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;


CREATE TABLE `t_user_2` (
  `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;


CREATE TABLE `t_user_3` (
  `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;

 1、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-1</artifactId>
    <version>1.0-SNAPSHOT</version>
    <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>


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

2、application.properties：

server.context-path=/sharding
server.port=9999
#mybatis xml目录配置
mybatis.mapper-locations=classpath*:Mapper/*Mapper.xml


# 配置Sharding-JDBC的分片策略
# 配置数据源，此处可配置多数据源
spring.shardingsphere.datasource.names=db1
# 配置数据源具体内容————————包含  连接池，  驱动，             地址，   用户名，    密码
# 由于上面配置数据源只有db1因此下面只配置db1.type,db1.driver-class-name,db1.url,db1.username,db1.password
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?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=******

# 配置表的分布，表的策略
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=db1.t_user_$->{1..3}

# 指定user表 主键id 生成策略为 SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

# 指定分片策略 约定id值是偶数添加到user_1表，如果id是奇数添加到user_2表
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}

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
#
#spring.main.allow-bean-definition-overriding=true

3、controller

（1）

package com.demo.controller;

import com.demo.model.SystemConfigDTO;
import com.demo.service.SystemConfigService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RequestMapping("/system")
@RestController
public class SystemConfigController {

    @Autowired
    private SystemConfigService systemConfigService;

    @RequestMapping("/add")
    public void add(String key,String value){
        SystemConfigDTO systemConfigDTO = new SystemConfigDTO();
        systemConfigDTO.setConfigKey(key);
        systemConfigDTO.setConfigValue(value);
        systemConfigService.add(systemConfigDTO);
    }
}

（2）

package com.demo.controller;

import com.demo.model.UserDTO;
import com.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.math.BigInteger;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @RequestMapping("/add")
    public void add(String name,Integer age){
        UserDTO userDTO = new UserDTO();
        userDTO.setAge(age);
        userDTO.setUserName(name);
        userService.add(userDTO);
    }

    @RequestMapping("/updateById")
    public void updateById(Long id){
        userService.updateById(id);
    }

    @RequestMapping("/getById")
    public UserDTO getById(Long id){
        UserDTO userDTO = userService.getById(id);
        return userDTO;
    }

    @RequestMapping("/deleteById")
    public void deleteById(Long id){
        userService.deleteById(id);
    }
}

4、service：

package com.demo.service.impl;
import com.demo.mapper.UserMapper;
import com.demo.model.UserDTO;
import com.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

@Service("userService")
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public void add(UserDTO userDTO) {
        userMapper.add(userDTO);
    }

    @Override
    public UserDTO getById(Long id) {
        return userMapper.getById(id);
    }

    @Override
    public void updateById(Long id) {
        userMapper.updateById(id);
    }

    @Override
    public void deleteById(Long id) {
        userMapper.deleteById(id);
    }


}

package com.demo.service.impl;

import com.demo.mapper.SystemConfigMapper;
import com.demo.model.SystemConfigDTO;
import com.demo.service.SystemConfigService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service("systemConfigService")
public class SystemConfigServiceImpl implements SystemConfigService {

    @Autowired
    private SystemConfigMapper systemConfigMapper;

    @Override
    public void add(SystemConfigDTO systemConfigDTO) {
        systemConfigMapper.add(systemConfigDTO);
    }
}

5、mapper.xml:
（1）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.mapper.UserMapper">

    <insert id="add" parameterType="com.demo.model.UserDTO">
        insert into t_user(user_name,age) values (#{userName},#{age})
    </insert>

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

    <update id="updateById">
        update   t_user set is_delete = 1 where id = #{id}
    </update>

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

（2）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.mapper.SystemConfigMapper">

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

6、启动类：

package com.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.ImportResource;
@MapperScan("com.demo.mapper")
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
public class DemoStart {

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

7、测试：简单的增删改查均可以精确路由

（1）新增分表：

    多次访问localhost:9999/sharding/user/add?name=张三1&age=1

    后台日志打印：

2021-12-21 14:43:50.053  INFO 15172 --- [nio-9999-exec-4] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-21 14:43:50.053  INFO 15172 --- [nio-9999-exec-4] ShardingSphere-SQL                       : Logic SQL: insert into t_user(user_name,age) values (?,?)
2021-12-21 14:43:50.053  INFO 15172 --- [nio-9999-exec-4] 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@60a1b006, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@76391fad])])
2021-12-21 14:43:50.053  INFO 15172 --- [nio-9999-exec-4] ShardingSphere-SQL                       : Actual SQL: db1 ::: insert into t_user_2 (user_name, age, id) VALUES (?, ?, ?) ::: [张三1, 1, 680062060024823809]
2021-12-21 14:43:50.963  INFO 15172 --- [nio-9999-exec-5] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-21 14:43:50.964  INFO 15172 --- [nio-9999-exec-5] ShardingSphere-SQL                       : Logic SQL: insert into t_user(user_name,age) values (?,?)
2021-12-21 14:43:50.964  INFO 15172 --- [nio-9999-exec-5] 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@60a1b006, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@76391fad])])
2021-12-21 14:43:50.964  INFO 15172 --- [nio-9999-exec-5] ShardingSphere-SQL                       : Actual SQL: db1 ::: insert into t_user_1 (user_name, age, id) VALUES (?, ?, ?) ::: [张三1, 1, 680062063845834752]

 验证表：id为偶数的插入为t_user_1表，id为奇数的插入在t_user_2表

 （2）查询分表：根据分片键查询

    多次访问localhost:9999/sharding/user/getById?id=

    

 后台日志打印：可以看到，根据id的奇、偶数进行了分表查询

 （3）更新分表：

  访问localhost:9999/sharding/user/updateById?id=

 后台日志打印：分表更新成功

（4）删除分表：

访问localhost:9999/sharding/user/deleteById?id=

后台打印日志：

（5）由于system_config表没有分表，所以只插入一张表

访问localhost:9999/sharding/system/add?key=aaa&value=aaa值

  

 8、以上是简单的增删改查分表，下面看下复杂的SQL 

（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:9999/sharding/user/batchInsert

后台日志打印：

2021-12-21 14:46:30.101  INFO 120 --- [nio-9999-exec-2] o.a.c.c.C.[.[localhost].[/sharding]      : Initializing Spring FrameworkServlet 'dispatcherServlet'
2021-12-21 14:46:30.101  INFO 120 --- [nio-9999-exec-2] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization started
2021-12-21 14:46:30.135  INFO 120 --- [nio-9999-exec-2] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization completed in 34 ms
2021-12-21 14:46:30.899  INFO 120 --- [nio-9999-exec-2] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-21 14:46:30.901  INFO 120 --- [nio-9999-exec-2] ShardingSphere-SQL                       : Logic SQL: insert into t_user(user_name,age) values
           
             (?,?)
          , 
             (?,?)
          , 
             (?,?)
          , 
             (?,?)
          , 
             (?,?)
          , 
             (?,?)
2021-12-21 14:46:30.902  INFO 120 --- [nio-9999-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=[]), AndCondition(conditions=[]), AndCondition(conditions=[]), AndCondition(conditions=[]), AndCondition(conditions=[]), AndCondition(conditions=[])])), 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@88aa0b, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@5cf7c4ad]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@519aeae3, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@2eed55a5]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6bf07a81, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@9c019fe]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@f0b27d7, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@4b0f1bee]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@2343447c, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@2a45a603]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@7c260903, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@4fc5bd02])])
2021-12-21 14:46:30.903  INFO 120 --- [nio-9999-exec-2] ShardingSphere-SQL                       : Actual SQL: db1 ::: insert into t_user_2 (user_name, age, id) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) ::: [张三0, 0, 680062734330494977, 张三2, 2, 680062734330494979, 张三4, 4, 680062734330494981]
2021-12-21 14:46:30.903  INFO 120 --- [nio-9999-exec-2] ShardingSphere-SQL                       : Actual SQL: db1 ::: insert into t_user_1 (user_name, age, id) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) ::: [张三1, 1, 680062734330494978, 张三3, 3, 680062734330494980, 张三5, 5, 680062734330494982]

查看数据表，分表插入成功

 

 （2）in查询：分片策略内所有分表路由（t_user_1、t_user_2）

@Override
    public List<UserDTO> inQuery(String ids) {
        String[] idArrays = ids.split(",");
        List<Long> idList = new ArrayList<>();
        for(int i=0;i<idArrays.length;i++){
            idList.add(Long.valueOf(idArrays[i]));
        }
        List<UserDTO> users = userMapper.inQuery(idList);
        return users;
    }
<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>
    </select>

 访问localhost:9999/sharding/user/inQuery?ids=680062734330494978,680108452814520325,680344683552964613,680108452814520322

返回

 后台日志打印：

2021-12-22 09:28:24.396  INFO 13440 --- [nio-9999-exec-1] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 09:28:24.396  INFO 13440 --- [nio-9999-exec-1] ShardingSphere-SQL                       : Logic SQL: select id,user_name userName,age from t_user where id in
         (  
            ?
         , 
            ?
         , 
            ?
         , 
            ?
         )
2021-12-22 09:28:24.396  INFO 13440 --- [nio-9999-exec-1] 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=IN, compareOperator=null, positionValueMap={}, positionIndexMap={0=0, 1=1, 2=2, 3=3})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=4, logicSQL=select id,user_name userName,age from t_user where id in
         (  
            ?
         , 
            ?
         , 
            ?
         , 
            ?
         ))), 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 09:28:24.396  INFO 13440 --- [nio-9999-exec-1] ShardingSphere-SQL                       : Actual SQL: db1 ::: select id,user_name userName,age from t_user_1 where id in
         (  
            ?
         , 
            ?
         , 
            ?
         , 
            ?
         ) ::: [680062734330494978, 680108452814520325, 680344683552964613, 680108452814520322]
2021-12-22 09:28:24.396  INFO 13440 --- [nio-9999-exec-1] ShardingSphere-SQL                       : Actual SQL: db1 ::: select id,user_name userName,age from t_user_2 where id in
         (  
            ?
         , 
            ?
         , 
            ?
         , 
            ?
         ) ::: [680062734330494978, 680108452814520325, 680344683552964613, 680108452814520322]

（3） or查询：分片策略内所有分表路由（t_user_1、t_user_2）

 @Override
    public List<UserDTO> orQuery(Long id1, Long id2) {
        return userMapper.orQuery(id1,id2);
    }
<select id="orQuery" resultType="com.demo.model.UserDTO">
        select id,user_name userName,age from t_user where
        id = #{id1} or id = #{id2}
    </select>

访问localhost:9999/sharding/user/orQuery?id1=680344284708208640&id2=680108452814520321 返回

 

 后台日志打印

2021-12-22 09:33:10.214  INFO 13440 --- [nio-9999-exec-6] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 09:33:10.214  INFO 13440 --- [nio-9999-exec-6] ShardingSphere-SQL                       : Logic SQL: select id,user_name userName,age from t_user where
        id = ? or id = ?
2021-12-22 09:33:10.214  INFO 13440 --- [nio-9999-exec-6] 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})]), AndCondition(conditions=[Condition(column=Column(name=id, 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 = ? or 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 09:33:10.214  INFO 13440 --- [nio-9999-exec-6] ShardingSphere-SQL                       : Actual SQL: db1 ::: select id,user_name userName,age from t_user_1 where
        id = ? or id = ? ::: [680344284708208640, 680108452814520321]
2021-12-22 09:33:10.214  INFO 13440 --- [nio-9999-exec-6] ShardingSphere-SQL                       : Actual SQL: db1 ::: select id,user_name userName,age from t_user_2 where
        id = ? or id = ? ::: [680344284708208640, 680108452814520321]

（4） 分页查询：全表路由（t_user_1、t_user_2、t_user_3）

@RequestMapping("/getUserPager")
    public Pager<UserDTO> getPagerLog(int pageIndex, int pageSize){
        Pager<UserDTO> userPager = userService.getUserPager(new PageParam(pageIndex,pageSize));
        return userPager;
    }
 @Override
    public Pager<UserDTO> getUserPager(PageParam pageParam) {
        int totalCount = userMapper.getPagerCount();
        List<UserDTO> pagerList = new ArrayList<>();
        if(totalCount > 0){
            pagerList	= userMapper.getPagerList(pageParam.getStartIndex(),pageParam.getPageSize());
        }

        return new Pager<>(totalCount, pagerList);
    }
<select id="getPagerCount" resultType="int">
        select count(1) from t_user
    </select>

    <select id="getPagerList"  resultType="com.demo.model.UserDTO">
        select
        id,user_name userName,age
        from t_user
        order by id
        limit #{startIndex},#{pageSize}
    </select>

 访问localhost:9999/sharding/user/getUserPager?pageIndex=1&pageSize=5 返回

 

后台日志打印：可以看到是全路由查询

2021-12-22 09:35:03.032  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 09:35:03.032  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Logic SQL: select count(1) from t_user
2021-12-22 09:35:03.032  INFO 13440 --- [io-9999-exec-10] 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=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=0, logicSQL=select count(1) from t_user)), containStar=false, firstSelectItemStartIndex=7, selectListStopIndex=14, groupByLastIndex=0, items=[AggregationSelectItem(type=COUNT, innerExpression=(1), alias=Optional.absent(), derivedAggregationSelectItems=[], index=1)], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2021-12-22 09:35:03.032  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Actual SQL: db1 ::: select count(1) from t_user_1
2021-12-22 09:35:03.032  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Actual SQL: db1 ::: select count(1) from t_user_2
2021-12-22 09:35:03.032  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Actual SQL: db1 ::: select count(1) from t_user_3
2021-12-22 09:35:03.034  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 09:35:03.035  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Logic SQL: select
        id,user_name userName,age
        from t_user
        order by id
        limit ?,?
2021-12-22 09:35:03.035  INFO 13440 --- [io-9999-exec-10] 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=[])), 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
        order by id
        limit ?,?)), containStar=false, firstSelectItemStartIndex=15, selectListStopIndex=39, 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=[OrderItem(owner=Optional.absent(), name=Optional.of(id), orderDirection=ASC, nullOrderDirection=ASC, index=1, expression=null, alias=Optional.absent())], limit=Limit(offset=LimitValue(value=-1, index=0, boundOpened=false), rowCount=LimitValue(value=-1, index=1, boundOpened=false)), subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2021-12-22 09:35:03.035  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Actual SQL: db1 ::: select
        id,user_name userName,age
        from t_user_1
        order by id
        limit ?,? ::: [0, 5]
2021-12-22 09:35:03.035  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Actual SQL: db1 ::: select
        id,user_name userName,age
        from t_user_2
        order by id
        limit ?,? ::: [0, 5]
2021-12-22 09:35:03.035  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Actual SQL: db1 ::: select
        id,user_name userName,age
        from t_user_3
        order by id
        limit ?,? ::: [0, 5]

 （5）符号查询：全表路由（t_user_1、t_user_2、t_user_3）

@Override
    public List<UserDTO> getMinAndMax(Long minId, Long maxId) {
        return userMapper.getMinAndMax(minId,maxId);
    }
<select id="getMinAndMax"  resultType="com.demo.model.UserDTO">
        select
        id,user_name userName,age
        from t_user
        where id &gt;= #{minId}
        and id &lt; #{maxId}
    </select>

 访问localhost:9999/sharding/user/getMinAndMaxUsers?minId=680344683552964611&maxId=680344683552964614 返回

后台日志打印：全路由

2021-12-22 09:38:34.277  INFO 13440 --- [nio-9999-exec-7] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 09:38:34.277  INFO 13440 --- [nio-9999-exec-7] ShardingSphere-SQL                       : Logic SQL: select
        id,user_name userName,age
        from t_user
        where id >= ?
        and id < ?
2021-12-22 09:38:34.277  INFO 13440 --- [nio-9999-exec-7] 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=[])), 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 id < ?)), containStar=false, firstSelectItemStartIndex=15, selectListStopIndex=39, 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 09:38:34.278  INFO 13440 --- [nio-9999-exec-7] ShardingSphere-SQL                       : Actual SQL: db1 ::: select
        id,user_name userName,age
        from t_user_1
        where id >= ?
        and id < ? ::: [680344683552964611, 680344683552964614]
2021-12-22 09:38:34.278  INFO 13440 --- [nio-9999-exec-7] ShardingSphere-SQL                       : Actual SQL: db1 ::: select
        id,user_name userName,age
        from t_user_2
        where id >= ?
        and id < ? ::: [680344683552964611, 680344683552964614]
2021-12-22 09:38:34.279  INFO 13440 --- [nio-9999-exec-7] ShardingSphere-SQL                       : Actual SQL: db1 ::: select
        id,user_name userName,age
        from t_user_3
        where id >= ?
        and id < ? ::: [680344683552964611, 680344683552964614]

 （6）between and 查询：实测未支持，报错：

（7）union（all）查询：精确分表查询

<select id="unionQuery"  resultType="com.demo.model.UserDTO">
        select
        id,user_name userName,age
        from t_user
        where id = #{id1}
        union all
        select
        id,user_name userName,age
        from t_user
        where id = #{id2}
    </select>

访问

localhost:9999/sharding/user/unionQueryid1=678603591103021058&id2=678603591103021062 

后台日志打印：

2021-12-17 15:26:27.701  INFO 12884 --- [nio-9999-exec-2] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-17 15:26:27.702  INFO 12884 --- [nio-9999-exec-2] ShardingSphere-SQL                       : Logic SQL: select
        id,user_name userName,age
        from t_user
        where id = ?
        union all
        select
        id,user_name userName,age
        from t_user
        where id = ?
2021-12-17 15:26:27.702  INFO 12884 --- [nio-9999-exec-2] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_user, alias=Optional.absent()), 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), TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=2, logicSQL=select
        id,user_name userName,age
        from t_user
        where id = ?
        union all
        select
        id,user_name userName,age
        from t_user
        where id = ?)), containStar=false, firstSelectItemStartIndex=15, selectListStopIndex=39, 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-17 15:26:27.702  INFO 12884 --- [nio-9999-exec-2] ShardingSphere-SQL                       : Actual SQL: db1 ::: select
        id,user_name userName,age
        from t_user_1
        where id = ?
        union all
        select
        id,user_name userName,age
        from t_user_1
        where id = ? ::: [678603591103021058, 678603591103021062]

 （8）聚合查询：全表路由（t_user_1、t_user_2、t_user_3）

 <select id="getMaxUserId" resultType="long">
        select
        max(id)
        from t_user
    </select>

访问localhost:9999/sharding/user/getMaxUserId返回 678611824060399622

后台日志打印：全路由

2021-12-22 09:42:34.700  INFO 13440 --- [nio-9999-exec-3] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 09:42:34.700  INFO 13440 --- [nio-9999-exec-3] ShardingSphere-SQL                       : Logic SQL: select
        max(id)
        from t_user
2021-12-22 09:42:34.700  INFO 13440 --- [nio-9999-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=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=0, logicSQL=select
        max(id)
        from t_user)), containStar=false, firstSelectItemStartIndex=15, selectListStopIndex=21, groupByLastIndex=0, items=[AggregationSelectItem(type=MAX, innerExpression=(id), alias=Optional.absent(), derivedAggregationSelectItems=[], index=-1)], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2021-12-22 09:42:34.700  INFO 13440 --- [nio-9999-exec-3] ShardingSphere-SQL                       : Actual SQL: db1 ::: select
        max(id)
        from t_user_1
2021-12-22 09:42:34.700  INFO 13440 --- [nio-9999-exec-3] ShardingSphere-SQL                       : Actual SQL: db1 ::: select
        max(id)
        from t_user_2
2021-12-22 09:42:34.700  INFO 13440 --- [nio-9999-exec-3] ShardingSphere-SQL                       : Actual SQL: db1 ::: select
        max(id)
        from t_user_3

（9）join查询：在system_config表插入一条数据，精确分表

<select id="idJoinQuery"  resultType="com.demo.model.UserDTO">
        select
        t.id,user_name userName,age
        from t_user t left join system_config s on t.id = s.cocnfig_value
        where t.id = #{id}
    </select>

访问localhost:9999/sharding/user/idJoinQuery?id=680062734330494980，返回

 后台日志打印：根据id分片，如果where的查询条件是非分片键，则会执行全表路由

2021-12-22 11:01:06.394  INFO 17956 --- [nio-9999-exec-5] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 11:01:06.394  INFO 17956 --- [nio-9999-exec-5] ShardingSphere-SQL                       : Logic SQL: select
        t.id,user_name userName,age
        from t_user t left join system_config s on t.id = s.cocnfig_value
        where t.id = ?
2021-12-22 11:01:06.394  INFO 17956 --- [nio-9999-exec-5] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_user, alias=Optional.of(t)), Table(name=system_config, alias=Optional.of(s))]), 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), TableToken(tableName=system_config, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=select
        t.id,user_name userName,age
        from t_user t left join system_config s on t.id = s.cocnfig_value
        where t.id = ?)), containStar=false, firstSelectItemStartIndex=15, selectListStopIndex=41, groupByLastIndex=0, items=[CommonSelectItem(expression=t.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 11:01:06.394  INFO 17956 --- [nio-9999-exec-5] ShardingSphere-SQL                       : Actual SQL: db1 ::: select
        t.id,user_name userName,age
        from t_user_1 t left join system_config s on t.id = s.cocnfig_value
        where t.id = ? ::: [680062734330494980]

二、标准分片策略：

建立2021~2021年各个月订单月表，总共24张表，表结构都一样的，如第一个：

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

接着第一个demo

1、application.properties新增分表策略：

#根据日期分表
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=db1.t_order_$->{2020..2021}_$->{1..12}
#不需要无重复的自增长主键，注释
#spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
#spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.preciseAlgorithmClassName=com.demo.config.OrderPreciseShardingConfig
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.rangeAlgorithmClassName=com.demo.config.OrderRangeShardingConfig
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 org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import org.springframework.stereotype.Component;

import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;

public class OrderPreciseShardingConfig implements PreciseShardingAlgorithm<Date>{

    //精准
    @Override
    public String doSharding(Collection<String> collection,
                             PreciseShardingValue<Date> preciseShardingValue) {

        //preciseShardingValue就是当前插入的字段值
        //collection 内就是所有的逻辑表
        //获取字段值
        Date value = preciseShardingValue.getValue();
        System.out.println("value..."+value);
        if(value == null){
            throw new UnsupportedOperationException("prec is null");
        }
        SimpleDateFormat format = new SimpleDateFormat("yyyy_M");
        String tableTailName=  "t_order_"+format.format(value);
        System.out.println("tableTailName.."+tableTailName);
        for (String tableName : collection) {//循环表名已确定使用哪张表
            if (tableName.equals(tableTailName)){  //表示相等就返回
                System.out.println("表名为"+tableName);
                return tableName;
            }
        }
        return null;
    }
}

（2）范围分片策略：

package com.demo.config;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
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 OrderRangeShardingConfig 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("yyyy_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、订单controller：

package com.demo.controller;

import com.demo.model.OrderDTO;
import com.demo.model.SystemConfigDTO;
import com.demo.model.UserDTO;
import com.demo.service.OrderService;
import com.demo.service.SystemConfigService;
import com.demo.service.UserService;
import com.demo.vo.PageParam;
import com.demo.vo.Pager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

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

@RequestMapping("/order")
@RestController
public class OrderController {

    @Autowired
    private OrderService orderService;

    @Autowired
    private UserService userService;

    @RequestMapping("/add")
    public void add(@RequestBody OrderDTO orderDTO){
        orderService.add(orderDTO);
    }

    @RequestMapping("/batchInsert")
    public void batchInsert(@RequestBody List<OrderDTO> orderDTOs){
        orderService.batchInsert(orderDTOs);
    }

    //带符号查询 >=、<=
    @RequestMapping("/getMinAndMaxs")
    public List<OrderDTO> getMinAndMax(Date minDate, Date maxDate){
        List<OrderDTO> orderDTOS = orderService.getMinAndMax(minDate,maxDate);
        return orderDTOS;
    }

    //between and 查询
    @RequestMapping("/betweenAndQuery")
    public List<OrderDTO> betweenAndQuery(Date minDate, Date maxDate){
        List<OrderDTO> orderDTOS = orderService.betweenAndQuery(minDate,maxDate);
        for(OrderDTO orderDTO : orderDTOS){
            SimpleDateFormat format = new SimpleDateFormat("yyyy_M");
            orderDTO.setOrderDateStr(format.format(orderDTO.getOrderDate()));
        }
        return orderDTOS;
    }
}

4、订单服务：

package com.demo.service.impl;

import com.demo.mapper.OrderMapper;
import com.demo.model.OrderDTO;
import com.demo.model.UserDTO;
import com.demo.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.Date;
import java.util.List;

@Service("orderService")
public class OrderServiceImpl implements OrderService {

    @Autowired
    private OrderMapper orderMapper;

    @Override
    public void add(OrderDTO orderDTO) {
        orderMapper.add(orderDTO);
    }

    @Override
    public List<OrderDTO> getMinAndMax(Date minDate, Date maxDate) {
        return orderMapper.getMinAndMax(minDate,maxDate);
    }

    @Override
    public List<OrderDTO> betweenAndQuery(Date minDate, Date maxDate) {
        return orderMapper.betweenAndQuery(minDate,maxDate);
    }

    @Override
    public void batchInsert(List<OrderDTO> orderDTOs) {
         orderMapper.batchInsert(orderDTOs);
    }
}

5、sql：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.mapper.OrderMapper">

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

    <select id="getMinAndMax" resultType="com.demo.model.OrderDTO">
       select id,order_number orderNumber,order_date orderDate
       from t_order where  order_date &gt;= #{minDate}
        and order_date &lt; #{maxDate}
    </select>

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

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

</mapper>

6、测试：简单看几个

（1）批量新增：精确分片

后台日志打印

value...2021-11-21 00:00:10.0
tableTailName..t_order_2021_11
collection..[t_order_2020_1, t_order_2020_2, t_order_2020_3, t_order_2020_4, t_order_2020_5, t_order_2020_6, t_order_2020_7, t_order_2020_8, t_order_2020_9, t_order_2020_10, t_order_2020_11, t_order_2020_12, t_order_2021_1, t_order_2021_2, t_order_2021_3, t_order_2021_4, t_order_2021_5, t_order_2021_6, t_order_2021_7, t_order_2021_8, t_order_2021_9, t_order_2021_10, t_order_2021_11, t_order_2021_12]
表名为t_order_2021_11
value...2020-10-21 00:00:10.0
tableTailName..t_order_2020_10
collection..[t_order_2020_1, t_order_2020_2, t_order_2020_3, t_order_2020_4, t_order_2020_5, t_order_2020_6, t_order_2020_7, t_order_2020_8, t_order_2020_9, t_order_2020_10, t_order_2020_11, t_order_2020_12, t_order_2021_1, t_order_2021_2, t_order_2021_3, t_order_2021_4, t_order_2021_5, t_order_2021_6, t_order_2021_7, t_order_2021_8, t_order_2021_9, t_order_2021_10, t_order_2021_11, t_order_2021_12]
表名为t_order_2020_10
value...2020-09-21 00:00:10.0
tableTailName..t_order_2020_9
collection..[t_order_2020_1, t_order_2020_2, t_order_2020_3, t_order_2020_4, t_order_2020_5, t_order_2020_6, t_order_2020_7, t_order_2020_8, t_order_2020_9, t_order_2020_10, t_order_2020_11, t_order_2020_12, t_order_2021_1, t_order_2021_2, t_order_2021_3, t_order_2021_4, t_order_2021_5, t_order_2021_6, t_order_2021_7, t_order_2021_8, t_order_2021_9, t_order_2021_10, t_order_2021_11, t_order_2021_12]
表名为t_order_2020_9
2021-12-22 10:03:54.872  INFO 13440 --- [nio-9999-exec-9] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 10:03:54.873  INFO 13440 --- [nio-9999-exec-9] ShardingSphere-SQL                       : Logic SQL: insert into t_order(order_number,order_date) values
          
            (?,?)
         , 
            (?,?)
         , 
            (?,?)
2021-12-22 10:03:54.873  INFO 13440 --- [nio-9999-exec-9] 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@2863b499, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@63be5b80]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@75caae32, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@328ad3ee]), InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@7787e5a, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@160b576e])])
2021-12-22 10:03:54.873  INFO 13440 --- [nio-9999-exec-9] ShardingSphere-SQL                       : Actual SQL: db1 ::: insert into t_order_2021_11 (order_number, order_date) VALUES (?, ?) ::: [aaa, 2021-11-21 00:00:10.0]
2021-12-22 10:03:54.873  INFO 13440 --- [nio-9999-exec-9] ShardingSphere-SQL                       : Actual SQL: db1 ::: insert into t_order_2020_10 (order_number, order_date) VALUES (?, ?) ::: [bbb, 2020-10-21 00:00:10.0]
2021-12-22 10:03:54.873  INFO 13440 --- [nio-9999-exec-9] ShardingSphere-SQL                       : Actual SQL: db1 ::: insert into t_order_2020_9 (order_number, order_date) VALUES (?, ?) ::: [ccc, 2020-09-21 00:00:10.0]

 （2）between and：精确分片

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

 后台日志打印：

2021-12-22 10:04:45.392  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 10:04:45.392  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Logic SQL: select id,order_number orderNumber,order_date orderDate
       from t_order where  order_date between ?
        and  ?
2021-12-22 10:04:45.392  INFO 13440 --- [io-9999-exec-10] 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-22 10:04:45.393  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Actual SQL: db1 ::: select id,order_number orderNumber,order_date orderDate
       from t_order_2020_9 where  order_date between ?
        and  ? ::: [2020-09-20 00:00:10.0, 2021-12-22 00:00:10.0]
2021-12-22 10:04:45.393  INFO 13440 --- [io-9999-exec-10] ShardingSphere-SQL                       : Actual SQL: db1 ::: select id,order_number orderNumber,order_date orderDate
       from t_order_2021_12 where  order_date between ?
        and  ? ::: [2020-09-20 00:00:10.0, 2021-12-22 00:00:10.0]

（3）join：如在所有order表加上user_id字段，

 <select id="joinQuery"  resultType="com.demo.model.OrderDTO">
      select tu.id,order_number orderNumber,order_date orderDate
      from t_order tod left join t_user tu on tod.user_id = tu.id
      where tu.id = #{userId}
      and tod.order_date = #{date}
    </select>

该SQL的where条件，均为两个表的分片键，则可以精确到具体的表

value...2021-10-21 00:00:10.0
tableTailName..t_order_2021_10
表名为t_order_2021_10
2021-12-22 11:31:08.706  INFO 17132 --- [nio-9999-exec-1] ShardingSphere-SQL                       : Rule Type: sharding
2021-12-22 11:31:08.706  INFO 17132 --- [nio-9999-exec-1] ShardingSphere-SQL                       : Logic SQL: select tu.id,order_number orderNumber,order_date orderDate
      from t_order tod left join t_user tu on tod.user_id = tu.id
      where tu.id = ?
      and tod.order_date = ?
2021-12-22 11:31:08.706  INFO 17132 --- [nio-9999-exec-1] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=t_order, alias=Optional.of(tod)), Table(name=t_user, alias=Optional.of(tu))]), 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=order_date, tableName=t_order), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=t_order, quoteCharacter=NONE, schemaNameLength=0), TableToken(tableName=t_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=2, logicSQL=select tu.id,order_number orderNumber,order_date orderDate
      from t_order tod left join t_user tu on tod.user_id = tu.id
      where tu.id = ?
      and tod.order_date = ?)), containStar=false, firstSelectItemStartIndex=7, selectListStopIndex=57, groupByLastIndex=0, items=[CommonSelectItem(expression=tu.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-22 11:31:08.706  INFO 17132 --- [nio-9999-exec-1] ShardingSphere-SQL                       : Actual SQL: db1 ::: select tu.id,order_number orderNumber,order_date orderDate
      from t_order_2021_10 tod left join t_user_1 tu on tod.user_id = tu.id
      where tu.id = ?
      and tod.order_date = ? ::: [680062734330494978, 2021-10-21 00:00:10.0]

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

w_t_y_y

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值