专辑：分库分表ShardingSphere_5种分片策略：场景&案例详解-Java充电社【公众号：Java充电社】

由于库的分片策略和表的分片在代码上基本上是一样的，为了更简洁，下面所有案例，只演示在一个库中分表的情况，掌握分表策略之后，库的分片代码自然就会了。

> 表的分片策略调用`TableRuleConfiguration#setTableShardingStrategyConfig`进行设置，而库的分片策略调用`TableRuleConfiguration#setDatabaseShardingStrategyConfig`进行设置，都是类似的，一通则通。

本章案例所有代码

```java
https://gitee.com/javacode2018/shardingsphere-demo
```

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/411/c9965351-7fd5-4acb-84b1-60c41f57c914.png)

## 7.1、行表达式分片策略（InlineShardingStrategy）

### 7.1.1、适合的场景

- 对应InlineShardingStrategy类
- 只支持单字段分片
- 通过分片字段查询，只支持=和in，不支持`>=、<=、>、<、BETWEEN`范围操作，否则报错
- 使用Groovy的表达式，对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: `t_user_$->{u_id % 8}` 表示t_user表根据u_id模8，而分成8张表，表名称为`t_user_0`到`t_user_7`。

### 7.1.2、案例

#### 1）需求

ds_iss库中含有2个用户表：t_user_0、t_user_1，t_user_0存放id为偶数的用户，另外一张表存放id为基数的用户。

#### 2）sql脚本

```sql
drop database if exists ds_iss;
create database ds_iss;
use ds_iss;

drop table if exists t_user_0;
create table t_user_0(
	id bigint not null primary key,
	name varchar(64) not null
);

drop table if exists t_user_1;
create table t_user_1(
	id bigint not null primary key,
	name varchar(64) not null
);
```

#### 3）创建测试类

> 关键代码都在下面的init方法中，重点关注InlineShardingStrategyConfiguration的配置。

```java
public class InlineShardingStrategyTest {

private static DataSource dataSource;

@BeforeAll
    public static void init() throws SQLException {
        HikariDataSource ds = new HikariDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setJdbcUrl("jdbc:mysql://localhost:3306/ds_sss?characterEncoding=UTF-8");
        ds.setUsername("root");
        ds.setPassword("root123");

/**
         * 1.配置真实数据源
         */
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("ds", ds);

/**
         * 2、配置t_user分片规则
         */
        TableRuleConfiguration userRuleConfiguration = new TableRuleConfiguration("t_user", "ds.t_user_$->{0..1}");
        //设置t_user表的分片规则
        final InlineShardingStrategyConfiguration userTableShardingStrategy = new InlineShardingStrategyConfiguration("id", "t_user_$->{id % 2}");
        userRuleConfiguration.setTableShardingStrategyConfig(userTableShardingStrategy);

/**
         * 3、加入表的分片规则
         */
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(userRuleConfiguration);

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);
    }

@Test
    public void test1() throws SQLException {
        String sql = "insert t_user (id,name) value (?,?)";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);) {
            for (long id = 1; id <= 4; id++) {
                int parameterIndex = 1;
                ps.setLong(parameterIndex++, id);
                ps.setString(parameterIndex++, "路人-" + id);
                ps.executeUpdate();
            }
        }
    }

}
```

下面来上测试案例，所有测试案例代码都位于上面这个测试类中。

#### 4）测试1：插入4条数据，看sql路由情况

先把上面的sql脚本执行一遍，清下数据，然后执行下面代码

```java
@Test
public void test1() throws SQLException {
    String sql = "insert t_user (id,name) value (?,?)";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);) {
        for (long id = 1; id <= 4; id++) {
            int parameterIndex = 1;
            ps.setLong(parameterIndex++, id);
            ps.setString(parameterIndex++, "路人-" + id);
            ps.executeUpdate();
        }
    }
}
```

输出，

```sql
Logic SQL: insert t_user (id,name) value (?,?)
Actual SQL: ds ::: insert t_user_1 (id,name) value (?, ?) ::: [1, 路人-1]
Logic SQL: insert t_user (id,name) value (?,?)
Actual SQL: ds ::: insert t_user_0 (id,name) value (?, ?) ::: [2, 路人-2]
Logic SQL: insert t_user (id,name) value (?,?)
Actual SQL: ds ::: insert t_user_1 (id,name) value (?, ?) ::: [3, 路人-3]
Logic SQL: insert t_user (id,name) value (?,?)
Actual SQL: ds ::: insert t_user_0 (id,name) value (?, ?) ::: [4, 路人-4]
```

#### 5）测试2：批量插入数据，看sql路由情况

> 验证`insert t_user (id,name) value (?,?), (?,?), (?,?), (?,?)`插入效果。

先把上面的sql脚本执行一遍，清下数据，然后执行下面代码

```java
@Test
public void test2() throws SQLException {
    String sql = "insert t_user (id,name) value (?,?), (?,?), (?,?), (?,?)";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);) {
        int parameterIndex = 1;
        for (long id = 1; id <= 4; id++) {
            ps.setLong(parameterIndex++, id);
            ps.setString(parameterIndex++, "路人-" + id);
        }
        System.out.println("count:" + ps.executeUpdate());
    }
}
```

输出如下，注意看下，一条sql被拆分为2条sql，是不是很强大，count的值为4，成功插入4条记录

```sql
Logic SQL: insert t_user (id,name) value (?,?), (?,?), (?,?), (?,?)
Actual SQL: ds ::: insert t_user_1 (id,name) value (?, ?), (?, ?) ::: [1, 路人-1, 3, 路人-3]
Actual SQL: ds ::: insert t_user_0 (id,name) value (?, ?), (?, ?) ::: [2, 路人-2, 4, 路人-4]
count:4
```

#### 6）测试3：查询所有数据

```java
@Test
public void test3() throws SQLException {
    String sql = "select id,name from t_user";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);
         ResultSet rs = ps.executeQuery();) {
        while (rs.next()) {
            final long id = rs.getLong("id");
            final String name = rs.getString("name");
            System.out.println(String.format("id:%s,name:%s",id,name));
        }
    }
}
```

运行输出

```sql
Logic SQL: select id,name from t_user
Actual SQL: ds ::: select id,name from t_user_0
Actual SQL: ds ::: select id,name from t_user_1
id:2,name:路人-2
id:4,name:路人-4
id:1,name:路人-1
id:3,name:路人-3
```

