Mybatis自定义插件实现分表_mybatis分表插件-优快云博客

本文链接：https://blog.youkuaiyun.com/abc2675811870/article/details/143742112

Mybatis自定义插件实现分表功能

背景

因为公司项目中需要使用到简单分表的功能，但是使用sharding-jdbc组件比较庞大，并且公司大多数项目都要支持各种国产数据库，比如达梦，大金、华为等等，用sharding-jdbc不支持一些国产数据库。

支持功能

1、带分片参数的增删改
2、查询和支持pageHelper分页查询（带不带分片参数都支持）
3、可以结合tkMapper和pageHelper无缝使用
后续会进行多数据源分库功能添加和范围查询优化（between、in等，面前这些范围是扫所有表。union all）

设计思路

1、因为是简单的分表功能，所以不考虑模仿sharding-jdbc的写法（重写DataSource、Conncetion连接等等），用mybatis插件也可以实现分表的简单功能，分表需要支持分页pageHelper插件功能。

2、分表，肯定要指定字段和分表策略。可以通过注解的方式来指定每个sql是否执行分表，以及分表的策略和字段。如果不使用注解，则不进行分表功能。

3、在Mybatis插件中，需要做的事情有：

判断当前语句是否开启分表功能
分表字段和参数值对应提取
获取原sql，识别到原sql的逻辑表名，根据自定义的分表策略，将逻辑表名替换成真实表名
如果有参数，可能要进行参数的拼接，比如根据时间去分表，但是你没带时间查询，带了其他条件，这时候需要遍历所有表，使用union all连接。比如select * from order_2024_11 where title = ? union all select * from order_2024_12
分页处理，使用pageHelper的分页功能，在插件中获取的sql可能不支持你直接拼接，需要对分页的sql进行特殊处理（后续会体现到）
需要将处理完的sql和参数重新通过反射功能设置到对应Mybatis实体，后续流程交给Mybatis自动处理。

项目依赖：

<properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.6.13</spring-boot.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.1.5</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!--druid依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.3</version>
        </dependency>

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.10</version>
        </dependency>

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

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

代码拆解

代码有点多…也使用了一些设计模式，工厂、模板、策略、建造者

1、分表策略接口

public interface TableStrage {

    //根据传进来的参数，返回表名或者库名
    String assignTableNamePrefix(Object paramValue);
}

这里的参数，指定是mapper接口传递的param参数

实现子类目前提供了mod取余分片和时间分片，业务人员也可以自定义拓展，只需实现TableStrage接口即可。

@Component
public class ModNumberTableStrage implements TableStrage {
    private Integer number = 2;

    @Override
    public String assignTableNamePrefix(Object paramValue) {
        if((paramValue instanceof Long || paramValue instanceof Integer) && ((Long)paramValue) >= 0){
            return String.valueOf((Long) paramValue % number);
        }else{
            throw new ClassCastException("必须传入正整数类型！");
        }
    }
}

/**
 * @date 2024-11-04 17:22
 * 时间表达式策略，根据传进来的时间格式进行分片，默认：YYYY_MM，可以自行指定express表达式
 */
@Component
public class TimeExprTableStrage implements TableStrage {
    private String express = "YYYY_MM";

    @Override
    public String assignTableNamePrefix(Object paramValue) {
        if(paramValue instanceof Date){
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat(express);
            return simpleDateFormat.format( (Date)paramValue);
        }else{
            throw new ClassCastException("必须传入日期类型");
        }
    }
}

2、分表注解定义：ShardingRouter

/**
 * 分库分表使用注解 用于mapper语句上
 */

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.METHOD})
public @interface ShardingRouter {
    //是否开启分表
    boolean splitTable() default false;

    //分表字段
    String splitTableKey() default "";

    //是否开启between、in、>、 <、 >=、 <=的范围操作
    TypeEnum rangeSearch() default TypeEnum.NO_RANGE_SEARCH;

    //是否开启分库：目前没使用分库功能，可以不加。后面考虑加上动态数据源
    boolean splitDB() default false;

    //分库字段：目前没使用分库功能，可以不加。后面考虑加上动态数据源
    String splitDBKey() default "";

    //分表策略
    Class<? extends TableStrage> tableStrageClazz();

    //真实表列举，例如：["order_2024_08", "order_2024_09"]
    String[] sourceTableNames() default {};

    //真实表列举扩展接口：如果真实表难以列举，比如按年月动态的。可以让业务人员自定义实现SourceTableNameExt接口，注入到spring容器中
    Class<? extends SourceTableNameExt> sourceTablesExt() default NoneSourceNameExt.class;
}

定义获取所有真实表表名的接口（用于后续自定义扩展，在注解中使用sourceTablesExt属性）

/**
 * @date 2024-11-04 21:48
 * 自定义策略获取当前mapper的所有来源表
 */
public interface SourceTableNameExt {
    public String[] sourceTableNames();
}

3、为了获取到pageHelper分页处理之前的原始sql，用了ThreadLocal存储原始sql。

public class SqlSourceContextHolder {

    private static ThreadLocal<String> sqlContextHolder = new ThreadLocal<>();
	
	//设置sql
    public static void setContext(String sourceSql){
        sqlContextHolder.set(sourceSql);
    }
	
	//清除sql
    public static void clearContext(){
        sqlContextHolder.remove();
    }
	
	//获取当前sql
    public static String getContext(){
        return sqlContextHolder.get();
    }

}

4、自定义Mybatis插件，针对Excutor组件类型

