ShardingSphere4.1.1 | Sharding-JDBC基于Hint的数据分片和强制路由

简介

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

1.分片字段不存在SQL中、数据库表结构中，而存在于外部业务逻辑。

2.强制在主库进行某些数据操作。

基于Hint实现数据分片和强制路由

1、项目构建

创建一个SpringBoot项目，引入如下依赖：

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">

    <parent>
        <groupId>cn.zwqh</groupId>
        <artifactId>sharding-sphere-4.1.1</artifactId>
        <version>1.0-SNAPSHOT</version>
    </parent>

    <modelVersion>4.0.0</modelVersion>
    <groupId>cn.zwqh</groupId>
    <artifactId>sharding-sphere-demo-6</artifactId>
    <version>${parent.version}</version>

    <packaging>jar</packaging>

    <name>sharding-sphere-demo-6</name>
    <description>Demo project for Spring Boot</description>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>

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

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <mainClass>cn.zwqh.shardingspheredemo6.ShardingSphereDemo6Application</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

2、创建数据库 sharding_sphere_0，sharding_sphere_1

3、创建实体类

Order

@Data
public class Order {

    private long orderId;

    private int userId;

    private long addressId;

    private String status;
}

OrderItem

@Data
public class OrderItem{
    
    private long orderItemId;
    
    private long orderId;
    
    private int userId;
    
    private String status;

}

Address

@Data
public class Address {
    
    private Long addressId;
    
    private String addressName;
}

4、Mapper层

CommonMapper

public interface CommonMapper<T, P> {
    
    /**
     * 如果表不存在则创建表
     * 
     * @throws SQLException SQL exception
     */
    void createTableIfNotExists() throws SQLException;
    
    /**
     * 删除表
     * 
     * @throws SQLException SQL exception
     */
    void dropTable() throws SQLException;
    
    /**
     * 清空表
     * 
     * @throws SQLException SQL exception
     */
    void truncateTable() throws SQLException;
    
    /**
     * 新增数据
     * 
     * @param entity entity
     * @return generated primary key
     * @throws SQLException SQL exception
     */
    P insert(T entity) throws SQLException;
    
    /**
     * 删除数据
     * 
     * @param primaryKey primaryKey
     * @throws SQLException SQL exception
     */
    void delete(P primaryKey) throws SQLException;
    
    /**
     * 查询数据
     * 
     * @return all data
     * @throws SQLException SQL exception
     */
    List<T> selectAll() throws SQLException;
}

OrderMapper

public interface OrderMapper extends CommonMapper<Order,Long>{
}

OrderMapperImpl

public class OrderMapperImpl implements OrderMapper {

    private DataSource dataSource;

    public OrderMapperImpl(final DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public void createTableIfNotExists() throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, address_id BIGINT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id))";
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            statement.executeUpdate(sql);
        }
    }

    @Override
    public void dropTable() throws SQLException {
        String sql = "DROP TABLE t_order";
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            statement.executeUpdate(sql);
        }
    }

    @Override
    public void truncateTable() throws SQLException {
        String sql = "TRUNCATE TABLE t_order";
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            statement.executeUpdate(sql);
        }
    }

    @Override
    public Long insert(final Order order) throws SQLException {
        String sql = "INSERT INTO t_order (user_id, address_id, status) VALUES (?, ?, ?)";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            preparedStatement.setInt(1, order.getUserId());
            preparedStatement.setLong(2, order.getAddressId());
            preparedStatement.setString(3, order.getStatus());
            preparedStatement.executeUpdate();
            try (ResultSet resultSet = preparedStatement.getGeneratedKeys()) {
                if (resultSet.next()) {
                    order.setOrderId(resultSet.getLong(1));
                }
            }
        }
        return order.getOrderId();
    }

    @Override
    public void delete(final Long orderId) throws SQLException {
        String sql = "DELETE FROM t_order WHERE order_id=?";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setLong(1, orderId);
            preparedStatement.executeUpdate();
        }
    }

    @Override
    public List<Order> selectAll() throws SQLException {
        String sql = "SELECT * FROM t_order";
        return getOrders(sql);
    }

    protected List<Order> getOrders(final String sql) throws SQLException {
        List<Order> result = new LinkedList<>();
        try (Connection connection = dataSource.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                Order order = new Order();
                order.setOrderId(resultSet.getLong(1));
                order.setUserId(resultSet.getInt(2));
                order.setAddressId(resultSet.getLong(3));
                order.setStatus(resultSet.getString(4));
                result.add(order);
            }
        }
        return result;
    }
}