#### 7）测试4：查询id为1的用户

```java
@Test
public void test4() throws SQLException {
    String sql = "select id,name from t_user where id = 1";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);
         ResultSet rs = ps.executeQuery();) {
        while (rs.next()) {
            final long id = rs.getLong("id");
            final String name = rs.getString("name");
            System.out.println(String.format("id:%s,name:%s",id,name));
        }
    }
}
```

运行输出

```sql
Logic SQL: select id,name from t_user where id = 1
Actual SQL: ds ::: select id,name from t_user_1 where id = 1
id:1,name:路人-1
```

#### 8）测试5：in 查询多个用户

```java
@Test
public void test5() throws SQLException {
    String sql = "select id,name from t_user where id in (1,2)";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);
         ResultSet rs = ps.executeQuery();) {
        while (rs.next()) {
            final long id = rs.getLong("id");
            final String name = rs.getString("name");
            System.out.println(String.format("id:%s,name:%s", id, name));
        }
    }
}
```

运行输出

```sql
Logic SQL: select id,name from t_user where id in (1,2)
Actual SQL: ds ::: select id,name from t_user_0 where id in (1,2)
Actual SQL: ds ::: select id,name from t_user_1 where id in (1,2)
id:2,name:路人-2
id:1,name:路人-1
```

#### 9）测试6：!=查询

> `!=,not in,<>`，这种类型的，由于分片规则不知道查询的数据具体在哪个库哪个表，所以会路由到所有表。

下面查询id不等于1的用户

```java
@Test
public void test6() throws SQLException {
    String sql = "select id,name from t_user where id != 1";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);
         ResultSet rs = ps.executeQuery();) {
        while (rs.next()) {
            final long id = rs.getLong("id");
            final String name = rs.getString("name");
            System.out.println(String.format("id:%s,name:%s", id, name));
        }
    }
}
```

运行输出

```sql
Logic SQL: select id,name from t_user where id != 1
Actual SQL: ds ::: select id,name from t_user_0 where id != 1
Actual SQL: ds ::: select id,name from t_user_1 where id != 1
id:2,name:路人-2
id:4,name:路人-4
id:3,name:路人-3
```

#### 10）测试7：不支持范围查询，报错

> InlineShardingStrategy策略不支持对分片字段采用`>=、<=、>、<、BETWEEN`查询

下面案例将报错

```java
@Test
public void test7() throws SQLException {
    String sql = "select id,name from t_user where id between 1 and 10";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);
         ResultSet rs = ps.executeQuery();) {
        while (rs.next()) {
            final long id = rs.getLong("id");
            final String name = rs.getString("name");
            System.out.println(String.format("id:%s,name:%s", id, name));
        }
    }
}
```

运行报错啦

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/411/1913a3c8-4fba-4ce6-a8c3-ed44e119a432.png)

### 7.1.3、InlineShardingStrategyTest完整代码

```java
package com.itsoku.shardingstrategy;

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;

@Slf4j
public class InlineShardingStrategyTest {

private static DataSource dataSource;

@BeforeAll
    public static void init() throws SQLException {
        HikariDataSource ds = new HikariDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setJdbcUrl("jdbc:mysql://localhost:3306/ds_iss?characterEncoding=UTF-8");
        ds.setUsername("root");
        ds.setPassword("root123");

/**
         * 1.配置真实数据源
         */
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("ds", ds);

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);
    }

@Test
    public void test2() throws SQLException {
        String sql = "insert t_user (id,name) value (?,?), (?,?), (?,?), (?,?)";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);) {
            int parameterIndex = 1;
            for (long id = 1; id <= 4; id++) {
                ps.setLong(parameterIndex++, id);
                ps.setString(parameterIndex++, "路人-" + id);
            }
            System.out.println("count:" + ps.executeUpdate());
        }
    }

@Test
    public void test3() throws SQLException {
        String sql = "select id,name from t_user";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                System.out.println(String.format("id:%s,name:%s", id, name));
            }
        }
    }

@Test
    public void test4() throws SQLException {
        String sql = "select id,name from t_user where id = 1";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                System.out.println(String.format("id:%s,name:%s", id, name));
            }
        }
    }

@Test
    public void test5() throws SQLException {
        String sql = "select id,name from t_user where id in (1,2)";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                System.out.println(String.format("id:%s,name:%s", id, name));
            }
        }
    }

@Test
    public void test6() throws SQLException {
        String sql = "select id,name from t_user where id != 1";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                System.out.println(String.format("id:%s,name:%s", id, name));
            }
        }
    }

@Test
    public void test7() throws SQLException {
        String sql = "select id,name from t_user where id between 1 and 10";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                System.out.println(String.format("id:%s,name:%s", id, name));
            }
        }
    }

}
```

## 7.2、标准分片策略（StandardShardingStrategy）

### 7.2.1、适合的场景

- 对应StandardShardingStrategy类

- 提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持

- StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法

- PreciseShardingAlgorithm是必选的，用于处理=和IN的分片

- RangeShardingAlgorithm是可选的，用于处理BETWEEN AND, >, <, >=, <=分片，如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理

### 7.2.2、案例

#### 1）需求

- ds_sss库中含有2个用户表：t_user_0、t_user_1
- 每个表有2个字段(id,name)，id>0

- t_user_0：存放id范围在[1,3]内的数据，为了测试方便，范围设置的比较小，重点在于能够掌握用法
- t_user_1：存放id位于[4,+∞)范围内的数据

#### 2）sql脚本

```sql
drop database if exists ds_sss;
create database ds_sss;
use ds_sss;

drop table if exists t_user_0;
create table t_user_0(
	id bigint not null primary key,
	name varchar(64) not null
);

drop table if exists t_user_1;
create table t_user_1(
	id bigint not null primary key,
	name varchar(64) not null
);
```

#### 3）创建测试类

代码如下，重点关注策略的配置。

