mybatis的sql拦截器应用
前言
在项目上线前的开发及调试中,都需要清楚的知道每条sql的用时来避免在生产环境出现慢查询,甚至对于业务逻辑较复杂的应用,还需防止大对象的产生(比如千万级别的集合),此时我们完全可以使用mybatis的sql拦截器来实现以上效果。下面是一个sql拦截器的简单记录,以加深印象。
作用
拦截器实现的功能有:
- 拦截全表查询sql(可配置白名单)
- 防止大对象的产生(集合大于4万的sql进行告警,并不拦截)
- 输出完整的sql
- 输出sql的大约用时
示例代码
一、引入相关mybatis的maven
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
//用于解析sql
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.9.5</version>
</dependency>
(具体拦截器配置省略,如不清楚该学习了)
二、sql拦截器具体实现
@Slf4j
@Intercepts({
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})}
)
@Component
public class SqlInterceptor implements Interceptor {
/**
* 全表扫的白名单,接入了disconf,也可以hardcode字符串
*/
@Autowired(required = false)
private SqlIntercepterWhiteListConf sqlIntercepterWhiteListConf;
/**
* DQL最大返回值
*/
private Integer allowMaxReturn;
@Override
public Object intercept(Invocation invocation) throws Throwable {
EmailPool emailPool = EmailPool.getInstance();
String whiteList = sqlIntercepterWhiteListConf.getSqlWhiteList();
Object result;
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
Object parameter = invocation.getArgs()[1];
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
String sql = boundSql.getSql();
Map<String, String> whereResult = getWhere(sql);
String where = whereResult.get("success");
String join = whereResult.get("join");
if (Objects.nonNull(where) && StringUtils.isBlank(join)) {
where = where.replace("1 = 1", "");
String tableName = getMainTableName(sql);
if (StringUtils.isNotEmpty(whiteList) && !StringUtils.containsIgnoreCase(whiteList, tableName) && StringUtils.isBlank(where)) {
String emailMes = "vin-sql拦截器【已拦截】:出现不在配置内的全表扫描:" + tableName + "--->" + beautifySql(sql);
log.warn(emailMes);
emailPool.putEmailToQueue(emailMes);
// try {
// EmailUtil.sendMessage(emailMes, "tech-vin@qipeipu.com");
// } catch (Exception e) {
// log.warn("vin-sql拦截器发送邮件失