초보코더의 메모장

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

IBSheet.chm

Oracle 에서 데이터 중에 자바 프로퍼티가 들어가야 할 경우가 있다.

이때 자바 컨벤션에 맞게 치환해주는 함수를 만들어 봄.

예) DATA_FIELD_SET - > dataFieldSet

--함수만들기

------------------------

CREATE OR REPLACE FUNCTION UF_REPLACE_CONVENTION( V_P IN VARCHAR2)
RETURN VARCHAR2  IS
    V_RESULT VARCHAR2(1000) := '';
    V_TEMP VARCHAR2(1000) := '';
    V_TEMPINDEX NUMBER := 0;
BEGIN

V_TEMP :=  LOWER(V_P);

WHILE INSTR(V_TEMP, '_' ) > 0
LOOP
 V_TEMPINDEX := INSTR(V_TEMP, '_' );
    V_TEMP := SUBSTR(V_TEMP, 0, V_TEMPINDEX-1)|| UPPER(SUBSTR(V_TEMP, V_TEMPINDEX + 1, 1))|| SUBSTR(V_TEMP, V_TEMPINDEX + 2);
END LOOP;

 V_RESULT := V_TEMP;
RETURN V_RESULT;

END UF_REPLACE_CONVENTION;

--사용

SELECT UF_Replace_convention('DATA_FIELD_SET' )
FROM DUAL;