```java
public class StandardShardingStrategyTest {
    private static DataSource dataSource;

/**
         * 1.配置真实数据源
         */
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("ds", ds);

//创建一个Map用来存放：id范围和表名映射关系，路由的时候会根据这个信息来找到目标表
        Map<Range<Comparable>, String> idRangeTableNameMap = new HashMap<>();
        idRangeTableNameMap.put(Range.closed(1, 3), "t_user_0");
        idRangeTableNameMap.put(Range.atLeast(4), "t_user_1");
        System.out.println(idRangeTableNameMap);

/**
         * 2、配置t_user分片规则
         */
        TableRuleConfiguration userRuleConfiguration = new TableRuleConfiguration("t_user", "ds.t_user_$->{0..1}");
        //设置 =,in 的算法策略
        PreciseShardingAlgorithm preciseShardingAlgorithm = new PreciseShardingAlgorithm() {
            @Override
            public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {
                for (Map.Entry<Range<Comparable>, String> idRangeTableNameEntity : idRangeTableNameMap.entrySet()) {
                    final Range<Comparable> idRange = idRangeTableNameEntity.getKey();
                    final String tableName = idRangeTableNameEntity.getValue();
                    final Comparable id = shardingValue.getValue();
                    if (idRange.contains(id)) {
                        System.out.println(String.format("准确路由,id：%s, tableName：%s", id, tableName));
                        return tableName;
                    }
                }
                return null;
            }
        };
        //设置 BETWEEN AND, >, <, >=, <= 范围算法策略
        RangeShardingAlgorithm rangeShardingAlgorithm = new RangeShardingAlgorithm() {
            @Override
            public Collection<String> doSharding(Collection availableTargetNames, RangeShardingValue shardingValue) {
                List<String> tableNameList = new ArrayList<>();
                for (Map.Entry<Range<Comparable>, String> idRangeTableNameEntity : idRangeTableNameMap.entrySet()) {
                    final Range<Comparable> idRange = idRangeTableNameEntity.getKey();
                    final String tableName = idRangeTableNameEntity.getValue();
                    final Range valueRange = shardingValue.getValueRange();
                    //判断2个区间是否有交集
                    if (idRange.isConnected(valueRange)) {
                        tableNameList.add(tableName);
                    }
                }
                System.out.println(String.format("范围路由,id：%s, tableNameList：%s", shardingValue, tableNameList));
                return tableNameList;
            }
        };
        //配置标注路由策略
        final StandardShardingStrategyConfiguration userTableShardingStrategy =
                new StandardShardingStrategyConfiguration("id",
                        preciseShardingAlgorithm,
                        rangeShardingAlgorithm);
        //设置表的路由策略
        userRuleConfiguration.setTableShardingStrategyConfig(userTableShardingStrategy);

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);
    }

}
```

#### 4）重点代码

上面中重点代码如下，重点在于分片策略StandardShardingStrategyConfiguration的配置，需要指定2个算法策略

- PreciseShardingAlgorithm：用来支持=、in的算法策略
- RangeShardingAlgorithm：用来支持BETWEEN AND, >, <, >=, <= 范围的算法策略

代码中用到了shardingsphere中为我们提供的一个表示区间的工具类：Range，其内部提供了很多方法来处理区间的问题，如：创建区间、判断区间是否有交集等等。

```java
//创建一个Map用来存放：id范围和表名映射关系，路由的时候会根据这个信息来找到目标表
Map<Range<Comparable>, String> idRangeTableNameMap = new HashMap<>();
idRangeTableNameMap.put(Range.closed(1, 3), "t_user_0");
idRangeTableNameMap.put(Range.atLeast(4), "t_user_1");
System.out.println(idRangeTableNameMap);

/**
 * 2、配置t_user分片规则
 */
TableRuleConfiguration userRuleConfiguration = new TableRuleConfiguration("t_user", "ds.t_user_$->{0..1}");
//设置 =,in 的算法策略
PreciseShardingAlgorithm preciseShardingAlgorithm = new PreciseShardingAlgorithm() {
    @Override
    public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {
        for (Map.Entry<Range<Comparable>, String> idRangeTableNameEntity : idRangeTableNameMap.entrySet()) {
            final Range<Comparable> idRange = idRangeTableNameEntity.getKey();
            final String tableName = idRangeTableNameEntity.getValue();
            final Comparable id = shardingValue.getValue();
            if (idRange.contains(id)) {
                System.out.println(String.format("准确路由,id：%s, tableName：%s", id, tableName));
                return tableName;
            }
        }
        return null;
    }
};
//设置 BETWEEN AND, >, <, >=, <= 范围算法策略
RangeShardingAlgorithm rangeShardingAlgorithm = new RangeShardingAlgorithm() {
    @Override
    public Collection<String> doSharding(Collection availableTargetNames, RangeShardingValue shardingValue) {
        List<String> tableNameList = new ArrayList<>();
        for (Map.Entry<Range<Comparable>, String> idRangeTableNameEntity : idRangeTableNameMap.entrySet()) {
            final Range<Comparable> idRange = idRangeTableNameEntity.getKey();
            final String tableName = idRangeTableNameEntity.getValue();
            final Range valueRange = shardingValue.getValueRange();
            //判断2个区间是否有交集
            if (idRange.isConnected(valueRange)) {
                tableNameList.add(tableName);
            }
        }
        System.out.println(String.format("范围路由,id：%s, tableNameList：%s", shardingValue, tableNameList));
        return tableNameList;
    }
};
//配置标注路由策略
final StandardShardingStrategyConfiguration userTableShardingStrategy =
        new StandardShardingStrategyConfiguration("id",
                preciseShardingAlgorithm,
                rangeShardingAlgorithm);
```

下面来8个测试案例，代码都位于上面StandardShardingStrategyTest类中

#### 5）测试1：插入4条数据，看sql路由情况

先把上面的sql脚本执行一遍，清下数据，然后执行下面代码

输出如下，注意看日志，前3个id位于[1,3]区间，被路由到t_user_0了，4倍路由到t_user_1了，强大

```sql
准确路由,id：1, tableName：t_user_0
Logic SQL: insert t_user (id,name) value (?,?)
Actual SQL: ds ::: insert t_user_0 (id,name) value (?, ?) ::: [1, 路人-1]

准确路由,id：2, tableName：t_user_0
Logic SQL: insert t_user (id,name) value (?,?)
Actual SQL: ds ::: insert t_user_0 (id,name) value (?, ?) ::: [2, 路人-2]

准确路由,id：3, tableName：t_user_0
Logic SQL: insert t_user (id,name) value (?,?)
Actual SQL: ds ::: insert t_user_0 (id,name) value (?, ?) ::: [3, 路人-3]

准确路由,id：4, tableName：t_user_1
Logic SQL: insert t_user (id,name) value (?,?)
Actual SQL: ds ::: insert t_user_1 (id,name) value (?, ?) ::: [4, 路人-4]
```

