在Mybatis的攔截器中照雁，只能統(tǒng)計(jì)出最終執(zhí)行的Sql語(yǔ)句，無(wú)法統(tǒng)計(jì)出每行語(yǔ)句執(zhí)行的操作人答恶。
如果想看一個(gè)用戶(hù)主動(dòng)對(duì)數(shù)據(jù)庫(kù)的操作日志饺蚊，則單使用攔截器無(wú)法實(shí)現(xiàn)。
可以借助SpringMvc的攔截器悬嗓，將請(qǐng)求頭的信息記錄下來(lái)污呼，這樣就能獲取到每一個(gè)人的操作日志。
新建一個(gè) MyBatisIntercept 類(lèi)包竹，繼承 HandlerInterceptorAdapter 攔截器 并 實(shí)現(xiàn) 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) {
}
}
創(chuàng)建一個(gè)全局ThreadLocal對(duì)象燕酷，用于存儲(chǔ)用SpringMvc攔截器進(jìn)行來(lái)的用戶(hù)身份信息
private ThreadLocal<Object> threadLocal = new InheritableThreadLocal<>();
@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
// 將請(qǐng)求中可以標(biāo)識(shí)用戶(hù)信息的數(shù)據(jù)給塞進(jìn)去
objectThreadLocal.set("");
return true;
}
如果是同步操作的話，SpringMvc的攔截器和Mybatis的攔截器必然會(huì)在一個(gè)線程里面映企。
在攔截器中將用戶(hù)信息給取出來(lái)悟狱，然后處理一下Mybatis的Sql語(yǔ)句静浴，這樣就能對(duì)整個(gè)語(yǔ)句進(jìn)行一個(gè)操作人的記錄堰氓。
@Override
public Object intercept(Invocation invocation) throws Throwable {
// 能將先前存儲(chǔ)的用戶(hù)信息給獲取出來(lái)
threadLocal.get();
}
這樣的方式用于記錄操作人是可行的，但是這種方式會(huì)使Mybatis攔截器的職責(zé)不明確苹享，需要去處理請(qǐng)求里面的內(nèi)容双絮。
如果有使用日志框架，可以使用MDC對(duì)象得问，MDC對(duì)象對(duì)ThreadLocal進(jìn)行了一個(gè)優(yōu)化囤攀，可以將request中的信息保存到MDC對(duì)象中，
然后配置logback的配置文件宫纬，直接將日志通過(guò)mq的方式進(jìn)行存儲(chǔ)處理焚挠。
最后成了這樣：
/**
* @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();
// 傳入的對(duì)象
Object obj = args[1];
MappedStatement mappedStatement = (MappedStatement) args[0];
// 記錄執(zhí)行結(jié)果
Object resultObj = invocation.proceed();
String name = mappedStatement.getSqlCommandType().name().toUpperCase();
//執(zhí)行的sql
BoundSql boundSql = mappedStatement.getBoundSql(obj);
Configuration configuration = mappedStatement.getConfiguration();
String sql;
try {
sql = showSql(configuration, boundSql);
}catch (Exception e){
sql = "SQL分析出錯(cuò)";
logger.warn("SQL分析出錯(cuò) {}",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中的參數(shù)就是條件
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("查詢(xún)結(jié)果 -> {} , {}", 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>