这个插件的功能目前仅仅是保存原始sql，在未被pageHelper处理之前，后面就会用到这个保存的原始sql。

[!NOTE]

Mybatis的插件可拦截的类型有Excutor（最先执行，在sql执行前）
StatmentHandler（excutor插件执行完后会执行此类型的插件，可以进行sql的重写和参数的参数）
ParameterHandler：对参数设置进行增强，一般不进行使用
ResultSetHandler：对返回值设置进行增强，一般不进行使用

在这里插入图片描述

/**
 * 为了分页操作，保存原始sql。
 */
@Intercepts({
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})
})
public class ShardingExcutePlugin implements Interceptor {
    
    private String dialect;

    public String getDialect() {
        return dialect;
    }

    public void setDialect(String dialect) {
        this.dialect = dialect;
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        try {
            //获取参数
            Object[] args = invocation.getArgs();
            MappedStatement ms = (MappedStatement) args[0];
            Object parameter = args[1];

            BoundSql boundSql = ms.getBoundSql(parameter);
            String originalSql = boundSql.getSql();
            //获取到原始sql，将sql保存在当前上下文中
            SqlSourceContextHolder.setContext(this.dialect + Constant.MS_ID_SQL_SPLIT + ms.getId() + Constant.MS_ID_SQL_SPLIT  + originalSql);
            // 执行原始方法
            return invocation.proceed();
        }finally {
            //清除当前上下文
            SqlSourceContextHolder.clearContext();
        }


    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    /**
     * 设置SQL方言
     * @param properties
     */
    @Override
    public void setProperties(Properties properties) {
        // 设置sql方言，为了后续pageHelper分页的sql语句处理
        this.dialect = properties.getProperty("dialect");
    }
    
}

5、自定义Mybatis插件，针对StatmentHandler类型
这里的代码就比较复杂了，比如sql获取，解析，参数替换等

/**
 * Mybatis 拦截器，通过对 SQL 语句的拦截处理，修改分表信息
 */
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class ShardingPlugin implements Interceptor {

    private static final ReflectorFactory defaultReflectorFactory = new DefaultReflectorFactory();

    private static final Logger logger = LoggerFactory.getLogger(ShardingPlugin.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // MetaObject是mybatis里面提供的一个工具类，类似反射的效果
        MetaObject metaObject = getMetaObject(invocation);
        BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
        // 获取 Configuration 对象
        Configuration configuration = getConfiguration(invocation);
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        ShardingRouter shardingRouter = getTableShardAnnotation(mappedStatement);

        //如果没有使用分片路由注解，直接跳过
        if(Objects.isNull(shardingRouter) || !shardingRouter.splitTable()){
            return invocation.proceed();
        }

        String tableKey = shardingRouter.splitTableKey();
        Assert.notEmpty(tableKey, "必须选择逻辑表的分片键！");

        //根据分片键、逻辑表、分片策略等信息
        Object splitKeyValue = getParamValue(invocation, tableKey);

        TableStrage tableStrage = null;
        String tableNamePrefix = null;
        String sql = boundSql.getSql();
        // 替换SQL表名 USER 为 USER_001
        Matcher matcher = Constant.pattern.matcher(sql);
        String tableName = null;
        String operationType = null;
        if (matcher.find()) {
            operationType = matcher.group(1);
            tableName = matcher.group(2).trim();
        }
        String replaceSql = null;

        AbstartConvertSQLStrage convertSQLStrage = new ConvertSqlStrageFactory().matcher(matcher)
                .mapperStatementInfo(new MapperStatementInfo(mappedStatement.getSqlCommandType(), boundSql, configuration, metaObject))
                .tableEntity(new TableEntity(shardingRouter.tableStrageClazz(), splitKeyValue, shardingRouter, tableName, operationType, tableNamePrefix))
                .builder();

        replaceSql = convertSQLStrage.convertedShardingSql();

        //如果判断需要更新sql，通过反射修改SQL语句
        if(StringUtils.hasText(replaceSql)){
            logger.info("分表前的sql：{}", sql);
            Field field = boundSql.getClass().getDeclaredField("sql");
            field.setAccessible(true);
            field.set(boundSql, replaceSql);
            field.setAccessible(false);
            logger.info("分表后的sql：{}", replaceSql);
        }


        return invocation.proceed();
    }

    public Configuration getConfiguration(Invocation invocation){
        //         获取方法参数
        Object[] args = invocation.getArgs();

        // 获取 StatementHandler 对象
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();

        // 获取实际的 StatementHandler 实现类
        while (statementHandler instanceof org.apache.ibatis.executor.statement.RoutingStatementHandler) {
            MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
            statementHandler = (StatementHandler) metaObject.getValue("delegate");
        }

        // 获取 MetaObject 对象
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        // 获取 Configuration 对象
        Configuration configuration = (Configuration) metaObject.getValue("configuration");

        return configuration;
    }
    public Object getParamValue(Invocation invocation, String key){
//         获取方法参数
        Object[] args = invocation.getArgs();

        // 获取 StatementHandler 对象
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();

        // 获取实际的 StatementHandler 实现类
        while (statementHandler instanceof org.apache.ibatis.executor.statement.RoutingStatementHandler) {
            MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
            statementHandler = (StatementHandler) metaObject.getValue("delegate");
        }

        // 获取 MetaObject 对象
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);

        // 获取 BoundSql 对象
        BoundSql boundSql = (BoundSql) metaObject.getValue("boundSql");

        // 获取 SQL 语句
        String sql = boundSql.getSql();
        logger.info("Executing SQL: {}", sql);

        // 获取参数对象
        Object parameterObject = boundSql.getParameterObject();

        // 获取参数映射列表
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();

        // 获取 Configuration 对象
        Configuration configuration = (Configuration) metaObject.getValue("configuration");

        // 获取参数值
        Map<String, Object> parameterValues = getParameterValues(parameterObject, parameterMappings, configuration);

        return parameterValues.get(key);
    }



