C3P0 How to configure connection pooling

The configuration of C3P0 is divided into three types, namely
1.setters setting individual configuration items individually
2. A c3p0.properties file is provided under the Classpath
3. A c3p0-config.xml file is provided under the Classpath

1.setters setting individual configuration items individually
This is the most tedious way, generally in the form of:

Properties Props = new properties ();
InputStream in = ConnectionManager.class.getResourceAsStream ("/c3p0.properties");
Props.load (in);
In.close ();

Combopooleddatasource CPDs = new Combopooleddatasource ();
Cpds.setdriverclass (Props.getproperty ("Driverclass"));
Cpds.setjdbcurl (Props.getproperty ("Jdbcurl"));
Cpds.setuser (Props.getproperty ("user"));
Cpds.setpassword (Props.getproperty ("password"));
Because it is cumbersome, so it is not suitable for adoption, so the document provides another way.

2. A c3p0.properties file is provided under the Classpath
The name of the file must be c3p0.properties, with the format of the configuration item:

C3p0.driverclass=com.mysql.jdbc.driver
C3p0.jdbcurl=jdbc:mysql://localhost:3306/jdbc
C3p0.user=root
C3p0.password=java
The above only provides the most basic configuration items, the other configuration items refer to the document configuration, remember is c3p0. The name of the property followed by IS, the last way to initialize the data source is as simple as this:
private static Combopooleddatasource ds = new Combopooleddatasource ();
public static Connection getconnection () {
try {
return Ds.getconnection ();
} catch (SQLException e) {
throw new RuntimeException (e);
}
}
3. A c3p0-config.xml file is provided under the Classpath
This method is used in the same way as the second, but with more advantages
(1). More intuitive, very similar to hibernate and spring configuration
(2). Can serve multiple data sources, provide default-config and named-config two configuration methods
Here is a configuration template:
<c3p0-config>
<default-config>
<property name= "User" >root</property>
<property name= "Password" >java</property>
<property name= "Driverclass" >com.mysql.jdbc.Driver</property>
<property name= "Jdbcurl" >jdbc:mysql://localhost:3306/jdbc</property>
<property name= "Initialpoolsize" >10</property>
<property name= "MaxIdleTime" >30</property>
<property name= "Maxpoolsize" >100</property>
<property name= "Minpoolsize" >10</property>
</default-config>

<named-config name= "MYAPP" >
<property name= "User" >root</property>
<property name= "Password" >java</property>
<property name= "Driverclass" >com.mysql.jdbc.Driver</property>
<property name= "Jdbcurl" >jdbc:mysql://localhost:3306/jdbc</property>

<property name= "Initialpoolsize" >10</property>
<property name= "MaxIdleTime" >30</property>
<property name= "Maxpoolsize" >100</property>
<property name= "Minpoolsize" >10</property>
</named-config>
</c3p0-config>
If you want to use Default-config to initialize the data source in the same way as the second one, if you want to use Named-config to configure the initialization data source, you can use a Combopooleddatasource constructor with parameters.
private static Combopooleddatasource ds = new Combopooleddatasource ("myApp");
Here's a look at the understanding of the C3P0 configuration from the documentation and on-line learning (User,password,driverclass,jdbcurl not to say necessary)
1. Basic Configuration Items

Acquireincrement
Default:3
Connection pooling the number of new database connections created one time when no idle connections are available

Initialpoolsize
Default:3
Number of connections created when connection pooling is initialized

Maxpoolsize
Default:15
The maximum number of connections that are in the connection pool, and if a new connection is made, the total number of connections will exceed this value and no new connections will be obtained, but wait for
Other connections are released, so this value may be designed to be very large

MaxIdleTime
default:0 Unit S
Maximum idle time for the connection, and if a database connection has not been used at this time, the connection will be disconnected
If 0, the connection will never be disconnected

Minpoolsize
Default:3
The minimum number of connections maintained by the connection pool, followed by the maxidletimeexcessconnections used to mitigate the load on the connection pool
2. Manage connection pool size and the lifetime of the connection

Maxconnectionage
default:0 Unit S
Configures the lifetime of the connection to be dropped by the connection pool automatically when the connection exceeds this time. Of course, the connection being used is not immediately broken, but waiting for
It close again disconnects. When configured to 0, the lifetime of the connection is not limited.

Maxidletimeexcessconnections
default:0 Unit S
This configuration is primarily intended to reduce the load on the connection pool, such as connections in the pool because a large number of data connections are created because of the peak data access
However, the number of database connections required in the latter time period is minimal, so there is absolutely no need for the connection pool to maintain so many connections, so it is necessary to
Disconnect drops some connections to relieve the load and must be less than maxidletime. If the configuration is not 0, the number of connections in the connection pool will be kept to minpoolsize.
The 0 is not processed.
MaxIdleTime can also be attributed to this category, which has been written in front.

3. Configure the connection test: Because the connection pool of the database connection is likely to maintain a few hours of connection, it is likely because the database server problems, network problems and so on caused the actual connection is invalid, but the connection pool inside the connection is still valid, if you get a connection at this time will certainly occur an exception, It is therefore necessary to verify the validity of the connection by testing the connection.
The first three items below are used to configure how the connection is tested, and the last three configurations are timed to test the connection.

Automatictesttable
Default:null
A way to configure a test connection. Configure a table name, the connection pool creates an empty table based on this table name,
and test the database connection on this empty table with your own test SQL statement
This table can only be used by C3P0, the user cannot operate, and the user-configured preferredtestquery will be ignored.

