C3P0-----Open Source JDBC Connection pool usage and configuration

First, a connection, introduce the difference between the various connection pools and performance differences (BONECP newer, higher performance) connection pool difference

C3P0 is an open source JDBC connection pool that can be C3P0-JDBC3 Connection and Statement if the English is good pooling

The version used here is c3p0-0.9.5.2:

The required JDK is 1.6 or above
the jar packages that need to be imported are: C3p0-0.9.5.2.jar and Mchange-commons-java-0.2.11.jar  can be downloaded from maven, Post-Download the Web-inf/lib under the project (as long as the Classpath path is available)

The principle of the connection pool to borrow a picture:

Without the connection pool, the DAO layer (data connection object) connects directly to the data source (lowest level), performs a connection – closes the operation, so that the DAO performs a connection once every crud is performed-Performs a sql– close connection, If the business needs to be frequently crud, the connection will be shut down frequently, resulting in a waste of performance.

With a pool connection, the project will create a connection pool and get a connection to the data source (initial connection, maximum connection, minimum connection can be set), so that the DAO needs to manipulate crud, there is no need to directly access the data source, directly with the pool, because the pool is in memory, This way the DAO gets the same speed with the pool each time, and the close connection is turned off and the connection pool is closed, that is, the connection and shutdown are operation connection pooling and do not directly manipulate the data source. (Access to the hard disk is much faster than memory access).

Since JDBC connections do not need to be duplicated in each of these boilerplate code, the creation and connection of the connection pool can be written as a tool-class utils.

Package com.dataSource.c3p0;
Import java.beans.PropertyVetoException;
Import java.io.IOException;
Import java.sql.Connection;

Import java.sql.SQLException;

Import Com.mchange.v2.c3p0.ComboPooledDataSource;
    public class DataSource {private static DataSource DataSource;

    Private Combopooleddatasource CPDs; Private DataSource () throws IOException, SQLException, propertyvetoexception {//When this class is constructed, the C3P0 database connection pool is created CPDs = new Com
        Bopooleddatasource (); Cpds.setdriverclass ("Com.mysql.jdbc.Driver");
        Loads the JDBC driver cpds.setjdbcurl ("Jdbc:mysql://localhost/test");
        Cpds.setuser ("root");

        Cpds.setpassword ("root"); The settings below are optional-c3p0 can work with defaults cpds.setminpoolsize (5); Minimum connection number cpds.setacquireincrement (5); Increase the number of cpds.setmaxpoolsize (20) When the connection is insufficient; Maximum number of connections} public static DataSource getinstance () throws IOException, SQLException, propertyvetoexception {//Get single Example if (dAtasource = = null) {DataSource = new DataSource ();
        return datasource;
        else {return datasource;
    } public Connection getconnection () throws SQLException {return this.cpds.getConnection (); }

}

To use a pool connection at the DAO Layer:

try {
            connection = Datasource.getinstance (). getconnection ();
            statement = Connection.createstatement ();
            ResultSet = Statement.executequery ("SELECT * from Employee");
             while (Resultset.next ()) {
                 System.out.println ("EmployeeID:" + resultset.getstring ("EmployeeID"));
                 System.out.println ("EmployeeName:" + resultset.getstring ("EmployeeName"));
             }
        catch (SQLException e) {
            e.printstacktrace ();
        } finally {
            if (resultSet!= null) try {resultset.close ();} C Atch (SQLException e) {e.printstacktrace ();}
            if (statement!= null) try {statement.close ();} catch (SQLException e) {e.printstacktrace ();}
            if (connection!= null) try {connection.close ();} catch (SQLException e) {e.printstacktrace ();}
        }