sharding-sphere Hint强制路由实现方案（springboot）

设计方案

由于业务需求，需要实现水平分表分库，筛选市面技术后，计划使用sharding-sphere进行方案实现，
分片方式为根据省份进行分库，根据地市进行分表，既同一省份中的地市在同一库中，省份库中按地市
进行分表。分片键为orgNo，sharding-sphere中的的分片算法，标准分片，和复杂分片算法，都是需要保证物理表中存在分片键orgNo，并且考虑到我们是不需要全局路由的情况，既如果要使用标准分片，复杂分片的话每个sql中必须要传入orgNo，这样会使开发中不方便，所以考虑使用强制路由 Hint算法，实现分片策略

一、引入必要依赖

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.20</version>
        </dependency>

二、添加配置

#Mon Jul 19 15:00:47 CST 2021
#Persisted by DefaultConfig
#Mon Aug 23 09:19:32 CST 2021
#定义数据源
spring.shardingsphere.datasource.names=oc01,oc02
#数据源oc01
spring.shardingsphere.datasource.oc01.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.oc01.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.oc01.url=jdbc:mysql://localhost:3306/ds01?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.oc01.username=root
spring.shardingsphere.datasource.oc01.password=root
spring.shardingsphere.datasource.oc01.testOnBorrow=false
spring.shardingsphere.datasource.oc01.connectionProperties=druid.stat.mdergeSql\=true;druid.stat.slowSqlMillis\=5000
spring.shardingsphere.datasource.oc01.initialSize=5
spring.shardingsphere.datasource.oc01.maxActive=20
spring.shardingsphere.datasource.oc01.maxPoolPreparedStatementPerConnectionSize=20
spring.shardingsphere.datasource.oc01.minEvictableIdleTimeMillis=300000
spring.shardingsphere.datasource.oc01.timeBetweenEvictionRunsMillis=60000
spring.shardingsphere.datasource.oc01.minIdle=5
spring.shardingsphere.datasource.oc01.maxWait=5000
spring.shardingsphere.datasource.oc01.poolPreparedStatements=true
spring.shardingsphere.datasource.oc01.testOnReturn=false
spring.shardingsphere.datasource.oc01.filters=stat
spring.shardingsphere.datasource.oc01.validationQuery=SELECT 1 FROM DUAL
#数据源oc02
spring.shardingsphere.datasource.oc02.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.oc02.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.oc02.url=jdbc:mysql://localhost:3306/ds02?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.oc02.username=root
spring.shardingsphere.datasource.oc02.password=root
spring.shardingsphere.datasource.oc02.testOnBorrow=false
spring.shardingsphere.datasource.oc02.connectionProperties=druid.stat.mdergeSql\=true;druid.stat.slowSqlMillis\=5000
spring.shardingsphere.datasource.oc02.initialSize=5
spring.shardingsphere.datasource.oc02.maxActive=20
spring.shardingsphere.datasource.oc02.maxPoolPreparedStatementPerConnectionSize=20
spring.shardingsphere.datasource.oc02.minEvictableIdleTimeMillis=300000
spring.shardingsphere.datasource.oc02.timeBetweenEvictionRunsMillis=60000
spring.shardingsphere.datasource.oc02.minIdle=5
spring.shardingsphere.datasource.oc02.maxWait=5000
spring.shardingsphere.datasource.oc02.poolPreparedStatements=true
spring.shardingsphere.datasource.oc02.testOnReturn=false
spring.shardingsphere.datasource.oc02.filters=stat
spring.shardingsphere.datasource.oc02.validationQuery=SELECT 1 FROM DUAL

#t_order 路由范围
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=oc$->{['oc01.t_order_01','oc02.t_order_02']}
#t_order 表 Hint 分表路由
spring.shardingsphere.sharding.tables.t_order.table-strategy.hint.algorithm-class-name=com.shtdhr.shardingsphere.config.MyHintShardingAlgorithm
#默认 Hint分库路由
spring.shardingsphere.sharding.default-database-strategy.hint.algorithm-class-name=com.shtdhr.shardingsphere.config.MyHintShardingAlgorithm
#sql 日志打印
spring.shardingsphere.props.sql.show=true

