[스프링부트 3.0 이상] p6spy 초기 설정

build.gradle

dependencies {
    // P6Spy
    implementation 'com.github.gavlyukovskiy:p6spy-spring-boot-starter:1.9.0'    
}

• 스프링부트 3.0 이상인 경우 반드시 1.9.0 버전 사용

P6SpyFormatter

package study.community.config.p6spy;

import java.util.Locale;

import org.hibernate.engine.jdbc.internal.FormatStyle;

import com.p6spy.engine.logging.Category;
import com.p6spy.engine.spy.appender.MessageFormattingStrategy;

public class P6SpyFormatter implements MessageFormattingStrategy {

    private static final String NEW_LINE = "\n";
    private static final String TAP = "\t";
    private static final String CREATE = "create";
    private static final String ALTER = "alter";
    private static final String DROP = "drop";
    private static final String COMMENT = "comment";

    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
        if (sql.trim().isEmpty()) {
            return formatByCommand(category);
        }
        return formatBySql(sql, category) + getAdditionalMessages(elapsed);
    }

    private static String formatByCommand(String category) {
        return NEW_LINE
            + "Execute Command : "
            + NEW_LINE
            + NEW_LINE
            + TAP
            + category
            + NEW_LINE
            + NEW_LINE
            + "----------------------------------------------------------------------------------------------------";
    }

    private String formatBySql(String sql, String category) {
        if (isStatementDDL(sql, category)) {
            return NEW_LINE
                + "Execute DDL : "
                + NEW_LINE
                + FormatStyle.DDL
                .getFormatter()
                .format(sql);
        }
        return NEW_LINE
            + "Execute DML : "
            + NEW_LINE
            + FormatStyle.BASIC
            .getFormatter()
            .format(sql);
    }

    private String getAdditionalMessages(long elapsed) {
        return NEW_LINE
            + NEW_LINE
            + String.format("Execution Time: %s ms", elapsed)
            + NEW_LINE
            + "----------------------------------------------------------------------------------------------------";
    }

    private boolean isStatementDDL(String sql, String category) {
        return isStatement(category) && isDDL(sql.trim().toLowerCase(Locale.ROOT));
    }

    private boolean isStatement(String category) {
        return Category.STATEMENT.getName().equals(category);
    }

    private boolean isDDL(String lowerSql) {
        return lowerSql.startsWith(CREATE)
            || lowerSql.startsWith(ALTER)
            || lowerSql.startsWith(DROP)
            || lowerSql.startsWith(COMMENT);
    }
}

P6SpyEventListener

package study.community.config.p6spy;

import java.sql.SQLException;

import com.p6spy.engine.common.ConnectionInformation;
import com.p6spy.engine.event.JdbcEventListener;
import com.p6spy.engine.spy.P6SpyOptions;

public class P6SpyEventListener extends JdbcEventListener {

    @Override
    public void onAfterGetConnection(ConnectionInformation connectionInformation, SQLException e) {
        P6SpyOptions.getActiveInstance().setLogMessageFormat(P6SpyFormatter.class.getName());
    }
}

P6SpyConfig

package study.community.config.p6spy;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Profile({"default", "test"})
@Configuration
public class P6SpyConfig {

    @Bean
    public P6SpyEventListener p6SpyCustomEventListener() {
        return new P6SpyEventListener();
    }

    @Bean
    public P6SpyFormatter p6SpyCustomFormatter() {
        return new P6SpyFormatter();
    }
}

application.yml

spring:
  datasource:
    url: jdbc:h2:tcp://localhost/~/community
    username: sa
    password:
    driver-class-name: org.h2.Driver
  jpa:
    hibernate:
      ddl-auto: create

logging:
  level:
    p6spy: info

decorator:
  datasource:
    p6spy:
      enable-logging: true

결과

참조

P6Spy 포맷 설정 공유합니다. - 인프런 | 커뮤니티 질문&답변