본문 바로가기

IT칼럼/JAVA

Spring Boot + MyBatis 에서 SQL Result 로그찍기

1. pom.xml

<!-- log4jdbc -->
<dependency>
    <groupId>org.bgee.log4jdbc-log4j2</groupId>
    <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
    <version>1.16</version>
</dependency>

 

2. application.properties  

 

붉은 색으로 표시된 부분 추가하기

spring.datasource.driverClassName=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.url=jdbc:log4jdbc:postgresql://localhost:5432/postgres?charSet=UTF-8&prepareThreshold=1

 

3. log4jdbc.log4j2.properties 파일 추가하기

 

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator
log4jdbc.dump.sql.maxlinelength=0

maxlinelength=0 일경우 모든 결과를 출력하게 됨

 

4. logback.xml 파일 추가하기

<configuration>
    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%d{yyyyMMdd HH:mm:ss.SSS} [%thread] %-3level %logger{5} - %msg %n</pattern>
        </encoder>
    </appender>

    <logger name="jdbc" level="OFF"/>

    <logger name="jdbc.sqlonly" level="OFF"/>
    <logger name="jdbc.sqltiming" level="DEBUG"/>
    <logger name="jdbc.audit" level="OFF"/>
    <logger name="jdbc.resultset" level="OFF"/>
    <logger name="jdbc.resultsettable" level="DEBUG"/>
    <logger name="jdbc.connection" level="OFF"/>

    <root level="INFO">
        <appender-ref ref="STDOUT" />
    </root>

</configuration>

 

<결과>

 SELECT distinct
        c1.contract_no, c1.customer_no, c2.bizname_kor, c2.bizname_eng, c3.name_kor, c3.name_eng, c2.phone_num
        FROM contract as c1
        inner join customer_info as c2 on c1.customer_no = c2.customer_no
        inner join c_info_detail as c3 on c1.customer_no = c3.customer_no
        where
        c2.bizname_kor like  '%'||'신나고'||'%'';
 {executed in 4 msec} 
20190329 15:40:23.953 [http-nio-8080-exec-1] INFO j.resultsettable - 
|--------------|-------------|------------|------------|---------|---------|------------|
|contract_no   |customer_no  |bizname_kor |bizname_eng |name_kor |name_eng |phone_num   |
|--------------|-------------|------------|------------|---------|---------|------------|
|M201903270935 |201903081635 |신나고         |sinago      |나가수      |nagasu   |6048885999, |
|--------------|-------------|------------|------------|---------|---------|------------|