"Hello World!": DBCP

2008년 2월 4일 월요일

Tomcat & Oracle DBCP 설정

Download URL

DBCP : http://commons.apache.org/dbcp/downloads.html
Pool : http://commons.apache.org/pool/downloads.html
Collection : http://commons.apache.org/downloads/download_collections.cgi

pool.jocl

DBCPInit.java

package taesuz.dbpool;

import javax.servlet.http.HttpServlet;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import java.util.StringTokenizer;

public class DBCPInit extends HttpServlet {

public void init(ServletConfig config) throws ServletException {
try {
String drivers = config.getInitParameter("pool");
StringTokenizer st = new StringTokenizer(drivers, ",");
while (st.hasMoreTokens()) {
String DB = st.nextToken();
Class.forName(DB);
}

Class.forName("org.apache.commons.dbcp.PoolingDriver");

} catch(Exception ex) {
throw new ServletException(ex);
}
}
}

web.xml

<web-app>
<servlet>
<servlet-name>DBCPInit</servlet-name>
<servlet-class>taesuz.dbpool.DBCPInit</servlet-class>
<init-param>
<param-name>pool</param-name>
<param-value>oracle.jdbc.driver.OracleDriver</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
</web-app>

설정 끝~~~~

테스트를 위한 JSP와 커넥션을 가져오는 클래스

DBConnect.java

package taesuz.dbpool;

import java.sql.*;

public class DBConnect {
/**
* APP/Web 환경에서 쓰이도록 지정 DBCP를 사용한다.
* default poolName : pool
* @return Connection
* @throws SQLException
*/

public static Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:apache:commons:dbcp:/pool");
}

/**
* APP/Web 환경에서 쓰이도록 지정 DBCP를 사용한다.
* @param poolName
* @return Connection
* @throws SQLException
*/

public static Connection getConnection(String poolName) throws SQLException {
return DriverManager.getConnection("jdbc:apache:commons:dbcp:/"
+ poolName);
}

}

test.jsp

<%@ page contentType="text/html;charset=euc-kr" %>
<%@ page import="java.sql.*" %>
<%@ page import="taesuz.dbpool.DBConnect" %>

<%
try{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

conn = DBConnect.getConnection();
String query = "select * from DUAL";
stmt = conn.createStatement();
rs = stmt.executeQuery(query);

if(rs.next())
out.print(rs.getString(1));

} catch(Exception e) {
System.out.println(e);
} finally {
   if (rs != null) try { rs.close(); } catch (Exception e) {}
   if (stmt != null) try { stmt.close(); } catch (Exception e) {}
   if (conn != null) try { conn.close(); } catch (Exception e) {}
}

%>

다른 관련 설정 트랙백 참조.

2008년 1월 17일 목요일

Tomcat 에 Oracle DataSource 설정..

Download URL

DBCP : http://commons.apache.org/dbcp/downloads.html
Pool : http://commons.apache.org/pool/downloads.html
Collection : http://commons.apache.org/downloads/download_collections.cgi

%app_dir%/WEB-INF/web.xml
----------------------------------------------------
<resource-ref>
   <description>DB Connection</description>
   <res-ref-name>jdbc/jdbcTest</res-ref-name>
   <res-type>javax.sql.DataSource</res-type>
   <res-auth>Container</res-auth>
</resource-ref>

%server_dir%/conf/sever.xml
----------------------------------------------------
<GlobalNamingResources>
......
<Resource auth="Container" driverClassName="oracle.jdbc.driver.OracleDriver"
maxActive="20" maxIdle="10" maxWait="-1" name="jdbc/jdbcTest" password="1111"
type="javax.sql.DataSource" url="jdbc:oracle:thin:@127.0.0.1:1521:testdb" username="tester" />
....
</GlobalNamingResources>

%server_dir%/context.xml
----------------------------------------------
<Context>
...
<ResourceLink global="jdbc/jdbcTest" name="jdbc/jdbcTest" type="javax.sql.DataSource" />
...
</Context>

TestDbcp.jsp
----------------------------------------------------------
<%@ page contentType="text/html;charset=euc-kr" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
try{
//DBCP init
Context initCtx = new InitialContext();
Context envCtx = (Context)initCtx.lookup("java:comp/env");
DataSource ds = (DataSource)envCtx.lookup("jdbc/jdbcTest");

Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();

String sql="SELECT sysdate FROM dual";

ResultSet rs = stmt.executeQuery(sql);

if (rs.next())
out.println(rs.getString("SYSDATE"));

stmt.close();
conn.close();

} catch(Exception e) {
out.println("error : " + e);
}

%>

위 설정중 빨간색은 사용자에 맞게 세팅.

마지막 테스트 코드를 실행 했을때 날짜가 나오면 성공이다.