[log4jdbc]SQL query log

 

1. table 생성

create table testuser(
id varchar2(100),
pass varchar2(100)
);

insert into testuser values('a', '1234');
insert into testuser values('b', '3456');
commit;

 

2. 사용되는 라이브러리들.

log4j.jar
log4jdbc3-1.2.jar
ojdbc6.jar
slf4j-api-1.5.1.jar
slf4j-log4j12-1.5.1.jar

 

3. SqlLogMain.java

import java.sql.*;
public class SqlLogMain {

 private static final String DB_DRIVER = "net.sf.log4jdbc.DriverSpy"; 
 private static final String DB_CONNECTION = "jdbc:log4jdbc:oracle:thin:@localhost:1521:orcl";
 
// private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
/

 

/ private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:orcl";
 private static final String DB_USER = "id";
 private static final String DB_PASSWORD = "pass";
 public static void main(String[] args){
  
  
  try {
    selectRecordsFromDbUserTable();
   } catch (SQLException e) {
    System.out.println(e.getMessage());
   }
  
 }
 private static void selectRecordsFromDbUserTable() throws SQLException {
  Connection dbConnection = null;
  PreparedStatement preparedStatement = null;
  StringBuffer strSQL = new StringBuffer();
  strSQL.append("select * from testuser where id= ? ");
  try {
  dbConnection = getDBConnection();
  preparedStatement = dbConnection.prepareStatement(strSQL.toString());
  preparedStatement.setString(1, "a");
  
    
  ResultSet rs = preparedStatement.executeQuery();
  while (rs.next()) {
  String pass = rs.getString("pass");
  System.out.println("pass : " + pass);
  }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
  if (preparedStatement != null) {
  preparedStatement.close();
  }
  if (dbConnection != null) {
  dbConnection.close();
  }
  }
  }
 
 
 private static Connection getDBConnection() {
 Connection dbConnection = null;
 try {
 Class.forName(DB_DRIVER);
 } catch (ClassNotFoundException e) {
 System.out.println(e.getMessage());
 }
 try {
 dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
 DB_PASSWORD);
 return dbConnection;
 } catch (SQLException e) {
 System.out.println(e.getMessage());
 }

 return dbConnection;
 }

}

 

 

4. properties/log4j.properties

 

log4j.rootLogger=INFO, console

# Console log
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%-5p %l - %m%n

 

log4j.logger.jdbc.sqlonly=console

 

 

5. properties 폴더를 프로젝트에 소스 폴더로 포함시킨다.

 

 

------------결과-------------

INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.connectionOpened(Slf4jSpyLogDelegator.java:537) - 1. Connection opened
INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.methodReturned(Slf4jSpyLogDelegator.java:162) - 1. Connection.new Connection returned
INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.methodReturned(Slf4jSpyLogDelegator.java:162) - 1. PreparedStatement.new PreparedStatement returned
INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.methodReturned(Slf4jSpyLogDelegator.java:162) - 1. Connection.prepareStatement(select * from testuser where id= ? ) returned net.sf.log4jdbc.PreparedStatementSpy@1dc7e0e8
INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.methodReturned(Slf4jSpyLogDelegator.java:162) - 1. PreparedStatement.setString(1, "a") returned
DEBUG net.sf.log4jdbc.Slf4jSpyLogDelegator.sqlOccured(Slf4jSpyLogDelegator.java:221) -  SqlLogMain.selectRecordsFromDbUserTable(SqlLogMain.java:32)
1. select * from testuser where id= 'a'

INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.sqlTimingOccured(Slf4jSpyLogDelegator.java:370) - select * from testuser where id= 'a' 
 {executed in 13 msec}
INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.methodReturned(Slf4jSpyLogDelegator.java:162) - 1. ResultSet.new ResultSet returned
INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.methodReturned(Slf4jSpyLogDelegator.java:162) - 1. PreparedStatement.executeQuery() returned net.sf.log4jdbc.ResultSetSpy@5f122d62
INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.methodReturned(Slf4jSpyLogDelegator.java:162) - 1. ResultSet.next() returned true
INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.methodReturned(Slf4jSpyLogDelegator.java:162) - 1. ResultSet.getString(pass) returned 1234
pass : 1234
INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.methodReturned(Slf4jSpyLogDelegator.java:162) - 1. ResultSet.next() returned false
INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.methodReturned(Slf4jSpyLogDelegator.java:162) - 1. PreparedStatement.close() returned
INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.connectionClosed(Slf4jSpyLogDelegator.java:556) - 1. Connection closed
INFO  net.sf.log4jdbc.Slf4jSpyLogDelegator.methodReturned(Slf4jSpyLogDelegator.java:162) - 1. Connection.close() returned

 

 

 

 

 

 

 

 

 

 

 

 

SqlLogEx01.zip