DB Connection 정리

1.      전제 조건

1)     DBMS PRODUCT : Oracle 11g

2)     SERVICE PORT : 1521

3)     SERVICE NAME : framework

4)     USER : framework_test

5)     PASSWORD : framework_test

 

2.      DriverManager

DriverManager 를 이용하여 DB CONNECTION을 생성하는 로직을 작성하시오.

 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException;   public class DriverManagerTest {          public Connection getConnection() {                           String url = "jdbc:oracle:thin:@localhost:1521:framework";              String user = "framework_test";              String password = "framework_test";                           Connection conn = null;                           try {                        DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver());                     conn = DriverManager.getConnection(url, user, password);                                  } catch (SQLException e) {                     e.printStackTrace();              }                           return conn;        } }

 

3.      CONNECTION POOL

CONNECTION POOL의 필요성을 기술하시오.

 

①     DBMS로의 연결이 오래 걸린다.

②     DB 작업을 할 때마다 Connection ~ Close 까지의 반복 작업이 비효율적이다.

③     Connection Pool에 Connection 객체를 생성해 놓고 DB 작업 시 Pool에서 빌려 사용 후 다시 반납이 가능하다.

 

4.      JNDI

LOCAL WAS의 DATA SOURCE를 JNDI 방식으로 LOOKUP하는 로직을 작성하시오.

(JNDI NAME : "jdbc/myDS")

 

1)      JAVA Source

import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource;     public class JNDITest {          public DataSource getDataSource() {                     DataSource ds = null;                            try {                     Context initContext;                     initContext = new InitialContext();                     Context envContext = (Context)initContext.lookup("java:/comp/env");                     ds = (DataSource)envContext.lookup("jdbc/myDS");                                  } catch (NamingException e) {                     e.printStackTrace();              }                                return ds;        } }

 

2)      web.xml

<resource-ref>     <description>Oracle DB Connection</description>     <res-ref-name>jdbc/myDS</res-ref-name>     <res-type>javax.sql.DataSource</res-type>     <res-auth>Container</res-auth> </resource-ref>

 

3)      tomcat6 서버 설정 (conf/server.xml)

<Context path="" docBase="" reloadable="false">     <Resource name="jdbc/myDS" auth="Container"               type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"               url="jdbc:oracle:thin:@localhost:1521:framework"               username="framework_test" password="framework_test"               initialSize="50" maxActive="100" maxIdle="50" maxWait="10000"               validationQuery="SELECT 1 FROM DUAL" validationInterval="300000"/> </Context>

 

4)      JEUS6 서버 설정 (JEUSMain.xml)

<database>     <vendor>oracle</vendor>     <export-name>jdbc/myDS</export-name>     <data-source-class-name> oracle.jdbc.pool.OracleConnectionPoolDataSource </data-source-class-name>     <data-source-type>ConnectionPoolDataSource</data-source-type>     <user>framework_test</user>     <password>framework_test</password>     <property>         <name>URL</name>         <type>java.lang.String</type>         <value>jdbc:oracle:thin:@localhost:1521:framework</value>     </property>     <connection-pool>         <pooling>             <min>50</min>             <max>100</max>             <step>5</step>             <period>3600000</period>         </pooling>         <wait-free-connection>             <enable-wait>true</enable-wait>         </wait-free-connection>         <check-query>SELECT 1 FROM DUAL</check-query>         <non-validation-interval>200000</non-validation-interval>         <check-query-period>300000</check-query-period>     </connection-pool> </database>

 

5.      APACHE COMMONS DBCP

APACHE COMMONS DBCP(http://commons.apache.org/dbcp/index.html, v1.3 이상)의 org.apache.commons.dbcp.BasicDataSource를 이용하여 DATA SOURCE를 생성하는 로직을 작성하시오. DATA SOURCE 생성시 필요한 파라미터에 대해서 기술하시오. 또한, 해당 로직 실행에 관련된 오픈소스 라이브러리를 모두 기술하시오.

 

1)      JAVA Source

import javax.sql.DataSource;   import org.apache.commons.dbcp.BasicDataSource;   public class DBCPTest {               public DataSource getDataSource() {                           String classname = "oracle.jdbc.driver.OracleDriver";              String url = "jdbc:oracle:thin:@localhost:1521:framework";              String user = "framework_test";              String password = "framework_test";                           BasicDataSource ds = null;                           ds = new BasicDataSource();                         ds.setDriverClassName(classname);              ds.setUrl(url);              ds.setUsername(user);              ds.setPassword(password);              ds.setMaxActive(10);              ds.setInitialSize(10);              ds.setMinIdle(5);              ds.setMaxWait(5000);              ds.setPoolPreparedStatements(true);                           return ds;        } }

 