    Object parseKeyValue(){
        return null;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }


    /**
     * 获取参数值
     *
     * @param parameterObject 参数对象
     * @param parameterMappings 参数映射列表
     * @param configuration Configuration 对象
     * @return 参数值映射
     */
    private Map<String, Object> getParameterValues(Object parameterObject, List<ParameterMapping> parameterMappings, Configuration configuration) {
        Map<String, Object> parameterValues = new HashMap<>();
        TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();

        for (ParameterMapping parameterMapping : parameterMappings) {
            String propertyName = parameterMapping.getProperty();
            if (propertyName != null && !propertyName.isEmpty()) {
                Object value = getParameterValue(parameterObject, propertyName, typeHandlerRegistry);
                if (value != null) {
                    parameterValues.put(propertyName, value);
                }
            }
        }
        return parameterValues;
    }

    /**
     * 获取单个参数值
     *
     * @param parameterObject 参数对象
     * @param propertyName 参数名
     * @param typeHandlerRegistry 类型处理器注册表
     * @return 参数值
     */
    private Object getParameterValue(Object parameterObject, String propertyName, TypeHandlerRegistry typeHandlerRegistry) {
        if (parameterObject instanceof Map) {
            return ((Map<?, ?>) parameterObject).get(propertyName);
        } else {
            MetaObject metaObject = SystemMetaObject.forObject(parameterObject);
            return metaObject.getValue(propertyName);
        }
    }


    /**
     * 获取方法上的SHar注解
     *
     * @param mappedStatement MappedStatement
     * @return TableShard注解
     */
    private ShardingRouter getTableShardAnnotation(MappedStatement mappedStatement) {
        ShardingRouter tablePrepare = null;
        ShardingRouter clazzAnnotation = null;
        try {
            String id = mappedStatement.getId();
            String className = id.substring(0, id.lastIndexOf("."));
            String methodName = id.substring(id.lastIndexOf(".") + 1);
            Class<?> clazz = Class.forName(className);
            //如果是pageHelper的分页count语句，methodName默认会加上_COUNT的后缀
            if(methodName.endsWith(Constant.COUNT_METHOD_SUFFIX)){
                //需要去掉这个后缀才能拿到原始method，进而拿到原始method的注解
                methodName = methodName.replace(Constant.COUNT_METHOD_SUFFIX, "");
            }

            final Method[] method = clazz.getMethods();
            //类注解
            clazzAnnotation = clazz.getAnnotation(ShardingRouter.class);
            for (Method me : method) {
                if (me.getName().equals(methodName) && me.isAnnotationPresent(ShardingRouter.class)) {
                    //方法注解
                    tablePrepare = me.getAnnotation(ShardingRouter.class);
                    break;
                }
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        //如果方法注解没有，类注解有，使用类注解
        return tablePrepare == null ? clazzAnnotation : tablePrepare;
    }


    /**
     * @description: 获取MetaObject对象-mybatis里面提供的一个工具类，类似反射的效果
     * @param invocation
     * @return: MetaObject
     */
    private MetaObject getMetaObject(Invocation invocation) {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        // MetaObject是mybatis里面提供的一个工具类，类似反射的效果
        MetaObject metaObject = MetaObject.forObject(statementHandler,
                SystemMetaObject.DEFAULT_OBJECT_FACTORY,
                SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,
                defaultReflectorFactory
        );

        return metaObject;
    }


    @Override
    public void setProperties(Properties properties) {

    }

}

6、查询类型枚举类：

public enum TypeEnum {

    /**
     * 范围查询
     */
    RANGE_SEARCH(1),

    /**
     * 非范围查询
     */
    NO_RANGE_SEARCH(0);

    private int value;

    TypeEnum(int value) {
        this.value = value;
    }

    public int getValue() {
        return value;
    }
}

7、Spring容器上下文存储类，用于在mybatis插件中可以获取到bean策略对象

@Component
public class ApplicationContextHolder implements ApplicationContextAware {
    private static ApplicationContext applicationContext;

    public static ApplicationContext getContext(){
        return applicationContext;
    }

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        this.applicationContext = applicationContext;
    }
}

8、逻辑表sql处理替换成真实表的处理类

抽象父类 AbstartConvertSQLStrage：

public abstract class AbstartConvertSQLStrage {
    public AbstartConvertSQLStrage(SqlCommandType sqlCommandType, Matcher matcher, MapperStatementInfo mapperStatementInfo, TableEntity tableEntity) {
        this.sqlCommandType = sqlCommandType;
        this.matcher = matcher;
        this.tableEntity = tableEntity;
        this.mapperStatementInfo = mapperStatementInfo;
    }


    /**
     * 查询类型：<SELECT UPDATE INSERT DELETE>.
     */
    protected SqlCommandType sqlCommandType;

    /**
     * 获取表名的正则匹配：(from|into|update)[\s]{1,}(\w{1,})
     */
    protected Matcher matcher;

    protected MapperStatementInfo mapperStatementInfo;

    /**
     * 表信息
     */
    protected TableEntity tableEntity;


