MyBatis分表拦截器-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_38501485/article/details/126609318

分析上面登陆，当前表按照年份分表了，最大的一张表超过500w建议分表

注意:之前写的经过多方测试发现遍历的时候参数传递不进去，现如今已经完善

package org.jeecg.config.mybatis;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.binding.MapperMethod.ParamMap;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.shiro.SecurityUtils;
import org.jeecg.common.system.vo.LoginUser;
import org.jeecg.common.util.oConvertUtils;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.util.Date;
import java.util.Properties;

/**
 * mybatis拦截器，自动注入创建人、创建时间、修改人、修改时间
 *
 * @Author scott
 * @Date 2019-01-19
 */
@Slf4j
@Component
@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})})
public class MybatisAddCommonValuesInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
        Object parameter = invocation.getArgs()[1];
        if (parameter == null) {
            return invocation.proceed();
        }
        if (SqlCommandType.INSERT == sqlCommandType) {
            LoginUser sysUser = this.getLoginUser();
            Field[] fields = oConvertUtils.getAllFields(parameter);
            for (Field field : fields) {
                try {
                    if ("createBy".equals(field.getName())) {
                        field.setAccessible(true);
                        Object local_createBy = field.get(parameter);
                        field.setAccessible(false);
                        if (local_createBy == null || local_createBy.equals("")) {
                            if (sysUser != null) {
                                // 登录人账号
                                field.setAccessible(true);
                                field.set(parameter, sysUser.getUsername());
                                field.setAccessible(false);
                            }
                        }
                    }
                    // 注入创建时间
                    if ("createTime".equals(field.getName())) {
                        field.setAccessible(true);
                        Object local_createDate = field.get(parameter);
                        field.setAccessible(false);
                        if (local_createDate == null || local_createDate.equals("")) {
                            field.setAccessible(true);
                            field.set(parameter, new Date());
                            field.setAccessible(false);
                        }
                    }
                    //注入部门编码
                    if ("sysOrgCode".equals(field.getName())) {
                        field.setAccessible(true);
                        Object local_sysOrgCode = field.get(parameter);
                        field.setAccessible(false);
                        if (local_sysOrgCode == null || local_sysOrgCode.equals("")) {
                            // 获取登录用户信息
                            if (sysUser != null) {
                                field.setAccessible(true);
                                field.set(parameter, sysUser.getOrgCode());
                                field.setAccessible(false);
                            }
                        }
                    }
                } catch (Exception e) {
                }
            }
        }
        if (SqlCommandType.UPDATE == sqlCommandType) {
            LoginUser sysUser = this.getLoginUser();
            Field[] fields = null;
            if (parameter instanceof ParamMap) {
                ParamMap<?> p = (ParamMap<?>) parameter;
                //update-begin-author:scott date:20190729 for:批量更新报错issues/IZA3Q--
                if (p.containsKey("et")) {
                    parameter = p.get("et");
                } else {
                    parameter = p.get("param1");
                }
                //update-end-author:scott date:20190729 for:批量更新报错issues/IZA3Q-

                //update-begin-author:scott date:20190729 for:更新指定字段时报错 issues/#516-
                if (parameter == null) {
                    return invocation.proceed();
                }
                //update-end-author:scott date:20190729 for:更新指定字段时报错 issues/#516-

                fields = oConvertUtils.getAllFields(parameter);
            } else {
                fields = oConvertUtils.getAllFields(parameter);
            }

            for (Field field : fields) {
                log.debug("------field.name------" + field.getName());
                try {
                    if ("updateBy".equals(field.getName())) {
                        //获取登录用户信息
                        if (sysUser != null) {
                            // 登录账号
                            field.setAccessible(true);
                            field.set(parameter, sysUser.getUsername());
                            field.setAccessible(false);
                        }
                    }
                    if ("updateTime".equals(field.getName())) {
                        field.setAccessible(true);
                        field.set(parameter, new Date());
                        field.setAccessible(false);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return invocation.proceed();
    }

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

    @Override
    public void setProperties(Properties properties) {
        // TODO Auto-generated method stub
    }

    //update-begin--Author:scott  Date:20191213 for：关于使用Quzrtz 开启线程任务， #465
    private LoginUser getLoginUser() {
        LoginUser sysUser = null;
        try {
            sysUser = SecurityUtils.getSubject().getPrincipal() != null ? (LoginUser) SecurityUtils.getSubject().getPrincipal() : null;
        } catch (Exception e) {
            //e.printStackTrace();
            sysUser = null;
        }
        return sysUser;
    }
    //update-end--Author:scott  Date:20191213 for：关于使用Quzrtz 开启线程任务， #465

}

package org.jeecg.config.mybatis;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.jeecg.common.config.mqtoken.UserTokenContext;
import org.jeecg.common.constant.CommonConstant;
import org.jeecg.common.util.DateUtils;
import org.jeecg.common.util.RedisUtil;
import org.jeecg.common.util.SpringContextUtils;
import org.jeecg.common.util.oConvertUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;

/**
 * @version 1.0
 * @Author zhaozhiqiang
 * @Date 2022/9/5 20:06
 * @Description //TODO  动态修改表名字
 */
@Slf4j
@Component

//@Signature(type = Executor.class,method = "query",args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
//@Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class })
@Intercepts({

        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})

}
)
public class MybatisFixTableInterceptor implements Interceptor {
    private static RedisUtil redisUtil;
    private final static Map<String, String> TABLE_MAP = new LinkedHashMap<>();


