虽然Mybatis-plus有分表工具类,但是只能根据id取模或者日期进行分表操作,如果我们需要根据我们的用户编号进行分表存储,就不可以了,今天我们自己定义接口定义规范,进行分表处理

第一步:定义接口规范

import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlCommandType;
import java.util.List;

/**
* @desc   
* @date   2022/2/15 15:30
* @author zhanghy
*/
public interface CustomerTableNameHandler {

    String dynamicTableName(String sql, String value, Object parameterObject, List<ParameterMapping> parameterMappings,
                            SqlCommandType sct);

}

第二步:重新 InnerInterceptor 接口实现


import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.core.toolkit.TableNameParser;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils.MPBoundSql;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils.MPStatementHandler;
import com.baomidou.mybatisplus.core.toolkit.TableNameParser.SqlToken;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import org.apache.ibatis.executor.Executor;
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.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

public class CustomerDynamicTableNameInnerInterceptor implements InnerInterceptor {

    private Map<String, CustomerTableNameHandler> tableNameHandlerMap;
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
        SqlCommandType sct = ms.getSqlCommandType();
        if (!InterceptorIgnoreHelper.willIgnoreDynamicTableName(ms.getId())) {
            mpBs.sql(this.changeTable(mpBs.sql(), mpBs, sct));
        }
    }

    public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
        MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
        MappedStatement ms = mpSh.mappedStatement();
        SqlCommandType sct = ms.getSqlCommandType();
        if (sct == SqlCommandType.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {
            if (InterceptorIgnoreHelper.willIgnoreDynamicTableName(ms.getId())) {
                return;
            }
            MPBoundSql mpBs = mpSh.mPBoundSql();
            mpBs.sql(this.changeTable(mpBs.sql(), mpBs, sct));
        }

    }

    protected String changeTable(String sql, PluginUtils.MPBoundSql mpBs, SqlCommandType sct) {
        TableNameParser parser = new TableNameParser(sql);
        List<SqlToken> names = new ArrayList();
        parser.accept(names::add);
        StringBuilder builder = new StringBuilder();
        int last = 0;
        SqlToken name;
        for(Iterator var6 = names.iterator(); var6.hasNext(); last = name.getEnd()) {
            name = (SqlToken)var6.next();
            int start = name.getStart();
            if (start != last) {
                builder.append(sql, last, start);
                String value = name.getValue();
                CustomerTableNameHandler handler = this.tableNameHandlerMap.get(value);
                if (handler != null) {
                    builder.append(handler.dynamicTableName(sql, value, mpBs.parameterObject(), mpBs.parameterMappings(), sct));
                } else {
                    builder.append(value);
                }
            }
        }
        if (last != sql.length()) {
            builder.append(sql.substring(last));
        }
        return builder.toString();
    }

    public Map<String, CustomerTableNameHandler> getTableNameHandlerMap() {
        return this.tableNameHandlerMap;
    }

    public void setTableNameHandlerMap(final Map<String, CustomerTableNameHandler> tableNameHandlerMap) {
        this.tableNameHandlerMap = tableNameHandlerMap;
    }

    public boolean equals(final Object o) {
        if (o == this) {
            return true;
        } else if (!(o instanceof DynamicTableNameInnerInterceptor)) {
            return false;
        } else {
            CustomerDynamicTableNameInnerInterceptor other = (CustomerDynamicTableNameInnerInterceptor)o;
            if (!this.canEqual(this)) {
                return false;
            } else {
                Object this$tableNameHandlerMap = this.getTableNameHandlerMap();
                Object other$tableNameHandlerMap = other.getTableNameHandlerMap();
                if (this$tableNameHandlerMap == null) {
                    if (other$tableNameHandlerMap != null) {
                        return false;
                    }
                } else if (!this$tableNameHandlerMap.equals(other$tableNameHandlerMap)) {
                    return false;
                }
                return true;
            }
        }
    }


    protected boolean canEqual(final Object other) {
        return other instanceof CustomerDynamicTableNameInnerInterceptor;
    }

    public String toString() {
        return "DynamicTableNameInnerInterceptor(tableNameHandlerMap=" + this.getTableNameHandlerMap() + ")";
    }

    public CustomerDynamicTableNameInnerInterceptor() {
    }

    public CustomerDynamicTableNameInnerInterceptor(final Map<String, CustomerTableNameHandler> tableNameHandlerMap) {
        this.tableNameHandlerMap = tableNameHandlerMap;
    }
}

第三步:编写动态表名称处理器


import com.fdas.integral.record.common.datascope.CustomerTableNameHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlCommandType;

import java.lang.reflect.Field;
import java.util.List;

/**
* @desc   动态表名解析器
* @date   2022/2/15 15:31
* @author zhanghy
*/
@Slf4j
public class UserIdModTableNameParser implements CustomerTableNameHandler {

    private Integer mod;
    private String fieldName;


    public UserIdModTableNameParser(Integer modValue, String fieldName) {
        this.mod = modValue;
        this.fieldName = fieldName;
    }

    @Override
    public String dynamicTableName(String sql, String tableName, Object parameterObject, List<ParameterMapping> parameterMappings, SqlCommandType sct) {
        try {
            Long model = getFieldValue(parameterObject)%mod;
            String suffix = String.valueOf(model);
            log.debug(" tableName: {}", tableName + "_" + suffix);
            return tableName + "_" + suffix;
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;
    }
    public Long getFieldValue(Object obj) throws NoSuchFieldException, IllegalAccessException {
        Field field = obj.getClass().getDeclaredField(fieldName);
        field.setAccessible(true);
        return (Long) field.get(obj);
    }

}

第四步:配置Mybatis-plus


import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.fdas.integral.record.common.datascope.CustomerDynamicTableNameInnerInterceptor;
import com.fdas.integral.record.common.datascope.CustomerTableNameHandler;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import java.util.HashMap;

/**
* @desc   mybatis-plus配置
* @date   2022/2/15 15:36
* @author zhanghy
*/
@Configuration
@EnableTransactionManagement(order = 2)//由于引入多数据源,所以让spring事务的aop要在多数据源切换aop的后面
public class MybatisPlusConfig {
    /**
     * mybatis-plus 插件配置
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 配置分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        // 动态表名处理拦截器
        CustomerDynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new CustomerDynamicTableNameInnerInterceptor();
        HashMap<String, CustomerTableNameHandler> map = new HashMap<>();
        //这里为不同的表设置对应表名处理器
        map.put("integral_increase", new UserIdModTableNameParser(100,"userId"));
        dynamicTableNameInnerInterceptor.setTableNameHandlerMap(map);
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        return interceptor;
    }
}

 

大攻搞成:其他的操作数据库不变,自动根据用户userId/100进行分表存储

最后修改于 2022-02-15 15:38:48
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付
上一篇