Preferredtestquery
Default:null
Another way to configure a test connection. With the above automatictesttable can only choose one.
If you want to test the connection with it, do not set it to null, otherwise the test process will be time-consuming and ensure that the tables in the SQL statement must exist in the database.

Connectiontesterclassname
Default:com.mchange.v2.c3p0.impl.DefaultConnectionTester
The class that the connection pool uses to support automatictesttable and preferredtestquery tests must be a full class name, as the default is,
You can customize your own test methods by implementing Unifiedconnectiontester interfaces or inheriting abstractconnectiontester.
Idleconnectiontestperiod
default:0
Used to configure the time interval for testing idle connections. The test method is also one of the two above, can be used to solve the problem of MySQL8 hours disconnection. because it
Ensure that the connection pool will test the idle connection at a certain time to ensure that a valid idle connection can access the database at a certain time, and the MySQL
8 hours without session of the state break. The 0 is not tested.

Testconnectiononcheckin
Default:false
If true, the validity of the connection is tested at close. In order to improve the test performance, it can be used in conjunction with Idleconnectiontestperiod
Configuring Preferredtestquery or automatictesttable can also speed up testing.
Testconnectiononcheckout
Default:false
High performance consumption. If true, it will be tested every time getconnection, in order to improve performance,
Can be used in conjunction with the Idleconnectiontestperiod,
Configuring Preferredtestquery or automatictesttable can also speed up testing.
4. Configuring the PreparedStatement Cache
Maxstatements
default:0
The total number of preparedstatement that the connection pool is cached for the data source. Since PreparedStatement belong to a single connection,
This number should be calculated based on the average number of connections in the application multiplied by the average preparedstatement of each connection. is not cached when it is 0,
The maxstatementsperconnection configuration is not valid at the same time.
Maxstatementsperconnection
default:0
The connection pool is the number of PreparedStatement for a single connection cache of the data source, which is more meaningful than maxstatements because
The service object It caches is a single data connection, and if set well, it is certainly possible to improve performance. is not cached at 0.
5. Re-connect the relevant configuration
Acquireretryattempts
Default:30
Number of retries when connection pooling fails to obtain a new connection, and infinite retry if less than or equal to 0 until the connection succeeds
Acquireretrydelay
default:1000 Unit ms
The interval between connection pooling when a new connection is obtained
Breakafteracquirefailure
Default:false
If true, the data source is automatically closed when connection acquisition fails, unless the application is restarted. So generally not.
Personally feel that the above three is not necessary to change, but you can configure the Acquireretrydelay to a shorter
6. Customizing the lifecycle of Management connection
Connectioncustomizerclassname
Default:null
Used to customize the management of connection, such as setting the connection isolation level when connection acquire, or
Connection is discarded when the resource is closed, you can inherit a abstractconnectioncustomizer to achieve the relevant
method, use the full class name when configuring. A bit like the role of a listener.
For example:
Import java.sql.Connection;
Import Com.mchange.v2.c3p0.AbstractConnectionCustomizer;
public class Connectioncustomizer extends abstractconnectioncustomizer{
@Override
public void Onacquire (Connection C, String Parentdatasourceidentitytoken)
Throws Exception {
System.out.println ("Acquire:" + C);
}
@Override
public void Oncheckin (Connection C, String Parentdatasourceidentitytoken)
Throws Exception {
System.out.println ("Checkin:" + C);
}
@Override
public void Oncheckout (Connection C, String Parentdatasourceidentitytoken)
Throws Exception {
SYSTEM.OUT.PRINTLN ("Checkout:" + C);
}
@Override
public void OnDestroy (Connection C, String Parentdatasourceidentitytoken)
Throws Exception {
System.out.println ("Destroy:" + C);
}
}
<property name= "Connectioncustomizerclassname" >liuyun.zhuge.db.ConnectionCustomizer</property>
7. Configuration of uncommitted transactions
Autocommitonclose
Default:false
Whether the connection pool commits transactions automatically when the database connection is reclaimed
If False, the uncommitted transaction is rolled back
If true, the transaction is automatically committed
Forceignoreunresolvedtransactions
Default:false
This configuration is strongly not recommended for true.
In general, of course, the transaction is closed by itself, why should the connection pool to deal with this careless problem?
8. Configure Debug and Recycle connection
Unreturnedconnectiontimeout
default:0 Unit S
When 0 is required, all connection must be closed in the application. If not 0, it is forced to recycle after a set time has arrived
Connection must be carefully set to ensure that all database operations are completed before recycling. This limitation reduces connection not shutting down
The situation is not very applicable. The connection is not recycled for 0, even though it is not closed.
Debugunreturnedconnectionstacktraces
Default:false
If True and Unreturnedconnectiontimeout is set to a value greater than 0, when all the connections are getconnection out
When the unreturnedconnectiontimeout time is up, the stack information is printed. Only applicable in debug mode, because
Printing stack information slows down the speed of getconnection
As with the seventh item, the connection is closed, so do not let the connection pool be reclaimed by unreturnedconnectiontimeout.
9. Other configuration items: Because some configuration items are almost not necessary to their own configuration, use the default value is good, so no more write it out
Checkouttimeout
default:0
Configures the wait time for application getconnection when all connection pooling connections are exhausted. 0 waits indefinitely until another connection is released
or create a new connection, not 0 when the time is up, if you still don't get the connection, it throws a SqlException

C3p0 How connection pooling is configured