Poor memory: 22-JAVA database connection pool C3P0, 22-javac3p0

Poor memory: 22-JAVA database connection pool C3P0, 22-javac3p0

C3P0 is an open-source JDBC connection pool that implements data source and JNDI binding and supports JDBC3 specifications and standard extensions of JDBC2. Currently, open-source projects using it include Hibernate and Spring. C3P0 data sources are used in many projects.

1. Difference Between c3p0 and dbcp

Dbcp does not automatically recycle idle connections

C3p0 automatically recycles idle connections

C3p0 supports more database connection pool options.

 

2. Import relevant jar packages

C3p0-0.9.0.jar

3. Detailed description of C3P0 Parameters

Datasource. c3p0. acquireIncrement = 10 when connections in the connection pool are used up, the number of new connections created at one time by C3P0;

Datasource. c3p0. minPoolSize = 50 the minimum number of connections retained in the connection pool. The default value is 15.

Datasource. c3p0. maxPoolSize = 400 maximum number of connections retained in the connection pool. The default value is 15;

Datasource. c3p0. initialPoolSize = 50 the number of connections created during initialization. The value should be between minPoolSize and maxPoolSize. The default value is 3;

Datasource. c3p0. maxIdleTime = 1800 maximum idle time. connections that exceed the idle time will be discarded. 0 or negative. The default value is 0;

Datasource. c3p0. acquireRetryAttempts = 100 defines the number of times that a new connection fails to be retrieved from the database. The default value is 30;

Datasource. c3p0. acquireRetryDelay = 20 the interval between two connections, in milliseconds. The default value is 1000;

Datasource. c3p0. debugUnreturnedConnectionStackTraces = true

Datasource. c3p0. maxStatements = 0JDBC standard parameter, used to control the number of PreparedStatement loaded in the data source. However, the pre-Cache Statement belongs to a single Connection rather than the entire Connection pool. Therefore, you need to consider multiple factors when setting this parameter. If both maxStatements and maxStatementsPerConnection are 0, the cache is disabled. The default value is 0;

Datasource. c3p0. idleConnectionTestPeriod = 1800 check the idle connections in all connection pools every second. The default value is 0, indicating no check;

Datasource. c3p0. breakAfterAcquireFailure = true if the connection fails to be obtained, an exception will be thrown by all threads waiting for the connection to be obtained. However, the data source is still valid, and the next call to getConnection () will continue to try to obtain the connection. If it is set to true, the data source will be declared disconnected and permanently closed after the connection fails to be obtained. The default value is false;

Datasource. c3p0. testConnectionOnCheckout = false because of high performance consumption, please use it only when necessary. If it is set to true, the validity of each connection is verified when it is submitted. We recommend that you use idleConnectionTestPeriod or automaticTestTable

Datasource. c3p0. autoCommitOnClose = true when the connection is closed, all uncommitted operations are rolled back by default. The default value is false;

Datasource. c3p0. maxStatementsPerConnection = 100 maximum number of cached Statement owned by a single connection pool. The default value is 0;

 

4. Use C3PO to implement source code of the Database Connection Pool

 

Package com. db;

 

Import java. SQL. Connection;

Import java. SQL. PreparedStatement;

Import java. SQL. ResultSet;

Import java. SQL. SQLException;

Import java. SQL. Statement;

 

Import com. mchange. v2.c3p0. ComboPooledDataSource;

/**

* Using the C3P0 open-source tool, you can easily create a database connection pool.

* @ Author fan fangming

*/

Public class EasyC3p0 {

Privatestatic ComboPooledDataSource ds = null;

// Create a database connection pool in a static code block

Static {

Try {

// Create a C3P0 database connection pool using code

Ds = new ComboPooledDataSource ();

Ds. setDriverClass ("oracle. jdbc. driver. OracleDriver ");

Ds. setJdbcUrl ("jdbc: oracle: thin: @ 140.207.38.242: 1521: O2O ");

Ds. setUser ("bm114_test ");

Ds. setPassword ("bm114_test ");

Ds. setInitialPoolSize (10 );

Ds. setMinPoolSize (5 );

Ds. setMaxPoolSize (20 );

} Catch (Exception e ){

E. printStackTrace ();

}

}

 

// Obtain the database connection from the data source

Publicstatic Connection getConnection () throws SQLException {

Returnds. getConnection ();

}

 

// Close the database connection

Publicstatic void close (Connection conn, Statement st, ResultSet rs ){

If (rs! = Null ){

Try {

// Close the ResultSet object that stores the query results

Rs. close ();

} Catch (Exception e ){

E. printStackTrace ();

}

Rs = null;

}

If (st! = Null ){

Try {

// Close the Statement object that executes the SQL command

St. close ();

} Catch (Exception e ){

E. printStackTrace ();

}

}

 

If (conn! = Null ){

Try {

// Return the Connection object to the Database Connection Pool

Conn. close ();

} Catch (Exception e ){

E. printStackTrace ();

}

}

}

 

Publicstatic void main (String [] args ){

Connectioncon = null;

PreparedStatementst = null;

ResultSetrs = null;

Try {

// Obtain the database connection

Con = EasyC3p0. getConnection ();

Stringsql = "select sysdate from dual ";

PreparedStatementps = con. prepareStatement (SQL );

Rs = ps.exe cuteQuery ();

 

While (rs. next ()){

Stringvalue = rs. getString ("sysdate ");

System. out. println (value + ", database connection successful ");

}

} Catch (Exception e ){

E. printStackTrace ();

} Finally {

// Release resources

EasyC3p0. close (con, st, rs );

}

}

}

5. Running result

16:16:27. 0, database connection successful