0%

mybatis插件配合SpringMVC拦截器实现日志统计

在Mybatis的拦截器中,只能统计出最终执行的Sql语句,无法统计出每行语句执行的操作人。

如果想看一个用户主动对数据库的操作日志,则单使用拦截器无法实现。

可以借助SpringMvc的拦截器,将请求头的信息记录下来,这样就能获取到每一个人的操作日志。

新建一个 MyBatisIntercept 类,继承 HandlerInterceptorAdapter 拦截器 并 实现 Mybatis的Interceptor接口
拦截Update和Query操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

@Intercepts(
{
@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
}
)
public class MyBatisIntercept extends HandlerInterceptorAdapter implements Interceptor {
@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {

return true;
}
@Override
public Object intercept(Invocation invocation) throws Throwable {

}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}

@Override
public void setProperties(Properties properties) {

}
}

创建一个全局ThreadLocal对象,用于存储用SpringMvc拦截器进行来的用户身份信息

1
2
3
4
5
6
7
8
9

private ThreadLocal<Object> threadLocal = new InheritableThreadLocal<>();

@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
// 将请求中可以标识用户信息的数据给塞进去
objectThreadLocal.set("");
return true;
}

如果是同步操作的话,SpringMvc的拦截器和Mybatis的拦截器必然会在一个线程里面。

在拦截器中将用户信息给取出来,然后处理一下Mybatis的Sql语句,这样就能对整个语句进行一个操作人的记录。

1
2
3
4
5
6

@Override
public Object intercept(Invocation invocation) throws Throwable {
// 能将先前存储的用户信息给获取出来
threadLocal.get();
}

这样的方式用于记录操作人是可行的,但是这种方式会使Mybatis拦截器的职责不明确,需要去处理请求里面的内容。

如果有使用日志框架,可以使用MDC对象,MDC对象对ThreadLocal进行了一个优化,可以将request中的信息保存到MDC对象中,
然后配置logback的配置文件,直接将日志通过mq的方式进行存储处理。

最后成了这样:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150

/**
* @create : 2018-01-08 10:29
* mybatis 操作拦截器
* sql直接拷贝 http://phncz310.iteye.com/blog/2251712
*/
@Intercepts(
{
@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
}
)
public class MyBatisIntercept extends HandlerInterceptorAdapter implements Interceptor {

private static final Logger logger = LoggerFactory.getLogger(MyBatisIntercept.class);

@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
MDC.put("operationType", request.getHeader("operationType"));
return true;
}


@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
// 传入的对象
Object obj = args[1];

MappedStatement mappedStatement = (MappedStatement) args[0];
// 记录执行结果
Object resultObj = invocation.proceed();
String name = mappedStatement.getSqlCommandType().name().toUpperCase();
//执行的sql
BoundSql boundSql = mappedStatement.getBoundSql(obj);
Configuration configuration = mappedStatement.getConfiguration();
String sql;
try {
sql = showSql(configuration, boundSql);
}catch (Exception e){
sql = "SQL分析出错";
logger.warn("SQL分析出错 {}",JSONObject.toJSONString(resultObj));
return resultObj;
}
if (name.startsWith("INSERT")) {
logger.info("{}||{}", sql, sql.substring(sql.toUpperCase().indexOf("INTO") + 4, sql.toUpperCase().indexOf("(")).trim());
}
if (name.startsWith("UPDATE")) {
// 找where和limit中的参数就是条件
String keywords = sql.substring(sql.toUpperCase().lastIndexOf("WHERE")).toUpperCase();
if (keywords.contains("LIMIT")) {
keywords = keywords.substring("WHERE".length(), sql.toUpperCase().lastIndexOf("LIMIT"));
}
StringBuilder sb = new StringBuilder();
for (String key : keywords.split("AND")) {
sb.append(key.split("=")[1].trim()).append(",");
}
logger.info("{}||{}||{}", sql, sql.substring(name.length(), sql.toUpperCase().lastIndexOf("SET")).trim(), sb.toString());
}
if (name.startsWith("DELETE")) {
String keywords = sql.substring(sql.toUpperCase().lastIndexOf("WHERE")).toUpperCase();
if (keywords.contains("LIMIT")) {
keywords = keywords.substring("WHERE".length(), sql.toUpperCase().lastIndexOf("LIMIT"));
}
StringBuilder sb = new StringBuilder();
for (String key : keywords.split("AND")) {
sb.append(key.split("=")[1].trim()).append(",");
}
logger.info("{}||{}||{}", sql, sql.substring(sql.toUpperCase().lastIndexOf("FROM"), sql.toUpperCase().lastIndexOf("WHERE")).trim(), sb.toString());
}
if (name.startsWith("SELECT")) {
logger.info("查询结果 -> {} , {}", sql, JSONObject.toJSONString(resultObj));
}
return resultObj;
}

@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}

@Override
public void setProperties(Properties properties) {

}

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;
}

public static String showSql(Configuration configuration, BoundSql boundSql) {
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
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));
} else {
Map map = (Map) metaObject;
sql = sql.replaceFirst("\\?", getParameterValue(map.get(propertyName)));
}
}
}
}
return sql;
}
private String camelToUnderline(String param){
if (param==null||"".equals(param.trim())){
return "";
}
int len=param.length();
StringBuilder sb=new StringBuilder(len);
for (int i = 0; i < len; i++) {
char c=param.charAt(i);
if (Character.isUpperCase(c)){
sb.append("_");
sb.append(Character.toLowerCase(c));
}else{
sb.append(c);
}
}
return sb.toString();
}

}

logback的配置文件:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

<springProperty name="host" source="spring.rabbitmq.host"/>
<springProperty name="username" source="spring.rabbitmq.username"/>
<springProperty name="password" source="spring.rabbitmq.password"/>

<appender name="AMQP" class="org.springframework.amqp.rabbit.logback.AmqpAppender">
<layout>
<pattern>%X{operationType}||%X{operationId}||%X{X-B3-TraceId}||%X{X-B3-SpanId}||%m%n</pattern>
</layout>
<filter class="ch.qos.logback.core.filter.EvaluatorFilter">
<evaluator>
<!--判断操作人不为空-->
<expression>
mdc.get("operationType") != null
</expression>
</evaluator>
<OnMatch>ACCEPT</OnMatch>
<OnMismatch>DENY</OnMismatch>
</filter>
<host>${host}</host>
<port>5672</port>
<username>${username}</username>
<password>${password}</password>
<applicationId>AmqpAppenderTest</applicationId>
<generateId>true</generateId>
<exchangeName>operationWithParamDestination</exchangeName>
<charset>UTF-8</charset>
<durable>false</durable>
<deliveryMode>NON_PERSISTENT</deliveryMode>
</appender>