spring data jpa 打印sql配置

你的名字 2021-12-10 08:27 1078阅读 0赞

spring data jpa不支持输出完整的sql，下面的方式通过依赖log4jdbc的方式可以实现。

pom.xml引入:

<dependency>
        <groupId>com.googlecode.log4jdbc</groupId>
        <artifactId>log4jdbc</artifactId>
        <version>1.2</version>
    </dependency>

修改配置文件：

spring:
      datasource:
        url: jdbc:log4jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
        username: root
        password: 123456
        # 使用druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: net.sf.log4jdbc.DriverSpy # com.mysql.cj.jdbc.Driver

这样就可以实现输出完整的sql（包括占位符也能替换），因为输出的日志较多，通过logback进行过滤，具体的配置如下：

最后得到的效果就是：

2019-07-13 13:57:28.638 [http-nio-8001-exec-10] INFO  o.h.hql.internal.QueryTranslatorFactoryInitiator - HHH000397: Using ASTQueryTranslatorFactory
    2019-07-13 13:57:28.912 [http-nio-8001-exec-10] INFO  jdbc.sqltiming - select userinfo0_.user_id as user_id1_0_, userinfo0_.address as address2_0_, userinfo0_.create_time 
    as create_t3_0_, userinfo0_.id_card as id_card4_0_, userinfo0_.password as password5_0_, userinfo0_.phone 
    as phone6_0_, userinfo0_.real_name as real_nam7_0_, userinfo0_.status as status8_0_, userinfo0_.update_time 
    as update_t9_0_, userinfo0_.user_name as user_na10_0_ from user userinfo0_ 
     { executed in 34 msec}

其中executed就是执行的时间，可以用来排查慢查询。