Tomcat JDBC Pool初探

为什么选择Tomcat JDBCPool

The JDBC Connection Pool org.apache.tomcat.jdbc.pool is areplacement or an alternative to theApacheCommons DBCP connection pool.

So why do we need a new connection pool?

Here are a few of the reasons:

1. Commons DBCP 1.xis single threaded. In order to be thread safe Commons locks the entire poolfor short periods during both object allocation and object return. Note thatthis does not apply to Commons DBCP 2.x.

2. Commons DBCP 1.xcan be slow. As the number of logical CPUs grows and the number of concurrentthreads attempting to borrow or return objects increases, the performancesuffers. For highly concurrent systems the impact can be significant. Note thatthis does not apply to Commons DBCP 2.x.

3. Commons DBCP isover 60 classes. tomcat-jdbc-pool core is 8 classes, hence modifications forfuture requirement will require much less changes. This is all you need to runthe connection pool itself, the rest is gravy.

4. Commons DBCP usesstatic interfaces. This means you have to use the right version for a given JREversion or you may see NoSuchMethodException exceptions.

5. It's not worthrewriting over 60 classes, when a connection pool can be accomplished with amuch simpler implementation.

6. Tomcat jdbc poolimplements the ability retrieve a connection asynchronously, without addingadditional threads to the library itself.

7. Tomcat jdbc poolis a Tomcat module, it depends on Tomcat JULI, a simplified logging frameworkused in Tomcat.

8. Retrieve theunderlying connection using the javax.sql.PooledConnection interface.

9. Starvation proof.If a pool is empty, and threads are waiting for a connection, when a connectionis returned, the pool will awake the correct thread waiting. Most pools will simplystarve.

Features added over other connection pool implementations

1. Support for highlyconcurrent environments and multi core/cpu systems.

2. Dynamicimplementation of interface, will support java.sql and javax.sql interfaces foryour runtime environment (as long as your JDBC driver does the same), even whencompiled with a lower version of the JDK.

3. Validationintervals - we don't have to validate every single time we use the connection,we can do this when we borrow or return the connection, just not more frequentthan an interval we can configure.

4. Run-Once query, aconfigurable query that will be run only once, when the connection to thedatabase is established. Very useful to setup session settings, that you wantto exist during the entire time the connection is established.

5. Ability toconfigure custom interceptors. This allows you to write custom interceptors toenhance the functionality. You can use interceptors to gather query stats,cache session states, reconnect the connection upon failures, retry queries,cache query results, and so on. Your options are endless and the interceptorsare dynamic, not tied to a JDK version of a java.sql/javax.sql interface.

6. High performance -we will show some differences in performance later on

7. Extremely simple,due to the very simplified implementation, the line count and source file countare very low, compare with c3p0 that has over 200 source files(last time wechecked), Tomcat jdbc has a core of 8 files, the connection pool itself isabout half that. As bugs may occur, they will be faster to track down, andeasier to fix. Complexity reduction has been a focus from inception.

8. Asynchronousconnection retrieval - you can queue your request for a connection and receivea Future<Connection> back.

9. Better idleconnection handling. Instead of closing connections directly, it can still poolconnections and sizes the idle pool with a smarter algorithm.

10.You can decide at what moment connections are consideredabandoned, is it when the pool is full, or directly at a timeout by specifyinga pool usage threshold.

11.The abandon connection timer will reset upon astatement/query activity. Allowing a connections that is in use for a long timeto not timeout. This is achieved using the ResetAbandonedTimer

12.Close connections after they have been connected for acertain time. Age based close upon return to the pool.

13.Get JMX notifications and log entries when connectionsare suspected for being abandoned. This is similar to the removeAbandonedTimeoutbut it doesn't take any action, only reports the information. This is achievedusing the suspectTimeout attribute.

14.Connections can be retrieved from a java.sql.Driver, javax.sql.DataSourceor javax.sql.XADataSource This is achieved using the dataSource and dataSourceJNDIattributes.

15.XA connection support

 

参考文档

官方文档：http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#As_a_Resource

