mybatis拦截器打印完整sql并记录数据库

叁歲伎倆 2023-10-07 17:25 37阅读 0赞

#### 结合自定义注解 ####

import java.lang.annotation.*;
    
    /**
     * 指定mapper方法中开启sql同步
     *
     * @author zhoumin
     */
    @Target(ElementType.METHOD)
    @Retention(RetentionPolicy.RUNTIME)
    @Documented
    public @interface SqlLogs {
        
    
        /**
         * 是否同步sql
         */
        boolean hasSqlLog() default false;
    
    }

#### 自定义拦截器中获取bean ####

import org.springframework.beans.BeansException;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.ApplicationContextAware;
    import org.springframework.stereotype.Component;
    
    @Component
    public class SpringContextHolder implements ApplicationContextAware {
        
    
        private static ApplicationContext applicationContext;
    
        @Autowired
        @Override
        public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        
            SpringContextHolder.applicationContext = applicationContext;
        }
    
    
        public static ApplicationContext getApplicationContext() {
        
            return applicationContext;
        }
    
    
        public static Object getBean(String name) {
        
            return getApplicationContext().getBean(name);
        }
    
        /**
         * 通过class获取Bean.
         *
         * @param clazz
         * @param <T>
         * @return
         */
        public static <T> T getBean(Class<T> clazz) {
        
            return getApplicationContext().getBean(clazz);
        }
    
        /**
         * 通过name,以及Clazz返回指定的Bean
         *
         * @param name
         * @param clazz
         * @param <T>
         * @return
         */
        public static <T> T getBean(String name, Class<T> clazz) {
        
            return getApplicationContext().getBean(name, clazz);
        }
    
    }

#### 自定义拦截器 ####

import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
    import com.baomidou.mybatisplus.extension.handlers.AbstractSqlParserHandler;
    import com.chinadci.fj.entity.Handlerecord;
    import com.chinadci.fj.mapper.HandlerecordMapper;
    import lombok.AllArgsConstructor;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.lang.StringUtils;
    import org.apache.ibatis.executor.Executor;
    import org.apache.ibatis.executor.statement.StatementHandler;
    import org.apache.ibatis.mapping.*;
    import org.apache.ibatis.plugin.*;
    import org.apache.ibatis.reflection.MetaObject;
    import org.apache.ibatis.reflection.SystemMetaObject;
    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.stereotype.Component;
    
    import java.lang.reflect.Method;
    import java.sql.Connection;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    import java.util.Properties;
    
    /**
     * sql同步
     *
     * @author zhoumin
     */
    @Slf4j
    @AllArgsConstructor
    @Intercepts({
        
            @Signature(type = Executor.class, method = "query", args = {
        MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
            @Signature(type = StatementHandler.class, method = "prepare", args = {
        Connection.class, Integer.class})
    })
    @Component
    public class SqlLogsInterceptor extends AbstractSqlParserHandler implements Interceptor {
        
    
        @Override
        public Object intercept(Invocation invocation) throws Throwable {
        
            Boolean hasSqlLogs = false;
            StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());
            MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
            this.sqlParser(metaObject);
            MappedStatement mappedStatement =
                    (MappedStatement) metaObject.getValue("delegate.mappedStatement");
            if (SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) {
        
                return invocation.proceed();
            }
            //获取执行方法的位置
            String namespace = mappedStatement.getId();
            //获取mapper名称
            String className = namespace.substring(0, namespace.lastIndexOf("."));
            //获取方法名
            String methedName = namespace.substring(namespace.lastIndexOf(".") + 1);
            //获取当前mapper 的方法
            Method[] ms = Class.forName(className).getMethods();
            for (Method m : ms) {
        
                if (m.getName().equals(methedName)) {
        
                    SqlLogs annotation = m.getAnnotation(SqlLogs.class);
                    if (annotation != null) {
        
                        hasSqlLogs = annotation.hasSqlLog();
                    }
                }
            }
            //如果是有注解值为true，便获取sql处理参数
            if (hasSqlLogs) {
        
                BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
                // 获取sql
                //String sql = formatSql(boundSql, mappedStatement.getConfiguration()).concat(";");
                String sql = formatSql(boundSql, mappedStatement.getConfiguration());
                Handlerecord handlerecord = new Handlerecord();
                handlerecord.setSqlstr(sql);
                handlerecord.setTitle(methedName);
                HandlerecordMapper handlerecordMapper = SpringContextHolder.getBean(HandlerecordMapper.class);
                handlerecordMapper.insert(handlerecord);
            }
            return invocation.proceed();
    
        }
    
    
        @Override
        public Object plugin(Object target) {
        
            return Plugin.wrap(target, this);
        }
    
        @Override
        public void setProperties(Properties properties) {
        
    
        }
    
    
        private static final ThreadLocal<SimpleDateFormat> DATE_FORMAT_THREAD_LOCAL = ThreadLocal.withInitial(() -> new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));
    
        /**
         * 获取完整的sql实体的信息
         *
         * @param boundSql
         * @return
         */
        private String formatSql(BoundSql boundSql, Configuration configuration) {
        
            String sql = boundSql.getSql();
            List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
            Object parameterObject = boundSql.getParameterObject();
            // 输入sql字符串空判断
            if (StringUtils.isBlank(sql)) {
        
                return "";
            }
            if (configuration == null) {
        
                return "";
            }
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            sql = beautifySql(sql);
            // 参考mybatis 源码 DefaultParameterHandler
            if (parameterMappings != null) {
        
                for (ParameterMapping parameterMapping : parameterMappings) {
        
                    if (parameterMapping.getMode() != ParameterMode.OUT) {
        
                        Object value;
                        String propertyName = parameterMapping.getProperty();
                        if (boundSql.hasAdditionalParameter(propertyName)) {
        
                            value = boundSql.getAdditionalParameter(propertyName);
                        } else if (parameterObject == null) {
        
                            value = null;
                        } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
        
                            value = parameterObject;
                        } else {
        
                            MetaObject metaObject = configuration.newMetaObject(parameterObject);
                            value = metaObject.getValue(propertyName);
                        }
                        String paramValueStr = "";
                        if (value instanceof String) {
        
                            paramValueStr = "'" + value + "'";
                        } else if (value instanceof Date) {
        
                            paramValueStr = "'" + DATE_FORMAT_THREAD_LOCAL.get().format(value) + "'";
                        } else {
        
                            paramValueStr = value + "";
                        }
                        sql = sql.replaceFirst("\\?", paramValueStr);
                    }
                }
            }
            return sql;
        }
    
        private String beautifySql(String sql) {
        
            sql = sql.replaceAll("[\\s\n ]+", " ");
            return sql;
        }
    
    
    }

#### 在mapper中使用 ####

@SqlLogs(hasSqlLog=true)
       void insertOverseer(Overseer overseer);