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拦截器发送邮件失败！"); // } return getDefaultReturnValue(invocation); } } result = invocation.proceed(); if (result instanceof Collection) { Collection c = (Collection) result; int size = c.size(); if (size > allowMaxReturn) { // result = getDefaultReturnValue(invocation); try { Configuration configuration = mappedStatement.getConfiguration(); sql = getSql(configuration, boundSql); String emailMes = "vin-sql拦截器【未拦截】：发现DQL返回结果:" + size + "大于最大返回值数量" + allowMaxReturn + "---> " + sql; log.warn(emailMes); emailPool.putEmailToQueue(emailMes); // EmailUtil.sendMessage(emailMes, "tech-vin@qipeipu.com"); } catch (Exception e) { log.warn("vin-sql拦截器发送邮件失败！"); } } } return result; } @Override public Object plugin(Object target) { if (target instanceof Executor) { return Plugin.wrap(target, this); } else { return target; } } @Override public void setProperties(Properties properties) { if (Objects.isNull(properties)) { return; } if (properties.containsKey("allowMaxReturn")) { allowMaxReturn = Integer.valueOf(properties.getProperty("allowMaxReturn")); } } private String getSql(Configuration configuration, BoundSql boundSql) { // 输入sql字符串空判断 String sql = boundSql.getSql(); if (StringUtils.isBlank(sql)) { return ""; } //美化sql sql = beautifySql(sql); //填充占位符 Object parameterObject = boundSql.getParameterObject(); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); if (!parameterMappings.isEmpty() && parameterObject != null) { TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { sql = this.replacePlaceholder(sql, parameterObject); } else { MetaObject metaObject = configuration.newMetaObject(parameterObject); for (ParameterMapping parameterMapping : parameterMappings) { String propertyName = parameterMapping.getProperty(); if (metaObject.hasGetter(propertyName)) { Object obj = metaObject.getValue(propertyName); sql = replacePlaceholder(sql, obj); } else if (boundSql.hasAdditionalParameter(propertyName)) { Object obj = boundSql.getAdditionalParameter(propertyName); sql = replacePlaceholder(sql, obj); } } } } return sql; } private String replacePlaceholder(String sql, Object parameterObject) { String result; if (parameterObject instanceof String) { result = "'" + parameterObject.toString() + "'"; } else if (parameterObject instanceof Date) { result = "'" + getDate2String((Date) parameterObject) + "'"; } else { result = parameterObject.toString(); } return sql.replaceFirst("\\?", result); } private String getDate2String(Date parameterObject) { return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(parameterObject); } /** * 获取where后的条件语句 * * @param sql * @return * @throws JSQLParserException */ public Map<String, String> getWhere(String sql) throws JSQLParserException { CCJSqlParserManager parserManager = new CCJSqlParserManager(); Map<String, String> result = Maps.newHashMap(); try { Select select = (Select) parserManager.parse(new StringReader(sql)); PlainSelect plain = (PlainSelect) select.getSelectBody(); Expression whereExpression = plain.getWhere(); //获取join List<Join> joins = plain.getJoins(); result.put("success", Objects.isNull(whereExpression) ? "" : whereExpression.toString()); result.put("join", CollectionUtils.isEmpty(joins) ? "" : "1"); return result; } catch (Exception e) { // log.warn("vin-sql拦截器无法解析sql:{}", sql); return result; } } /** * 获取主表名 * * @param sql * @return * @throws JSQLParserException */ public String getMainTableName(String sql) throws JSQLParserException { try { Statement statement = CCJSqlParserUtil.parse(sql); Select selectStatement = (Select) statement; List<String> tableList = new TablesNamesFinder().getTableList(selectStatement); return tableList.get(0); } catch (Exception e) { log.warn("vin-sql拦截器无法解析sql:{}", sql); return ""; } } /** * 返回默认的值，list类型的返回空list,数值类型的返回0 * * @param invocation * @return */ private Object getDefaultReturnValue(Invocation invocation) { Class returnType = invocation.getMethod().getReturnType(); if (returnType.equals(List.class)) { return Lists.newArrayList(); } else if (returnType.equals(Set.class)) { return Sets.newHashSet(); } else if (returnType.equals(Integer.TYPE) || returnType.equals(Long.TYPE) || returnType.equals(Integer.class) || returnType.equals(Long.class)) { return 0; } return null; } /** * 美化sql * * @param sql * @return */ private String beautifySql(String sql) { return sql.replaceAll("[\\s\n ]+", " "); } }