三、代码实现

1、MyHintShardingAlgorithm.java

package com.shtdhr.shardingsphere.config;

import org.apache.shardingsphere.api.hint.HintManager;
import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;

import java.util.ArrayList;
import java.util.Collection;

public class MyHintShardingAlgorithm implements HintShardingAlgorithm<String> {
    /**
     * @Author shtdhr
     * @Description  Hint方式路由算法
     * @Date 2021/8/23 0023 下午 5:42
     * @Param availableTargetNames 分片的路由范围（库+表）HintType.DATABASE_TABLES 情况下调用此方法，HintType.DATABASE_ONLY不调用
     * @Param shardingValue 分片值
     * @return Collection<String>
     */
    @Override
    public Collection<String> doSharding(final Collection<String> availableTargetNames, final HintShardingValue<String> shardingValue) {
        System.out.println("shardingValue=" + shardingValue);
        System.out.println("availableTargetNames=" + availableTargetNames);
        Collection<String> result = new ArrayList<>();
        for (String each : availableTargetNames) {
            for (String value : shardingValue.getValues()) {
                if (each.endsWith(String.valueOf(value).toLowerCase())) {
                    result.add(each);
                }
            }
        }
        return result;
    }
/**
 * @Author shtdhr
 * @Description  Hint方式设置路由规则
 * @Date 2021/8/23 0023 下午 5:39
 * @Param  hintManager
 * @Param  TYPE 分片类型 DATABASE_TABLES 分库分表，DATABASE_ONLY 只分库
 * @Param  tableName 分片表（需要分表的表名）
 * @Param  value 分片值 orgNo (具体看自己需求)
 * @return
 */
    public static void setHintValue(final HintManager hintManager, HintType TYPE, String tableName, String value) {
        switch (TYPE) {
            case DATABASE_TABLES:
                hintManager.addDatabaseShardingValue(tableName, value);
                hintManager.addTableShardingValue(tableName, value);
                return;
            case DATABASE:
                hintManager.addDatabaseShardingValue(tableName, value);
                return;
            case TABLES:
                hintManager.addTableShardingValue(tableName, value);
                return;
            case DATABASE_ONLY:
                hintManager.setDatabaseShardingValue(value);
                return;
            default:
                throw new UnsupportedOperationException("unsupported type");
        }
    }

}

2、HintType.java

package com.shtdhr.shardingsphere.config;
/**
 * @Author shtdhr
 * @Description 数据库分片方式
 * @Date 2021/8/23 0023 下午 5:56
 * @Param
 * @return
 */
public enum HintType {
    //只分库
    DATABASE_ONLY,
    //分库分表
    DATABASE_TABLES,
    //分库
    DATABASE,
    //分表
    TABLES
}

3、ShardingJDBCAop.java

package com.shtdhr.shardingsphere.config;

import com.alibaba.fastjson.JSON;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.api.hint.HintManager;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.Signature;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.aspectj.lang.reflect.SourceLocation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.Map;
/**
 * @Author shtdhr
 * @Description 数据库分页切面（前置加后置），动态增加 数据库分页 相关信息
 * @Date 2021/8/23 0023 下午 5:52
 * @Param
 * @return
 */
@Aspect
@Component
public class ShardingJDBCAop {
    private HintManager hintManager;
    @Autowired
    private XXXX userBiz;
    private Map<String, String> dbOrgNoRuleMap;
    //数据库分库时，地市orgNo与省份orgNo对应关系，key为地市orgNo，value为省份orgNo
    @Value("${shtdhr.sharding.dborgnomap:#{null}}")
    public void setDbOrgNoRuleMap(String dbOrgNoRuleMapStr) {
        if (StringUtils.isNotBlank(dbOrgNoRuleMapStr)) {
            Map<String, String> orgNoMap = JSON.parseObject(dbOrgNoRuleMapStr, Map.class);
            dbOrgNoRuleMap = orgNoMap;
        } else {
            dbOrgNoRuleMap = new HashMap<>();
            dbOrgNoRuleMap.put("SS01", "SS01");
            dbOrgNoRuleMap.put("SS02", "SS01");
            dbOrgNoRuleMap.put("SS03", "SS01");
            dbOrgNoRuleMap.put("SS04", "SS01");
            dbOrgNoRuleMap.put("SS05", "SS01");

            dbOrgNoRuleMap.put("SC01", "SC01");
            dbOrgNoRuleMap.put("SC02", "SC01");
            dbOrgNoRuleMap.put("SC03", "SC01");
            dbOrgNoRuleMap.put("SC04", "SC01");
            dbOrgNoRuleMap.put("SC05", "SC01");
        }

    }

