项目中使用的MybatisPlus，要做一个基于Elastic的日志查看功能，需要统计sql相关信息。
定义一个ExecuteSqlPrintInnerInterceptor，继承 InnerInterceptor。
package com.xxxx.interceptor;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.visitor.SchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
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.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletRequest;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.sql.SQLException;
import java.text.DateFormat;
import java.util.*;
/**
* @description：
* @author: zhanglao
* @date: 2022/3/28 11:03 上午
*/
@Slf4j
public class ExecuteSqlPrintInnerInterceptorimplements InnerInterceptor{
@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException{
Configuration configuration = ms.getConfiguration();
handleSql(configuration, boundSql, ms.getId());
}
@Override
public void beforeUpdate(Executor executor, MappedStatement ms, Object parameter) throws SQLException{
Configuration configuration = ms.getConfiguration();
handleSql(configuration, ms.getBoundSql(parameter), ms.getId());
}
private static void handleSql(Configuration configuration, BoundSql boundSql, String sqlId) {
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
//替换空格、换行、tab缩进等
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
if (parameterMappings.size() >0 && parameterObject !=null) {
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?", getParameterValue(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 = sql.replaceFirst("\\?", getParameterValue(obj));
} else if (boundSql.hasAdditionalParameter(propertyName)) {
Object obj = boundSql.getAdditionalParameter(propertyName);
sql = sql.replaceFirst("\\?", getParameterValue(obj));
}
}
}
}
logs(sql, sqlId);
}
private static StringgetParameterValue(Object obj) {
String value;
if (objinstanceof String) {
value ="'" + obj.toString() +"'";
} else if (objinstanceof Date) {
DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
value ="'" + formatter.format(new Date()) +"'";
} else {
if (obj !=null) {
value = obj.toString();
} else {
value ="";
}
}
return value.replace("$", "\\$");
}
/**
* log打印
*/
private static void logs(String sql, String sqlId) {
ExecuteParseEntity executeParseEntity =new ExecuteParseEntity();
HttpServletRequest request =((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
Enumeration<String> params = request.getAttributeNames();
executeParseEntity.setMethodType(request.getMethod());
executeParseEntity.setRequestAddress(getRemoteHost(request));
executeParseEntity.setRequestURI(request.getRequestURI());
executeParseEntity.setSqlId(sqlId);
executeParseEntity.setSql(sql);
parseSql(executeParseEntity);
log.info(JSON.toJSONString(executeParseEntity));
}
private static void parseSql(ExecuteParseEntity executeParseEntity) {
List<SQLStatement> sqlStatements = SQLUtils.parseStatements(executeParseEntity.getSql(), JdbcConstants.MYSQL);
for (SQLStatement sqlStatement : sqlStatements) {
SchemaStatVisitor schemaStatVisitor = SQLUtils.createSchemaStatVisitor(JdbcConstants.MYSQL);
sqlStatement.accept(schemaStatVisitor);
Map<TableStat.Name, TableStat> tables = schemaStatVisitor.getTables();
Collection<TableStat.Column> columns = schemaStatVisitor.getColumns();
if (Objects.nonNull(tables)) {
List<Map<String, List<String>>> tableInfo =new ArrayList<>();
tables.forEach(((name, tableStat) ->{
Map<String, List<String>> tabMap =new HashMap<>();
List<String> columsList =new ArrayList<>();
if (tableStat.getCreateCount() >0 || tableStat.getInsertCount() >0) {
log.info("update to: table[{}]", name.getName().toLowerCase());
columns.stream().filter(column -> Objects.equals(column.getTable().toLowerCase(), name.getName().toLowerCase())).forEach(column ->{
columsList.add(column.getName());
// log.info("to: table[{}] column[{}]", column.getTable().toLowerCase(), column.getName().toLowerCase());
});
tabMap.put(name.getName(), columsList);
} else if (tableStat.getSelectCount() >0) {
log.info("select from: table[{}]", name.getName().toLowerCase());
columns.stream().filter(column -> Objects.equals(column.getTable().toLowerCase(), name.getName().toLowerCase())).forEach(column ->{
columsList.add(column.getName());
// log.info("from: table[{}] column[{}]", column.getTable().toLowerCase(), column.getName().toLowerCase());
});
tabMap.put(name.getName(), columsList);
}
tableInfo.add(tabMap);
}));
executeParseEntity.setTableInfo(tableInfo);
}
}
}
/**
* 获取请求body内容
*/
public static StringgetBodyString(HttpServletRequest request) {
StringBuilder sb =new StringBuilder();
InputStream inputStream =null;
BufferedReader reader =null;
try {
inputStream = request.getInputStream();
reader =new BufferedReader(
new InputStreamReader(inputStream, Charset.forName("UTF-8")));
char[] bodyCharBuffer =new char[1024];
int len =0;
while ((len = reader.read(bodyCharBuffer)) != -1) {
sb.append(new String(bodyCharBuffer, 0, len));
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (inputStream !=null) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (reader !=null) {
try {
reader.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return sb.toString();
}
/**
* 获取目标主机的ip
* @param request
* @return
*/
private static StringgetRemoteHost(HttpServletRequest request) {
String ip = request.getHeader("x-forwarded-for");
if (ip ==null || ip.length() ==0 ||"unknown".equalsIgnoreCase(ip)) {
ip = request.getHeader("Proxy-Client-IP");
}
if (ip ==null || ip.length() ==0 ||"unknown".equalsIgnoreCase(ip)) {
ip = request.getHeader("WL-Proxy-Client-IP");
}
if (ip ==null || ip.length() ==0 ||"unknown".equalsIgnoreCase(ip)) {
ip = request.getRemoteAddr();
}
return ip.contains("0:0:0:0:0:0:0:1") ?"127.0.0.1" : ip;
}
}
ExecuteParseEntity就是个实体接收，方便查看参数含义。
package com.xxxx.interceptor;
import com.sun.javafx.collections.MappingChange;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
import java.util.Map;
/**
* @description：
* @author: zhanglao
* @date: 2022/3/28 3:38 下午
*/
@Data
public class ExecuteParseEntity implements Serializable {
private static final long serialVersionUID = -1673663624670778651L;
/**
* 方法类型
*/
private String methodType;
/**
* 请求域名
*/
private String requestAddress;
/**
* 请求api
*/
private String requestURI;
/**
* sql方法
*/
private String sqlId;
/**
* sql语句
*/
private String sql;
/**
* 表相关信息
*/
private List<Map<String, List<String>>> tableInfo;
@Override
public String toString() {
return "ExecuteParseEntity{" +
"requestAddress='" + requestAddress + '\'' +
", requestURI='" + requestURI + '\'' +
", sqlId='" + sqlId + '\'' +
", sql='" + sql + '\'' +
", tableInfo=" + tableInfo +
'}';
}
}
最后需要在**MybatisPlusConfig**将拦截器添加
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new ExecuteSqlPrintInnerInterceptor());
return interceptor;
}
结果：
> {
> "requestAddress":"127.0.0.1",
> "requestURI":"/admin/role/permission/list",
> "sql":"SELECT id,role_id,permission_id,gmt_create,create_nick,gmt_modified,modified_nick
> FROM role_permission WHERE (role_id IN (1))",
"sqlId":"com.xxxx.dal.mapper.RolePermissionMapper.selectList",
"tableInfo":[{"role_permission":["id","role_id","permission_id","gmt_create","create_nick","gmt_modified","modified_nick"]}]
}
