1 Introduction

    Database connection is a key limited and expensive resource, which is particularly prominent in multi-user web applications. The management of database connections can significantly affect the scalability and robustness of the entire application, and affect the performance indicators of the program. The database connection pool is proposed to solve this problem.
    The database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection rather than re-establish it. It also releases database connections with idle time exceeding the maximum idle time to avoid missing database connections caused by not releasing database connections. This technology can significantly improve the performance of database operation.

2 Connection pool types

Finally, this article provides an example to download!!!!

• C3P0
• Druid
• JNDI
• DBCP
• proxool
• BoneCP
  
  3 code example
  I used the maven project spring+mybatis+mysql
  maven->poi.xml

<dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-webmvc</artifactId>
        <version>4.0.2.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>4.3.4.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.4.1</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis-spring</artifactId>
        <version>1.3.0</version>
    </dependency>
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>6.0.6</version>
    </dependency>
    <!-- druid Database connection pool -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.3</version>
    </dependency>
    <!-- c3p0 Database connection pool -->
    <dependency>
        <groupId>c3p0</groupId>
        <artifactId>c3p0</artifactId>
        <version>0.9.1.2</version>
    </dependency>
    <!-- apache dbcp Database connection pool -->
    <dependency>
        <groupId>commons-dbcp</groupId>
        <artifactId>commons-dbcp</artifactId>
        <version>1.4</version>
    </dependency>
    <!-- jndi -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-mock</artifactId>
        <version>2.0.8</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>simple-jndi</groupId>
        <artifactId>simple-jndi</artifactId>
        <version>0.11.4.1</version>
        <scope>test</scope>
    </dependency>
    <!-- proxool -->
    <dependency>
        <groupId>com.cloudhopper.proxool</groupId>
        <artifactId>proxool</artifactId>
        <version>0.9.1</version>
    </dependency>
    <!-- bonecp -->
    <dependency>
      <groupId>com.jolbox</groupId>
      <artifactId>bonecp-spring</artifactId>
      <version>0.8.0.RELEASE</version>
    </dependency>
</dependencies>

New TestDao, Test_Mapper.xml, Test

package com.zhs.test.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

@Repository
public interface TestDao {

    List<com.zhs.test.empty.Test> getTestList();

}

package com.zhs.test.empty;

public class Test {

    private String name;
    private Integer age;
    private String sex;

    public Test(String name, Integer age, String sex) {
        super();
        this.name = name;
        this.age = age;
        this.sex = sex;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    @Override
    public String toString() {
        return "Test [name=" + name + ", age=" + age + ", sex=" + sex + "]";
    }




}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--Namespace should be the package name of the corresponding interface+Interface name -->
<mapper namespace="com.zhs.test.dao.TestDao">

    <resultMap type="com.zhs.test.empty.Test" id="BaseResultMap">
        <constructor>
            <arg column="name" jdbcType="VARCHAR" javaType="java.lang.String"/>
            <arg column="age" jdbcType="INTEGER" javaType="java.lang.Integer"/>
            <arg column="sex" jdbcType="VARCHAR" javaType="java.lang.String"/>
        </constructor>
    </resultMap>
    <select id="getTestList" resultMap="BaseResultMap">
        select name,age,sex from test
    </select>
</mapper>

New Test Table in Database

• C3P0
  c3p0 configuration file and test sample
  New Application Context-c3p0.xml
  Detailed explanations have been given in all configuration files, but not all.

<! - Global context class configuration - >
        <mvc:annotation-driven />
        <context:component-scan base-package="com.zhs.test"></context:component-scan>