#### 6）测试2：批量插入数据，看sql路由情况

> 验证`insert t_user (id,name) value (?,?), (?,?), (?,?), (?,?)`插入效果。

先把上面的sql脚本执行一遍，清下数据，然后执行下面代码

输出如下，注意看代码和下面的日志，不多解释，路由很ok

```sql
准确路由,id：1, tableName：t_user_0
准确路由,id：2, tableName：t_user_0
准确路由,id：3, tableName：t_user_0
准确路由,id：4, tableName：t_user_1
Logic SQL: insert t_user (id,name) value (?,?), (?,?), (?,?), (?,?)
Actual SQL: ds ::: insert t_user_0 (id,name) value (?, ?), (?, ?), (?, ?) ::: [1, 路人-1, 2, 路人-2, 3, 路人-3]
Actual SQL: ds ::: insert t_user_1 (id,name) value (?, ?) ::: [4, 路人-4]
count:4
```

#### 7）测试3：查询所有数据

运行输出如下，全表查询，没有走路由算法，直接走所有的表

```sql
Logic SQL: select id,name from t_user
Actual SQL: ds ::: select id,name from t_user_0
Actual SQL: ds ::: select id,name from t_user_1
id:1,name:路人-1
id:2,name:路人-2
id:3,name:路人-3
id:4,name:路人-4
```

#### 8）测试4：查询id为1的用户

运行输出如下，路由ok

```sql
准确路由,id：1, tableName：t_user_0
Logic SQL: select id,name from t_user where id = 1
Actual SQL: ds ::: select id,name from t_user_0 where id = 1
id:1,name:路人-1
```

#### 9）测试5：in 查询多个用户

```java
@Test
public void test5() throws SQLException {
    String sql = "select id,name from t_user where id in (1,2,4)";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);
         ResultSet rs = ps.executeQuery();) {
        while (rs.next()) {
            final long id = rs.getLong("id");
            final String name = rs.getString("name");
            System.out.println(String.format("id:%s,name:%s", id, name));
        }
    }
}
```

运行输出如下，3条记录，被路由到了2个表，强啊

```sql
准确路由,id：1, tableName：t_user_0
准确路由,id：2, tableName：t_user_0
准确路由,id：4, tableName：t_user_1
Logic SQL: select id,name from t_user where id in (1,2,4)
Actual SQL: ds ::: select id,name from t_user_0 where id in (1,2,4)
Actual SQL: ds ::: select id,name from t_user_1 where id in (1,2,4)
id:1,name:路人-1
id:2,name:路人-2
id:4,name:路人-4
```

#### 10）测试6：!=查询

> `!=,not in,<>`，这种类型的，由于分片规则不知道查询的数据具体在哪个库哪个表，所以会路由到所有表。

下面查询id不等于1的用户

运行输出如下，被路由到了所有表

#### 11）测试7：支持范围查询

> InlineShardingStrategy策略不支持对分片字段采用`>=、<=、>、<、BETWEEN`查询

下面案例将报错

```java
@Test
public void test7() throws SQLException {
    String sql = "select id,name from t_user where id between 1 and 2";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);
         ResultSet rs = ps.executeQuery();) {
        while (rs.next()) {
            final long id = rs.getLong("id");
            final String name = rs.getString("name");
            System.out.println(String.format("id:%s,name:%s", id, name));
        }
    }
}
```

运行输出如下，走了范围路由，路由结果ok

```sql
范围路由,id：RangeShardingValue(logicTableName=t_user, columnName=id, valueRange=[1‥2]), tableNameList：[t_user_0]
Logic SQL: select id,name from t_user where id between 1 and 2
Actual SQL: ds ::: select id,name from t_user_0 where id between 1 and 2
id:1,name:路人-1
id:2,name:路人-2
```

#### 12）测试8：复合条件(between & or)

```java
@Test
public void test8() throws SQLException {
    String sql = "select id,name from t_user where id between 1 and 2 or id>=4";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);
         ResultSet rs = ps.executeQuery();) {
        while (rs.next()) {
            final long id = rs.getLong("id");
            final String name = rs.getString("name");
            System.out.println(String.format("id:%s,name:%s", id, name));
        }
    }
}
```

运行输出

```java
范围路由,id：RangeShardingValue(logicTableName=t_user, columnName=id, valueRange=[1‥2]), tableNameList：[t_user_0]
范围路由,id：RangeShardingValue(logicTableName=t_user, columnName=id, valueRange=[4‥+∞)), tableNameList：[t_user_1]
Logic SQL: select id,name from t_user where id between 1 and 2 or id>=4
Actual SQL: ds ::: select id,name from t_user_0 where id between 1 and 2 or id>=4
Actual SQL: ds ::: select id,name from t_user_1 where id between 1 and 2 or id>=4
id:1,name:路人-1
id:2,name:路人-2
id:4,name:路人-4
```

### 7.2.3、StandardShardingStrategyTest完整代码

```java
package com.itsoku.shardingstrategy;

import com.google.common.collect.Range;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
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.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class StandardShardingStrategyTest {
    private static DataSource dataSource;

/**
         * 1.配置真实数据源
         */
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("ds", ds);

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);
    }

@Test
    public void test5() throws SQLException {
        String sql = "select id,name from t_user where id in (1,2,4)";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                System.out.println(String.format("id:%s,name:%s", id, name));
            }
        }
    }

@Test
    public void test7() throws SQLException {
        String sql = "select id,name from t_user where id between 1 and 2";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                System.out.println(String.format("id:%s,name:%s", id, name));
            }
        }
    }

@Test
    public void test8() throws SQLException {
        String sql = "select id,name from t_user where id between 1 and 2 or id>=4";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                System.out.println(String.format("id:%s,name:%s", id, name));
            }
        }
    }
}
```

## 7.3、复合分片策略（ComplexShardingStrategy）

### 7.3.1、适合的场景

- 对应ComplexShardingStrategy类
- 提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持
- ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。

### 7.3.2、案例

#### 1）需求

