各种连接池的比较

• hibernate开发组推荐使用c3p0;
• spring开发组推荐使用dbcp(dbcp连接池有weblogic连接池同样的问题，就是强行关闭连接或数据库重启后，无法reconnect ，告诉连接被重置,这个设置可以解决);
• hibernate in action推荐使用c3p0和proxool;

dbcp的配置

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">

<property name="driverClassName" value="${db.driverClassName}"/>

<property name="url" value="${db.url}"/>

<property name="username" value="${db.username}"/>

<property name="password" value="${db.password}"/>

<property name="validationQuery">

<value>SELECT 1</value>

</property>

<property name="testOnBorrow">

<value>true</value>

</property>

</bean>

 

-------------------------------------

c3p0的配置， 注意是''driverClass' , 'jdbcUrl', 'user' , 'password'

<bean id="dataSource"

   class="com.mchange.v2.c3p0.ComboPooledDataSource"

   destroy-method="close">

   <property name="driverClass">

    <value>oracle.jdbc.driver.OracleDriver</value>

   </property>

   <property name="jdbcUrl">

    <value>jdbc:oracle:thin:@218.202.151.179:58580:ORA9I</value>

   </property>

   <property name="user">

    <value>oa</value>

   </property>

   <property name="password">

    <value>XXXX</value>

   </property>

   <property name="minPoolSize">

    <value>15</value>

   </property>

   <property name="acquireIncrement">

    <value>5</value>

   </property>

   <property name="maxPoolSize">

    <value>25</value>

   </property>

</bean>

 

或

<bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"

        destroy-method="close">

        <property name="driverClass">

            <value>${db.driverClass}</value>

        </property>

        <property name="jdbcUrl">

            <value>${db.url}</value>

        </property>

         <!--

        <property name="user">

            <value>${db.user}</value>

        </property>

        <property name="password">

            <value>${db.pass}</value>

        </property>

        -->

        <property name="properties">

            <props>

                <prop key="c3p0.acquire_increment">5</prop>

                <prop key="c3p0.idle_test_period">100</prop>

                <prop key="c3p0.max_size">100</prop>

                <prop key="c3p0.max_statements">0</prop>

                <prop key="c3p0.min_size">10</prop>

                <prop key="user">${db.user}</prop>

                <prop key="password">${db.pass}</prop>

            </props>

        </property>

</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">

 

        <property name="hibernateProperties">

<props>

<prop key="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</prop>

                             <prop key="hibernate.connection.url">jdbc:hsqldb:hsql://localhost/gpcswebdb</prop>

<prop key="hibernate.connection.username">sa</prop>

<prop key="hibernate.connection.password"></prop>

<prop key="hibernate.c3p0.min_size">5</prop>

<prop key="hibernate.c3p0.max_size">20</prop>

<prop key="hibernate.c3p0.timeout">1800</prop>

<prop key="hibernate.c3p0.max_statements">50</prop>

 

<prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>

 

<prop key="hibernate.show_sql">false</prop>

<prop key="hibernate.use_outer_join">true</prop>

 

<prop key="hibernate.hbm2ddl.auto">update</prop>

 

<prop key="hibernate.jdbc.batch_size">0</prop>

<prop key="hibernate.jdbc.batch_versioned_data">true</prop>

<prop key="hibernate.max_fetch_depth">1</prop>

<prop key="hibernate.jdbc.use_streams_for_binary">true</prop>

</props>

</property>

<property name="mappingResources">

<list>

<value>com/citi/rpc/webentity/BusinessDay.hbm.xml</value>

<value>com/citi/rpc/webentity/Duty.hbm.xml</value>

<value>com/citi/rpc/webentity/Employee.hbm.xml</value>

<value>com/citi/rpc/webentity/TeamMember.hbm.xml</value>

<value>com/citi/rpc/webentity/Project.hbm.xml</value>

</list>

</property>

</bean>

 

---------------------------------------

xapool的配置

<bean id="dataSource" class="org.enhydra.jdbc.pool.StandardPoolDataSource" destroy-method="stopPool">

    <constructor-arg index="0">

      <bean class="org.enhydra.jdbc.standard.StandardConnectionPoolDataSource">

        <property name="driverName"><value>com.mysql.jdbc.Driver</value></property>

        <property name="url"><value>jdbc:mysql://localhost/dbname</value></property>

      </bean>

    </constructor-arg>

    <property name="user"><value>root</value></property>

    <property name="password"><value>mypass</value></property>

    <property name="minSize"><value>1</value></property>

    <property name="maxSize"><value>5</value></property>

    <property name="jdbcTestStmt"><value>select 1</value></property>

</bean>

 

--------------------------------------

c-jdbc的配置

<bean id="dataSource" class="org.objectweb.cjdbc.driver.DataSource">

    <property name="url"><value>jdbc:cjdbc://127.0.0.1:25322/vdb?user=vuser</value></property>

</bean>

 

weblogic的连接池解决办法:Test Reserved Connections: 如果选择了这个选项，服务器会在把连接提供给客户端之前

