1、前言
这个东西卡了我一天,终于弄好了,写个博客庆祝一下,顺便让帮助别人。
我司有个需求,记录特定功能执行的sql,并存入数据库,这时我就想到了mybatis的拦截器。
2、参考文献:
https://blog.csdn.net/minghao0508/article/details/124420953
https://www.jb51.net/article/236467.htm
https://blog.csdn.net/tif_very/article/details/115034221
https://pythonjishu.com/mgghxgbqafqmtmt/
3、获得完整SQL、 存入数据库
问题在于存入数据库,Mybatis的插件先于spring容器的完全初始化,虽然加了@Component会被扫描加入容器管理,但是此时Mybatis的拦截器Dblnterceptor注入的对象EncryptManager是还未初始化到容器的。所以通过这种方式拿到的bean为空。
简单来说就是@Autowired 不管用,得用别的方法,这个方法就是创建SpringBeanUtils工具类getBean,文献3有代码。
package com.ty.crm.util;
import com.spire.pdf.packages.spreG;
import com.ty.crm.DO.Sys_Operation_LogDo;
import com.ty.crm.mapper.OperationLogMapper;
import com.ty.crm.shior.token.manager.TokenManager;
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.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
@Intercepts(
{
@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
}
)
@Component
public class SqlInterceptor implements Interceptor {
Properties properties = null;
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object target = invocation.getTarget();
Method method = invocation.getMethod();
Object[] args = invocation.getArgs();
this.addSqlLog(args);
Object result = invocation.proceed();
return result;
}
@Override
public Object plugin(Object target) {
if (target instanceof Executor) {
return Plugin.wrap(target, this);
}
return target;
}
@Override
public void setProperties(Properties properties) {
this.properties = properties;
}
public void addSqlLog(Object[] args) {
List<String> whiteList = Arrays.asList(
"com.ty.crm.mapper.OrderMapper.insertpurchase2List",
"com.ty.crm.mapper.OrderMapper.updatePurchaserequisition2Detail",
"com.ty.crm.mapper.OrderMapper.updatePurchaserequisition1Detail"
);
String sql = null;
MappedStatement mappedStatement = null;
try {
mappedStatement = (MappedStatement) args[0];
Object parameter = args[1];
if (!whiteList.contains(mappedStatement.getId())) {
return;
}
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
Configuration configuration = mappedStatement.getConfiguration();
sql = getSql(configuration, boundSql);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("日志保存异常");
}
if (sql == null || "".equals(sql)) {
return;
}
Sys_Operation_LogDo sys_operation_logDo = new Sys_Operation_LogDo();
sys_operation_logDo.setAccount_no(TokenManager.getAccountNo());
sys_operation_logDo.setLog_usr_id(TokenManager.getUserId().intValue());
sys_operation_logDo.setLog_date(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
sys_operation_logDo.setLog_sql(sql);
try {
OperationLogMapper operationLogMapper = SpringBeanUtils.getBean(OperationLogMapper.class);
operationLogMapper.insertOperationLog(sys_operation_logDo);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("日志保存异常");
}
}
public static String getSql(Configuration configuration, BoundSql boundSql) {
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
if (parameterMappings != null && parameterMappings.size() != 0 && parameterObject != null) {
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(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("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
} else if (boundSql.hasAdditionalParameter(propertyName)) {
Object obj = boundSql.getAdditionalParameter(propertyName);
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
} else {
sql = sql.replaceFirst("\\?", "缺失");
}
}
}
}
return sql;
}
private static String getParameterValue(Object obj) {
String value = null;
if (obj instanceof String) {
value = "'" + obj.toString() + "'";
} else if (obj instanceof 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;
}
}
mybatis配置xml文件添加
<!-- 拦截器-->
<plugin interceptor="com.ty.crm.util.SqlInterceptor">
<property name="param1" value="value1"/>
</plugin>