- ds_sss库中含有1个文件表：t_file_0，t_file_1，t_file_2
- 每个表有2个字段(id,storage_type,file_name)
  - storage_type：表示存储在哪里，0：存储在阿里云oss上，1：表示存储在本地磁盘
- 数据存分配规则
  - storage_type为0的，数据量比较大，我们用前2张表来存储，规则如下
  - t_file_0：存储storage_type为0的，且 id 为偶数的
  - t_file_1：存储storage_type为0的，且 id 为基数的
  - t_file_3：存储storage_type为1的所有文件

#### 2）分析需求

此需求有2个分片字段：id和storage_type，对于多字段的分片需要用到ComplexShardingStrategy

#### 3）sql脚本

```sql
drop database if exists ds_css;
create database ds_css;
use ds_css;

drop table if exists t_file_0;
create table t_file_0(
	id bigint not null primary key comment 'id',
  storage_type smallint(1) not null comment '表示存储在哪里？0：存储在oss上，1：存储在本地磁盘',
	name varchar(64) not null comment '文件名称'
);

drop table if exists t_file_1;
create table t_file_1(
	id bigint not null primary key comment 'id',
  storage_type smallint(1) not null comment '表示存储在哪里？0：存储在oss上，1：存储在本地磁盘',
	name varchar(64) not null comment '文件名称'
);

drop table if exists t_file_2;
create table t_file_2(
	id bigint not null primary key comment 'id',
  storage_type smallint(1) not null comment '表示存储在哪里？0：存储在oss上，1：存储在本地磁盘',
	name varchar(64) not null comment '文件名称'
);
```

#### 3）创建测试类

```java
public class ComplexShardingStrategyTest {
    private static DataSource dataSource;

@BeforeAll
    public static void init() throws SQLException {
        HikariDataSource ds = new HikariDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setJdbcUrl("jdbc:mysql://localhost:3306/ds_css?characterEncoding=UTF-8");
        ds.setUsername("root");
        ds.setPassword("root123");

/**
         * 1.配置真实数据源
         */
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("ds", ds);

/**
         * 2、配置 t_file分片规则
         */
        //逻辑表名
        final String logicTable = "t_file";
        //对应的实际表（3张）
        final String actualDataNodes = "ds.t_file_0,ds.t_file_1,ds.t_file_2";
        TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration(logicTable, actualDataNodes);

//混合分片策略配置
        ComplexShardingStrategyConfiguration complexShardingStrategyConfiguration = new ComplexShardingStrategyConfiguration(
                "id,storage_type", //2个分片字段（最终落到那个表，由这两个字段的值决定）
                new ComplexKeysShardingAlgorithm() { //混合分片算法
                    @Override
                    public Collection<String> doSharding(Collection availableTargetNames, ComplexKeysShardingValue shardingValue) {
                        List<String> tableNames = new ArrayList<>();
                        //=、in 走这里
                        final Map columnNameAndShardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
                        Collection<Integer> storage_types = (Collection<Integer>) columnNameAndShardingValuesMap.get("storage_type");
                        Collection<Long> ids = (Collection<Long>) columnNameAndShardingValuesMap.get("id");
                        if (storage_types != null) {
                            for (Integer storage_type : storage_types) {
                                if (storage_type == 0) {
                                    if (ids != null) {
                                        for (Long id : ids) {
                                            if (id % 2 == 0) {
                                                tableNames.add("t_file_0");
                                            } else {
                                                tableNames.add("t_file_1");
                                            }
                                        }
                                    } else {
                                        tableNames.add("t_file_0");
                                        tableNames.add("t_file_1");
                                    }
                                } else if (storage_type == 1) {
                                    tableNames.add("t_file_2");
                                }
                            }
                        }
                        // 范围的走这里,留给大家自己实现
                        final Map<String, Range<Comparable>> columnNameAndRangeValuesMap = shardingValue.getColumnNameAndRangeValuesMap();

System.out.println(String.format("路由信息,tableNames：%s, id值：%s, storage_type值：%s", tableNames, ids, storage_types));

return tableNames.isEmpty() ? availableTargetNames : tableNames;
                    }
                });
        tableRuleConfiguration.setTableShardingStrategyConfig(complexShardingStrategyConfiguration);
        /**
         * 3、加入表的分片规则
         */
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(tableRuleConfiguration);

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);
    }

}
```

#### 4）重点代码

上面中重点代码如下，重点在于分片策略StandardShardingStrategyConfiguration的配置，需要指定2个算法策略

```java
/**
 * 2、配置 t_file分片规则
 */
//逻辑表名
final String logicTable = "t_file";
//对应的实际表（3张）
final String actualDataNodes = "ds.t_file_0,ds.t_file_1,ds.t_file_2";
TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration(logicTable, actualDataNodes);

//混合分片策略配置
ComplexShardingStrategyConfiguration complexShardingStrategyConfiguration = new ComplexShardingStrategyConfiguration(
        "id,storage_type", //2个分片字段（最终落到那个表，由这两个字段的值决定）
        new ComplexKeysShardingAlgorithm() { //混合分片算法
            @Override
            public Collection<String> doSharding(Collection availableTargetNames, ComplexKeysShardingValue shardingValue) {
                List<String> tableNames = new ArrayList<>();
                //=、in 走这里
                final Map columnNameAndShardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
                Collection<Integer> storage_types = (Collection<Integer>) columnNameAndShardingValuesMap.get("storage_type");
                Collection<Long> ids = (Collection<Long>) columnNameAndShardingValuesMap.get("id");
                if (storage_types != null) {
                    for (Integer storage_type : storage_types) {
                        if (storage_type == 0) {
                            if (ids != null) {
                                for (Long id : ids) {
                                    if (id % 2 == 0) {
                                        tableNames.add("t_file_0");
                                    } else {
                                        tableNames.add("t_file_1");
                                    }
                                }
                            } else {
                                tableNames.add("t_file_0");
                                tableNames.add("t_file_1");
                            }
                        } else if (storage_type == 1) {
                            tableNames.add("t_file_2");
                        }
                    }
                }
                // 范围的走这里,留给大家自己实现
                final Map<String, Range<Comparable>> columnNameAndRangeValuesMap = shardingValue.getColumnNameAndRangeValuesMap();

System.out.println(String.format("路由信息,tableNames：%s, id值：%s, storage_type值：%s", tableNames, ids, storage_types));

return tableNames.isEmpty() ? availableTargetNames : tableNames;
            }
        });
tableRuleConfiguration.setTableShardingStrategyConfig(complexShardingStrategyConfiguration);
```

