java实现获取项目执行的完整sql
一,继承AbstractSqlParserHandler并实现拦截器(Interceptor)
package com.cjl.industrial.interceptor;
import cn.hutool.core.date.DateUtil;
import com.baomidou.mybatisplus.core.enums.IEnum;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.handlers.AbstractSqlParserHandler;
import com.cjl.industrial.utils.Result;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.CollectionUtils;
import org.springframework.web.client.RestTemplate;
import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
/**
* 自定义mybatis插件,实现输出实际执行sql语句
*
* @author tangjizhouchn@foxmail.com
* @date 2019-08-18
*/
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}),
@Signature(type = StatementHandler.class, method = "batch", args = {Statement.class})
})
@Slf4j
public class MybatisSqlInterceptor extends AbstractSqlParserHandler implements Interceptor {
/**
* 获取配置中需要拦截的表
*/
@Value("#{'${tmall.sync.tables:}'.split(',')}")
private List<String> tableNames;
@Resource
private RestTemplate restTemplate;
@Value("${manager.data}")
private String url;
/**
* 忽略插入sql_log表的语句
*/
private static final String IGNORE_SQL_PREFIX = "select";
private static final String IGNORE_SQL_INSERT = "insert";
@Override
public Object intercept(Invocation invocation) throws Throwable {
if (CollectionUtils.isEmpty(tableNames)) {
return invocation.proceed();
}
StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
String sql = boundSql.getSql().replaceAll("\\s+", " ").toLowerCase();
if (sql.toLowerCase().startsWith(IGNORE_SQL_PREFIX) || sql.toLowerCase().startsWith(IGNORE_SQL_INSERT)) {
return invocation.proceed();
}
List<ParameterMapping> parameterMappings = new ArrayList<>(boundSql.getParameterMappings());
Object parameterObject = boundSql.getParameterObject();
if (parameterMappings.isEmpty() && parameterObject == null) {
log.warn("parameterMappings is empty or parameterObject is null");
return invocation.proceed();
}
Configuration configuration = mappedStatement.getConfiguration();
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
try {
this.sqlParser(metaObject);
String parameter = "null";
MetaObject newMetaObject = configuration.newMetaObject(parameterObject);
for (ParameterMapping parameterMapping : parameterMappings) {
if (parameterMapping.getMode() == ParameterMode.OUT) {
continue;
}
String propertyName = parameterMapping.getProperty();
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
parameter = getParameterValue(parameterObject);
} else if (newMetaObject.hasGetter(propertyName)) {
parameter = getParameterValue(newMetaObject.getValue(propertyName));
} else if (boundSql.hasAdditionalParameter(propertyName)) {
parameter = getParameterValue(boundSql.getAdditionalParameter(propertyName));
}
// fixme 此处不严谨,若sql语句中有❓,则替换错位。?️
sql = sql.replaceFirst("\\?", parameter);
}
// 将拦截到的sql语句插入日志表中()
//自己操作
} catch (Exception e) {
log.error(String.format("intercept sql error: [%s]", sql), e);
}
return invocation.proceed();
}
/**
* 获取参数
*
* @param param Object类型参数
* @return 转换之后的参数
*/
private static String getParameterValue(Object param) {
if (param == null) {
return "null";
}
if (param instanceof Number) {
return param.toString();
}
String value = null;
if (param instanceof String) {
value = param.toString();
} else if (param instanceof Date) {
DateUtil.format((Date) param, "yyyy-MM-dd HH:mm:ss");
} else if (param instanceof IEnum) {
value = String.valueOf(((IEnum) param).getValue());
} else {
value = param.toString();
}
return StringUtils.quotaMark(value);
}
@Override
public Object plugin(Object o) {
if (o instanceof StatementHandler) {
return Plugin.wrap(o, this);
}
return o;
}
@Override
public void setProperties(Properties properties) {
}
}
二,yml文件,填上需要获取sql 的表名称
tmall:
sync:
tables: gas_basic_industrial_cylinder,record