OrderItemMapper

public interface OrderItemMapper extends CommonMapper<OrderItem,Long> {
}

OrderItemMapperImpl

public class OrderItemMapperImpl implements OrderItemMapper{

    private DataSource dataSource;

    public OrderItemMapperImpl(final DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public void createTableIfNotExists() throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS t_order_item "
                + "(order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id))";
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            statement.executeUpdate(sql);
        }
    }

    @Override
    public void dropTable() throws SQLException {
        String sql = "DROP TABLE t_order_item";
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            statement.executeUpdate(sql);
        }
    }

    @Override
    public void truncateTable() throws SQLException {
        String sql = "TRUNCATE TABLE t_order_item";
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            statement.executeUpdate(sql);
        }
    }

    @Override
    public Long insert(final OrderItem orderItem) throws SQLException {
        String sql = "INSERT INTO t_order_item (order_id, user_id, status) VALUES (?, ?, ?)";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            preparedStatement.setLong(1, orderItem.getOrderId());
            preparedStatement.setInt(2, orderItem.getUserId());
            preparedStatement.setString(3, orderItem.getStatus());
            preparedStatement.executeUpdate();
            try (ResultSet resultSet = preparedStatement.getGeneratedKeys()) {
                if (resultSet.next()) {
                    orderItem.setOrderItemId(resultSet.getLong(1));
                }
            }
        }
        return orderItem.getOrderItemId();
    }

    @Override
    public void delete(final Long orderItemId) throws SQLException {
        String sql = "DELETE FROM t_order_item WHERE order_item_id=?";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setLong(1, orderItemId);
            preparedStatement.executeUpdate();
        }
    }

    @Override
    public List<OrderItem> selectAll() throws SQLException {
        String sql = "SELECT i.* FROM t_order o, t_order_item i WHERE o.order_id = i.order_id";
        return getOrderItems(sql);
    }

    protected List<OrderItem> getOrderItems(final String sql) throws SQLException {
        List<OrderItem> result = new LinkedList<>();
        try (Connection connection = dataSource.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                OrderItem orderItem = new OrderItem();
                orderItem.setOrderItemId(resultSet.getLong(1));
                orderItem.setOrderId(resultSet.getLong(2));
                orderItem.setUserId(resultSet.getInt(3));
                orderItem.setStatus(resultSet.getString(4));
                result.add(orderItem);
            }
        }
        return result;
    }
}

AddressMapper

public interface AddressMapper extends CommonMapper<Address, Long> {
}

AddressMapperImpl

public class AddressMapperImpl implements AddressMapper {

    private DataSource dataSource;