下面上测试案例，代码都位于上面StandardShardingStrategyTest类中

#### 5）测试1：插入4条数据，看sql路由情况

> 先把上面的sql脚本执行一遍，清下数据，然后执行下面代码
>
> 下面storage_type为0和1的，各插入2条数据

```java
@Test
public void test1() throws SQLException {
    String sql = "insert t_file (id,storage_type,name) value (?,?,?)";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);) {
        long id = 1;
        for (int storage_type = 0; storage_type <= 1; storage_type++) {
            for (; id <= storage_type * 2 + 2; id++) {
                int parameterIndex = 1;
                ps.setLong(parameterIndex++, id);
                ps.setInt(parameterIndex++, storage_type);
                ps.setString(parameterIndex++, "ShardingSphere高手笔记-" + id);
                ps.executeUpdate();
            }
        }
    }
}
```

运行输出如下，t_file_0和t_file_1分别落入了1条数据，t_file_2落入了2条数据

```java
路由信息,tableNames：[t_file_1], id值：[1], storage_type值：[0]
Logic SQL: insert t_file (id,storage_type,name) value (?,?,?)
Actual SQL: ds ::: insert t_file_1 (id,storage_type,name) value (?, ?, ?) ::: [1, 0, ShardingSphere高手笔记-1]

路由信息,tableNames：[t_file_0], id值：[2], storage_type值：[0]
Logic SQL: insert t_file (id,storage_type,name) value (?,?,?)
Actual SQL: ds ::: insert t_file_0 (id,storage_type,name) value (?, ?, ?) ::: [2, 0, ShardingSphere高手笔记-2]

路由信息,tableNames：[t_file_2], id值：[3], storage_type值：[1]
Logic SQL: insert t_file (id,storage_type,name) value (?,?,?)
Actual SQL: ds ::: insert t_file_2 (id,storage_type,name) value (?, ?, ?) ::: [3, 1, ShardingSphere高手笔记-3]

路由信息,tableNames：[t_file_2], id值：[4], storage_type值：[1]
Logic SQL: insert t_file (id,storage_type,name) value (?,?,?)
Actual SQL: ds ::: insert t_file_2 (id,storage_type,name) value (?, ?, ?) ::: [4, 1, ShardingSphere高手笔记-4]
```

#### 6）测试2：批量插入

> 先把上面的sql脚本执行一遍，清下数据，然后执行下面代码

```java
@Test
public void test2() throws SQLException {
    String sql = "insert t_file (id,storage_type,name) value (?,?,?), (?,?,?), (?,?,?), (?,?,?)";
    try (Connection connection = dataSource.getConnection();
         PreparedStatement ps = connection.prepareStatement(sql);) {
        int parameterIndex = 1;
        for (long id = 1; id <= 4; id++) {
            ps.setLong(parameterIndex++, id);
            ps.setInt(parameterIndex++, (int) (id % 2));
            ps.setString(parameterIndex++, "Spring高手系列-" + id);
        }
        System.out.println("count:" + ps.executeUpdate());
    }
}
```

运行输出

```sql
路由信息,tableNames：[t_file_2], id值：[1], storage_type值：[1]
路由信息,tableNames：[t_file_0], id值：[2], storage_type值：[0]
路由信息,tableNames：[t_file_2], id值：[3], storage_type值：[1]
路由信息,tableNames：[t_file_0], id值：[4], storage_type值：[0]
Logic SQL: insert t_file (id,storage_type,name) value (?,?,?), (?,?,?), (?,?,?), (?,?,?)
Actual SQL: ds ::: insert t_file_2 (id,storage_type,name) value (?, ?, ?), (?, ?, ?) ::: [1, 1, Spring高手系列-1, 3, 1, Spring高手系列-3]
Actual SQL: ds ::: insert t_file_0 (id,storage_type,name) value (?, ?, ?), (?, ?, ?) ::: [2, 0, Spring高手系列-2, 4, 0, Spring高手系列-4]
count:4
```

#### 7）其他案例

还有6个案例，就不一一给大家演示效果了，都在下面完整的代码中，大家自己跑跑感受下。

```java
package com.itsoku.shardingstrategy;

import com.google.common.collect.Range;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.ComplexShardingStrategyConfiguration;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class ComplexShardingStrategyTest {
    private static DataSource dataSource;

@BeforeAll
    public static void init() throws SQLException {
        HikariDataSource ds = new HikariDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setJdbcUrl("jdbc:mysql://localhost:3306/ds_css?characterEncoding=UTF-8");
        ds.setUsername("root");
        ds.setPassword("root123");

/**
         * 1.配置真实数据源
         */
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("ds", ds);

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);
    }

@Test
    public void test1() throws SQLException {
        String sql = "insert t_file (id,storage_type,name) value (?,?,?)";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);) {
            long id = 1;
            for (int storage_type = 0; storage_type <= 1; storage_type++) {
                for (; id <= storage_type * 2 + 2; id++) {
                    int parameterIndex = 1;
                    ps.setLong(parameterIndex++, id);
                    ps.setInt(parameterIndex++, storage_type);
                    ps.setString(parameterIndex++, "ShardingSphere高手笔记-" + id);
                    ps.executeUpdate();
                }
            }
        }
    }

@Test
    public void test2() throws SQLException {
        String sql = "insert t_file (id,storage_type,name) value (?,?,?), (?,?,?), (?,?,?), (?,?,?)";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);) {
            int parameterIndex = 1;
            for (long id = 1; id <= 4; id++) {
                ps.setLong(parameterIndex++, id);
                ps.setInt(parameterIndex++, (int) (id % 2));
                ps.setString(parameterIndex++, "Spring高手系列-" + id);
            }
            System.out.println("count:" + ps.executeUpdate());
        }
    }

@Test
    public void test3() throws SQLException {
        String sql = "select id,storage_type,name from t_file";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                final String storage_type = rs.getString("storage_type");
                System.out.println(String.format("id:%s,storage_type:%s,name:%s", id, storage_type, name));
            }
        }
    }

@Test
    public void test4() throws SQLException {
        String sql = "select id,storage_type,name from t_file where storage_type = 0";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                final String storage_type = rs.getString("storage_type");
                System.out.println(String.format("id:%s,storage_type:%s,name:%s", id, storage_type, name));
            }
        }
    }

@Test
    public void test5() throws SQLException {
        String sql = "select id,storage_type,name from t_file where id in (1,2,4)";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                final String storage_type = rs.getString("storage_type");
                System.out.println(String.format("id:%s,storage_type:%s,name:%s", id, storage_type, name));
            }
        }
    }

@Test
    public void test6() throws SQLException {
        String sql = "select id,storage_type,name from t_file where id != 1";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                final String storage_type = rs.getString("storage_type");
                System.out.println(String.format("id:%s,storage_type:%s,name:%s", id, storage_type, name));
            }
        }
    }

@Test
    public void test7() throws SQLException {
        String sql = "select id,storage_type,name from t_file where id between 1 and 2";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                final String storage_type = rs.getString("storage_type");
                System.out.println(String.format("id:%s,storage_type:%s,name:%s", id, storage_type, name));
            }
        }
    }

@Test
    public void test8() throws SQLException {
        String sql = "select id,storage_type,name from t_file where id between 1 and 20 or storage_type=0";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                final String storage_type = rs.getString("storage_type");
                System.out.println(String.format("id:%s,storage_type:%s,name:%s", id, storage_type, name));
            }
        }
    }
}
```