对其进行测试。 Test Created Connections: 如果选择了这个选项，就会在创建一个JDBC

连接之后和在把它添加到JDBC连接池中的可用连接列表之前，对该JDBC连接进行测试。

tomcat的jndi关于dbcp的配置:

 

<parameter>

         <name>factory</name>

         <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>

       </parameter>

       <parameter>

         <name>driverClassName</name>

         <value>com.sybase.jdbc2.jdbc.SybDriver</value>

       </parameter>

       <parameter>

         <name>url</name>

         <value>xyz</value>

       </parameter>

       <parameter>

         <name>username</name>

         <value>xyz</value>

       </parameter>

       <parameter>

         <name>password</name>

         <value>xyz</value>

       </parameter>

       <parameter>

         <name>maxActive</name>

         <value>5</value>

       </parameter>

       <parameter>

         <name>maxIdle</name>

         <value>5</value>

       </parameter>

       <parameter>

         <name>maxWait</name>

         <value>-1</value>

       </parameter>

       <parameter>

         <name>removeAbandoned</name>

         <value>true</value>

       </parameter>

       <parameter>

         <name>validationQuery</name>

         <value>select count(*) from sometable where 1 = 0</value>

       </parameter>

       <parameter>

         <name>testOnBorrow</name>

         <value>true</value>

       </parameter>

 

 

---------------------------------------------------------------------------

很多人都说proxool很好 —— 强烈推荐proxool，因为他不但可以监控后台。还可以有效的释放连接。在connection close时，也就是归还connection，

关闭所有的statement，并且判断是否autocommit，如果不行，就rollback，并且设置true，

可以参考proxool的org.logicalcobwebs.proxool.ConnectionResetter类

connection pool把connection reset置回initial state。

但是在网上搜索spring中配置proxool作为数据源连接池的资料却几乎找不到详细点的。So自己整理如下：

 

web.xml

-------------------------

<servlet>

    <servlet-name>proxoolServletConfigurator</servlet-name>

    <servlet-class>org.logicalcobwebs.proxool.configuration.ServletConfigurator</servlet-class>

    <init-param>

      <param-name>xmlFile</param-name>

      <param-value>WEB-INF/proxool.xml</param-value>

    </init-param>

    <load-on-startup>1</load-on-startup>

</servlet>

<servlet>

    <servlet-name>proxooladmin</servlet-name>

    <servlet-class>org.logicalcobwebs.proxool.admin.servlet.AdminServlet</servlet-class>

</servlet>

<servlet-mapping>

    <servlet-name>proxooladmin</servlet-name>

    <url-pattern>/proxooladmin</url-pattern>

</servlet-mapping>

 

-------------------------

 

proxool.xml

-------------------------

<?xml version="1.0" encoding="UTF-8"?>

<proxool-config>

<proxool>

    <alias>blogdb</alias>

    <driver-url>jdbc:oracle:thin:@127.0.0.1:1521:ORADB</driver-url>

    <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>

    <driver-properties>

      <property name="user" value="lizongbo"/>

      <property name="password" value="password"/>

    </driver-properties>

    <maximum-connection-count>10</maximum-connection-count>

    <house-keeping-test-sql>select 1 from dual</house-keeping-test-sql>

</proxool>

</proxool-config>

 

-------------------------

 

spring里的两种配置：

one: <!-- 与上面的proxool以及web.xml里结合使用-->

 

<bean id="mainDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

    <property name="driverClassName">

    <value>org.logicalcobwebs.proxool.ProxoolDriver</value>

    </property>

    <property name="url">

    <value>proxool.blogdb</value>

    </property>

    </bean>

 

 

two:<!-- 单独的类似dbcp的使用-->

<bean id="mainDataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource" destroy-method="close">

    <property name="driver">

      <value>oracle.jdbc.driver.OracleDriver</value>

    </property>

    <property name="driverUrl">

      <value>jdbc:oracle:thin:lizongbo/password@127.0.0.1:1521:ORADB</value>    

<!--用户名和密码写在一起才调用成功,不然会报调用无效的参数 错误，下面设置用户名和密码的property好像是无效的，其它数据库也是这样

比如mysql的也要把用户名和密码写在url里：<property name="driverUrl" value="jdbc:mysql://localhost:3306/blogdb?user=lizongbo&password=lizongbo" />

-->

    </property>

    <property name="user"> <!-- 必须在这里也设置，但是 proxool却不使用它，或许是个bug-->

      <value>lizongbo</value>

    </property>

    <property name="password"> <!-- 必须在这里也设置，但是 proxool却不使用它，或许是个bug-->

      <value>lizongbo</value>

    </property>

    <property name="alias">

      <value>lizongbo</value>

    </property>

    <property name="houseKeepingSleepTime">

      <value>90000</value>

    </property>

    <property name="prototypeCount">

      <value>5</value>

    </property>

    <property name="maximumConnectionCount">

      <value>100</value>

    </property>

    <property name="minimumConnectionCount">

      <value>10</value>

    </property>

    <property name="trace">

      <value>true</value>

    </property>

    <property name="verbose">

      <value>true</value>

    </property>

</bean>