    public AddressMapperImpl(final DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public void createTableIfNotExists() throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS t_address "
                + "(address_id BIGINT NOT NULL, address_name VARCHAR(100) NOT NULL, PRIMARY KEY (address_id))";
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            statement.executeUpdate(sql);
        }
    }

    @Override
    public void dropTable() throws SQLException {
        String sql = "DROP TABLE t_address";
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            statement.executeUpdate(sql);
        }
    }

    @Override
    public void truncateTable() throws SQLException {
        String sql = "TRUNCATE TABLE t_address";
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            statement.executeUpdate(sql);
        }
    }

    @Override
    public Long insert(final Address entity) throws SQLException {
        String sql = "INSERT INTO t_address (address_id, address_name) VALUES (?, ?)";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setLong(1, entity.getAddressId());
            preparedStatement.setString(2, entity.getAddressName());
            preparedStatement.executeUpdate();
        }
        return entity.getAddressId();
    }

    @Override
    public void delete(final Long primaryKey) throws SQLException {
        String sql = "DELETE FROM t_address WHERE address_id=?";
        try (Connection connection = dataSource.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            preparedStatement.setLong(1, primaryKey);
            preparedStatement.executeUpdate();
        }
    }

    @Override
    public List<Address> selectAll() throws SQLException {
        String sql = "SELECT * FROM t_address";
        return getAddress(sql);
    }

    private List<Address> getAddress(final String sql) throws SQLException {
        List<Address> result = new LinkedList<>();
        try (Connection connection = dataSource.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                Address address = new Address();
                address.setAddressId(resultSet.getLong(1));
                address.setAddressName(resultSet.getString(2));
                result.add(address);
            }
        }
        return result;
    }
}

5、Service层

ExampleService

public interface ExampleService {

    /**
     * 初始化环境
     * Initialize environment.
     *
     * @throws SQLException SQL exception
     */
    void initEnvironment() throws SQLException;

    /**
     * 清除环境
     * Clean environment.
     *
     * @throws SQLException SQL exception
     */
    void cleanEnvironment() throws SQLException;

    /**
     * 流程成功
     * Process success.
     *
     * @throws SQLException SQL exception
     */
    void processSuccess() throws SQLException;

    /**
     * 流程失败
     * Process failure.
     *
     * @throws SQLException SQL exception
     */
    void processFailure() throws SQLException;

    /**
     * 打印数据
     * Print data.
     *
     * @throws SQLException SQL exception
     */
    void printData() throws SQLException;
}

OrderServiceImpl

public class OrderServiceImpl implements ExampleService{
    private OrderMapperImpl orderMapperImpl;

    private OrderItemMapperImpl orderItemMapperImpl;

    private AddressMapperImpl addressMapperImpl;

    public OrderServiceImpl(final DataSource dataSource) {
        this.orderMapperImpl = new OrderMapperImpl(dataSource);
        this.orderItemMapperImpl = new OrderItemMapperImpl(dataSource);
        this.addressMapperImpl = new AddressMapperImpl(dataSource);
    }

    public OrderServiceImpl( OrderMapperImpl orderMapperImpl,  OrderItemMapperImpl orderItemMapperImpl,  AddressMapperImpl addressMapperImpl) {
        this.orderMapperImpl = orderMapperImpl;
        this.orderItemMapperImpl = orderItemMapperImpl;
        this.addressMapperImpl = addressMapperImpl;
    }

    @Override
    public void initEnvironment() throws SQLException {
        orderMapperImpl.createTableIfNotExists();
        orderItemMapperImpl.createTableIfNotExists();
        orderMapperImpl.truncateTable();
        orderItemMapperImpl.truncateTable();
        initAddressTable();
    }

    private void initAddressTable() throws SQLException {
        addressMapperImpl.createTableIfNotExists();
        addressMapperImpl.truncateTable();
        initAddressData();
    }

    private void initAddressData() throws SQLException {
        for (int i = 0; i < 10; i++) {
            insertAddress(i);
        }
    }

    private void insertAddress(final int i) throws SQLException {
        Address address = new Address();
        address.setAddressId((long) i);
        address.setAddressName("address_" + i);
        addressMapperImpl.insert(address);
    }

    @Override
    public void cleanEnvironment() throws SQLException {
        orderMapperImpl.dropTable();
        orderItemMapperImpl.dropTable();
        addressMapperImpl.dropTable();
    }

    @Override
    public void processSuccess() throws SQLException {
        System.out.println("-------------- Process Success Begin ---------------");
        List<Long> orderIds = insertData();
        printData();
 //       deleteData(orderIds);
 //       printData();
        System.out.println("-------------- Process Success Finish --------------");
    }

    @Override
    public void processFailure() throws SQLException {
        System.out.println("-------------- Process Failure Begin ---------------");
        insertData();
        System.out.println("-------------- Process Failure Finish --------------");
        throw new RuntimeException("Exception occur for transaction test.");
    }