    private static final String SHARDING_TABLE_SPLIT = "_";





    /**
     * 根据当前的分片键和值，以及不同的SQL操作类型，重写分片后的SQL。
     * 模板方法模式，交由不同的子类去执行
     * @return
     */
     public String convertedShardingSql(){
         //如果有分片键值或者不是范围查询
         if(Objects.nonNull(tableEntity.getSplitKeyValue())){
             //在容器中拿到表分片策略实现类
             TableStrage tableStrage  = ApplicationContextHolder.getContext().getBean(tableEntity.getShardingRouter().tableStrageClazz());
             //根据分片策略获取到表名前缀
             String tableNamePrefix = tableStrage.assignTableNamePrefix(tableEntity.getSplitKeyValue());
             //替换sql
             Assert.notEmpty(tableEntity.getTableName(), "获取到的sql语句的表名为空！");
             String replaceSql = matcher.replaceAll(tableEntity.getOperationType() + " " + tableEntity.getTableName() + SHARDING_TABLE_SPLIT + tableNamePrefix);
             return replaceSql;
         }else{
             return this.doConvertSQLNoSplitValue();
         }

     }

    /**
     * 判断是否是pageHelper的count语句
     * @param sql
     * @return
     */
     public boolean isPageCountSql(String sql){
         return sql.endsWith(Constant.PAGE_COUNT_SQL_SUFFIX);
     }

    /**
     * 条件没有带分片键的转化sql的方法
     * @return
     */
    protected abstract String doConvertSQLNoSplitValue();

    public SqlCommandType getSqlCommandType() {
        return sqlCommandType;
    }

    public Matcher getMatcher() {
        return matcher;
    }

    public MapperStatementInfo getMapperStatementInfo() {
        return mapperStatementInfo;
    }

    public TableEntity getTableEntity() {
        return tableEntity;
    }
}

处理子类有增删改子类OperationConvertSQLStrage和查询处理子类SelectConvertSQLStrage

/**
 * 增删改 方法转化分片SQL策略，不带分片参数直接抛错
 * @date 2024-11-08 16:37
 */
public class OperationConvertSQLStrage extends AbstartConvertSQLStrage{

    public OperationConvertSQLStrage(SqlCommandType sqlCommandType, Matcher matcher, MapperStatementInfo mapperStatementInfo, TableEntity tableEntity) {
        super(sqlCommandType, matcher, mapperStatementInfo, tableEntity);
    }

    @Override
    protected String doConvertSQLNoSplitValue() {
       throw new UnsupportedOperationException("增删改操作必须带分配键值！");
    }

}


/**
 * select 方法转化分片SQL策略
 * @author cqz
 * @date 2024-11-08 16:37
 */
public class SelectConvertSQLStrage extends AbstartConvertSQLStrage{

    public SelectConvertSQLStrage(SqlCommandType sqlCommandType, Matcher matcher, MapperStatementInfo mapperStatementInfo, TableEntity tableEntity) {
        super(sqlCommandType, matcher, mapperStatementInfo, tableEntity);
    }

    @Override
    protected String doConvertSQLNoSplitValue() {
        TableEntity table = getTableEntity();
        MapperStatementInfo statementInfo = getMapperStatementInfo();

        //不是pageHelper生成的分页count语句的情况下：必须传分片键对应参数值或者开启范围查询
//        if(!isPageCountSql(statementInfo.getBoundSql().getSql()) && Objects.isNull(table.getSplitKeyValue()) && table.getShardingRouter().rangeSearch() == TypeEnum.NO_RANGE_SEARCH){
//            throw new RuntimeException("必须传分片键对应的param参数或者开启范围查询！");
//        }

        //如果是查询操作的话，可以用union all方式，或者用拆分成多个语句执行
        String[] tableNames = table.getShardingRouter().sourceTableNames();
        StringBuilder selectSql = new StringBuilder();
        //扩展表的个数
        int extTableCount = 0;

        String sqlSourceContext = SqlSourceContextHolder.getContext();
        String[] split = sqlSourceContext.split(Constant.MS_ID_SQL_SPLIT);
        AbstartDialect dialect = DialectFactory.getDialect(split[0]);
        Assert.notNull(dialect,"ShardingExcutePlugin的properties的dialect必须指定！【mysql | oracle | sqlserver | pg】");

        Matcher sourceSqlMatcher = Constant.pattern.matcher(split[2]);
        for (int i = 0; i < tableNames.length; i++){
            //是否含有分页语句
            if(dialect.containPageSQL(mapperStatementInfo.getBoundSql().getSql())){
                //如果不是最后一个物理表的sql语句，将不含分页的原始的sql的逻辑名替换成物理名
                if(tableNames.length > 1 && i < tableNames.length - 1){
                    selectSql.append(sourceSqlMatcher.replaceAll(table.getOperationType() + " "  + tableNames[i]));
                }else if(i == tableNames.length - 1){
                    //如果是最后一个物理表的sql，将pageHelper分页处理后的sql的逻辑名替换成物理名
                    selectSql.append(matcher.replaceAll(table.getOperationType() + " "  + tableNames[i]));
                }
            }else{
                String replaceSql = matcher.replaceAll(table.getOperationType() + " " + tableNames[i]);
                //如果是pageHelper分页count查询条数的语句
                if(isPageCountSql(replaceSql)){
                    //拼接count语句开头
                    if(i == 0){
                        selectSql.append(Constant.PAGE_COUNT_SQL_PREFIX);
                    }
                    //拼接原始sql
                    selectSql.append(sourceSqlMatcher.replaceAll(table.getOperationType() + " "  + tableNames[i]));
                    //最后拼接count语句的结束
                    if(i == tableNames.length - 1){
                        selectSql.append(Constant.PAGE_COUNT_SQL_SUFFIX);
                    }
                }else{
                    //如果不是分页语句，故没被pageHelper处理过的sql，直接拼接sql。
                    selectSql.append(replaceSql);
                }

            }

            if(i != tableNames.length - 1) {
                selectSql.append(" union all ");
                extTableCount++;
            }
        }
        //如果是动态拼接sql，然后有其他条件的参数，需要进行参数的拼接，不然参数会对应不上sql
        buildNewParam(statementInfo.getBoundSql(),statementInfo.getConfiguration(),statementInfo.getMetaObject(),extTableCount);
        String replaceSql = selectSql.toString();
        return replaceSql;
    }