JDBC Source：http://tomcat.apache.org/tomcat-7.0-doc/jndi-resources-howto.html#JDBC_Data_Sources

 

案例

软件包：apache tomcat 1.7

使用数据库：mysql 5.0+

JDK:1.7

IDE:MyEclipse 1.0 x86

注：

 

 

Steps:

1.      新建工程

运行myeclipse，新建一个工程jtpooltest

2.      进入tomcat目录下的lib目录，将tomcat-jdbc.jar拷贝进工程目录下WEB-INF/lib中

3.      修改tomcat目录下的conf/context.xml文档,在context节点下插入Resource

<Resource name="jdbc/test"

         auth="Container"

         type="javax.sql.DataSource"

         factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

         testWhileIdle="true"

         testOnBorrow="true"

         testOnReturn="false"

         validationQuery="SELECT 1"

         validationInterval="30000"

         timeBetweenEvictionRunsMillis="30000"

          maxActive="100"

         minIdle="10"

         maxWait="10000"

         initialSize="10"

         removeAbandonedTimeout="60"

         removeAbandoned="true"

         logAbandoned="true"

         minEvictableIdleTimeMillis="30000"

         jmxEnabled="true"

         jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;

           org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"

         username="root"

         password=""

         driverClassName="com.mysql.jdbc.Driver"

         url="jdbc:mysql://localhost:3306/test"/>

4.      在jtpooltest工程下的WEB-INF/web.xm中web-app节点下添加

 

<resource-ref>

    <description>

    test factory

    </description>

    <res-ref-name>jdbc/testDB</res-ref-name>

    <res-type>javax.sql.DataSource</res-type>

    <res-auth>Container</res-auth>

 </resource-ref>

 

5.在mysql中新建test数据库，新建一张test表

 插入一条数据

6.      新建一个servlet，

代码为：

package com.servlets;

 

import java.io.IOException;

import java.sql.*;

import java.io.PrintWriter;

 

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.sql.DataSource;

 

publicclass JTPool extends HttpServlet {

 

    /**

     * The doGet method of the servlet. <br>

     *

     * This method is called when a form has its tag value method equals to get.

     *

     * @param request the request send by the client to the server

     * @param response the response send by the server to the client

     * @throws ServletException if an error occurred

     * @throws IOException if an error occurred

     */

    publicvoid doGet(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

 

        response.setContentType("text/html");

        PrintWriter out = response.getWriter();

       

        //获取DataSource

        DataSource ds = null;

       

        //数据库连接需要的

        Connection conn = null;

        Statement stat = null;

        ResultSet rst = null;

       

        try{

           

            //找寻datasource

            Context context = new InitialContext();

            //这里的jdbc/test是<res-ref-name>jdbc/test</res-ref-name>中配置的

            ds = (DataSource) context.lookup("java:comp/env/jdbc/test");

           

            //接下来从数据源连接池中获取connetion

            conn = ds.getConnection();

           

            //查询数据

            stat = conn.createStatement();

            rst = stat.executeQuery("select * from test");

            while( rst.next() ) {

                out.print("ID:"+rst.getString("id")+" foo:"+rst.getString("foo")+" bar:"+rst.getString("bar"));

            }

           

        }catch(Exception e){

            e.printStackTrace();

        }finally {

            //关闭JDBC连接资源

            if( rst!=null ) {

                try {

                    rst.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

            }

            if( stat!=null ) {

                try {

                    stat.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

            }

            if( conn!=null ) {

                try {

                    conn.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

            }

        }

       

       

    }

 

    /**

     * The doPost method of the servlet. <br>

     *

     * This method is called when a form has its tag value method equals to post.

     *

     * @param request the request send by the client to the server

     * @param response the response send by the server to the client

     * @throws ServletException if an error occurred

     * @throws IOException if an error occurred

     */

    publicvoid doPost(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

 

        this.doGet(request, response);

    }

 

}

 

7.      部署

启动tomcat，没有报错的情况下，打开浏览器http://localhost:8080/jtpooltest/jtpool

成功！