[전자정부프레임워크] SQL 로그 Console 출력 - 우라질레이터 사마 황상규 -

log4jdbc-log4j2를 이용하여
전자정부 프레임워크에서 SQL 로그를 Console에 출력하기 위한 설정

(테스트 환경)

• 전자정부프레임워크 버전 : eGovFrameDev-3.8.0-64bit

• DBMS : PostgreSQL.10

1. log4j2.xml 수정

(1) 설명

- jdbc.sqlonly는 preparedstatment의 변수가 binding 형태로 출력시키므로, level을 INFO로 설정한다.

- jdbc, jdbc.connection, jdbc.audit, jdbc.resultset, jdbc.resultsettable은 로그출력을 많이 되므로 OFF로 설정한다

...
<Loggers>
    	<Logger name="jdbc.sqlonly" level="INFO" additivity="false">
            <AppenderRef ref="console" />
        </Logger>
        <Logger name="java.sql" level="OFF" additivity="false">
            <AppenderRef ref="console" />
        </Logger>
        <Logger name="egovframework" level="DEBUG" additivity="false">
            <AppenderRef ref="console" />
        </Logger>
		  <!-- log SQL with timing information, post execution -->
	    <Logger name="jdbc.sqltiming" level="OFF" additivity="false">
	        <AppenderRef ref="console" />
	    </Logger>	
	    <Logger name="jdbc" 				level="OFF"/>
	    <Logger name="jdbc.connection" 		level="OFF"/>
	    <logger name="jdbc.audit" 			level="OFF"/>
	    <Logger name="jdbc.resultset" 		level="OFF"/>
	    <Logger name="jdbc.resultsettable" 	level="OFF"/>
	    	    	
	    <Logger name="org.springframework" level="INFO" additivity="false">
	        <AppenderRef ref="console" />
	    </Logger>
        <Root level="INFO">
            <AppenderRef ref="console" />
        </Root>
    </Loggers>
    ...

 

2. globals.properties에서 사용하는 DBMS 설정값 변경
(1) 파일(수정) : globals.properties

(2) 변경 전 / 변경 후

- 변경 전

#postgreSQL
Globals.postgres.DriverClassName=org.postgresql.Driver
Globals.postgres.Url=jdbc:postgresql://localhost:5432/db_hsg
Globals.postgres.UserName=hsg
Globals.postgres.Password=1

- 변경 후

#postgreSQL
#Globals.postgres.DriverClassName=org.postgresql.Driver
#Globals.postgres.Url=jdbc:postgresql://localhost:5432/db_hsg
Globals.postgres.DriverClassName=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
Globals.postgres.Url=jdbc:log4jdbc:postgresql://localhost:5432/db_hsg
Globals.postgres.UserName=hsg
Globals.postgres.Password=1

 

3. 출력결과

2019-04-11 14:36:36,623 DEBUG [egovframework.com.hsg.com.impl.DeptDAO] queryId = deptDAO.selectList
2019-04-11 14:36:36,626  INFO [jdbc.sqlonly] /* DeptDAO.selectList */ SELECT T.DEPT_ID , T.DEPT_NM , T.DEPT_MNGR_ID , (SELECT K1.DEPT_NM 
FROM TG_DEPT K1 WHERE K1.DEPT_ID = T.PDEPT_ID) AS DEPT_MNGR_NM , T.PDEPT_ID , (SELECT K1.DEPT_NM 
FROM TG_DEPT K1 WHERE K1.DEPT_ID = T.PDEPT_ID) AS PDEPT_NM , T.DSCPT , T.USE_YN , T.START_DE 
, T.END_DE , T.FRST_REGIST_DT , T.FRST_REGISTER_ID , T.LAST_UPDT_DT , T.LAST_UPDUSR_ID FROM 
TG_DEPT T WHERE 1 = 1 

2019-04-11 14:36:36,644 DEBUG [egovframework.com.hsg.com.impl.DeptDAO] queryId = deptDAO.selectListTotalCnt
2019-04-11 14:36:36,645  INFO [jdbc.sqlonly] /* DeptDAO.selectListTotalCnt */ SELECT COUNT(*) TOTAL_COUNT FROM TG_DEPT WHERE 1=1 

 

출처: https://urajilator.tistory.com/category/?page=4 ["만인은 밥 앞에 평등하다." - 우라질레이터 사마 황상규 -]