mybaits自定义拦截器
1.今天遇到个问题，有个项目要分为不同企业显示不同数据，如果直接在原sql修改工作量太大，看了网上的
依赖：
<!--pagehelper 分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version>
</dependency>
mybatis拦截器，很适用于当前的问题，不仅能减少工作量
CompanyIntercept.class
package com.sale.config.mybatisintercept;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.autoconfigure.PageHelperAutoConfiguration;
import com.sale.config.annotation.InterceptAnnotation;
import com.sale.entity.acl.ACLCompany;
import com.sale.entity.acl.ACLUser;
import com.sale.util.session.CompanySession;
import com.sale.util.session.UserSession;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
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.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.ApplicationListener;
import org.springframework.context.annotation.Import;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.Properties;
/*@AutoConfigureAfter(PageHelperAutoConfiguration.class)*/
@Component
@Intercepts({
@Signature(
type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class
})
/* @Signature(type = Executor.class, method = "query", args = {
MappedStatement.class, Object.class, RowBounds.class,
ResultHandler.class })*/
})
/*@Import(PageHelperAutoConfiguration.class)*/
@Slf4j
public class CompanyIntercept implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
// 方法一
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
//先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，其实现类是BaseStatementHandler，然后就到BaseStatementHandler的成员变量mappedStatement
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
//id为执行的mapper方法的全路径名，如com.uv.dao.UserMapper.insertUser
String id = mappedStatement.getId();
//sql语句类型 select、delete、insert、update
String sqlCommandType = mappedStatement.getSqlCommandType().toString();
BoundSql boundSql = statementHandler.getBoundSql();
//获取到原始sql语句
String sql = boundSql.getSql();
String mSql = sql;
//TODO 修改位置
//注解逻辑判断 添加注解了才拦截
Class<?> classType = Class.forName(mappedStatement.getId().substring(0, mappedStatement.getId().lastIndexOf(".")));
String mName = mappedStatement.getId().substring(mappedStatement.getId().lastIndexOf(".") + 1, mappedStatement.getId().length());
for (Method method : classType.getDeclaredMethods()) {
if (method.isAnnotationPresent(InterceptAnnotation.class) && mName.equals(method.getName())) {
InterceptAnnotation interceptorAnnotation = method.getAnnotation(InterceptAnnotation.class);
if (interceptorAnnotation.flag()) {
//此处应为你的sql拼接，替换第一个where可以实现绝大多数sql，当然复杂sql除外，所以复杂sql还是需要例外处理
ACLCompany company = null;
company = CompanySession.sessionCompany();
if (company != null) {
if (sql.contains("WHERE")) {
//originalSql = replace(originalSql, "where", "where "+atv+"='"+tid+"' and");
mSql = sql.replace("WHERE", "INNER JOIN acl_user_company auc ON a.user_id = auc.user_id LEFT JOIN acl_company ac ON auc.company_id=ac.id WHERE ac.id='" + company.getId() + "' and");
} else {
if (sql.contains("LIMIT")) {
mSql = sql.replace("LIMIT", "INNER JOIN acl_user_company auc ON a.user_id = auc.user_id LEFT JOIN acl_company ac ON auc.company_id=ac.id WHERE ac.id='" + company.getId() + "' LIMIT");
} else {
mSql = sql + "INNER JOIN acl_user_company auc ON a.user_id = auc.user_id LEFT JOIN acl_company ac ON auc.company_id=ac.id WHERE ac.id='" + company.getId() + "'";
}
}
}
/*
mSql = sql + " limit 2";*/
}
}
}
//通过反射修改sql语句
Field field = boundSql.getClass().getDeclaredField("sql");
field.setAccessible(true);
field.set(boundSql, mSql);
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
} else {
return target;
}
}
@Override
public void setProperties(Properties properties) {
}
}
InterceptAnnotation.class
package com.sale.config.annotation;
import java.lang.annotation.*;
/**
* Mybatis租户过滤注解，拦截StatementHandler的prepare方法 拦截器见TenantInterceptor
* 无值表示不过滤 有值表示过滤的租户字段 如a.tenant_id
* @author bbq
*/
/**
* Mybatis租户过滤注解，拦截StatementHandler的prepare方法 拦截器见TenantInterceptor
* 无值表示不过滤 有值表示过滤的租户字段 如a.tenant_id
* @author bbq
*/
@Target({ElementType.METHOD,ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
public @interface InterceptAnnotation {
boolean flag() default true;
}
MybatisDatasourceConfig.class
package com.sale.config;
import com.fasterxml.jackson.databind.annotation.JsonAppend;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInterceptor;
import com.sale.config.mybatisintercept.CompanyIntercept;
import com.sale.config.mybatisintercept.GenerateTimeIntercepter;
import com.sale.util.common.MyMapper;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Properties;
/**
* @author wjn
* @version 1.0, 2020/6/17
* @description
*/
@Configuration
// 精确到 mapper 目录，以便跟其他数据源隔离
@MapperScan(basePackages = "com.sale.dao", markerInterface = MyMapper.class, sqlSessionFactoryRef = "sqlSessionFactory") /*.setting.acl*/
public class MybatisDatasourceConfig {
@Resource
CompanyIntercept mybatisSqlInterceptor;
/*@Resource
GenerateTimeIntercepter generateTimeIntercepter;*/
@Resource
@Qualifier("dataSource")
private DataSource ds;
@Bean
public SqlSessionFactory sqlSessionFactory(PageInterceptor pageHelper) throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(ds);
//指定mapper xml目录
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
factoryBean.setMapperLocations(resolver.getResources("classpath:mapper/**/*.xml"));
factoryBean.setPlugins(new Interceptor[]{mybatisSqlInterceptor});
factoryBean.setPlugins(new Interceptor[] { pageHelper});
try {
return factoryBean.getObject();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
@Bean
public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
@Bean
public PageInterceptor pageHelper() {
PageInterceptor pageHelper = new PageInterceptor();
Properties p = new Properties();
p.setProperty("offsetAsPageNum", "true");
p.setProperty("rowBoundsWithCount", "true");
p.setProperty("reasonable", "true");
//p.setProperty("dialect", "mysql");
pageHelper.setProperties(p);
return pageHelper;
}
//关于事务管理器，不管是JPA还是JDBC等都实现自接口 PlatformTransactionManager
// 如果你添加的是 spring-boot-starter-jdbc 依赖，框架会默认注入 DataSourceTransactionManager 实例。
//在Spring容器中，我们手工注解@Bean 将被优先加载，框架不会重新实例化其他的 PlatformTransactionManager 实现类。
@Bean(name = "transactionManager")
@Primary
public DataSourceTransactionManager masterTransactionManager() {
//MyBatis自动参与到spring事务管理中，无需额外配置，只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源
// 与DataSourceTransactionManager引用的数据源一致即可，否则事务管理会不起作用。
return new DataSourceTransactionManager(ds);
}
}
自定义注解主要作用于dao层
SysLogController.class
package com.sale.controller.sysconfig.acl; import com.github.pagehelper.Page; import com.github.pagehelper.PageHelper; import com.sale.config.annotation.SysLogAnnotation; import com.sale.entity.SysLog; import com.sale.entity.acl.ACLUser; import com.sale.service.setting.acl.SysLogService; import com.sale.util.common.Comment; import com.sale.util.session.UserSession; import org.apache.commons.lang3.StringUtils; import org.apache.shiro.authz.annotation.RequiresPermissions; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.propertyeditors.CustomDateEditor; import org.springframework.web.bind.WebDataBinder; import org.springframework.web.bind.annotation.*; import java.text.ParseException; import java.text.ParsePosition; import java.text.SimpleDateFormat; import java.util.*; @RestController @CrossOrigin public class SysLogController { @Autowired private SysLogService sysLogService; @RequiresPermissions(value = {"system-log/select"}) @GetMapping("/selectSysLogByUserId") public Object selectSysLogByUserId(@RequestParam(required = false, defaultValue = "1") int pageIndex, @RequestParam(required = false, defaultValue = "10") int pageSize, SysLog sysLog) throws ParseException { Map<String, Object> map = new HashMap<>(); if (sysLog != null) { if (sysLog.getStartTime() != null && sysLog.getEndTime() != null) { sysLog.setStartTime(Comment.getStartTime(sysLog.getStartTime())); sysLog.setEndTime(Comment.getnowEndTime(sysLog.getEndTime())); } } List<SysLog> sysLogList = sysLogService.selectSysLogByUserId(sysLog); Page page = PageHelper.startPage(pageIndex, pageSize); map.put("currpage", String.valueOf(page.getPageNum())); map.put("totalpages", String.valueOf(page.getPages())); map.put("totalrecords", String.valueOf(page.getTotal())); map.put("sysLogList", sysLogList); return map; } @InitBinder public void init(WebDataBinder binder) { binder.registerCustomEditor(Date.class, new CustomDateEditor(new SimpleDateFormat("yyyy-MM-dd"), true)); } }
