[JEUS] log4jdbc 설정 가이드

본 포스팅에서는 Log4JDBC 설정 방법에 대해 가이드 하도록 하겠습니다.

---

1. Log4jdbc 드라이버

log4jdbc is a Java JDBC driver that can log SQL and/or JDBC calls (and optionally SQL timing information) for other JDBC drivers using theSimple Logging Facade For Java (SLF4J) logging system.

  

2. slf4j.jar

logger라이브러리를 선택해주는 interface프로그램으로 생각하면 된다.

( The Simple Logging Facade for Java (SLF4J) serves as a simple facade or abstraction for various logging frameworks (e.g. java.util.logging, logback, log4j) allowing the end user to plug in the desired logging framework at deployment time.)

SLF4J does not rely on any special class loader machinery. In fact, each SLF4J binding is hardwired at compile time to use one and only one specific logging framework. For example, the slf4j-log4j12-1.7.5.jar binding is bound at compile time to use log4j. In your code, in addition to slf4j-api-1.7.5.jar, you simply drop one and only one binding of your choice onto the appropriate class path location. Do not place more than one binding on your class path. Here is a graphical illustration of the general idea. 

      

 

3. log4jdbc-remix.jar

What was log4jdbc-remix?

log4jdbc-remix was an experimental fork of log4jdbc that:

 

Can log result sets as tables

Can be configured as a Spring Datasource

Can use a plugable SQL formatter

Is available in the sonatype maven repository. https://oss.sonatype.org/index.html#nexus-search;quick~log4jdbc

Only supports Java 6 and above (log4jdbc which also supports java 1.4 and 1.5)

Its now superseded by log4jdbc-log4j2 at https://code.google.com/p/log4jdbc-log4j2/ which also supports JDBC 4.1 (Java 7), JDBC 4 (Java 6), JDBC 3 (Java 5). The original log4jdbc also supports java 1.4.

=> reultset을 table방식으로 보여주는등의 기타 기능이 있음.

 

4. log4j.jar

 보편적으로 쓰는 로깅 라이브러리. 여기 예에서는 log4j를 통해 로깅 설정을 한다.

 

 * jog4jdbc-log4j2 VS log4jdbc

  => log4jdbc-log4j2는 log4j2를 지원하기 위한 것이다.

log4jdbc-log4j2 is a modification of log4jdbc to natively use Log4j 2 (or SLF4J as usual), that supports JDBC 4.1 to JDBC 3, includes all the improvements of log4jdbc-remix, and provides new improvements on its own. log4jdbc-log4j2:

log4j + log4jbc

log4j2 + log4jdbc-log4j2

  

5. 라이브러리 다운

log4j 라이브러리 : http://logging.apache.org/log4j/1.2/download.html

slf4j  라이브러리 : http://slf4j.org/download.html
log4jdbc 라이브러리: https://code.google.com/p/log4jdbc/

log4jdbc-remix : http://code.google.com/p/log4jdbc-remix/ 

                   https://oss.sonatype.org/index.html#nexus-search

  quick~log4jdbc-remix (2.7버전이 안정적이라고 함)

 

현재(2013.11.23) log4jdbc사이트에 가보면. final 버전이 1.2인데, 이넘과의 궁합은 sfl4j12.1.5임을 알 수 있고, 이넘과의 log4j궁합은 log4j12.1.5 이다.

 

즉 아래와 같은 라이브리러 가 필요하다.

 

log4j-1.2.15.jar log4jdbc4-1.2.jar slf4j-api-1.5.0.jar slf4j-log4j12-1.5.0.jar log4jdbc-remix-0.2.7.jar(옵션)

 * log4jdbc4-1.2.jar, log4jdbc-remix-0.2.7.jar 2개 모두 사용하면 안됨. 

sql결과를 table형태로 보여주기 위해서는  log4jdbc-remix-0.2.7.jar만

사용하면 된다.

 * log4jdbc-remix는 JDK1.6에서 지원

6. JEUSMain.xml

<database>             <vendor>others</vendor>             <export-name>datasource</export-name>             <data-source-class-name>jeus.jdbc.driver.blackbox.BlackboxConnectionPoolDataSource</data-source-class-name>             <data-source-type>ConnectionPoolDataSource</data-source-type>             <user>scott</user>             <password>tiger</password>             <property>                 <name>URL</name>                 <type>java.lang.String</type>                 <value>jdbc:log4jdbc:oracle:thin:@nrson:1521:orcl</value>             </property>               <property>                <name>DriverClassName</name>                <type>java.lang.String</type>                <value>net.sf.log4jdbc.DriverSpy</value>            </property>             <connection-pool>                <pooling>                   <min>10</min>                   <max>40</max>                   <step>5</step>                   <period>300000</period>                </pooling>                <wait-free-connection>                   <enable-wait>true</enable-wait>                   <wait-time>10000</wait-time>                </wait-free-connection>                <check-query>SELECT 1 FROM dual</check-query>                <check-query-period>300000</check-query-period>             </connection-pool>          </database>

 

