JDBC 설정

Jboss-5.1.0.GA 기준.

1. Jboss-5.1.0.GA\server\default\lib 폴더에 JDBC 관련 JAR르 파일을 복사합니다.

2. Jboss-5.1.0.GA/Jboss-5.1.0.GA\server\default\deployers\ejb3.deployer/META-INF/jpa-deployers-jboss-beans.xml 파일을 아래와 같이 변경 및 확인 합니다.

hibernate.transaction.manager_lookup_class=org.hibernate.transaction.JBossTransactionManagerLookup
#hibernate.connection.release_mode=after_statement
#hibernate.transaction.flush_before_completion=false
#hibernate.transaction.auto_close_session=false
#hibernate.query.factory_class=org.hibernate.hql.ast.ASTQueryTranslatorFactory
#hibernate.hbm2ddl.auto=create-drop
#hibernate.hbm2ddl.auto=create
hibernate.hbm2ddl.auto=update
hibernate.cache.provider_class=org.hibernate.cache.HashtableCacheProvider
# Clustered cache with TreeCache
#hibernate.cache.provider_class=org.jboss.ejb3.entity.TreeCacheProviderHook
#hibernate.treecache.mbean.object_name=jboss.cache:service=EJB3EntityTreeCache
#hibernate.dialect=org.hibernate.dialect.HSQLDialect
hibernate.dialect=org.hibernate.dialect.Oracle9iDialect
hibernate.jndi.java.naming.factory.initial=org.jnp.interfaces.NamingContextFactory
hibernate.jndi.java.naming.factory.url.pkgs=org.jboss.naming:org.jnp.interfaces
hibernate.bytecode.use_reflection_optimizer=false
# I don't think this is honored, but EJB3Deployer uses it
hibernate.bytecode.provider=javassist

3. Jboss-5.1.0.GA\docs\examples\jca/oracle-ds.xml 파일을 Jboss-5.1.0.GA\server\default\deploy에 복사하고 다음과 같이 수정한다.

<datasources>

  <local-tx-datasource>
    <jndi-name>oracleds</jndi-name>
    <connection-url>jdbc:oracle:thin:@210.123.91.168:1521:ORA9i</connection-url>

 <!--
  Here are a couple of the possible OCI configurations.
  For more information, see http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/java.920/a96654/toc.htm
 <connection-url>jdbc:oracle:oci:@youroracle-tns-name</connection-url>
  or
 <connection-url>jdbc:oracle:oci:@(description=(address=(host=youroraclehost)(protocol=tcp)(port=1521))(connect_data=(SERVICE_NAME=yourservicename)))</connection-url>
  Clearly, its better to have TNS set up properly.
  -->

    <!-- Oracle 8i
    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
    -->
    <!-- Oracle 9i/10g -->
    <driver-class>oracle.jdbc.OracleDriver</driver-class>
    <user-name>META</user-name>
    <password>PASSWORD</password>
    <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
    <!--valid-connection-checker-class-name>
          org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker
    </valid-connection-checker-class-name-->
    <!-- Checks the Oracle error codes and messages for fatal errors -->
    <exception-sorter-class-name>
          org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
    </exception-sorter-class-name>
        <!-- sql to call when connection is created
        <new-connection-sql>some arbitrary sql</new-connection-sql>
        -->

        <!-- sql to call on an existing pooled connection when it is obtained from pool - the OracleValidConnectionChecker is prefered
        <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
        -->

      <min-pool-size>5</min-pool-size>
      <max-pool-size>20</max-pool-size>

      <blocking-timeout-millis>5000</blocking-timeout-millis>
      <idle-timeout-minutes>10</idle-timeout-minutes>

      <prepared-statement-cache-size>100</prepared-statement-cache-size>
      <track-statements>false</track-statements>

      <valid-connection-checker-class-name>
            org.jboss.resource.adapter.jdbc.vendor.OralceValidConnectionChecker
      </valid-connection-checker-class-name>

      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
      <metadata>
         <type-mapping>Oracle9i</type-mapping>
      </metadata>
  </local-tx-datasource>

</datasources>

4. 샘플 프로그램: Jboss-5.1.0.GA\server\default\deploy\ROOT.war\oracleds.jsp로 저장한다.

<%@ page language="java" contentType="text/html; charset=EUC-KR" pageEncoding="EUC-KR"%>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.sql.DataSource" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.ResultSet" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>META SYSTEM</title>
</head>
<body>
 <table border="1">
   <tr>
     <td>CD_ID</td>
     <td>CD_ID_NM</td>
   </tr>
 <%
 Context ctx = null;
 DataSource ds = null;
 Connection con = null;
 Statement stmt = null;
 ResultSet rs = null;
 
 String sql = "select cd_id, cd_id_nm from aplsht_cd_info";
 try {
     ctx  = new InitialContext();
     ds   = (DataSource)ctx.lookup("java:oracleds");
     con  = ds.getConnection();
     stmt = con.createStatement();
     rs   = stmt.executeQuery(sql);
     while(rs.next()) {
 %>
    <tr>
       <td><%= rs.getString("CD_ID") %></td>
       <td><%= rs.getString("CD_ID_NM") %></td>
    </tr>
 <%
  }
 } catch(Exception e) {
     e.printStackTrace();
 %>
   <tr>
     <td colspan="3">Error : <%= e.getMessage() %></td>
   </tr>
 <%
 } finally {
     if(rs != null) try { rs.close(); } catch(Exception ignore) {}
     if(stmt != null) try { stmt.close(); } catch(Exception ignore) {}
     if(con != null) try { con.close(); } catch(Exception ignore) {}
 }
 %>
 </table>
</body>
</html>

출처: micropilot.tistory.com 참조.