效果：

三、估算sql耗时拦截器具体实现

@Slf4j @Intercepts({ @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}), @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})} ) public class SqlInterceptor implements Interceptor { private int MIN_SIZE = -1; private int OPMITIZE_SIZE = 0; @Override public Object intercept(Invocation invocation) throws Throwable { MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0]; Object parameter = null; if (invocation.getArgs().length > 1) { parameter = invocation.getArgs()[1]; } String sqlId = mappedStatement.getId(); BoundSql boundSql = mappedStatement.getBoundSql(parameter); Configuration configuration = mappedStatement.getConfiguration(); long startTime = System.currentTimeMillis(); Object result = null; try { result = invocation.proceed(); } finally { try { long sqlCostTime = System.currentTimeMillis() - startTime; String sql = getSql(configuration, boundSql); formatSqlLog(mappedStatement.getSqlCommandType(), sqlId, sql, sqlCostTime, result); } catch (Exception ignored) { } } return result; } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { if (properties == null) { return; } if (properties.containsKey("minLogSize")) { MIN_SIZE = Integer.valueOf(properties.getProperty("minLogSize")); } } private String getSql(Configuration configuration, BoundSql boundSql) { // 输入sql字符串空判断 String sql = boundSql.getSql(); if (StringUtils.isBlank(sql)) { return ""; } //美化sql sql = beautifySql(sql); //填充占位符, 目前基本不用mybatis存储过程调用,故此处不做考虑 Object parameterObject = boundSql.getParameterObject(); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); if (!parameterMappings.isEmpty() && parameterObject != null) { TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { sql = this.replacePlaceholder(sql, parameterObject); } else { MetaObject metaObject = configuration.newMetaObject(parameterObject); for (ParameterMapping parameterMapping : parameterMappings) { String propertyName = parameterMapping.getProperty(); if (metaObject.hasGetter(propertyName)) { Object obj = metaObject.getValue(propertyName); sql = replacePlaceholder(sql, obj); } else if (boundSql.hasAdditionalParameter(propertyName)) { Object obj = boundSql.getAdditionalParameter(propertyName); sql = replacePlaceholder(sql, obj); } } } } return sql; } private String beautifySql(String sql) { return sql.replaceAll("[\\s\n ]+", " "); } private String replacePlaceholder(String sql, Object parameterObject) { String result; if (parameterObject instanceof String) { result = "'" + parameterObject.toString() + "'"; } else if (parameterObject instanceof Date) { result = "'" + getDate2String((Date) parameterObject) + "'"; } else { result = parameterObject.toString(); } return sql.replaceFirst("\\?", result); } private String getDate2String(Date parameterObject) { return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(parameterObject); } private void formatSqlLog(SqlCommandType sqlCommandType, String sqlId, String sql, long costTime, Object obj) { String log = String.format("DAO [%s]\n[%dms] ===> %s\n", sqlId, costTime, sql); if (sqlCommandType == SqlCommandType.UPDATE || sqlCommandType == SqlCommandType.INSERT || sqlCommandType == SqlCommandType.DELETE) { log += "Count ===> " + obj; } if (costTime > MIN_SIZE) { SqlInterceptor.log.warn(log); } } }

效果：

总结：

以上是sql拦截器的基本作用，不管在测试环境还是生产环境，效果还是挺明显的，妈妈再也不用担心会发生FGC了。此外，sql拦截器还可以实现更多的东西，多人关注的话会继续更。

ps：如有错误，欢迎指正。