    @Pointcut("@annotation(com.shtdhr.shardingsphere.config.TableJ) ||( execution(* com.shtdhr.*.mapper.*.*(..))) &&  !execution(* com.shtdhr.order.mapper.*.*(..))))")
    public void pc() {
    }

    @Before("pc()")
    public void tableJBefore(JoinPoint joinPoint) {
        MethodSignature signature  = (MethodSignature)  joinPoint.getSignature();
        Method method = signature.getMethod();
        TableJ annotation = method.getAnnotation(TableJ.class);
        String tableName = "";
        if(annotation!=null){
            tableName = annotation.value();
        }
        XXXXX
        String dbOrgNo = getDbOrgNo(XXXXX );
        //获取HintManager实例，Hint算法需要每次在执行sql前添加两个方法
        //1、HintManager.getInstance();
        //2、hintManager.addDatabaseShardingValue(tableName, value);hintManager.addTableShardingValue(tableName, value);
        //或 hintManager.setDatabaseShardingValue(value);
        // 方法已封装到MyHintShardingAlgorithm.setHintValue中
        //获取HintManager实例
        if(hintManager!=null){
            hintManager.close();
        }
        hintManager = HintManager.getInstance();
        if(StringUtils.isBlank(tableName)){
            //tableName 为空，则只有分库路由
            MyHintShardingAlgorithm.setHintValue(hintManager, HintType.DATABASE_ONLY, "", dbOrgNo);
        }else{
            //tableName 不为空，则分库，分表路由
            XXXXX
            //指定分库规则
            MyHintShardingAlgorithm.setHintValue(hintManager, HintType.DATABASE, tableName, dbOrgNo);
            //指定分表规则
            MyHintShardingAlgorithm.setHintValue(hintManager, HintType.TABLES, tableName, tableOrgNo);
        }
    }

    @After("pc()")
    public void after() {
        //每次执行完sql，执行hintManager.close()，防止自动关闭失败
        if(hintManager!=null){
            hintManager.close();
        }
    }
    private String getDbOrgNo( XXXXX){
        //获取当前用户orgNo，根据orgNo指定分片路由
        XXXX
        //获取地市一级orgNo的省一级路由
        orgNo = dbOrgNoRuleMap.get(XXXX);
        return orgNo;
    }
}

4、AspectConfig.java

package com.shtdhr.shardingsphere.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.context.annotation.Import;
/**
 * @Author shtdhr
 * @Description 数据库分片切面
 * @Date 2021/8/23 0023 下午 5:56
 * @Param
 * @return
 */
@EnableAspectJAutoProxy
@Configuration
@Import(ShardingJDBCAop.class)
public class AspectConfig {
}

5、TableJ.java

package com.shtdhr.shardingsphere.config;

import java.lang.annotation.*;

/**
 * @Target 此注解的作用目标，括号里METHOD的意思说明此注解只能加在方法上面
 * @Retention 注解的保留位置，括号里RUNTIME的意思说明注解可以存在于运行时，可以用于反射
 * @Documented 说明该注解将包含在javadoc中
 */

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TableJ {
    String value() default "";
}

六、特定表分片 用注解@TableJ（“tablename”）

    @Override
    @TableJ("t_order")
    public PageResponse<Order> findListPage(PageRequest<Order> pageRequest, UserVo userVo) {
        }