    /**
     * 如果没有分片键而传了其他的条件，需要调用此方法，配合union all 进行 参数添加处理
     * @param boundSql
     * @param configuration
     * @param  metaObject
     * @param extTableCount 扩展表的个数
     * @return
     */
    List<ParameterMapping> buildNewParam(BoundSql boundSql, Configuration configuration, MetaObject metaObject, int extTableCount){
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        List<ParameterMapping> newParameterMappings = null;

        if (parameterObject instanceof Map) {
            Map<String, Object> paramMap = (Map<String, Object>) parameterObject;

            //找出First_PageHelper和Second_PageHelper参数，要把分页参数放在参数数组的最后面！！！
            Object firstPageHelper = parameterMappings.stream().filter(param -> param.getProperty().equals(Constant.PAGEPARAMETER_FIRST)).findFirst().orElse(null);
            Object secondPageHelper = parameterMappings.stream().filter(param -> param.getProperty().equals(Constant.PAGEPARAMETER_SECOND)).findFirst().orElse(null);
            //将分页参数从数组参数中移除，防止新的参数数组copy进来
            parameterMappings.removeAll(Arrays.asList(firstPageHelper, secondPageHelper));

            newParameterMappings = new ArrayList<>(parameterMappings);

            for (ParameterMapping param : parameterMappings) {
                for (int i = 1; i <= extTableCount; i++) {
                    String property = param.getProperty();
                    String newProperty = property + i;

                    // 创建新的 ParameterMapping
                    ParameterMapping newParam = new ParameterMapping.Builder(configuration, newProperty, param.getTypeHandler())
                            .javaType(param.getJavaType())
                            .build();

                    // 添加新的 ParameterMapping 到列表
                    newParameterMappings.add(newParam);

                    // 更新参数对象中的值
                    paramMap.computeIfAbsent(newProperty, key ->  paramMap.get(property));
                }
            }

            //将第一个分页的参数添加进新的数组的最后面
            if(Objects.nonNull(firstPageHelper)){
                newParameterMappings.add((ParameterMapping) firstPageHelper);
            }

            //将第二个分页的参数添加进新的数组的最后面
            if(Objects.nonNull(secondPageHelper)){
                newParameterMappings.add((ParameterMapping) secondPageHelper);
            }

            // 更新 MetaObject 中的值
            metaObject.setValue("delegate.boundSql.parameterMappings", newParameterMappings);
            metaObject.setValue("delegate.boundSql.parameterObject", paramMap);
        }

        return newParameterMappings;
    }

}

sql处理策略工厂类，根据不同增删改查类型获取对应的处理类

/**
 * sql分片转化策略工厂
 * 根据不同类型创建不同策略类
 * @date 2024-11-08 17:11
 */

public class ConvertSqlStrageFactory {

    /**
     * 获取表名的正则匹配：(from|into|update)[\s]{1,}(\w{1,})
     */
    private Matcher matcher;

    private MapperStatementInfo mapperStatementInfo;

    /**
     * 表信息
     */
    private TableEntity tableEntity;



    public Matcher getMatcher() {
        return matcher;
    }

    public ConvertSqlStrageFactory matcher(Matcher matcher) {
        this.matcher = matcher;
        return this;
    }

    public MapperStatementInfo getMapperStatementInfo() {
        return mapperStatementInfo;
    }

    public ConvertSqlStrageFactory mapperStatementInfo(MapperStatementInfo mapperStatementInfo) {
        this.mapperStatementInfo = mapperStatementInfo;
        return this;
    }

    public TableEntity getTableEntity() {
        return tableEntity;
    }

    public ConvertSqlStrageFactory tableEntity(TableEntity tableEntity) {
        this.tableEntity = tableEntity;
        return this;
    }

    public AbstartConvertSQLStrage builder(){
        SqlCommandType sqlCommandType = mapperStatementInfo.getSqlCommandType();
        switch (mapperStatementInfo.getSqlCommandType()){
            case SELECT:
                return new SelectConvertSQLStrage(sqlCommandType,matcher,mapperStatementInfo,tableEntity);
            case UPDATE:
                return new OperationConvertSQLStrage(sqlCommandType,matcher,mapperStatementInfo,tableEntity);
            case INSERT:
                return new OperationConvertSQLStrage(sqlCommandType,matcher,mapperStatementInfo,tableEntity);
            case DELETE:
                return new OperationConvertSQLStrage(sqlCommandType,matcher,mapperStatementInfo,tableEntity);
            default:
                throw new UnsupportedOperationException("不支持的操作类型！");
        }
    }
}

sql信息封装的一些实体类，在sql处理过程中，需要的实体参数太多，拆分成多个实体参数（不重要，只是为了处理过程拆分实体属性）

/**
 * @date 2024-11-08 17:27
 */
public class MapperStatementInfo {
    /**
     * 查询类型：<SELECT UPDATE INSERT DELETE>.
     */
    protected SqlCommandType sqlCommandType;


