apache의 dbcp를 이용한 db이용 방법

 

Tomcat DBCP를 이용한 Connection Pooling

tomcat의 server.xml 설정

<!--====================  Main Context 프로그램 작업 entry ======================-->
<Context path="/exam" docBase="/home/WWW" debug="0"
reloadable="true" crossContext="true">
<!--====================  Main Context 프로그램 작업 entry ======================-->
<!-- ====================  java의 DBCP를 이용하기 위해서 ==============-->
<Resource name="jdbc/myoracle" auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/myoracle">
 <parameter>
<name>factory</name>
   <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
 </parameter>
 <parameter>
   <name>driverClassName</name>
   <value>oracle.jdbc.driver.OracleDriver </value>
 </parameter>
 <parameter>
   <name>url</name>
   <value>jdbc:oracle:thin:@111.111.111.111:1521:ORCL</value>
 </parameter>
</parameter>
 <parameter>
   <name>username</name>
   <value>tmp_user</value>
 </parameter>
 <parameter>
   <name>password</name>
   <value>tmp1234</value>
 </parameter>
 <parameter>
   <name>maxActive</name>
   <value>4</value>
 </parameter>
 <parameter>
   <name>maxIdle</name>
   <value>10</value>
 </parameter>
 <parameter>
   <name>maxWait</name>
   <value>-1</value>
 </parameter>
 <parameter>
    <name>removeAbandoned</name>
    <value>true</value>
  </parameter>
  <parameter>
     <name>removeAbandonedTimeout</name>
     <value>60</value>
  </parameter>
</ResourceParams>
</Context>
<!-- ====================  java의 DBCP를 이용하기 위해서 ==============-->

 

server.xml에 설정된 내용을 /home/WWW/WEB-INF/web.xml 파일에
다음의 내용을 추가한다.  <res-ref-name>의 값을 server.xml에 있는 값인 jdbc/myoracle 로
채운다.

 

<web-app>

............. 생략.....

 

 <!--     KIM SANG HYUN   Oracle Connection Pool을 하기 위해서 -->
     <resource-ref>
             <description>Oracle Connection pooling </description>
             <res-ref-name>jdbc/myoracle</res-ref-name>
             <res-type>javax.sql.DataSource</res-type>
             <res-auth>Container</res-auth>
     </resource-ref>

............... 생략 ...................

</web-app>

 

다음은 test용 jsp 파일이다. 

conpool.jsp

<%@ page contentType="text/html;charset=MS949"%>
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.Statement"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.sql.SQLException"%>
<%@ page import="org.apache.commons.dbcp.BasicDataSource"%>

<%

//Context initCtx = new InitialContext();
//Context envCtx = (Context) initCtx.lookup("java:comp/env");
// Look up our data source

/*
String connectURI = "jdbc:oracle:thin:@localhost:1521:ORCL";

BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("oracle.jdbc.OracleDriver");
ds.setUsername("mnkruser");
ds.setPassword("mnkruser8420");
       ds.setUrl(connectURI);
       */

       Context initContext = new InitialContext();
       Context envCtx  = (Context)initContext.lookup("java:/comp/env");
       DataSource ds = (DataSource)envCtx.lookup("jdbc/myoracle");

Connection conn = null;
Statement stmt = null;
ResultSet rset = null;

 try {
           out.println("Creating connection.<br>");
       //    conn = dataSource.getConnection();
           conn = ds.getConnection();
           out.println("Creating statement.<br>");
           stmt = conn.createStatement();
           out.println("Executing statement.<br>");
           //rset = stmt.executeQuery("SELECT zipcode1, dong FROM htzipcode WHERE ROWNUM < 2000");
           rset = stmt.executeQuery("SELECT juminno, name FROM htorder WHERE ROWNUM < 2000");
           out.println("Results:<br>");
           while(rset.next()) {
               out.println(rset.getString("juminno") +  " " + rset.getString("name") + "<br>");
           }

       } catch(SQLException e) {
           e.printStackTrace();
           out.println("<H1>" + e.getMessage() + "</h1>");
       } finally {
           try { rset.close(); } catch(Exception e) { }
           try { stmt.close(); } catch(Exception e) { }
           try { conn.close(); } catch(Exception e) { }
       }

%>