mybatis query log (변수 매핑된 쿼리 로그 출력)

Spring을 사용해서 쿼리 로그를 출력하는 법

 

1. 일단 pom.xml 을 열어서 아래내용을 추가해 줍니다. (로그 출력하는 dependency 추가)

<!-- 쿼리 로그 -->
		<!-- Sql Log -->
		<dependency>
		    <groupId>org.lazyluke</groupId>
		    <artifactId>log4jdbc-remix</artifactId>
		    <version>0.2.7</version>
		</dependency>

 

2. root-context.xml을 열어준다.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> 
		<property name="driverClassName" value="#{config['jdbc.driverClassName']}" />
        <property name="url" value="#{config['jdbc.url']}" />
        <property name="username" value="#{config['jdbc.username']}"/>
        <property name="password" value="#{config['jdbc.password']}" /> 
	</bean> 

상위의 글을 

<bean id="dataSourceSpied" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> 
		<property name="driverClassName" value="#{config['jdbc.driverClassName']}" />
        <property name="url" value="#{config['jdbc.url']}" />
        <property name="username" value="#{config['jdbc.username']}"/>
        <property name="password" value="#{config['jdbc.password']}" /> 
	</bean> 
	<bean id="dataSource" class="net.sf.log4jdbc.Log4jdbcProxyDataSource"> 
	    <constructor-arg ref="dataSourceSpied" /> 
	    <property name="logFormatter"> 
	        <bean class="net.sf.log4jdbc.tools.Log4JdbcCustomFormatter"> 
	            <property name="loggingType" value="MULTI_LINE" /> 
	            <property name="sqlPrefix" value="SQL : "/> 
	        </bean> 
	    </property> 
	</bean>

로 감싸준다.

 

마지막으로 log4j.xml 을 열어서 아래 내용을 추가해 줍니다. (어떠한 항목을 출력할껀지 설정)

<!-- SQL Logger -->
	<!--  SQL문과 해당 SQL을 실행시키는데 수행된 시간 정보(milliseconds) -->
	<logger name="jdbc.sqltiming" additivity="false">
	    <level value="info" />
	    <appender-ref ref="console"/> 
	</logger>
	<!--  SQL 쿼리 문장 -->
	<logger name="jdbc.sqlonly" additivity="false"> 
	    <level value="info"/> 
	    <appender-ref ref="console"/> 
	</logger>
	 
	<!--  ResultSet을 제외한 모든 JDBC 호출 정보 -->
	<logger name="jdbc.audit" additivity="false"> 
	    <level value="info"/>  
	    <appender-ref ref="console"/> 
	</logger> 
	 
	<!--  ResultSet을 포함한 모든 JDBC 호출 정보 -->
	<logger name="jdbc.resultset" additivity="false">
	    <level value="info" />
	    <appender-ref ref="console"/> 
	</logger>
	 
	<!-- SQL 결과 조회된 데이터의 table을 로그 -->
	<logger name="jdbc.resultsettable" additivity="false"> 
	    <level value="info"/>  
	    <appender-ref ref="console"/> 
	</logger> 
	

위 내용을 간단히 설명하자면

 

1. 로그 출력하도록 jar 선언

 

2. 쿼리 실행하는 부분을 새로 선언한 jar 에서 형식에 맞게 로그 출력

 

3. 퀴리 출력시 보고싶은 정보의 레벨을 설정

 

잘 출력된 쿼리 결과창><

INFO : jdbc.connection - 1. Connection opened
INFO : jdbc.audit - 1. Connection.new Connection returned 
INFO : jdbc.audit - 1. Connection.getAutoCommit() returned true
INFO : jdbc.audit - 1. PreparedStatement.new PreparedStatement returned 
INFO : jdbc.audit - 1. Connection.prepareStatement(SELECT * 
		FROM BOARD) returned net.sf.log4jdbc.PreparedStatementSpy@2de3d44b
INFO : jdbc.sqlonly - SQL : SELECT * 
		FROM BOARD
INFO : jdbc.sqltiming - SELECT * FROM BOARD  {executed in 98 msec}
INFO : jdbc.audit - 1. PreparedStatement.execute() returned true
INFO : jdbc.resultset - 1. ResultSet.new ResultSet returned 
INFO : jdbc.audit - 1. PreparedStatement.getResultSet() returned net.sf.log4jdbc.ResultSetSpy@1336e088
INFO : jdbc.resultset - 1. ResultSet.getMetaData() returned oracle.jdbc.driver.OracleResultSetMetaData@6fca3809
INFO : jdbc.resultset - 1. ResultSet.getType() returned 1003
INFO : jdbc.resultset - 1. ResultSet.next() returned true
INFO : jdbc.resultset - 1. ResultSet.getString(B_SEQ) returned 1
INFO : jdbc.resultset - 1. ResultSet.wasNull() returned false
INFO : jdbc.resultset - 1. ResultSet.getString(B_TITLE) returned LDY
INFO : jdbc.resultset - 1. ResultSet.wasNull() returned false
INFO : jdbc.resultset - 1. ResultSet.getString(B_CONTENT) returned LDY
INFO : jdbc.resultset - 1. ResultSet.wasNull() returned false
INFO : jdbc.resultset - 1. ResultSet.next() returned true
INFO : jdbc.resultset - 1. ResultSet.getString(B_SEQ) returned 2
INFO : jdbc.resultset - 1. ResultSet.wasNull() returned false
INFO : jdbc.resultset - 1. ResultSet.getString(B_TITLE) returned LDY
INFO : jdbc.resultset - 1. ResultSet.wasNull() returned false
INFO : jdbc.resultset - 1. ResultSet.getString(B_CONTENT) returned LDY
INFO : jdbc.resultset - 1. ResultSet.wasNull() returned false
INFO : jdbc.resultsettable - |------|--------|----------|
INFO : jdbc.resultsettable - |B_SEQ |B_TITLE |B_CONTENT |
INFO : jdbc.resultsettable - |------|--------|----------|
INFO : jdbc.resultsettable - |1     |LDY     |LDY       |
INFO : jdbc.resultsettable - |2     |LDY     |LDY       |
INFO : jdbc.resultsettable - |------|--------|----------|
INFO : jdbc.resultset - 1. ResultSet.next() returned false
INFO : jdbc.resultset - 1. ResultSet.close() returned null
INFO : jdbc.audit - 1. PreparedStatement.getConnection() returned net.sf.log4jdbc.ConnectionSpy@62535ca8
INFO : jdbc.audit - 1. Connection.getMetaData() returned org.apache.commons.dbcp.DelegatingDatabaseMetaData@2d05a4cd
INFO : jdbc.audit - 1. PreparedStatement.close() returned 
INFO : jdbc.connection - 1. Connection closed
INFO : jdbc.audit - 1. Connection.close() returned 

 

Refrence : huskdoll.tistory.com/948