    /**
     * mybatis中封装sql组件。包括sql语句、参数映射等信息
     */
    protected BoundSql boundSql;

    /**
     * mybatis配置类
     */
    protected Configuration configuration;

    /**
     * mybatis反射工具对象
     */
    protected MetaObject metaObject;

    public MapperStatementInfo(SqlCommandType sqlCommandType, BoundSql boundSql, Configuration configuration, MetaObject metaObject) {
        this.sqlCommandType = sqlCommandType;
        this.boundSql = boundSql;
        this.configuration = configuration;
        this.metaObject = metaObject;
    }

    public SqlCommandType getSqlCommandType() {
        return sqlCommandType;
    }

    public void setSqlCommandType(SqlCommandType sqlCommandType) {
        this.sqlCommandType = sqlCommandType;
    }

    public BoundSql getBoundSql() {
        return boundSql;
    }

    public void setBoundSql(BoundSql boundSql) {
        this.boundSql = boundSql;
    }

    public Configuration getConfiguration() {
        return configuration;
    }

    public void setConfiguration(Configuration configuration) {
        this.configuration = configuration;
    }

    public MetaObject getMetaObject() {
        return metaObject;
    }

    public void setMetaObject(MetaObject metaObject) {
        this.metaObject = metaObject;
    }
}

/**
 * @date 2024-11-08 17:17
 */
public class TableEntity {
    /**
     * 自定义分表策略类
     */
    private Class<? extends TableStrage> tableStrageClazz;

    /**
     * 分片键参数对应的值
     */
    private Object splitKeyValue;

    /**
     * 分片路由策略
     */
    private ShardingRouter shardingRouter;


    /**
     * 逻辑表名
     */
    private String tableName;

    /**
     * 操作类型符
     */
    private String operationType;
    /**
     * 表的前缀
     */
    private String tableNamePrefix;


    public TableEntity(Class<? extends TableStrage> tableStrageClazz, Object splitKeyValue, ShardingRouter shardingRouter, String tableName, String operationType, String tableNamePrefix) {
        this.tableStrageClazz = tableStrageClazz;
        this.splitKeyValue = splitKeyValue;
        this.shardingRouter = shardingRouter;
        this.tableName = tableName;
        this.operationType = operationType;
        this.tableNamePrefix = tableNamePrefix;
    }

    public Class<? extends TableStrage> getTableStrageClazz() {
        return tableStrageClazz;
    }

    public void setTableStrageClazz(Class<? extends TableStrage> tableStrageClazz) {
        this.tableStrageClazz = tableStrageClazz;
    }

    public Object getSplitKeyValue() {
        return splitKeyValue;
    }

    public void setSplitKeyValue(Object splitKeyValue) {
        this.splitKeyValue = splitKeyValue;
    }

    public ShardingRouter getShardingRouter() {
        return shardingRouter;
    }

    public void setShardingRouter(ShardingRouter shardingRouter) {
        this.shardingRouter = shardingRouter;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public String getOperationType() {
        return operationType;
    }

    public void setOperationType(String operationType) {
        this.operationType = operationType;
    }

    public String getTableNamePrefix() {
        return tableNamePrefix;
    }

    public void setTableNamePrefix(String tableNamePrefix) {
        this.tableNamePrefix = tableNamePrefix;
    }
}

为了处理不同的sql分页的语句，比如
mysql是limit ?,?
sqlServer是OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
具体处理是查看了pageHelper的源码
在这里插入图片描述
如果pageHelper版本不一样，对应的处理语句可能不同，这点需要根据环境去调整。

定义sql方言处理抽象类：AbstartDialect

/**
 * @date 2024-11-09 20:13
 * sql方言，目前用于判断是否是pageHelper的分页
 * 对不同sql方言分页语句的拆分拼接处理以及参数的copy
 */
public abstract class AbstartDialect{
     public abstract boolean supports(String dialect);

     public abstract boolean containPageSQL(String sql);

}

不同数据实现子类：

/**
 * @date 2024-11-09 21:28
 */
public class MysqlDialect extends AbstartDialect {
    @Override
    public boolean supports(String dialect) {
        return "mysql".equals(dialect);
    }

    @Override
    public boolean containPageSQL(String sql) {
        return sql.contains(" LIMIT ? ") || sql.contains(" LIMIT ?, ? ");
    }
}

/**
 * @date 2024-11-09 21:28
 */
public class OracleDialect extends AbstartDialect {
    @Override
    public boolean supports(String dialect) {
        return "oracle".equals(dialect);
    }

    @Override
    public boolean containPageSQL(String sql) {
        return sql.contains("SELECT * FROM ( ")
                && sql.contains(" SELECT TMP_PAGE.*, ROWNUM");
    }
}


/**
 * @date 2024-11-09 21:28
 */
public class PostgreSqlDialect extends AbstartDialect {
    @Override
    public boolean supports(String dialect) {
        return "pq".equals(dialect);
    }