    private List<Long> insertData() throws SQLException {
        System.out.println("---------------------------- Insert Data ----------------------------");
        List<Long> result = new ArrayList<>(10);
        for (int i = 1; i <= 10; i++) {
            Order order = insertOrder(i);
            insertOrderItem(i, order);
            result.add(order.getOrderId());
        }
        return result;
    }

    private Order insertOrder(final int i) throws SQLException {
        Order order = new Order();
        order.setUserId(i);
        order.setAddressId(i);
        order.setStatus("INSERT_TEST");
        orderMapperImpl.insert(order);
        return order;
    }

    private void insertOrderItem(final int i, final Order order) throws SQLException {
        OrderItem item = new OrderItem();
        item.setOrderId(order.getOrderId());
        item.setUserId(i);
        item.setStatus("INSERT_TEST");
        orderItemMapperImpl.insert(item);
    }

    private void deleteData(final List<Long> orderIds) throws SQLException {
        System.out.println("---------------------------- Delete Data ----------------------------");
        for (Long each : orderIds) {
            orderMapperImpl.delete(each);
            orderItemMapperImpl.delete(each);
        }
    }

    @Override
    public void printData() throws SQLException {
        System.out.println("---------------------------- Print Order Data -----------------------");
        for (Object each : orderMapperImpl.selectAll()) {
            System.out.println(each);
        }
        System.out.println("---------------------------- Print OrderItem Data -------------------");
        for (Object each : orderItemMapperImpl.selectAll()) {
            System.out.println(each);
        }
    }
}

6、Hint 配置

HintType 枚举

public enum HintType {
    DATABASE_ONLY, DATABASE_TABLES, MASTER_ONLY
}

hint-databases-only.yaml 分库配置文件

dataSources:
  ds_0: !!com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/sharding_sphere_0
    username: root
    password: 123456
  ds_1: !!com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/sharding_sphere_1
    username: root
    password: 123456

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
        props:
          worker.id: 123
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item
  bindingTables:
    - t_order,t_order_item
  # 广播表
  broadcastTables:
    - t_address
  # 默认分库策略
  defaultDatabaseStrategy:
    hint:
      algorithmClassName: cn.zwqh.shardingspheredemo6.hint.MyHintAlgorithm
  # 默认分表策略
  defaultTableStrategy:
    # 不分表
    none:

props:
  sql.show: true

hint-databases-tables.yaml 分库分表配置文件

dataSources:
  ds_0: !!com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/sharding_sphere_0
    username: root
    password: 123456
  ds_1: !!com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/sharding_sphere_1
    username: root
    password: 123456

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      databaseStrategy:
        hint:
          algorithmClassName: cn.zwqh.shardingspheredemo6.hint.MyHintAlgorithm
      tableStrategy:
        hint:
          algorithmClassName: cn.zwqh.shardingspheredemo6.hint.MyHintAlgorithm
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
        props:
          worker.id: 123
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
  bindingTables:
    - t_order,t_order_item
  broadcastTables:
    - t_address

  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id % 2}
  defaultTableStrategy:
    inline:
      shardingColumn: order_id
      algorithmExpression: t_order_item_${order_id % 2}

props:
  sql.show: true

hint-master-only.yaml 读写分离配置文件

dataSources:
  ds_master: !!com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/sharding_sphere_0
    username: root
    password: 123456
  ds_slave_0: !!com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3307/sharding_sphere_1
    username: root
    password: 123456

masterSlaveRule:
  name: ds_ms
  masterDataSourceName: ds_master
  slaveDataSourceNames: [ds_slave_0]

props:
  sql.show: true

MyHintAlgorithm 自定义分片算法

/**
 * Hint分片算法需要用户实现org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm接口。
 * ShardingSphere在进行Routing时，如果发现LogicTable的TableRule采用了 Hint的分片算法，将会从HintManager中获取分片值进行路由操作。
 */
