Mybatis通过拦截器打印并保存执行SQL

今天药忘吃喽~ 2022-03-27 02:05 433阅读 0赞

### Web项目 ###

**1.创建拦截器类**

import java.text.DateFormat;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Locale;
    import java.util.Map;
    import java.util.Properties;
    import java.util.UUID;
    
    import javax.servlet.http.HttpServletRequest;
    
    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.Interceptor;
    import org.apache.ibatis.plugin.Intercepts;
    import org.apache.ibatis.plugin.Invocation;
    import org.apache.ibatis.plugin.Plugin;
    import org.apache.ibatis.plugin.Signature;
    import org.apache.ibatis.reflection.MetaObject;
    import org.apache.ibatis.session.Configuration;
    import org.apache.ibatis.session.ResultHandler;
    import org.apache.ibatis.session.RowBounds;
    import org.apache.ibatis.type.TypeHandlerRegistry;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Component;
    import org.springframework.web.context.request.RequestAttributes;
    import org.springframework.web.context.request.RequestContextHolder;
    import org.springframework.web.context.request.ServletRequestAttributes;
    
    import cn.com.app.service.UserInfoService;
    
    @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 ExecuteSqlLogInterceptor implements Interceptor {
    	
    	@Autowired
    	private UserInfoService userInfoService;
    
    	@Override
    	public Object intercept(Invocation invocation) throws Throwable {
    		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    		MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
    		Object parameter = null;
    		if (invocation.getArgs().length > 1) {
    			parameter = invocation.getArgs()[1];
    		}
    		BoundSql boundSql = mappedStatement.getBoundSql(parameter);
    		Configuration configuration = mappedStatement.getConfiguration();
    		String exexcSql = getRuntimeExeSql(configuration, boundSql);
    		//System.out.println("----Sql----"+exexcSql);
    		//保存修改和删除操作日志
    		if(exexcSql.substring(0, 6).equals("UPDATE") || exexcSql.substring(0, 6).equals("DELETE")){
    			Map<String, Object> p = new HashMap<String, Object>();
    			String yhbh = "";
    			String xm = "";
    			String jh = "";
    			String ip = "";
    			RequestAttributes requestAttributes = RequestContextHolder.getRequestAttributes();
    			if(requestAttributes != null){ //登录后不为空,没登录执行定时任务时为空
    				HttpServletRequest request = ((ServletRequestAttributes) requestAttributes).getRequest();
    				yhbh = (String) request.getSession().getAttribute("yhbh");
    				jh = (String) request.getSession().getAttribute("jh");
    				xm = (String) request.getSession().getAttribute("xm");
    				ip = (String) request.getSession().getAttribute("ip");	
    			}
    			p.put("yhbh", yhbh);
    			p.put("jh", jh);
    			p.put("xm", xm);
    			p.put("ip", ip);
    			p.put("jlbh", UUID.randomUUID().toString().replaceAll("-", ""));
    			p.put("cznr", exexcSql.length()>2000?exexcSql.substring(0, 2000):exexcSql);
    			p.put("czsj", format.format(new Date()));
    			userInfoService.insertSys_Log(p);
    		}
    		return invocation.proceed();
    	}
    
    	@Override
    	public Object plugin(Object target) {
    		return Plugin.wrap(target, this);
    	}
    
    	@Override
    	public void setProperties(Properties props) {
    	}
    
    	private static String getRuntimeExeSql(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));
    					}
    				}
    			}
    		}
    		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;
    	}
    }

**2.mybatis-config添加拦截器插件**

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
    	
    	<plugins>
    		<plugin interceptor="cn.com.app.util.ExecuteSqlLogInterceptor"></plugin>
    	</plugins>
    	
    </configuration>

### Spring Boot项目 ###

**只需在拦截器类上面添加@Component注解实例化到spring容器中即可，无需配置mybatis-config**

**![20190111173521328.png][]**

[20190111173521328.png]: /images/20220327/bc437470a3d74e47bbbc1cc2e6964ec8.png