[Java,Tomcat] 第一次使用DBCP就上手

本篇文章記錄安裝Tomcat的DBCP(Data Base Connection Pool)過程

測試環境：
Apache Tomcat/6.0.29
JVM1.6.0_21-b06
Connector/J 5.1.5 (the official JDBC Driver)mysql-connector-java-5.1.5-bin.jar

開始設定DBCP

步驟一：DBCP Installation

DBCP uses the Commons Database Connection Pool. It relies on number of Commons components:

• Commons DBCP
• Commons Pool

These libraries are located in a single JAR at $CATALINA_HOME/lib/tomcat-dbcp.jar. However, only the classes needed for connection pooling have been included, and the packages have been renamed to avoid interfering with applications.


簡單來說就是不需要安裝額外的.jar檔


步驟二：Context configuration

Configure the JNDI DataSource in Tomcat by adding a declaration for your resource to your Context.
For example:

<Context>

    <!-- maxActive: Maximum number of dB connections in pool. Make sure you
         configure your mysqld max_connections large enough to handle
         all of your db connections. Set to -1 for no limit.
         -->

    <!-- maxIdle: Maximum number of idle dB connections to retain in pool.
         Set to -1 for no limit.  See also the DBCP documentation on this
         and the minEvictableIdleTimeMillis configuration parameter.
         -->

    <!-- maxWait: Maximum time to wait for a dB connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->

    <!-- username and password: MySQL dB username and password for dB connections  -->

    <!-- driverClassName: Class name for the old mm.mysql JDBC driver is
         org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
         Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
         -->
    
    <!-- url: The JDBC connection url for connecting to your MySQL dB.
         -->

  <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
               maxActive="100" maxIdle="30" maxWait="10000"
               username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/TestDB"/>

</Context> 

Tip:TestDB為資料庫的名字，請勿打錯~不然會造成Name jdbc is not bound in this Context
把上面的加到tomcat/conf/context.xml

步驟三：web.xml configuration
Now create a WEB-INF/web.xml for this test application.

<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">
  <description>MySQL Test App</description>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/TestDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>
  

Tip:為了要防止DataBase Connection Pool Leak

請加上以下三個屬性：

  removeAbandoned="true"
           removeAbandonedTimeout="60"
           logAbandoned="true"

將上述resource-ref加到你專案的web.xml設定內

步驟四：測試DBCP，以下範例只示範如果取得db connection

 // Look up our data source
  try {
   // Obtain our environment naming context
   Context initCtx = new InitialContext();
   Context envCtx = (Context) initCtx.lookup("java:comp/env");
   DataSource ds = (DataSource)envCtx.lookup("jdbc/TestDB");
   
   this.con = ds.getConnection();
   
  } catch (NamingException e) {
   
  } catch (SQLException e) {
   
  }

進階設定

由於在每個專案都要設定web.xml的話，有點麻煩，所以可以採用Global DBCP的設定方法，

參考至Tomcat 6 using JNDI global DBCP，擷錄以下片段：

Add this in your tomcat\conf\server.xml, the parameters are set according to your situation

<GlobalNamingResources>

    <!-- Editable user database that can also be used by

         UserDatabaseRealm to authenticate users

    --> <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"

               maxActive="100" maxIdle="30" maxWait="10000"

               username="root" password="" driverClassName="com.mysql.jdbc.Driver"

               url="jdbc:mysql://localhost:3306/TestDB?autoReconnect=true"/>

</GlobalNamingResources>

Add this to your tomcat\conf\context.xml

<ResourceLink name="jdbc/TestDB" global="jdbc/TestDB" type="javax.sql.DataSource"/>

測試遇到以下二個Exception：




1.Cannot get a connection, pool error Timeout waiting for idle object


2.Name jdbc is not bound in this Context
這個問題主要是用程式呼叫時候Context，lookup方段帶入的路徑參數錯誤

Context envCtx = (Context) initCtx.lookup("java:comp/env"); 

DataSource ds = (DataSource)envCtx.lookup("jdbc/TestDB");

<!--DBCP-->
    <Resource name="jdbc/test"
        auth="Container"
        type="javax.sql.DataSource"
        initialSize="1"
        maxActive="5"
        maxIdle="1"
        maxWait="60000"
        username="root"
        password="password"
        driverClassName="com.mysql.jdbc.Driver"
        removeAbandoned="true"
        url="jdbc:mysql://127.0.0.1:3306/test"
        autoReconnect="true"
        validationInterval="30000"
        timeBetweenEvictionRunsMillis="5000"
        minEvictableIdleTimeMillis="30000"
        removeAbandonedTimeout="300"
        logAbandoned="false"
        vaildationQuery="SELECT 1"
        vaildationQueryTimeout="5"
        testOnBorrow="true"
        testWhileIdle="true"
    />

Reference:
http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html
http://commons.apache.org/dbcp/configuration.html
Tomcat 6.0.14 JNDI Datasource HOW-TO DBCP 安裝過程分享
Tomcat 6 using JNDI global DBCP