基于mybatis 拦截器修改sql

背景：公司的分库分表中间件有一个自定义路由的功能，即指定需要路由的表后缀，需要在sql尾部加一段参数来指向该表

因此写了一个拦截器来实现这个功能，因此如果您需要利用拦截器来修改sql的话可以参考此方法

达到的效果：mybatis的 mapper上加上路由参数对象，拦截器自动在sql后面追加路由信息

PageList<Order> queryOrder(@Param("object")Order object, CDSRouter cdsRouter);
           

1，创建路由参数对象，如下

/**
 *
 * CDS使用宏路由物理表
 * 注意：与PageBounds 共同使用时，务必pageBounds.containsTotalCount=false
 * 否则统计语句将会出错
 *
 * Created by lxn on 2018/9/27.
 */
@AllArgsConstructor
@RequiredArgsConstructor
@Data
public class CDSRouter {

    public static final String partten1="/@ts=%s; sdb=%s@/";
    public static final String partten2="/@ts=%s@/";
    /**
     * 指定群组，如果分表后缀在集群中是唯一的，群组可以不指定。
     */
    private String sdb;

    /**
     *  指定分表后缀。
     */
    @NonNull  private String ts;

    public String getSqlPart(){
        if (StringUtils.hasText(sdb)&&StringUtils.hasText(ts)){
            return String.format(partten1,ts,sdb);
        }
        if (StringUtils.hasText(ts)){
            return String.format(partten2,ts);
        }
        return "";
    }

}
           

2，实现mybatis 拦截器

package xxxxx.interceptor;

import xxxxxxxxx.CDSRouter;
import com.github.miemiedev.mybatis.paginator.OffsetLimitInterceptor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * Created by lxn on 2018/9/27.
 */
@Slf4j
@Intercepts({
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class }),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class }) })
public class CDSRouterInterceptor implements Interceptor {

    static int MAPPED_STATEMENT_INDEX = 0;
    static int PARAMETER_INDEX = 1;
    static int ROWBOUNDS_INDEX = 2;
    static int RESULT_HANDLER_INDEX = 3;


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] queryArgs = invocation.getArgs();
        final MappedStatement ms = (MappedStatement)queryArgs[MAPPED_STATEMENT_INDEX];
        final Object parameter = queryArgs[PARAMETER_INDEX];

        if (!(parameter instanceof Map)){
            return invocation.proceed();
        }

        CDSRouter cdsRouter=null;
        for (Object each:((Map)parameter).values()){
            if (each instanceof CDSRouter){
                cdsRouter=(CDSRouter)each;
                break;
            }
        }
        //没有分页对象
        if (cdsRouter==null){
            return invocation.proceed();
        }

        final BoundSql boundSql = ms.getBoundSql(parameter);
        String sql =boundSql.getSql()+" "+cdsRouter.getSqlPart();
        queryArgs[MAPPED_STATEMENT_INDEX] = copyFromNewSql(ms,boundSql,sql, new ArrayList(boundSql.getParameterMappings()), parameter);
        return invocation.proceed();
    }

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

    @Override
    public void setProperties(Properties properties) {

    }

    private MappedStatement copyFromNewSql(MappedStatement ms, BoundSql boundSql,
                                           String sql, List<ParameterMapping> parameterMappings, Object parameter){
        BoundSql newBoundSql = copyFromBoundSql(ms, boundSql, sql, parameterMappings, parameter);
        return copyFromMappedStatement(ms, new OffsetLimitInterceptor.BoundSqlSqlSource(newBoundSql));
    }

    private BoundSql copyFromBoundSql(MappedStatement ms, BoundSql boundSql,
                                      String sql, List<ParameterMapping> parameterMappings,Object parameter) {
        BoundSql newBoundSql = new BoundSql(ms.getConfiguration(),sql, parameterMappings, parameter);
        for (ParameterMapping mapping : boundSql.getParameterMappings()) {
            String prop = mapping.getProperty();
            if (boundSql.hasAdditionalParameter(prop)) {
                newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
            }
        }
        return newBoundSql;
    }

    //see: MapperBuilderAssistant
    private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
        MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(),ms.getId(),newSqlSource,ms.getSqlCommandType());

        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.keyGenerator(ms.getKeyGenerator());
        if(ms.getKeyProperties() != null && ms.getKeyProperties().length !=0){
            StringBuffer keyProperties = new StringBuffer();
            for(String keyProperty : ms.getKeyProperties()){
                keyProperties.append(keyProperty).append(",");
            }
            keyProperties.delete(keyProperties.length()-1, keyProperties.length());
            builder.keyProperty(keyProperties.toString());
        }

        //setStatementTimeout()
        builder.timeout(ms.getTimeout());

        //setStatementResultMap()
        builder.parameterMap(ms.getParameterMap());

        //setStatementResultMap()
        builder.resultMaps(ms.getResultMaps());
        builder.resultSetType(ms.getResultSetType());

        //setStatementCache()
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());

        return builder.build();
    }
}
           

修改sql的方法是我从分页插件 mybatis-pagintor中抄过来的，并非原创