mybatis, jpa(hibernate)的展示sql的方式, 都是将模板和参数分开展示, 而p6spy可以在驱动级别展示实际执行的sql

官网地址: https://p6spy.readthedocs.io

添加p6spy支持的步骤如下:

1. 加入类库:
   以maven项目为例, pom.xml中加入:

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.7.0</version>
</dependency>

版本根据当前版本自选

2. 修改jdbc url和驱动程序类名

例如:

3. 从官网上下载spy.properties的样例, 复制到classpath中
   必要的修改点:

4. 默认输出例子如下: (例子以默认springboot日志格式输出)

2018-04-27 15:20:50.346  INFO 5216 --- [           main] p6spy                                    : 1524813650345|393|statement|connection 0|select employees0_.emp_no as emp_no1_0_, employees0_.birth_date as birth_da2_0_, employees0_.first_name as first_na3_0_, employees0_.gender as gender4_0_, employees0_.hire_date as hire_dat5_0_, employees0_.last_name as last_nam6_0_ from employees employees0_ where employees0_.birth_date<?|select employees0_.emp_no as emp_no1_0_, employees0_.birth_date as birth_da2_0_, employees0_.first_name as first_na3_0_, employees0_.gender as gender4_0_, employees0_.hire_date as hire_dat5_0_, employees0_.last_name as last_nam6_0_ from employees employees0_ where employees0_.birth_date<'1960-01-01 00:00:00'

默认格式如下:

%(currentTime)|%(executionTime)|%(category)|connection%(connectionId)|%(sqlSingleLine)

其中最后的sqlSingleLine由竖线分成两部分, 前者是参数模板, 后者才是实际执行的语句

5. 更好的实践:
   单行输出仍然不利于观察问题, 继续改进格式:
   写一个Format类: (这个类使用了hibernate中的Formatter , 如果不用jpa(hibernate), 可以自己找一个sql formatter)

package mytest.p6spy;

import com.p6spy.engine.spy.appender.MultiLineFormat;
import org.hibernate.engine.jdbc.internal.BasicFormatterImpl;
import org.hibernate.engine.jdbc.internal.Formatter;

/**
 * @author liude
 * pretty format working form p6spy
 */
public class PrettySqlMultiLineFormat extends MultiLineFormat {
    private static final Formatter FORMATTER = new BasicFormatterImpl();

    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql) {
        return super.formatMessage(connectionId, now, elapsed, category, FORMATTER.format(prepared), FORMATTER.format(sql));
    }
}

在spy.properties中更新:

现在日志输出变成:

2018-04-27 15:27:06.329  INFO 7728 --- [           main] p6spy                                    : #1524814026327 | took 465ms | statement | connection 0|
    select
        employees0_.emp_no as emp_no1_0_,
        employees0_.birth_date as birth_da2_0_,
        employees0_.first_name as first_na3_0_,
        employees0_.gender as gender4_0_,
        employees0_.hire_date as hire_dat5_0_,
        employees0_.last_name as last_nam6_0_ 
    from
        employees employees0_ 
    where
        employees0_.birth_date<?

    select
        employees0_.emp_no as emp_no1_0_,
        employees0_.birth_date as birth_da2_0_,
        employees0_.first_name as first_na3_0_,
        employees0_.gender as gender4_0_,
        employees0_.hire_date as hire_dat5_0_,
        employees0_.last_name as last_nam6_0_ 
    from
        employees employees0_ 
    where
        employees0_.birth_date<'1960-01-01 00:00:00';

好看多了

测试代码在 https://gitee.com/liuxy9999/mytest-p6spy