mybatis自定义拦截器实现统一过滤动态修改sql mybatis

需求：给原来的sql都加上一个条件过滤，实现多租户数据隔离。
一个是sql语句散布在xml里，dao注解里，量非常大，再一个是租户字段定义在实体基类中，接口参数是对象只需修改sql即可，倒是不麻烦，机械性复制粘贴，如果是非对象例如get（id），那就有的你改了，所以第一时间排除掉一个个修改sql。用mybatis自定义拦截器来对sql进行后期动态修改，原理和分页插件类似。
建一个mybatis拦截器处理sql 【mybatis自定义拦截器实现统一过滤动态修改sql】可拦截方法有 Executor、ParameterHandler 、ResultSetHandler 、StatementHandler，
由于项目分页插件是在Executor方法拦截，所以此例也是拦截Executor，它和StatementHandler获取sql的方式是不同的，在此不讨论。args参数可进入Executor接口里一一对应。Executor只能处理query、update，如果要拦截其它sql，得再写一个拦截StatementHandler的prepare方法。

@Intercepts({ @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}) }) public class TenantInterceptor extends BaseInterceptor {private static final long serialVersionUID = 1L; @Override public Object intercept(Invocation invocation) throws Throwable {final MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0]; Object parameter = invocation.getArgs()[1]; BoundSql boundSql = mappedStatement.getBoundSql(parameter); if (StringUtils.isBlank(boundSql.getSql())) { return null; } String originalSql = boundSql.getSql().trim(); String mid = mappedStatement.getId(); String nname = StringUtils.substringAfterLast(mid, "."); Class classType = Class.forName(mid.substring(0, mid.lastIndexOf("."))); addTenantId addTenantId = null; //拦截类 if (classType.isAnnotationPresent(addTenantId.class) && classType.getAnnotation(addTenantId.class) != null) { addTenantId = classType.getAnnotation(addTenantId.class); originalSql = handleSQL(originalSql, addTenantId); } else { //拦截方法 for (Method method : classType.getMethods()) { if (!nname.equals(method.getName())) { continue; } else { if (method.isAnnotationPresent(addTenantId.class) && method.getAnnotation(addTenantId.class) != null) { addTenantId = method.getAnnotation(addTenantId.class); originalSql = handleSQL(originalSql, addTenantId); } break; } } }BoundSql newBoundSql = new BoundSql(mappedStatement.getConfiguration(), originalSql, boundSql.getParameterMappings(), boundSql.getParameterObject()); if (Reflections.getFieldValue(boundSql, "metaParameters") != null) { MetaObject mo = (MetaObject) Reflections.getFieldValue(boundSql, "metaParameters"); Reflections.setFieldValue(newBoundSql, "metaParameters", mo); } MappedStatement newMs = copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(newBoundSql)); invocation.getArgs()[0] = newMs; return invocation.proceed(); }public String handleSQL(String originalSql, addTenantId addTenantId){ String atv = addTenantId.value(); if (StringUtils.isNotBlank(atv)){try{ /** 此处应为你的sql拼接，替换第一个where可以实现绝大多数sql，当然复杂sql除外，所以复杂sql还是需要例外处理 User user = null; user = UserUtils.getUser(); String tid; if(user != null && StringUtils.isNotBlank(tid = user.getTenantId())){ originalSql = replace(originalSql, "where", "where"+atv+"='"+tid+"' and"); originalSql = replace(originalSql, "WHERE", "WHERE"+atv+"='"+tid+"' and"); } **/ }catch (Exception e){ log.debug(e.getMessage()); } } return originalSql; }public static String replace(String string, String toReplace, String replacement) { //int pos = string.lastIndexOf(toReplace); int pos = string.indexOf(toReplace); if (pos > -1) { return string.substring(0, pos) + replacement + string.substring(pos + toReplace.length(), string.length()); } else { return string; } }@Override public Object plugin(Object target) { return Plugin.wrap(target, this); }@Override public void setProperties(Properties properties) { super.initProperties(properties); }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) { for (String keyProperty : ms.getKeyProperties()) { builder.keyProperty(keyProperty); } } builder.timeout(ms.getTimeout()); builder.parameterMap(ms.getParameterMap()); builder.resultMaps(ms.getResultMaps()); builder.cache(ms.getCache()); return builder.build(); }public static class BoundSqlSqlSource implements SqlSource { BoundSql boundSql; public BoundSqlSqlSource(BoundSql boundSql) { this.boundSql = boundSql; }public BoundSql getBoundSql(Object parameterObject) { return boundSql; } } }

通过反射获取类或方法的注解，他的反射执行方法比直接执行方法慢个几十倍，并不是很明显，当然优化有很多种优化，不作讨论。
建一个自定义注解需求虽然是大部分sql需要统一拦截，但是事实上绝对存在不要拦截的表又或者方法，这就需要自定义注解去区分开拦截。ElementType.METHOD,ElementType.TYPE 表示可打在类和方法上。

/** * Mybatis租户过滤注解，拦截StatementHandler的prepare方法拦截器见TenantInterceptor * 无值表示不过滤有值表示过滤的租户字段如a.tenant_id * @author bbq */ @Target({ElementType.METHOD,ElementType.TYPE}) @Retention(RetentionPolicy.RUNTIME) @Inherited @Documented public @interface addTenantId { String value() default ""; }

为dao基类打注解拦截实现统一处理为我的dao基类打上注解，注解值可以获取拼接在sql上。

public interface CrudDao extends BaseDao { @addTenantId("a.tenant_id") public T get(String id); @addTenantId("a.tenant_id") public T get(T entity); @addTenantId("a.tenant_id") public List findList(T entity); @addTenantId("a.tenant_id") public List findAllList(T entity); @addTenantId("a.tenant_id") @Deprecated public List findAllList(); public int insert(T entity); @addTenantId("tenant_id") public int update(T entity); @addTenantId("tenant_id") @Deprecated public int delete(String id); @addTenantId("tenant_id") public int delete(T entity); }

打空值注解跳过处理没有租户字段的表，也就是不需要拦截sql的dao打上空注解在拦截器里跳过处理