[Spring boot] mybatis SQL 로그 찍기 - log4jdbc

Spring Boot / Oracle / Mybatis 차례

 

log4jdbc로 sql 로그를 찍습니다.

pom.xml

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

위 부분을 추가합니다.

아래는 최종 상태입니다.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.9.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.nextday</groupId>
	<artifactId>nextday</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>nextday</name>
	<description>Nextday project for Spring Boot</description>

	<!-- OJDBC 드라이버 레포지토리 -->
	<repositories>
	    <repository>
	        <id>oracle</id>
	        <url>http://maven.jahia.org/maven2</url>
	    </repository>
	</repositories>
	
	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		
		<!-- lombok -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.8</version>
			<scope>provided</scope>
		</dependency>
		
		<!-- Spring boot JDBC Library -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		
		<!-- Connection pool Library -->
		<dependency>
		    <groupId>commons-dbcp</groupId>
		    <artifactId>commons-dbcp</artifactId>
		    <version>1.4</version>
		</dependency>
		
		<!-- Oracle JDBC Driver -->
		<dependency>
		    <groupId>com.oracle</groupId>
		    <artifactId>ojdbc6</artifactId>
		    <version>12.1.0.2</version>
		</dependency>
		
		<!-- MyBatis Library -->
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis</artifactId>
		    <version>3.4.1</version>
		</dependency>
		
		<!-- Spring MyBatis 연동 -->
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis-spring</artifactId>
		    <version>1.3.0</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
		
		<!-- MyBatis sql pretty -->
		<dependency>
		    <groupId>org.bgee.log4jdbc-log4j2</groupId>
		    <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
		    <version>1.16</version>
		</dependency>
		
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
			<plugin>
			    <groupId>org.apache.maven.plugins</groupId>
			    <artifactId>maven-surefire-plugin</artifactId>
			    <version>2.22.1</version>
			    <configuration>
			        <testFailureIgnore>true</testFailureIgnore>
			    </configuration>
			</plugin>
		</plugins>
	</build>

</project>

 

반응형

application.properties

# Database
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@localhost:1521/xe
spring.datasource.username=oracle
spring.datasource.password=1234

server.port = 8088

위부분을 아래처럼 수정합니다.

# Database
spring.datasource.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.url=jdbc:log4jdbc:oracle:thin:@localhost:1521/xe
spring.datasource.username=oracle
spring.datasource.password=1234

server.port = 8088

driver-class-name 이 변경되었습니다.

url이 jdbc:log4jdbc:oracle 부분이 수정되었습니다.

 

resources/log4jdbc.log4j2.properties 생성

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

log4jdbc.dump.sql.maxlinelength=0

 

log4jdbc.log4j2.properties 파일을 생성하고 위 내용을 넣습니다.

 

logback-spring.xml

<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"/>

logback-spring.xml 파일에 위 내용을 추가합니다.

아래는 최종본 입니다.

<?xml version="1.0" encoding="UTF-8"?>
<configuration>

    <!--로그 파일 저장 위치-->
    <property name="LOGS_PATH" value="./logs"/>

    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <layout class="ch.qos.logback.classic.PatternLayout">
            <Pattern>%d{HH:mm} %-5level %logger{36} - %msg%n</Pattern>
        </layout>
    </appender>
    <appender name="DAILY_ROLLING_FILE_APPENDER" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${LOGS_PATH}/logback.log</file>
        <encoder>
            <pattern>[%d{yyyy-MM-dd HH:mm:ss}:%-3relative][%thread] %-5level %logger{35} - %msg%n</pattern>
        </encoder>
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <fileNamePattern>${LOGS_PATH}/logback.%d{yyyy-MM-dd}.%i.log.gz</fileNamePattern>
            <timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">
                <!-- or whenever the file size reaches 100MB -->
                <maxFileSize>5MB</maxFileSize>
                <!-- kb, mb, gb -->
            </timeBasedFileNamingAndTriggeringPolicy>
            <maxHistory>30</maxHistory>
        </rollingPolicy>
    </appender>
    
    <logger name="com.nextday.gateway" level="INFO">
        <appender-ref ref="DAILY_ROLLING_FILE_APPENDER" />
    </logger>
    <root level="INFO">
        <appender-ref ref="STDOUT" />
    </root>
    
    <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"/>
    
</configuration>

 

sql 로그가 찍혔습니다.

 

 

Spring Boot / Oracle / Mybatis 차례