    @Override
    public boolean containPageSQL(String sql) {
        return sql.contains(" LIMIT ?") || sql.contains(" LIMIT ? OFFSET ?");
    }
}

/**
 * @date 2024-11-09 21:28
 */
public class SqlServerDialect extends AbstartDialect {
    @Override
    public boolean supports(String dialect) {
        return "sqlserver".equals(dialect);
    }

    @Override
    public boolean containPageSQL(String sql) {
        return sql.contains(" OFFSET ? ROWS FETCH NEXT ? ROWS ONLY ");
    }
}

定义了sql方言工厂：DialectFactory
根据插件设置的properties的dialect属性来获取，项目中的插入需指定。后面的使用体现到

/**
 * @date 2024-11-09 21:49
 */
public class DialectFactory {
    private static List<AbstartDialect> dialects = new ArrayList<>();
    static {
        dialects.add(new MysqlDialect());
        dialects.add(new OracleDialect());
        dialects.add(new PostgreSqlDialect());
        dialects.add(new SqlServerDialect());
    }

    public static AbstartDialect getDialect(String dialect) {
        return dialects.stream().filter(d -> d.supports(dialect)).findFirst().orElse(null);
    }

}

常量类型提取：Constant

/**
 * @date 2024-11-09 20:19
 */
public interface Constant {
    //分页的id后缀
    String SUFFIX_PAGE = "_PageHelper";
    //count查询的id后缀
    String SUFFIX_COUNT = SUFFIX_PAGE + "_Count";
    //第一个分页参数
    String PAGEPARAMETER_FIRST = "First" + SUFFIX_PAGE;
    //第二个分页参数
    String PAGEPARAMETER_SECOND = "Second" + SUFFIX_PAGE;

    //sql语句拼接的分隔符
    String MS_ID_SQL_SPLIT = ":";

    //pageHelper的count语句的前缀
    String PAGE_COUNT_SQL_PREFIX = "select count(0) from (";

    //pageHelper的count语句的后缀
    String PAGE_COUNT_SQL_SUFFIX = ") tmp_count";

    //pageHelper的count语句的后缀
    String COUNT_METHOD_SUFFIX = "_COUNT";