    //动态配置需要拦截表/分表的名字
    @Value("${mybatis-interceptor.splipTables}")
    public void setSplipTables(String msplipTables) {
        // 获取执行的SQL参数
        String currentYears = DateUtils.getDate("yyyy");
        log.info("动态配置需要拦截表:{}", msplipTables);
        //表名长的放前面，避免字符串匹配的时候先匹配替换子集
        if (oConvertUtils.isNotEmpty(msplipTables)) {
            String[] permissionUrl = msplipTables.split(",");
            for (String table : permissionUrl) {
                TABLE_MAP.put(table, table + "_" + currentYears);
            }
        }
        log.info("动态配置需要拦截表集合为:{}", TABLE_MAP.size());
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if( invocation.getTarget() instanceof  StatementHandler){//修改sql
            if (null == redisUtil) {
                redisUtil = (RedisUtil) SpringContextUtils.getBean("redisUtil");
            }

            StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
            MetaObject metaObject = MetaObject.forObject(statementHandler, new DefaultObjectFactory(),
                    new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
            BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
            // 获取执行的SQL
            String sql = boundSql.getSql();
            // 获取执行的SQL参数
            String token = UserTokenContext.getToken();
            if (isReplaceTableName(sql,token)) {
                String currentYears = "";
                if (StringUtils.isNotBlank(token)) {
                    Object years = redisUtil.get(CommonConstant.PREFIX_LOGIN_YEAR + token);
                    if (null != years) {
                        currentYears = (String) years;
                    }
                }
                for (Map.Entry<String, String> entry : TABLE_MAP.entrySet()) {
                    log.debug("原sql:{}",sql);
                    if (StringUtils.isNotBlank(currentYears)) {
                        sql = sql.replace(entry.getKey(), entry.getKey() + "_" + currentYears);
                    } else {
                        sql = sql.replace(entry.getKey(), entry.getValue());
                    }
                    log.debug("后sql:{}",sql);
                }
            }
            // 替换执行的的SQL.
            metaObject.setValue("delegate.boundSql.sql", sql);
            MappedStatement ms = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
            SqlCommandType sqlCommandType = ms.getSqlCommandType();
            if (sqlCommandType != SqlCommandType.UPDATE && sqlCommandType != SqlCommandType.INSERT) {
                return invocation.proceed();
            }
        }
        return invocation.proceed();
    }


    /***
     * 判断是否需要替换表名
     * @param sql
     * @return
     */
    private boolean isReplaceTableName(String sql,String token) {
        String currentYears = "";
        if (StringUtils.isNotBlank(token)) {
            Object years = redisUtil.get(CommonConstant.PREFIX_LOGIN_YEAR + token);
            if (null != years) {
                currentYears = (String) years;
            }
        }
        for (String tableName : TABLE_MAP.keySet()) {
            //不能无限制递归替换
            if (StringUtils.isNotBlank(currentYears)) {
                if (sql.contains(tableName) && !sql.contains(tableName + "_" + currentYears)) {
                    if(sql.split(tableName).length>1){//去除主子表,列如:item_type,item
                        boolean b = sql.split(tableName)[1].startsWith("_");
                        if(b){
                            return false;
                        }
                    }
                    return true;
                }
            } else {
                if (sql.contains(tableName) && !sql.contains(TABLE_MAP.get(tableName))) {
                    //!sql.split(tableName)[1].split("_")[0].split("\\s")[0].contains(currentYears)
                    if(sql.split(tableName).length>1){
                        boolean b = sql.split(tableName)[1].startsWith("_");
                        if(b){//去除主子表,列如:item_type,item
                            return false;
                        }
                    }
                    return true;
                }
            }

        }
        return false;
    }

    public static void main(String[] args) {
        String sql="select * from item_type";
        String tableName="item";
        String currentYears="2023";
        boolean test = test(sql, tableName, currentYears);
        System.out.println(test);

    }
    public  static boolean test(String sql,String tableName,String currentYears){
        if (sql.contains(tableName) && !sql.contains(tableName + "_" + currentYears)) {
            if(sql.split(tableName).length>1){
                String str= sql.split(tableName)[1].split("_")[0].split("\\s")[0];
                if(str.contains(currentYears)){
                    return true;
                }
                return false;
            }
            return true;
        }
        return false;
    }

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

    @Override
    public void setProperties(Properties properties) {
        // TODO Auto-generated method stub
        // 赋值成员变量，在其他方法使用
//        this.properties = properties;
    }

}

在yml中配置要分表的信息

#mybatis拦截分表配置,多个都好隔开
mybatis-interceptor:
  splipTables: pe_regist_detail_item,pe_result,pe_lis_return_data

注意:因为年表是从前端传递过来的，和登录token是一个级别，如果涉及多线程操作表的时候需要重新传递token，不然年表找不到，解决方案如下

前端传递过来的token级别的年表需要在登录接口中缓存到redis中

mybatis实现分表