## 7.4、强制路由策略(HintShardingStrategy)

ShardingSphere使用ThreadLocal管理分片键值进行Hint强制路由。可以通过编程的方式向HintManager中添加分片值，该分片值仅在当前线程内生效。

### 7.4.1、适合的场景

- 分片字段不存在SQL中、数据库表结构中
- 强制在主库进行某些数据操作

### 7.4.2、用法

Hint分片算法需要用户实现`org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm`接口。ShardingSphere在进行Routing时，如果发现LogicTable的`TableRule`采用了 Hint的分片算法，将会从`HintManager`中获取分片值进行路由操作。

#### 获取HintManager

```java
HintManager hintManager = HintManager.getInstance();
```

#### 添加分片键值

- 使用hintManager.addDatabaseShardingValue来添加数据源分片键值。
- 使用hintManager.addTableShardingValue来添加表分片键值。

> 分库不分表情况下，强制路由至某一个分库时，可使用`hintManager.setDatabaseShardingValue`方式添加分片。通过此方式添加分片键值后，将跳过SQL解析和改写阶段，从而提高整体执行效率。

#### 清除分片键值

分片键值保存在ThreadLocal中，所以需要在操作结束时调用hintManager.close()来清除ThreadLocal中的内容。

**hintManager实现了AutoCloseable接口，可推荐使用try with resource自动关闭。**

### 7.4.3、案例

#### 1）需求

ds_hss中有2张用户表t_user_0、t_user_1，具体查哪张表的数据，我们通过外部条件来控制。

#### 2）sql脚本

```sql
drop database if exists ds_hss;
create database ds_hss;
use ds_hss;

drop table if exists t_user_0;
create table t_user_0(
	id bigint not null primary key,
	name varchar(64) not null
);

insert into t_user_0 VALUES (1,'我是t_user_0');

drop table if exists t_user_1;
create table t_user_1(
	id bigint not null primary key,
	name varchar(64) not null
);

insert into t_user_1 VALUES (2,'我是t_user_1');
```

#### 3）创建测试类

```java
public class HintShardingStrategyTest {
    private static DataSource dataSource;

@BeforeAll
    public static void init() throws SQLException {
        HikariDataSource ds = new HikariDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setJdbcUrl("jdbc:mysql://localhost:3306/ds_hss?characterEncoding=UTF-8");
        ds.setUsername("root");
        ds.setPassword("root123");

/**
         * 1.配置真实数据源
         */
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("ds", ds);

/**
         * 2、配置 t_file分片规则
         */
        //逻辑表名
        final String logicTable = "t_user";
        //对应的实际表（3张）
        final String actualDataNodes = "ds.t_user_0,ds.t_user_1";
        TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration(logicTable, actualDataNodes);

//混合分片策略配置
        HintShardingStrategyConfiguration hintShardingStrategyConfiguration = new HintShardingStrategyConfiguration(
                new HintShardingAlgorithm<Integer>() {
                    @Override
                    public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Integer> shardingValue) {
                        final Object[] tables = availableTargetNames.toArray();

List<String> result = new ArrayList<>();
                        //HintManager.getInstance().addTableShardingValue放入的值都在shardingValue里面
                        final Collection<Integer> tableIndexList = shardingValue.getValues();
                        for (Integer tableIndex : tableIndexList) {
                            result.add((String) tables[tableIndex]);
                        }
                        return result;
                    }
                });
        tableRuleConfiguration.setTableShardingStrategyConfig(hintShardingStrategyConfiguration);
        /**
         * 3、加入表的分片规则
         */
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(tableRuleConfiguration);

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);
    }

}
```

> 下面上测试案例，代码都位于上面HintShardingStrategyTest类中

#### 4）测试1：获取第1个user表的数据

```java
@Test
public void test1() throws SQLException {
    String sql = "select id,name,name from t_user";
    try (HintManager instance = HintManager.getInstance();) {
        //设置查询表的索引，addTableShardingValue(逻辑表名,值)
        instance.addTableShardingValue("t_user", 0);
        try (
            Connection connection = dataSource.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                System.out.println(String.format("id:%s,name:%s", id, name));
            }
        }
    }
}
```

运行输出，如下，被路由到t_user_0了

```sql
Logic SQL: select id,name,name from t_user
Actual SQL: ds ::: select id,name,name from t_user_0
id:1,name:我是t_user_0
```

#### 5）测试2：获取第2个user表的数据

下面将上面代码调整下，将`instance.addTableShardingValue("t_user", 0);`中的值改为1，则会路由到第2个user表，代码如下

```java
@Test
public void test2() throws SQLException {
    String sql = "select id,name,name from t_user";
    try (HintManager instance = HintManager.getInstance();) {
       //设置查询表的索引，addTableShardingValue(逻辑表名,值)
        instance.addTableShardingValue("t_user", 1);
        try (
            Connection connection = dataSource.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                System.out.println(String.format("id:%s,name:%s", id, name));
            }
        }
    }
}

```