    //查表名的正则表达式
    Pattern pattern = Pattern.compile("(from|into|update)[\\s]{1,}(\\w{1,})", Pattern.CASE_INSENSITIVE);
}

上面已经完成分表组件的实现代码，接下来就是应用一下改组件实现分表功能

分表简单使用

创建sql表，可以复制成多个表。示例就是按年和月分的表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for order_2024_05
-- ----------------------------
DROP TABLE IF EXISTS `order_2024_05`;
CREATE TABLE `order_2024_05` (
  `id` bigint NOT NULL,
  `content` varchar(255) DEFAULT NULL COMMENT '内容',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  `orderId` bigint NOT NULL COMMENT '工单id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SET FOREIGN_KEY_CHECKS = 1;

这个项目中用的是mybatis + tkmapper + pageHelper分页

1、application.yml设置


server:
  port: 8081
#数据源配置
spring:
  profiles:
    active: sharding
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource #Druid连接池
    url: jdbc:mysql://localhost:3306/coursedb?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=true&nullCatalogMeansCurrent=true
    username: root #数据库用户名
    password: root #数据库密码
    driver-class-name: com.mysql.cj.jdbc.Driver #mysql驱动
    initialSize: 10 #初始化连接池大小
    minIdle: 10 #初始化最小连接池数量
    maxActive: 100 #初始化最大连接池数量
    maxWait: 6000 #配置获取连接等待超时的时间
    timeBetweenEvictionRunsMills: 6000 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    minEvictableIdleTimeMillis: 30000 #配置一个连接在池中最小生存的时间，单位是毫秒
    validationQuery: SELECT 'x' #测试连接
  main:
    allow-circular-references: true
mybatis:
  mapper-locations: classpath:/mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true
    #打印sql
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper:
  identity: MYSQL # 配置主键自动增长(使用MYSQL原生方式)
logging:
  level:
    com.xxx.sharding: debug

2、配置插件

[!NOTE]

注意：需要配置插件的properties的dialect参数。跟之前的Dialect接口处理对应上
我们自定义的插件必须要在PageHelper插件之前。这样才能进行分页的处理，不然就乱套了！！

/**
 * @author cqz
 * @date 2024-11-04 18:15
 */
@Configuration
public class DataSourceConfig {

    @Bean
    public ShardingPlugin shardingPlugin() {
        return new ShardingPlugin();
    }

    @Bean
    public ShardingExcutePlugin shardingExcutePlugin() {
        Properties properties = new Properties();
        properties.setProperty("dialect", "mysql");
        ShardingExcutePlugin shardingExcutePlugin = new ShardingExcutePlugin();
        shardingExcutePlugin.setProperties(properties);
        return shardingExcutePlugin;
    }

    @Bean
    public PageInterceptor pageInterceptor() {
        PageInterceptor pageInterceptor = new PageInterceptor();
        Properties properties = new Properties();
        properties.setProperty("helperDialect", "mysql");
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("params", "count=countSql");
        pageInterceptor.setProperties(properties);
        return pageInterceptor;
    }


    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappings/*.xml"));

        // 创建插件列表
        List<Interceptor> plugins = new ArrayList<>();
        plugins.add(shardingExcutePlugin());
        plugins.add(shardingPlugin());  // 确保你的插件在 PageHelper 插件之前
        plugins.add(pageInterceptor());

        factoryBean.setPlugins(plugins.toArray(new Interceptor[0]));

        return factoryBean.getObject();
    }

    @Bean
    public DataSourceTransactionManager transactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

3、实体、Mapper、测试Controller

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "order")
public class Order {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "ID")
    private Long id;

    @Column(name = "content")
    private String content;

    @Column(name = "createTime")
    private Date createTime;

    @Column(name = "orderId")
    private Long orderId;

}

最主要的这里的注解使用，如果类注解使用了，会全生效，如果方法也加上注解，方法会覆盖类的注解

@Repository
@Mapper
@ShardingRouter(splitTable = true, splitTableKey = "createTime",tableStrageClazz = TimeExprTableStrage.class, sourceTableNames = {"order_2024_11", "order_2024_05", "order_2024_12"})
public interface OrderMapper extends BaseMapper<Order> {

    @Select("select id, content, orderId, createTime from order where createTime = #{createTime}")
    @ShardingRouter(splitTable = true, splitTableKey = "createTime",tableStrageClazz = TimeExprTableStrage.class)
    List<Order> selectByCreateTime(@Param("createTime") Date createTime);

    @Select("select id, content, orderId, createTime from order where createTime between #{startCreateTime} and #{endCreateTime}")
    @ShardingRouter(splitTable = true, splitTableKey = "createTime",rangeSearch = TypeEnum.RANGE_SEARCH,tableStrageClazz = TimeExprTableStrage.class,sourceTableNames = {"order_2024_11", "order_2024_05", "order_2024_12"})
    List<Order> selectByCreateTimeRange(@Param("startCreateTime") Date startCreateTime,@Param("endCreateTime") Date endCreateTime);

    @Select("select id, content, orderId, createTime from order")
    @ShardingRouter(splitTable = true, splitTableKey = "createTime",tableStrageClazz = TimeExprTableStrage.class,sourceTableNames = {"order_2024_11", "order_2024_05", "order_2024_12"})
    List<Order> selectList();

    @Insert("insert into order (id, content, orderId, createTime) values (#{id}, #{content}, #{orderId}, #{createTime})")
    int insertOrder(Order order);

    @Select("select id, content, orderId, createTime from order where orderId = #{orderId}")
    @ShardingRouter(splitTable = true, splitTableKey = "createTime",tableStrageClazz = TimeExprTableStrage.class,sourceTableNames = {"order_2024_11", "order_2024_05", "order_2024_12"})
    List<Order> selectListByOrderId(@Param("orderId") Long orderId);
}

Controller测试：


@RestController
public class OrderController {
    @Autowired
    OrderMapper orderMapper;

    @GetMapping("add")
    public String addOrder(){
        for(int i = 0; i < 12; i++){
            Order order = new Order();
            order.setId((long) i);
            order.setOrderId((long) i);
            order.setContent("测试" + i);
            order.setCreateTime(new Date());
            orderMapper.insert(order);
        }
        return "hello";
    }

    @GetMapping("list")
    public String list() throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date parse = format.parse("2024-05-04 16:25:01");
        List<Order> orders = orderMapper.selectByCreateTime(parse);
//        List<Order> orders = orderMapper.selectAll();
        return orders.toString();
    }

    @GetMapping("list1")
    public String list1() throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date parse = format.parse("2024-05-04 16:25:01");
        List<Order> orders = orderMapper.selectList();
//        List<Order> orders = orderMapper.selectAll();
        return orders.toString();
    }

    @GetMapping("list2/{orderId}")
    public String list2(@PathVariable("orderId") Long orderId) throws ParseException {
        PageHelper.startPage(2, 2);
        List<Order> orders = orderMapper.selectListByOrderId(orderId);
        PageInfo<Order> pageInfo = new PageInfo<>(orders);
        return orders.toString();
    }


    @GetMapping("list3")
    public String list2() throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date parse1 = format.parse("2023-05-04 16:25:01");
        Date parse2 = format.parse("2024-05-04 16:25:01");

        PageHelper.startPage(1, 10);

        List<Order> orders = orderMapper.selectByCreateTimeRange(parse1,parse2);
        PageInfo<Order> pageInfo = new PageInfo<>(orders);
        return orders.toString();
    }


    @GetMapping("add1")
    public String add1() throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date parse = format.parse("2024-12-04 00:00:00");
        Order order = new Order();
        order.setId(2235L);
        order.setContent("测试插入");
        order.setCreateTime(parse);
        order.setOrderId(2335L);
//        List<Order> orders = orderMapper.selectAll();
//        int i = orderMapper.insertOrder(order);
        int i = orderMapper.insert(order);
        return i + "1";
    }


    @GetMapping("delete")
    public String delete() throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date parse = format.parse("2024-12-04 00:00:00");
        Order order = new Order();
        order.setCreateTime(parse);
        int i = orderMapper.delete(order);
        return i + "1";
    }

    @GetMapping("update")
    public String update() throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date parse = format.parse("2024-12-04 00:00:00");
        Order order = new Order();
        order.setCreateTime(parse);
        Order order1 = orderMapper.selectOne(order);
        order1.setContent("哈哈哈哈哈");
        int i = orderMapper.updateByPrimaryKeySelective(order1);
        return i + "";
    }


    @GetMapping("page")
    public String page() throws ParseException {
        PageHelper.startPage(1, 1);
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date parse = format.parse("2024-12-04 00:00:00");
        Order order = new Order();
        order.setCreateTime(parse);

        List<Order> list = orderMapper.selectByCreateTime(parse);
        PageInfo<Order> pageInfo = new PageInfo<>(list);

        return pageInfo.toString();
    }


}