7. log4j설정

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd"> <log4j:configuration xmlns:log4j='http://jakarta.apache.org/log4j/'>     <appender name="console" class="org.apache.log4j.ConsoleAppender">         <layout class="org.apache.log4j.PatternLayout">                 <param name="ConversionPattern" value="[%d{yyyy-MM-dd HH:mm:ss}] %-5p: %c - %m%n" />         </layout>     </appender>          <!-- info부터 적용 table사용시 -->     <logger name="jdbc.resultsettable" 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>         <logger name="jdbc.audit" additivity="false">           <level value="warn" />           <appender-ref ref="console" />     </logger>       <!-- info부터 적용 기본적인 resultset-->     <!-- resultsettable 만 사용하기 위해서 로그레벨은 warning시킨다. resurtset이 중복됨 -->      <logger name="jdbc.resultset" additivity="false">         <level value="warn" />         <appender-ref ref="console" />     </logger>       <!-- 수행시간을 찍는다-->     <logger name="jdbc.sqltiming" additivity="false">         <level value="info" />         <appender-ref ref="console" />     </logger>     <root>         <priority value="warn" />         <appender-ref ref="console" />     </root> </log4j:configuration>

*log4jdbc-remix를 사용하고,jdbc.resultsettabl 로거사용시 정상적으로 sql 결과가 table로 보이지 않을 때.

=>log4jdbc와 log4jdbc-remix가 같이 로딩되면서 log4jdbc가 먼져 로딩되서 문제다

=>log4jdbc-remix는 log4jdbc확장버전인듯 하여 log4jdbc-remix만 클래스 패스로 잡으면 된다..

 

8. sql수행후 로그 내용

2013.11.23 01:32:05][2][b288] [container1-56] [JDBC-0378] Data source [datasource] is configured with DEPRECATED BlackBoxDataSource driver. Please configure this to use proper ConnectionPoolDataSource of the database's driver. [2013.11.23 01:32:06][2][b288] [container1-56] [JMX-0011] create MBean : JEUS:j2eeType=JDBCDataSource,JMXManager=nrson_container1,JeusManager=nrson,J2EEServer=nrson_container1, JDBCResource=nrson_container1,name=datasource [2013.11.23 01:32:06][2][b288] [container1-56] [JMX-0011] create MBean : JEUS:j2eeType=JDBCDriver,JMXManager=nrson_container1,JeusManager=nrson,J2EEServer=nrson_container1, JDBCResource=nrson_container1,JDBCDataSource=datasource,name=others [2013.11.23 01:32:06][2][b288] [container1-56] [JMX-0011] create MBean : JEUS:j2eeType=JDBCConnectionInfo,JMXManager=nrson_container1,JeusManager=nrson,J2EEServer=nrson_container1, JDBCResource=nrson_container1,JDBCDataSource=datasource,name=datasource [2013-11-23 01:32:06] INFO : jdbc.sqlonly - SELECT 1 FROM dual  [2013-11-23 01:32:06] INFO : jdbc.sqltiming - SELECT 1 FROM dual  {executed in 22 msec} [2013-11-23 01:32:06] INFO : jdbc.sqlonly - select * from emp  [2013-11-23 01:32:06] INFO : jdbc.sqltiming - select * from emp  {executed in 15 msec} [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |------|-------|----------|-----|--------------------|-----|-----|-------| [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |EMPNO |ENAME  |JOB       |MGR  |HIREDATE            |SAL  |COMM |DEPTNO | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |------|-------|----------|-----|--------------------|-----|-----|-------| [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7369  |SMITH  |CLERK     |7902 |1980-12-17 00:00:00 |800  |null |20     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7499  |ALLEN  |SALESMAN  |7698 |1981-02-20 00:00:00 |1600 |300  |30     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7521  |WARD   |SALESMAN  |7698 |1981-02-22 00:00:00 |1250 |500  |30     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7566  |JONES  |MANAGER   |7839 |1981-04-02 00:00:00 |2975 |null |20     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7654  |MARTIN |SALESMAN  |7698 |1981-09-28 00:00:00 |1250 |1400 |30     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7698  |BLAKE  |MANAGER   |7839 |1981-05-01 00:00:00 |2850 |null |30     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7782  |CLARK  |MANAGER   |7839 |1981-06-09 00:00:00 |2450 |null |10     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7788  |SCOTT  |ANALYST   |7566 |1987-04-19 00:00:00 |3000 |null |20     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7839  |KING   |PRESIDENT |null |1981-11-17 00:00:00 |5000 |null |10     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7844  |TURNER |SALESMAN  |7698 |1981-09-08 00:00:00 |1500 |0    |30     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7876  |ADAMS  |CLERK     |7788 |1987-05-23 00:00:00 |1100 |null |20     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7900  |JAMES  |CLERK     |7698 |1981-12-03 00:00:00 |1000 |null |30     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7902  |FORD   |ANALYST   |7566 |1981-12-03 00:00:00 |3000 |null |20     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |7934  |MILLER |CLERK     |7782 |1982-01-23 00:00:00 |1300 |null |10     | [2013-11-23 01:32:06] INFO : jdbc.resultsettable - |------|-------|----------|-----|--------------------|-----|-----|-------|

 

다음에 뵙죠.