运行输出，如下，被路由到了t_user_1表了

```sql
Logic SQL: select id,name,name from t_user
Actual SQL: ds ::: select id,name,name from t_user_1
id:2,name:我是t_user_1
```

#### 6）测试3：获取2个user表所有数据

下面再把代码调整下，改成下面这样

```java
instance.addTableShardingValue("t_user", 0);
instance.addTableShardingValue("t_user", 1);
```

完整版如下

```java
@Test
public void test3() throws SQLException {
    String sql = "select id,name,name from t_user";
    try (HintManager instance = HintManager.getInstance();) {
        //设置查询表的索引，addTableShardingValue(逻辑表名,值)
        instance.addTableShardingValue("t_user", 0);
        instance.addTableShardingValue("t_user", 1);
        try (
            Connection connection = dataSource.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                final long id = rs.getLong("id");
                final String name = rs.getString("name");
                System.out.println(String.format("id:%s,name:%s", id, name));
            }
        }
    }
}
```

运行输出如下，这次被路由到了2张用户表

```sql
Logic SQL: select id,name,name from t_user
Actual SQL: ds ::: select id,name,name from t_user_0
Actual SQL: ds ::: select id,name,name from t_user_1
id:1,name:我是t_user_0
id:2,name:我是t_user_1
```

### 7.4.4、HintShardingStrategyTest完整代码

```java
package com.itsoku.shardingstrategy;

import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.HintShardingStrategyConfiguration;
import org.apache.shardingsphere.api.hint.HintManager;
import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.apache.shardingsphere.underlying.common.config.properties.ConfigurationPropertyKey;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class HintShardingStrategyTest {
    private static DataSource dataSource;

@BeforeAll
    public static void init() throws SQLException {
        HikariDataSource ds = new HikariDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setJdbcUrl("jdbc:mysql://localhost:3306/ds_hss?characterEncoding=UTF-8");
        ds.setUsername("root");
        ds.setPassword("root123");

/**
         * 1.配置真实数据源
         */
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("ds", ds);

/**
         * 4、配置一些属性
         */
        Properties props = new Properties();
        //输出sql
        props.put(ConfigurationPropertyKey.SQL_SHOW.getKey(), true);

/**
         * 5、创建数据源
         */
        dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);
    }

@Test
    public void test1() throws SQLException {
        String sql = "select id,name,name from t_user";
        try (HintManager instance = HintManager.getInstance();) {
            //设置查询表的索引，addTableShardingValue(逻辑表名,值)
            instance.addTableShardingValue("t_user", 0);
            try (
                    Connection connection = dataSource.getConnection();
                    PreparedStatement ps = connection.prepareStatement(sql);
                    ResultSet rs = ps.executeQuery();) {
                while (rs.next()) {
                    final long id = rs.getLong("id");
                    final String name = rs.getString("name");
                    System.out.println(String.format("id:%s,name:%s", id, name));
                }
            }
        }
    }

@Test
    public void test2() throws SQLException {
        String sql = "select id,name,name from t_user";
        try (HintManager instance = HintManager.getInstance();) {
            //设置查询表的索引，addTableShardingValue(逻辑表名,值)
            instance.addTableShardingValue("t_user", 1);
            try (
                    Connection connection = dataSource.getConnection();
                    PreparedStatement ps = connection.prepareStatement(sql);
                    ResultSet rs = ps.executeQuery();) {
                while (rs.next()) {
                    final long id = rs.getLong("id");
                    final String name = rs.getString("name");
                    System.out.println(String.format("id:%s,name:%s", id, name));
                }
            }
        }
    }

@Test
    public void test3() throws SQLException {
        String sql = "select id,name,name from t_user";
        try (HintManager instance = HintManager.getInstance();) {
            //设置查询表的索引，addTableShardingValue(逻辑表名,值)
            instance.addTableShardingValue("t_user", 0);
            instance.addTableShardingValue("t_user", 1);
            try (
                    Connection connection = dataSource.getConnection();
                    PreparedStatement ps = connection.prepareStatement(sql);
                    ResultSet rs = ps.executeQuery();) {
                while (rs.next()) {
                    final long id = rs.getLong("id");
                    final String name = rs.getString("name");
                    System.out.println(String.format("id:%s,name:%s", id, name));
                }
            }
        }
    }

}
```

<a style="display:none" target="_blank" href="https://mp.weixin.qq.com/s/_S1DD2JADnXvpexxaBwLLg" style="color:red; font-size:20px; font-weight:bold">继续收门徒，亲手带，月薪 4W 以下的可以来找我</a>

## 最新资料

1. <a href="https://mp.weixin.qq.com/s?__biz=MzkzOTI3Nzc0Mg==&mid=2247484964&idx=2&sn=c81bce2f26015ee0f9632ddc6c67df03&scene=21#wechat_redirect" target="_blank">尚硅谷 Java 学科全套教程（总 207.77GB）</a>
2. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484192&idx=1&sn=505f2faaa4cc911f553850667749bcbb&scene=21#wechat_redirect" target="_blank">2021 最新版 Java 微服务学习线路图 + 视频</a>
3. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484573&idx=1&sn=7f3d83892186c16c57bc0b99f03f1ffd&scene=21#wechat_redirect" target="_blank">阿里技术大佬整理的《Spring 学习笔记.pdf》</a>
4. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247484544&idx=2&sn=c1dfe907cfaa5b9ae8e66fc247ccbe84&scene=21#wechat_redirect" target="_blank">阿里大佬的《MySQL 学习笔记高清.pdf》</a>
5. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485167&idx=1&sn=48d75c8e93e748235a3547f34921dfb7&scene=21#wechat_redirect" target="_blank">2021 版 java 高并发常见面试题汇总.pdf</a>
6. <a href="https://mp.weixin.qq.com/s?__biz=MzkwOTAyMTY2NA==&mid=2247485664&idx=1&sn=435f9f515a8f881642820d7790ad20ce&scene=21#wechat_redirect" target="_blank">Idea 快捷键大全.pdf</a>

![](https://itsoku.oss-cn-hangzhou.aliyuncs.com/itsoku/blog/article/1/2883e86e-3eff-404a-8943-0066e5e2b454.png)