@Component
public class MyHintAlgorithm implements HintShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(final Collection<String> availableTargetNames, final HintShardingValue<Long> shardingValue) {
        Collection<String> result = new ArrayList<>();
        for (String each : availableTargetNames) {
            System.out.println("---------------each"+each);
            for (Long value : shardingValue.getValues()) {
                if (each.endsWith(String.valueOf(value % 2))) {
                    result.add(each);
                }
            }
        }
        return result;
    }
}

7、测试类

HintMain

public class HintMain {
    /**
     * 分库分表
     */
    private static final HintType TYPE = HintType.DATABASE_TABLES;
    /**
     * 分库
     */
    //private static final HintType TYPE = HintType.DATABASE_ONLY;

    /**
     * 读写分离
     */
    //private static final HintType TYPE = HintType.MASTER_ONLY;

    /**
     * demo运行入口
     *
     * @param args
     * @throws IOException
     * @throws SQLException
     */
    public static void main(String[] args) throws IOException, SQLException {
        DataSource dataSource = getDataSource();
        ExampleService exampleService = getExampleService(dataSource);
        exampleService.initEnvironment();
        exampleService.processSuccess();
        processWithHintValue(dataSource);
//        exampleService.cleanEnvironment();
    }

    /**
     * 根据TYPE创建不同配置的数据源
     *
     * @return
     * @throws IOException
     * @throws SQLException
     */
    private static DataSource getDataSource() throws IOException, SQLException {
        switch (TYPE) {
            case DATABASE_TABLES:
                return YamlShardingDataSourceFactory.createDataSource(getFile("/META-INF/hint-databases-tables.yaml"));
            case DATABASE_ONLY:
                return YamlShardingDataSourceFactory.createDataSource(getFile("/META-INF/hint-databases-only.yaml"));
            case MASTER_ONLY:
                return YamlMasterSlaveDataSourceFactory.createDataSource(getFile("/META-INF/hint-master-only.yaml"));
            default:
                throw new UnsupportedOperationException("unsupported type");
        }
    }

    /**
     * 获取配置文件
     *
     * @param configFile 绝对路径或者相对路径都可
     * @return
     */
    private static File getFile(final String configFile) {
        return new File(Thread.currentThread().getClass().getResource(configFile).getFile());
    }

    private static ExampleService getExampleService(final DataSource dataSource) {
        return new OrderServiceImpl(dataSource);
    }

    private static void processWithHintValue(final DataSource dataSource) throws SQLException {
        try (HintManager hintManager = HintManager.getInstance();
             Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            setHintValue(hintManager);
            statement.execute("select * from t_order");
            statement.execute("SELECT i.* FROM t_order o, t_order_item i WHERE o.order_id = i.order_id");
            statement.execute("select * from t_order_item");
            statement.execute("INSERT INTO t_order (user_id, address_id, status) VALUES (1, 1, 'init')");
        }
    }

    private static void setHintValue(final HintManager hintManager) {
        switch (TYPE) {
            case DATABASE_TABLES:
            		//设置数据源分片键值
                hintManager.addDatabaseShardingValue("t_order", 0L);
             		//设置表分片键值
                hintManager.addTableShardingValue("t_order", 1L);
                return;
            case DATABASE_ONLY:
            		//设置数据源分片键值
                hintManager.setDatabaseShardingValue(1L);
                return;
            case MASTER_ONLY:
            		//设置主库路由
                hintManager.setMasterRouteOnly();
                return;
            default:
                throw new UnsupportedOperationException("unsupported type");
        }
    }
}

添加分片键值

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

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

设置主库路由

• 使用hintManager.setMasterRouteOnly设置主库路由。

清除分片键值

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

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

JDK 8 中实现资源的自动关闭，要求所有资源必须在try子句中初始化。

8、测试结果

在执行过程中，如果没有指定分片算法，则使用默认分片策略。

只有使用HintManager，并进行相应的设置，才能使用指定的分片算法，否则使用默认分片策略。

源码地址

github

码云