Java OpenResty Spring Spring Boot MySQL Redis MongoDB PostgreSQL Linux Android Nginx 面试 小程序 Arthas JVM AQS juc Kubernetes Docker 诊断工具


Spring Boot JPA 打印慢查询 SQL 语句

Spring Boot JPA 大约 2727 字

开启配置

注意:配置的1000表示1000毫秒

spring:
  jpa:
    properties:
      hibernate:
        session.events.log.LOG_QUERIES_SLOWER_THAN_MS: 1000

日志样例

2022-11-04 16:36:00.926  INFO 24952 --- [           main] org.hibernate.SQL_SLOW                   : SlowQuery: 4583 milliseconds. 
SQL: 'HikariProxyPreparedStatement@1971858114 wrapping prep1: 
insert into user (id, create_by, create_ts, update_by, update_ts, version, id_card, name) values (default, ?, ?, ?, ?, ?, ?, ?)
{1: 'unknown', 2: TIMESTAMP '2022-11-04 16:35:52.5019959', 3: 'unknown', 4: TIMESTAMP '2022-11-04 16:35:52.5019959', 5: CAST(0 AS BIGINT), 6: '123', 7: 'zzz'}'

对应源码

hibernate配置文件

public final class Environment implements AvailableSettings {
    // ...
}

public interface AvailableSettings extends org.hibernate.jpa.AvailableSettings {

    String SHOW_SQL ="hibernate.show_sql";
    String FORMAT_SQL ="hibernate.format_sql";
    String GENERATE_STATISTICS = "hibernate.generate_statistics";
    // 慢 SQL 配置
    String LOG_SLOW_QUERY = "hibernate.session.events.log.LOG_QUERIES_SLOWER_THAN_MS";

}

初始化时logSlowQueryhibernate环境变量及配置中读取:

public class JdbcServicesImpl implements JdbcServices, ServiceRegistryAwareService, Configurable {

    @Override
    public void configure(Map configValues) {
        // ...
        final long logSlowQuery = ConfigurationHelper.getLong( Environment.LOG_SLOW_QUERY, configValues, 0 );

        this.sqlStatementLogger = new SqlStatementLogger( showSQL, formatSQL, highlightSQL, logSlowQuery );
        // ...
    }
}

从源码中看到:配置的值大于等于1时,会输出日志。

可以在日志中搜索SlowQuery: 关键字快速定位慢SQL

public class SqlStatementLogger {
    private final long logSlowQuery;

    public SqlStatementLogger(boolean logToStdout, boolean format, boolean highlight, long logSlowQuery) {
        // ...
        this.logSlowQuery = logSlowQuery;
    }

    @AllowSysOut
    public void logSlowQuery(String sql, long startTimeNanos) {
        if ( logSlowQuery < 1 ) {
            return;
        }
        if ( startTimeNanos <= 0 ) {
            throw new IllegalArgumentException( "startTimeNanos [" + startTimeNanos + "] should be greater than 0!" );
        }

        long queryExecutionMillis = TimeUnit.NANOSECONDS.toMillis( System.nanoTime() - startTimeNanos );

        if ( queryExecutionMillis > logSlowQuery ) {
            String logData = "SlowQuery: " + queryExecutionMillis + " milliseconds. SQL: '" + sql + "'";
            LOG_SLOW.info( logData );
            if ( logToStdout ) {
                System.out.println( logData );
            }
        }
    }
}

更多配置

更多hibernate配置可查看AvailableSettings接口。

AvailableSettings位于org.hibernate.cfg包下。

参考

https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html

阅读 980 · 发布于 2023-03-01

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb

扫描下方二维码关注公众号和小程序↓↓↓

扫描二维码关注我
昵称:
随便看看 换一批