在Mybatis的拦截器中，只能统计出最终执行的Sql语句，无法统计出每行语句执行的操作人。
如果想看一个用户主动对数据库的操作日志，则单使用拦截器无法实现。
可以借助SpringMvc的拦截器，将请求头的信息记录下来，这样就能获取到每一个人的操作日志。
新建一个 MyBatisIntercept 类，继承 HandlerInterceptorAdapter 拦截器 并 实现 Mybatis的Interceptor接口
拦截Update和Query操作
@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拦截器进行来的用户身份信息
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语句，这样就能对整个语句进行一个操作人的记录。
@Override
public Object intercept(Invocation invocation) throws Throwable {
// 能将先前存储的用户信息给获取出来
threadLocal.get();
}
这样的方式用于记录操作人是可行的，但是这种方式会使Mybatis拦截器的职责不明确，需要去处理请求里面的内容。
如果有使用日志框架，可以使用MDC对象，MDC对象对ThreadLocal进行了一个优化，可以将request中的信息保存到MDC对象中，
然后配置logback的配置文件，直接将日志通过mq的方式进行存储处理。
最后成了这样：
/**
* @author : 小咖啡
* @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的配置文件：
<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>
