Basic usage examples of three connection pools for Java's Spring framework

Comments on the three connection pools are as follows:

C3P0 is more resource intensive and may be less efficient.
DBCP is buggy in practice, and in some situations it can generate many empty connections that cannot be released. Hibernate3.0 has dropped support for DBCP.
Proxool has received less negative reviews and is now recommended, and it also provides the ability to instantly monitor the state of the connection pool to detect connection leaks.

Proxool connection pool

<bean id="proxool_dataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">

      <property name="driver-class" value="oracle.jdbc.driver.OracleDriver"/>

      <property name="driver-url" value="jdbc:oracle:thin:@localhost:1521/ssid"/>

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

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

      

      <!--  The test of SQL Execute the statement  -->

      <property name="houseKeepingTestSql" value="select CURRENT_DATE"/>

      <!--  Minimum number of idle connections left (default 2 A)  -->

      <property name="prototypeCount" value="2"/>

      <!-- proxool Automatically reconnoitre the time intervals of each connection state ( ms ), Detect an idle connection and retrieve it immediately , Timeout destruction by default 30 A second)  -->

      <property name="houseKeepingSleepTime" value="30"/>

      <!--  Maximum activity time ( After this time the thread will be kill, The default is 5 minutes ) -->

      <property name="maximumActiveTime" value="300"/>

      <!--  Maximum connection time ( The default is 4 hours ) -->

      <property name="maximumConnectionLifetime" value="30"/>

      <!--  Minimum number of connections (default 2 A)  -->

      <property name="minimumConnectionCount" value="10"/>

      <!--  Maximum number of connections (default 5 A)  -->

      <property name="maximumConnectionCount" value="30"/>

      <!--  And the maximum number of connections  -->

      <property name="simultaneousBuildThrottle" value="10"/>

</bean>

C3P0 connection pool

<beanid="dataSource"class="com.mchange.v2.c3p0.ComboPooledDataSource"destroy-method="close">

    <propertyname="driverClass"value="oracle.jdbc.driver.OracleDriver"/>

    <propertyname="jdbcUrl"value="jdbc:oracle:thin:@localhost:1521/ssid"/>

    <propertyname="user"value="user"/>

    <propertyname="password"value="password"/>

    <propertyname="initialPoolSize"value="10"/>

    <propertyname="minPoolSize"value="10"/>

    <propertyname="maxPoolSize"value="100"/>

</bean>

 

<!--

  C3P0 Through these properties, the data source can be effectively controlled: 

  acquireIncrement : when the connection pool is used up, C3P0 The number of new connections created at once; 

  acquireRetryAttempts : defines the number of repeated attempts to get a new connection from the database after it failed. The default is 30 ; 

  acquireRetryDelay : time between connections, in milliseconds, as default 1000 ; 

  autoCommitOnClose : all uncommitted operations are rolled back by default when the connection is closed. The default is false ; 

  automaticTestTable :  C3P0 I'm going to make one called Test And test it with its own query statement. If this parameter is defined, then the property preferredTestQuery Will be ignored. You can't in this one Test If you do anything on the table, it will be C3P0 The default is null ; 

  breakAfterAcquireFailure : failure to get a connection will cause all threads waiting to get a connection to throw an exception. But the data source is still valid and will be tuned next time   with getConnection() Continue trying to get the connection. If set to true , the data source will declare disconnected and permanently closed after a failed attempt to obtain a connection. The default is  false ; 

  checkoutTimeout : called by the client when the connection pool is used up getConnection() Wait to get a new connection after the timeout is thrown SQLException , such as a set 0 Wait indefinitely. In milliseconds, by default 0 ; 

  connectionTesterClassName : by implementing ConnectionTester or QueryConnectionTester To test the connection, set the class name to the fully qualified name. The default is  com.mchange.v2.C3P0.impl.DefaultConnectionTester ; 

  idleConnectionTestPeriod : checks for free connections in all connection pools every few seconds, by default 0 Does not check; 

  initialPoolSize : the number of connections created when initializing minPoolSize with maxPoolSize Between. The default is 3 ; 

  maxIdleTime : maximum idle time. Connections over idle time will be discarded. for 0 Or negative Numbers never go away. The default is 0 ; 

  maxPoolSize : the maximum number of connections left in the connection pool. The default is 15 ; 

  maxStatements : JDBC To control the loading in the data source PreparedStatement The number. But because of the pre-cache Statement Belong to a single Connection Instead of the entire connection pool. So setting this parameter needs to consider many factors, if maxStatements with  maxStatementsPerConnection Are all 0 , the cache is turned off. The default is 0 ; 

  maxStatementsPerConnection : the maximum cache a single connection has in the connection pool Statement The number. The default is 0 ; 

  numHelperThreads : C3P0 It's asynchronous, it's slow JDBC Operations are completed by the help process. Extending these operations can effectively improve performance by enabling multiple operations to be executed simultaneously using multiple threads. The default is 3 ; 

  preferredTestQuery : defines the test statements that all connection tests execute. In the case of connection testing this parameter can significantly improve the testing speed. The test table must exist at the time of the initial data source. The default is null ; 

  propertyCycle : the maximum number of seconds the user can wait before modifying the system configuration parameters. The default is 300 ; 

  testConnectionOnCheckout : use it only when you need it because it is expensive. If set to true So in each of the connection It will be validated when submitted. It is recommended to use idleConnectionTestPeriod or automaticTestTable

 Methods to improve the performance of connection testing. The default is false ; 

  testConnectionOnCheckin : if set as true Then the validity of the connection will be verified when the connection is obtained. The default is false . 

 -->

DBCP connection pool

<beanid="dbcp_dataSource"class="org.apache.commons.dbcp.BasicDataSource">

    <propertyname="driverClassName"value="oracle.jdbc.driver.OracleDriver"></property>

    <propertyname="url"value="jdbc:oracle:thin:@localhost:1521/ssid"></property>

    <propertyname="username"value="user"></property>

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

    <propertyname="initialSize"value="3"></property>

    <propertyname="maxActive"value="50"></property>

    <propertyname="maxIdle"value="20"></property>

    <propertyname="minIdle"value="5"></property>

    <propertyname="maxWait"value="10"></property>

  </bean>

 

<!--

    DBCP Data source property description 

    initialSize : number of initial connections when connection pool is initialized 

    maxActive : maximum value of connection pool 

    maxIdle : maximum idle value 

    minIdle : minimum idle value 

    maxWait : maximum connection setup wait time 

   -->