Spring Boot, Log4jdbc 설정

목적: Spring Boot 기반의 STS Console에서 query log를 확인

pom.xml
Maven 설정 파일인 pom.xml에 Log4Jdbc를 추가한다.

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

application.properties
Spring Boot 설정 파일인 application.properties 중 spring.datasource.driver-class-name와 spring.datasource.url를 다음과 같이 수정한다.

# ===================================================================
# COMMON SPRING BOOT PROPERTIES
# ===================================================================
# MySQL
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver // 수정 전
spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy // 수정 후
spring.datasource.validation-query=select 1
spring.datasource.test-on-borrow=true
#spring.datasource.url=jdbc:mysql://localhost:3306/sakila?autoReconnect=true // 수정 전
spring.datasource.url=jdbc:log4jdbc:mysql://localhost:3306/sakila?autoReconnect=true 수정 후
spring.datasource.username=root
spring.datasource.password=1234

log4jdbc.log4j2.properties

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

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>

Result

10. select 1
 {executed in 0 msec} 
SELECT * FROM sakila.actor WHERE first_name LIKE :firstName AND last_name LIKE :lastName
20170627 16:06:52.371 [http-nio-5000-exec-1] DEBUG j.sqltiming -  org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:688)
10. SELECT * FROM sakila.actor WHERE first_name LIKE '%%' AND last_name LIKE '%DAVIS%'
 {executed in 1 msec} 
20170627 16:06:52.374 [http-nio-5000-exec-1] INFO j.resultsettable - 
|---------|-----------|----------|----------------------|
|actor_id |first_name |last_name |last_update           |
|---------|-----------|----------|----------------------|
|4        |JENNIFER   |DAVIS     |2006-02-15 04:34:33.0 |
|101      |SUSAN      |DAVIS     |2006-02-15 04:34:33.0 |
|110      |SUSAN      |DAVIS     |2006-02-15 04:34:33.0 |
|---------|-----------|----------|----------------------|