        <! - C3P0 database connection pool - >
        <bean id="dataSource"  class="com.mchange.v2.c3p0.ComboPooledDataSource"  destroy-method="close">  
            <property name="driverClass" value="com.mysql.jdbc.Driver" /> 
            <property name="jdbcUrl" value="jdbc:mysql://139.159.219.106:3306/ch9188?useUnicode=true&amp;characterEncoding=utf8"/>    
            <property name="user" value="root"/>  
            <property name="password" value="zhs123147"/> 
            <! - Minimum number of connections reserved in connection pool. >  
            <property name="minPoolSize" value="5"/> 
             <! -- The maximum number of connections retained in the connection pool. Defau lt: 15 - > 
            <property name="maxPoolSize" value="50"/> 
            <! -- The number of connections obtained at initialization should be between minPoolSize and maxPoolSize. Defau lt: 3 - >  
            <property name="initialPoolSize" value="10"/> 
            <! - Maximum idle time, if not used within 60 seconds, the connection is discarded. If it is zero, it will never be discarded. Defau lt: 0 - >  
            <property name="maxIdleTime" value="60"/> 
            <! - The number of connections that c3p0 acquires simultaneously at one time when the connection in the connection pool is exhausted. Default: 3 - >  
            <property name="acquireIncrement" value="5"/> 
            <! -- The standard parameter of JDBC to control the number of reparedStatements loaded in the data source. But because of pre-cached statements  
      It belongs to a single connection rather than the entire connection pool. So setting this parameter needs to consider many factors.  
      If both maxStatements and maxStatements PerConnection are 0, the cache is closed. Default: 0 - >  
            <property name="maxStatements" value="0"/> 
            <! - Check free connections in all connection pools every 60 seconds. Defau lt: 0 - > 
            <property name="idleConnectionTestPeriod" value="60"/> 
            <! -- Defines the number of repeated attempts after failing to retrieve a new connection from the database. Defau lt: 30 - >  
            <property name="acquireRetryAttempts" value="30"/> 
             <! -- Failure to retrieve connections causes all threads waiting for connection pools to retrieve connections to throw exceptions. But data sources are still valid  
              Keep it and continue trying to get the connection the next time getConnection() is called. If set to true, try  
              The data source will declare that it has been disconnected and closed permanently after the access fails. Default: false - >  
            <property name="breakAfterAcquireFailure" value="true"/> 
            <! - Please use it only when you need it because of its high performance consumption. If set to true, then submitted at each connection  
              Its effectiveness will be checked at all times. It is recommended to use idle Connection TestPeriod or automaticTestTable  
              And other methods to improve the performance of connection testing. Default: false - >  
            <property name="testConnectionOnCheckout" value="false"/>   
        </bean>  
        <! - Configure sqlSessionFactory - >
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!--            <property name="configLocation" value="classpath:mybatis/mybatis-config.xml"/> -->
            <property name="dataSource" ref="dataSource" />
            <property name="mapperLocations">
                <list>
                    <value>classpath:mybatis/sqlmap/*Mapper.xml</value>
                </list>
            </property>
        </bean>
        <! - Automatically inject beans through scanning mode
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.zhs.test" />
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        </bean>

New TestC3P0.java test class

package com.zhs.test;

import java.util.List;

import org.springframework.context.support.GenericXmlApplicationContext;

import com.zhs.test.dao.TestDao;

public class TestC3p0 {

    public static void main(String[] args) {
        String path = "classpath:mybatis/applicationContext-c3p0.xml";
        GenericXmlApplicationContext context = new GenericXmlApplicationContext(path);
        context.start();

        TestDao testDao = context.getBean(TestDao.class);
        List<com.zhs.test.empty.Test> TestList = testDao.getTestList();
        for(com.zhs.test.empty.Test test:TestList){
            System.out.println(test);
        }


    }

}

• Druid
  Druid configuration file and test sample
  New application Context-druid.xml
  Detailed explanations have been given in all configuration files, but not all.
  New application Context-druid.xml

<!-- Overall situation context Class configuration -->
        <mvc:annotation-driven />
        <context:component-scan base-package="com.zhs.test"></context:component-scan>

        <!-- Ali druid Database connection pool -->
        <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
              init-method="init" destroy-method="close">
            <property name="driverClassName" value="com.mysql.jdbc.Driver" />
            <!-- 139.159.219.106 -->
            <property name="url" value="jdbc:mysql://139.159.219.106:3306/ch9188?useUnicode=true&amp;characterEncoding=utf8" />
            <property name="username" value="root" />
            <property name="password" value="zhs123147" />
            <!-- Configuration initialization size, minimum, maximum -->
            <property name="initialSize" value="1" />
            <property name="minIdle" value="1" />
            <property name="maxActive" value="10" />

            <!-- Configuration to get the connection waiting timeout time -->
            <property name="maxWait" value="10000" />

            <!-- How often is the configuration interval detected to detect idle connections that need to be closed in milliseconds? -->
            <property name="timeBetweenEvictionRunsMillis" value="60000" />

            <!-- Configure the minimum lifetime of a connection in the pool in milliseconds -->
            <property name="minEvictableIdleTimeMillis" value="300000" />

            <property name="testWhileIdle" value="true" />

            <!-- It is recommended that the configuration be configured TRUE，Prevent fetched connections from being unavailable -->
            <property name="testOnBorrow" value="true" />
            <property name="testOnReturn" value="false" />

            <!-- open PSCache，And specify on each connection PSCache Size -->
            <property name="poolPreparedStatements" value="true" />
            <property name="maxPoolPreparedStatementPerConnectionSize"
                      value="20" />

            <!-- Here, we configure the submission mode, which by default is TRUE，Configuration is not required -->

            <property name="defaultAutoCommit" value="true" />

            <!-- Verify that the connection is valid SQL，Different data configurations are different -->
            <property name="validationQuery" value="select 1 " />
            <property name="filters" value="stat,log4j" />
        </bean>
        <!-- To configure sqlSessionFactory -->
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!--            <property name="configLocation" value="classpath:mybatis/mybatis-config.xml"/> -->
            <property name="dataSource" ref="dataSource" />
            <property name="mapperLocations">
                <list>
                    <value>classpath:mybatis/sqlmap/*Mapper.xml</value>
                </list>
            </property>
        </bean>
        <!-- Scanning mode,Automatic injection bean -->
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.zhs.test" />
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        </bean>

New test class

package com.zhs.test;

import java.util.List;

import org.springframework.context.support.GenericXmlApplicationContext;

import com.zhs.test.dao.TestDao;

public class TestDruid {

    public static void main(String[] args) {
        String path = "classpath:mybatis/applicationContext-datasource.xml";
        GenericXmlApplicationContext context = new GenericXmlApplicationContext(path);
        context.start();

        TestDao testDao = context.getBean(TestDao.class);
        List<com.zhs.test.empty.Test> TestList = testDao.getTestList();
        for(com.zhs.test.empty.Test test:TestList){
            System.out.println(test);
        }


    }

}

• JNDI
  JNDI this example because of my own limited technology, debugging has failed, interested friends can download and debug their own, the source code at the end!
• DBCP
  DBCP configuration file and test sample
  Detailed explanations have been given in all configuration files, but not all.
  New application Context-dbcp.xml

<!-- Overall situation context Class configuration -->
        <mvc:annotation-driven />
        <context:component-scan base-package="com.zhs.test"></context:component-scan>

        <!-- dbcp Database connection pool -->
        <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
            <!-- Load driver -->
            <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
            <!-- Name of database -->
            <property name="url" value="jdbc:mysql://139.159.219.106:3306/ch9188?useUnicode=true&amp;characterEncoding=utf8"></property>
            <!-- User name password -->
            <property name="username" value="root"></property>
            <property name="password" value="zhs123147"></property>
            <!-- maximum connection -->
            <property name="maxActive" value="10000"></property>
            <!-- Maximum idleness -->
            <property name="maxIdle" value="0"></property>
            <!-- Maximum number of waiting seconds in milliseconds. Error messages will be reported when the waiting time exceeds. -->
            <property name="maxWait" value="1000"></property>
            <!-- Autocommit by default, which is related to transactions. true，Every execution is committed, so there are no transactions -->
            <property name="defaultAutoCommit" value="false"></property>
        </bean>
        <!-- To configure sqlSessionFactory -->
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!--            <property name="configLocation" value="classpath:mybatis/mybatis-config.xml"/> -->
            <property name="dataSource" ref="dataSource" />
            <property name="mapperLocations">
                <list>
                    <value>classpath:mybatis/sqlmap/*Mapper.xml</value>
                </list>
            </property>
        </bean>
        <!-- Scanning mode,Automatic injection bean -->
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.zhs.test" />
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        </bean>

New test class

package com.zhs.test;

import java.util.List;

import org.springframework.context.support.GenericXmlApplicationContext;

import com.zhs.test.dao.TestDao;

public class TestDBCP {

    public static void main(String[] args) {
        String path = "classpath:mybatis/applicationContext-dbcp.xml";
        GenericXmlApplicationContext context = new GenericXmlApplicationContext(path);
        context.start();

        TestDao testDao = context.getBean(TestDao.class);
        List<com.zhs.test.empty.Test> TestList = testDao.getTestList();
        for(com.zhs.test.empty.Test test:TestList){
            System.out.println(test);
        }


    }

}

• proxool
  proxool configuration file and test sample
  New application Context-proxool.xml

<!-- Overall situation context Class configuration -->
        <mvc:annotation-driven />
        <context:component-scan base-package="com.zhs.test"></context:component-scan>

        <!--springjdbc Database connection pool -->
        <bean id="dataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource">
            <property name="driver">
                <value>com.mysql.jdbc.Driver</value>
            </property>
            <property name="driverUrl">
                <value>jdbc:mysql://139.159.219.106:3306/ch9188?useUnicode=true&amp;characterEncoding=utf8</value>
            </property>
            <property name="user" value="root" />
            <property name="password" value="zhs123147" />
            <property name="alias" value="Pool_dbname" />
<!--            <property name="houseKeepingSleepTime" value="90000" />
 -->            <property name="prototypeCount" value="0" />
            <property name="maximumConnectionCount" value="50" />
            <property name="minimumConnectionCount" value="2" />
            <property name="simultaneousBuildThrottle" value="50" />
            <property name="maximumConnectionLifetime" value="14400000" />
            <property name="houseKeepingTestSql" value="select CURRENT_DATE" />
        </bean>
        <!-- To configure sqlSessionFactory -->
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!--            <property name="configLocation" value="classpath:mybatis/mybatis-config.xml"/> -->
            <property name="dataSource" ref="dataSource" />
            <property name="mapperLocations">
                <list>
                    <value>classpath:mybatis/sqlmap/*Mapper.xml</value>
                </list>
            </property>
        </bean>
        <!-- Scanning mode,Automatic injection bean -->
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.zhs.test" />
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        </bean>

Detailed explanations have been given in all configuration files, but not all.
New test class

package com.zhs.test;

import java.util.List;

import org.logicalcobwebs.proxool.ProxoolDataSource;
import org.springframework.context.support.GenericXmlApplicationContext;

import com.zhs.test.dao.TestDao;

public class TestProxool {

    public static void main(String[] args) {
        String path = "classpath:mybatis/applicationContext-proxool.xml";
        GenericXmlApplicationContext context = new GenericXmlApplicationContext(path);
        context.start();

        TestDao testDao = context.getBean(TestDao.class);
        List<com.zhs.test.empty.Test> TestList = testDao.getTestList();
        for(com.zhs.test.empty.Test test:TestList){
            System.out.println(test);
        }


    }

}

• BoneCP
  Detailed explanations have been given in all configuration files, but not all.
  JNDI configuration file and test sample
  New application Context-bonecp.xml

<! - Global context class configuration - >
        <mvc:annotation-driven />
        <context:component-scan base-package="com.zhs.test"></context:component-scan>

        <! - Spring BoneCP Data Source Configuration - >
        <bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
            <! - Database Driver - >
            <property name="driverClass" value="com.mysql.jdbc.Driver" />
            <! - jdbcUrl - > corresponding driver
            <property name="jdbcUrl" value="jdbc:mysql://139.159.219.106:3306/ch9188?useUnicode=true&amp;characterEncoding=utf8" />
            <! - User name of database - >
            <property name="username" value="root" />
            <! - The password of the database - >
            <property name="password" value="zhs123147" />
            <! - Check the interval between idle connections in the database connection pool in divisions, defau lt value: 240, set to 0 - > if cancelled.
            <property name="idleConnectionTestPeriod" value="60" />
            <! - Maximum lifetime of unused links in connection pool is divided in units, defau lt value: 60, set to 0 - > if you want to survive forever.
            <property name="idleMaxAge" value="30" />
            <! - Maximum number of connections per partition - >
            <property name="maxConnectionsPerPartition" value="150" />
            <! - The smallest number of connections per partition - >
            <property name="minConnectionsPerPartition" value="5" />
        </bean>
        <! - Configure sqlSessionFactory - >
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!--            <property name="configLocation" value="classpath:mybatis/mybatis-config.xml"/> -->
            <property name="dataSource" ref="dataSource" />
            <property name="mapperLocations">
                <list>
                    <value>classpath:mybatis/sqlmap/*Mapper.xml</value>
                </list>
            </property>
        </bean>
        <! - Automatically inject beans through scanning mode
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.zhs.test" />
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        </bean>

New test class

package com.zhs.test;

import java.util.List;

import org.springframework.context.support.GenericXmlApplicationContext;

import com.zhs.test.dao.TestDao;

public class TestBoneCP {

    public static void main(String[] args) {
        String path = "classpath:mybatis/applicationContext-bonecp.xml";
        GenericXmlApplicationContext context = new GenericXmlApplicationContext(path);
        context.start();

        TestDao testDao = context.getBean(TestDao.class);
        List<com.zhs.test.empty.Test> TestList = testDao.getTestList();
        for(com.zhs.test.empty.Test test:TestList){
            System.out.println(test);
        }


    }

}

4 Summary
Personal opinion, the current commonly used C3P0 in the project when the first start connection problems, need to try several times. If the first click connection of druid is slow for a long time without connection, the monitoring page of druid can be configured in web.xml to use. Code:

 <servlet>
        <!-- sql Column display -->
        <servlet-name>DruidStatView</servlet-name>
        <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
        <init-param>
            <!-- Allow clearance of statistical data -->
            <param-name>resetEnable</param-name>
            <param-value>true</param-value>
        </init-param>
        <init-param>
            <!-- User name -->
            <param-name>loginUsername</param-name>
            <param-value>druid</param-value>
        </init-param>
        <init-param>
            <!-- Password -->
            <param-name>loginPassword</param-name>
            <param-value>druid</param-value>
        </init-param>
    </servlet>

PS: Source download address: http://pan.baidu.com/s/1jIOjoz0