2)      파라미터 설명

①     initialSize : 초기에 생성될 커넥션개수.

②     maxActive : 커넥션풀이 제공할 최대 커넥션개수.

③     maxIdle : 사용되지 않고 풀에 저장될 수 있는 최대 커넥션개수, 음수일경우 제한 없음.

④     minIdle : 사용되지 않고 풀에 저장될 수 있는 최소 커넥션개수.

⑤     maxWait : 풀에 커넥션이 존재하지 않을 때, 커넥션이 풀에 다시 리턴 되기 까지 대기시간. 1/1000초 단위, -1일 경우 무한대기.

⑥     minEvictableIdleTimeMillis : 사용되지 않은 커넥션을 추출할 때, 이 속성에서 지정한 시간이상 비활성화 상태인 커넥션만 추출한다. 양수가 아닌 경우 비활성화 된 시간으로는 풀에서 제거 안됨.

⑦     timeBetweenEvictionRunsMillis : 사용되지 않은 커넥션을 추출하는 쓰레드의 실행주기를 설정. 양수가 아닌 경우 실행되지 않음.

⑧     numTestsPerEvictionRun : 사용되지 않은 커넥션을 몇 개 검사할지 지정.

⑨     testOnBorrow : true일 경우, 커넥션 풀에서 커넥션을 가져올 경우, 커넥션이 유효한지 검사.

⑩     testOnReturn : true일 경우, 커넥션 풀에 커넥션을 반환할 때, 커넥션이 유효한지 검사.

 

3)      필요한 라이브러리

①     commons-dbcp-1.4.jar

②     commons-collections-3.2.1.jar

③     commons-pool-1.6.jar

 

6.      SQL

아래 제시된 테이블을 SELECT, INSERT, UPDATE, DELETE 하는 로직을 쿼리를 포함하여 각각의 메소드로 작성하시오. 각 메소드의 파라미터는 DB CONNECTION 객체(java.sql.Connection)이며, PREPARED STATEMENT를 사용해야 한다. 또한, 내부에서 생성된 자원의 해제 로직도 모두 포함해야 함.

 

CREATE TABLE SAMPLE_ACC_M

(

     ACC_NO              VARCHAR2 (20)   NOT NULL,

     ACC_NAME           VARCHAR2 (128)  NOT NULL,

     ACC_PASSWD         VARCHAR2 (4)    NOT NULL,

     ACC_BALANCE        NUMBER   (17)   NOT NULL,

     CRE_DTM             VARCHAR2 (17)   NOT NULL,

     UPD_DTM             VARCHAR2 (17)   NOT NULL,

     CONSTRAINT SAMPLE_ACC_M_PK PRIMARY KEY (ACC_NO)

);

 

