mybatis日志问号替换

需求

项目里使用 logback+log4j 作为日志框架, mybatis 支持 log4j 作为日志框架, 默认即输出日志如

==>  Preparing: select * from idol where name = ?
==> Parameters: nico(String)
<==      Total: 1 

每次验证sql都要手动替换问号很麻烦, 就写个插件直接打印实际执行的完整sql

打印日志时机

// org.apache.ibatis.executor;
public class SimpleExecutor extends BaseExecutor {
    @Override
    public <E> List<E> doQuery(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        Statement stmt = null;
        try {
            Configuration configuration = ms.getConfiguration();
            StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameter, rowBounds, resultHandler, boundSql);
            // 打sql模板日志
            stmt = prepareStatement(handler, ms.getStatementLog());
            // 打实际参数
            return handler.<E>query(stmt, resultHandler);
        } finally {
            closeStatement(stmt);
        }
    }
}

// org.apache.ibatis.logging.jdbc;
public final class ConnectionLogger extends BaseJdbcLogger implements InvocationHandler {
    @Override
    public Object invoke(Object proxy, Method method, Object[] params) {
        // log sql template
    }
}

// org.apache.ibatis.logging.jdbc;
public final class PreparedStatementLogger extends BaseJdbcLogger implements InvocationHandler {
    @Override
    public Object invoke(Object proxy, Method method, Object[] params) {
        // log paramaters
    }
    
}

// org.apache.ibatis.logging.jdbc;
public final class ResultSetLogger extends BaseJdbcLogger implements InvocationHandler {
    @Override
    public Object invoke(Object proxy, Method method, Object[] params) {
        // log total
    }
}

用到的Logger: MapperRegistry <-> Configuration -> MappedStatement -> Log

org.apache.ibatis.executor.Executor: update(), query()
增/删/改查

实现

本来想通过侵入打日志代码的方式实现, 后来发现mybatis提供了插件功能, 可以自定义sql执行流程

最终实现如下

/**
 * @author hahahaha123567@qq.com
 * @description 注解拦截接口可选方法
 * Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
 * ParameterHandler (getParameterObject, setParameters)
 * ResultSetHandler (handleResultSets, handleOutputParameters)
 * StatementHandler (prepare, parameterize, batch, update, query)
 * @create 2019/01/03
 **/
@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})
})
@Component
public class MybatisInterceptor implements Interceptor {

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

    private static Properties properties;

    private static String getParameterValue(Object param) {
        String value;
        if (param == null) {
            value = "";
        } else {
            value = param.toString();
            if (param instanceof String) {
                value = "'" + value + "'";
            } else if (param instanceof CodeDescEnum) {
                value = ((CodeDescEnum) param).getCode().toString();
            }
        }
        return Matcher.quoteReplacement(value);
    }

    private static String showSql(Configuration configuration, BoundSql boundSql) {
        // 去掉空白字符
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        if (!StringUtils.isEmpty(sql) && Objects.nonNull(parameterObject) && !CollectionUtils.isEmpty(parameterMappings)) {
            // 替换参数
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                // 有对应的typeHandler
                sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));
            } else {
                // 没有对应的typeHandler
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        // 从metaObject里取值
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        // 从boundSql里取值
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    }
                }
            }
        }
        return sql;
    }

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

    @Override
    public void setProperties(Properties properties) {
        MybatisInterceptor.properties = properties;
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        MybatisFilter.enable();
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];

        // sql参数
        Object parameter = null;
        if (invocation.getArgs().length > 1) {
            parameter = invocation.getArgs()[1];
        }

        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        Configuration configuration = mappedStatement.getConfiguration();

        // 统计sql执行时间
        long start = System.currentTimeMillis();
        Object returnValue = null;
        Exception sqlException = null;
        try {
            returnValue = invocation.proceed();
        } catch (Exception e) {
            MybatisFilter.disable();
            sqlException = e;
        }
        long end = System.currentTimeMillis();
        long time = (end - start);
        // sql执行位置的logger
        Log statementLog = mappedStatement.getStatementLog();
        try {
            int size = -1;
            if (returnValue != null) {
                if (returnValue instanceof Collection) {
                    // executor.query()
                    size = ((Collection) returnValue).size();
                } else if (returnValue instanceof Number) {
                    // executor.update()
                    size = ((Number) returnValue).intValue();
                } else {
                    logger.error("处理sql结果出错, returnValue类型为 {}", returnValue.getClass().getName());
                }
            }
            String sql = showSql(configuration, boundSql);
            statementLog.debug(String.format("sql执行耗时: %dms, 结果数: %d, %s", time, size, sql));
        } catch (Exception e) {
            MybatisFilter.disable();
            statementLog.error(String.format("[LogHub]mybatis拦截器格式化sql出错, 调用位置%s, sql模板: %s", mappedStatement.getId(), boundSql.getSql()), e);
        }
        if (sqlException != null) {
            throw sqlException;
        }
        return returnValue;
    }
}

把插件注册到mybatis

@Bean
@Primary
public SqlSessionFactory sqlSessionFactory(DataSource dataSource, PageHelper pageHelper, MybatisInterceptor mybatisInterceptor) throws Exception {
    logger.info("==== sqlSessionFactory execute ====");

    VFS.addImplClass(SpringBootVFS.class);

    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

    //添加插件
    Interceptor[] plugins = new Interceptor[]{pageHelper, mybatisInterceptor};
    bean.setPlugins(plugins);

    // 添加数据源
    bean.setDataSource(dataSource);
    // 注册 *Mapper.xml 配置文件
    PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
    bean.setMapperLocations(resolver.getResources("classpath*:/mapper/**/*Mapper.xml"));
    bean.setTypeHandlersPackage("com.haha.model.enums");
    return bean.getObject();
}