1)      SQLTest Class

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;   public class SQLTest {          public Connection getConnection() {                           String url = "jdbc:oracle:thin:@localhost:1521:framework";              String user = "framework_test";              String password = "framework_test";                           Connection conn = null;                           try {                     DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver());                     conn = DriverManager.getConnection(url, user, password);                                  } catch (SQLException e) {                     e.printStackTrace();              }                         return conn;        }               public List<AccVO> select(String accName) {                           PreparedStatement pstmt = null;              ResultSet rs = null;              List<AccVO> resultList = new ArrayList<AccVO>();                           String selectQuery = "SELECT ACC_NO, ACC_NAME, ACC_PASSWD, " + "ACC_BALANCE, CRE_DTM, UPD_DTM FROM SAMPLE_ACC_M " + "WHERE ACC_NAME = ?";                           Connection conn = getConnection();                                  if(conn!=null){                     try {                            pstmt = conn.prepareStatement(selectQuery);                            pstmt.setString(1, accName);                            rs = pstmt.executeQuery();                                                         while(rs.next()){                                  AccVO accVo = new AccVO();                                                                   accVo.setAccNo(rs.getString("ACC_NO"));                                  accVo.setAccName(rs.getString("ACC_NAME"));                                  accVo.setAccPasswd(rs.getString("ACC_PASSWD"));                                  accVo.setAccBalance(rs.getLong("ACC_BALANCE"));                                  accVo.setCreDtm(rs.getString("CRE_DTM"));                                  accVo.setUpdDtm(rs.getString("UPD_DTM"));                                                                   resultList.add(accVo);                            }                                                  } catch (SQLException e) {                            e.printStackTrace();                     }finally{                            try {                                  if(rs!=null) rs.close();                            } catch (SQLException e) {                                  e.printStackTrace();                            }                            try {                                  if(pstmt!=null) pstmt.close();                            } catch (SQLException e) {                                  e.printStackTrace();                            }                            try {                                  if(conn!=null) conn.close();                            } catch (SQLException e) {                                  e.printStackTrace();                            }                     }              }                         return resultList;        }               public int insert(AccVO accVo) {                           PreparedStatement pstmt = null;              int cnt = -1;                           String insertQuery = "INSERT INTO SAMPLE_ACC_M VALUES " + "(?, ?, ?, ?, ?, ?)";                           Connection conn = getConnection();                                  if(conn!=null){                     try {                            pstmt = conn.prepareStatement(insertQuery);                                                       pstmt.setString(1, accVo.getAccNo());                            pstmt.setString(2, accVo.getAccName());                            pstmt.setString(3, accVo.getAccPasswd());                            pstmt.setLong(4, accVo.getAccBalance());                            pstmt.setString(5, accVo.getCreDtm());                            pstmt.setString(6, accVo.getUpdDtm());                                                       cnt = pstmt.executeUpdate();                                                  } catch (SQLException e) {                            e.printStackTrace();                     }finally{                            try {                                  if(pstmt!=null) pstmt.close();                            } catch (SQLException e) {                                  e.printStackTrace();                            }                            try {                                  if(conn!=null) conn.close();                            } catch (SQLException e) {                                  e.printStackTrace();                            }                     }              }                         return cnt;        }               public int update(AccVO accVo) {                           PreparedStatement pstmt = null;              int cnt = -1;                           String insertQuery = "UPDATE SAMPLE_ACC_M SET ACC_NAME = ?, " + "ACC_PASSWD = ?, ACC_BALANCE = ?, CRE_DTM = ?, " + "UPD_DTM = ? WHERE ACC_NO = ?";                           Connection conn = getConnection();                                  if(conn!=null){                     try {                            pstmt = conn.prepareStatement(insertQuery);                                                       pstmt.setString(2, accVo.getAccName());                            pstmt.setString(2, accVo.getAccPasswd());                            pstmt.setLong(3, accVo.getAccBalance());                            pstmt.setString(4, accVo.getCreDtm());                            pstmt.setString(5, accVo.getUpdDtm());                            pstmt.setString(6, accVo.getAccNo());                                                       cnt = pstmt.executeUpdate();                                                  } catch (SQLException e) {                            e.printStackTrace();                     }finally{                            try {                                  if(pstmt!=null) pstmt.close();                            } catch (SQLException e) {                                  e.printStackTrace();                            }                            try {                                  if(conn!=null) conn.close();                            } catch (SQLException e) {                                  e.printStackTrace();                            }                     }              }                         return cnt;        }               public int delete(String accNo) {                           PreparedStatement pstmt = null;              int cnt = -1;                           String insertQuery = "DELETE FROM SAMPLE_ACC_M WHERE ACC_NO = ?";                           Connection conn = getConnection();                                  if(conn!=null){                     try {                            pstmt = conn.prepareStatement(insertQuery);                            pstmt.setString(1, accNo);                                               cnt = pstmt.executeUpdate();                                                  } catch (SQLException e) {                            e.printStackTrace();                     }finally{                            try {                                  if(pstmt!=null) pstmt.close();                            } catch (SQLException e) {                                  e.printStackTrace();                            }                            try {                                  if(conn!=null) conn.close();                            } catch (SQLException e) {                                  e.printStackTrace();                            }                     }              }                         return cnt;        } }

 

2)      AccVO Class

public class AccVO {               String accNo;        String accName;        String accPasswd;        long accBalance;        String creDtm;        String updDtm;               public String getAccNo() {              return accNo;        }        public void setAccNo(String accNo) {              this.accNo = accNo;        }        public String getAccName() {              return accName;        }        public void setAccName(String accName) {              this.accName = accName;        }        public String getAccPasswd() {              return accPasswd;        }        public void setAccPasswd(String accPasswd) {              this.accPasswd = accPasswd;        }        public long getAccBalance() {              return accBalance;        }        public void setAccBalance(long accBalance) {              this.accBalance = accBalance;        }        public String getCreDtm() {              return creDtm;        }        public void setCreDtm(String creDtm) {              this.creDtm = creDtm;        }        public String getUpdDtm() {              return updDtm;        }        public void setUpdDtm(String updDtm) {              this.updDtm = updDtm;        } }