C3P0 Optimization Detail Configuration

<!--c3p0 The number of connections that are fetched at the same time when the connection in the connection pool is exhausted. Default:3--<propert name= "acquireretryattempts" >30</property>

<!--the interval between two connections, in milliseconds. default:1000--<property name= "Acquireretrydelay" >1000</property>

< All uncommitted actions are rolled back by default when the connection is closed!--. Default:false--<property name= "Autocommitonclose" >false</property>

<!--C3P0 will build a blank table named Test and test it with its own query statement. If this parameter is defined, then the attribute preferredtestquery will be ignored. You cannot do anything on this test sheet, it will be used only for C3P0 testing. default:null--> <property name= "automatictesttable" >Test</property>

<!--getting a connection failure will cause any thread that waits for the connection pool to get the connection to throw an exception. However, the data source is still valid and continues to try to get the connection the next time you call Getconnection (). If set to True, the data source will declare broken and permanently shut down after attempting to acquire a connection failure. default:false--> <property name= "Breakafteracquirefailure" >false</property>

<!--when the connection pool is exhausted, the client calls getconnection () to wait for the new connection, and after the timeout, the SQLException is thrown and, if set to 0, waits indefinitely. Unit milliseconds. default:0--<property name= "Checkouttimeout" >100</property>

<!--test the connection by implementing a class of Connectiontester or Queryconnectiontester. The class name needs to be set to the full path. default:com.mchange.v2.c3p0.impl.defaultconnectiontester--> <property name= "ConnectionTesterClassName" > </property>

<!--Specify the path to the C3P0 libraries, if (usually so) you can get it locally without the need to set, default NULL to Default:null--> <property name= " Factoryclasslocation ">null</property>

<!--strongly disrecommended. Setting this to true may leads to subtle and bizarre bugs. Author strongly recommends not to use a property---<property name= "Forceignoreunresolvedtransactions" >false</property>

<!--check for idle connections in all connection pools every 60 seconds. default:0--<property name= "Idleconnectiontestperiod" >60</property>

<!--get three connections when initializing, the value should be between Minpoolsize and Maxpoolsize. Default:3--<property name= "Initialpoolsize" >3</property>

<!--maximum idle time, unused in 60 seconds, the connection is discarded. If 0, it will never be discarded. default:0--<property name= "MaxIdleTime" >60</property>

<!--The maximum number of connections that are kept in the connection pool. Default:15--<property name= "Maxpoolsize" >15</property>

<!--the standard parameters of JDBC to control the number of preparedstatements loaded within the data source. However, because the pre-cached statements belong to a single connection instead of the entire connection pool. So setting this parameter takes into account a variety of factors. If both maxstatements and maxstatementsperconnection are 0, the cache is closed. default:0--> <property name= "Maxstatements" >100</property>

<!--maxstatementsperconnection defines the maximum number of cache statements that a single connection in a connection pool has. default:0--<property name= "Maxstatementsperconnection" ></property>

<!--C3P0 is asynchronous, and slow JDBC operations are done through the help process. Extending these operations can effectively improve performance by implementing multiple operations at the same time through multithreading. default:3--> <property name= "Numhelperthreads" >3</property>

<!--when a user calls getconnection () to make the root user the user who is going to get the connection. Used primarily when connection pooling is connected to a non-c3p0 data source. default:null--> <property name= "Overridedefaultuser" >root</property>

<!--a parameter that corresponds to the Overridedefaultuser parameter. default:null--> <property name= "Overridedefaultpassword" >password</property>
<!--password. default:null--> <property name= "password" ></property>

<!--define test statements that are executed by all connection tests. This one significantly improves the test speed in the case of connection testing. Note: The test table must exist at the time of the initial data source. default:null--> <property name= "preferredtestquery" >select ID from test where id=1</property>

<!--the user to wait up to 300 seconds before modifying the system configuration parameters. default:300--<property name= "Propertycycle" >300</property>

<!--use it only when you need it, due to its high performance consumption. If set to true then the validity of each connection submission is officer. We recommend using methods such as Idleconnectiontestperiod or automatictesttable to improve the performance of your connectivity tests. Default:false--<property name= "Testconnectiononcheckout" >false</property>

<!--If set to true then officer the validity of the connection while the connection is made. Default:false--<property name= "Testconnectiononcheckin" >true</property>

<!--user name. default:null--> <property name= "user" >root</property>

<!--earlier versions of C3P0 use dynamic reflection proxies for JDBC interfaces. This parameter allows the user to revert to the dynamic reflection agent to resolve an unstable failure in the case of a wide range of uses in earlier versions. The newest non-reflection agents are faster and have started to be widely used, so this parameter may not be useful. The original dynamic reflection is now supported at the same time as the new non-reflection agent, but a possible future version may not support the dynamic reflection proxy. default:false--> <property name= "Usestraditionalreflectiveproxies" >false</property>

<!--the time to wait for a new connection after the client calls Getconnection () after the connection pool has run out, and the timeout is SQLException, as set to 0 waits indefinitely. Unit milliseconds. default:0--<property name= "Checkouttimeout" >100</property>

<!--C3P0 will build a blank table named Test and test it with its own query statement. If this parameter is defined, then the attribute preferredtestquery will be ignored. You cannot do anything on this test sheet, it will be used only for C3P0 testing. default:null--> <property name= "automatictesttable" >Test</property>

<!--the standard parameters of JDBC to control the number of preparedstatements loaded within the data source. However, because the pre-cached statements belong to a single connection instead of the entire connection pool. So setting this parameter takes into account a variety of factors. If both maxstatements and maxstatementsperconnection are 0, the cache is closed. Default:0--><property name= "Maxstatements" >100</property>

<!--maxstatementsperconnection defines the maximum number of cache statements that a single connection in a connection pool has. default:0--<property name= "Maxstatementsperconnection" ></property>

<!--C3P0 is asynchronous, and slow JDBC operations are done through the help process. Extending these operations can effectively improve performance by implementing multiple operations at the same time through multithreading. Default:3--><property name= "Numhelperthreads" >3</property>

<!--the user to wait up to 300 seconds before modifying the system configuration parameters. default:300--<property name= "Propertycycle" >300</property>

The most recent project to solve MySQL 8-hour problem is using the C3P0 connection pool in Hibernate, the database is MYSQL. There is no problem with development testing, and there is an exception in the run for each long idle time: Java code 1. Org.hibernate.exception.JDBCConnectionException:could not execute Query 2. At Org.hibernate.exception.SQLStateConverter.convert (Sqlstateconve rter.java:74) 3. At Org.hibernate.exception.JDBCExceptionHelper.convert (Jdbcexcepti onhelper.java:43) 4. ....... 5. Caused by:com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException:No operations allowed after connection Closed. Connection was implicitly closed due to underlying exception/error:6. * * BEGIN NESTED Exception * * 7. Com.mysql.jdbc.CommunicationsException 8. Message:communications link failure due to underlying exception:9. * * BEGIN NESTED Exception * 10.java.net.socketexcept Ion 11.message:broken pipe 12.stacktrace:13.java.net.socketexception:broken pipe 14.at Java.net.SocketOutputStream.socketWrite0 (Native Method) 15 .... 16.** END NESTED EXCEPTION * *

Read the MySQL documentation, as well as the connector/j documentation and online instructions found that the reason for this exception is: Mysql server default "Wait_timeout" is 8 hours, that is, a connection idle for more than 8 hours, Mys QL will automatically disconnect the connection. This is the problem, in the C3P0 pools connections if idle for more than 8 hours, Mysql will disconnect it, and c3p0 do not know that the connection has failed, if there is a Client request connection,c3p0 Supplying the failed Connection to the Client will cause the above exception. There are 3 ways to solve this problem: 1. Increase the time of wait_timeout. 2. Reduce the lifetime of Connection in Connection pools. 3. Test the effectiveness of Connection in Connection pools. Of course, the best way is to combine the above 3 methods, the following DBCP and C3P0 to do a separate description, assuming wait_timeout for the default 8 hours DBCP Add the following configuration information: 1.//set to ' SELECT 1 ' 2. Validationquery = "Select 1" 3. Set to ' true ' 4. Testwhileidle = "true" 5. Some positive integer 6. Timebetweenevictionrunsmillis = 3600000 7. Set to something smaller than ' wait_timeout ' 8. Minevictableidletimemillis = 18000000 9. If you don't mind a hits for every getconnection () and set to "true" 10.testOnBorrow = "true" c3p0 add the following configuration information: 1.2. 3.4. 5.//Get connnection when the test is valid Testconnectiononcheckin = TRUE//The table name of the automated test automatictesttable=C3p0testtable//set to something much less than wait_timeout, prevents connections from going stale 6. Idleconnectiontestperiod = 18000 7. Set to something slightly less than wait_timeout, preventing ' stale ' connections from being handed out 8. MaxIdleTime = 25000 9. If you can take the performance ' hits ', set to ' true ' 10.testConnectionOnCheckout = True

When initializing, get three connections, and the value should be between Minpoolsize and Maxpoolsize. Default:3 initialpoolsize ds.setinitialpoolsize (3);

The maximum number of connections that are kept in the connection pool. Default:15 maxpoolsize ds.setmaxpoolsize (10);

The minimum number of connections that are kept in the connection pool. Ds.setminpoolsize (1);

When the connection in the connection pool runs out, c3p0 the number of connections that are fetched at one time. Default:3 acquireincrement ds.setacquireincrement (1);

Check for idle connections in all connection pools every 60 seconds. default:0 idleconnectiontestperiod Ds.setidleconnectiontestperiod (60);

Maximum idle time, unused in 25,000 seconds, the connection is discarded. If 0, it will never be discarded. default:0 maxidletime Ds.setmaxidletime (25000);

All uncommitted operations are rolled back by default when the connection is closed. Default:false autocommitonclose Ds.setautocommitonclose (true);

Defines test statements that are executed by all connection tests. This one significantly improves the test speed in the case of connection testing. Note: The test table must exist at the time of the initial data source. Default:null preferredtestquery ds.setpreferredtestquery ("Select Sysdate from Dual");

Please use it only when you need it because of high performance consumption. If set to true then the validity of each connection submission is officer. We recommend using methods such as Idleconnectiontestperiod or automatictesttable to improve the performance of your connectivity tests. Default:false testconnectiononcheckout Ds.settestconnectiononcheckout (true);

If set to true then the validity of the officer connection is obtained while the connection is made. Default:false Testconnectiononcheckin Ds.settestconnectiononcheckin (true);

Defines the number of repeated attempts to obtain a new connection from the database after a failure. Default:30 acquireretryattempts ds.setacquireretryattempts (30);

Interval in two connections, in milliseconds. default:1000 Acquireretrydelay Ds.setacquireretrydelay (1000);

Getting a connection failure will cause any thread that waits for the connection pool to get the connection to throw an exception. However, the data source is still valid and continues to try to get the connection the next time you call Getconnection (). If set to True, the data source will declare broken and permanently shut down after attempting to acquire a connection failure. Default:false breakafteracquirefailure Ds.setbreakafteracquirefailure (true);

Java Configuration c3p0
Import java.sql.Connection;

Import java.sql.SQLException;

Import Com.mchange.v2.c3p0.ComboPooledDataSource;

Import com.mchange.v2.c3p0.DataSources;

Public final class ConnectionManager {

private static ConnectionManager instance;

Private Combopooleddatasource DS;

Private ConnectionManager () throws Exception {

ds = new Combopooleddatasource ();

Ds.setdriverclass ("Oracle.jdbc.driver.OracleDriver");

Ds.setjdbcurl ("JDBC:ORACLE:THIN:@127.0.0.1:1521:ORCL");

Ds.setuser ("test"); Ds.setpassword ("Testtest");}

public static final ConnectionManager getinstance () {

if (instance = = null) {

try {instance = new ConnectionManager ();} catch (Exception e) {e.printstacktrace ();}

} return instance;

}

Public synchronized final Connection getconnection () {

try {return ds.getconnection ();} catch (SQLException e) {e.printstacktrace ();} return null;

}

protected void Finalize () throws Throwable {Datasources.destroy (DS);//Close DataSource super.finalize ();}

}//Call database connection pool

Import java.sql.Connection; Import Java.sql.ResultSet; Import java.sql.Statement;

public class Testc3p0 {

Public testc3p0 () {//TODO automatically generate constructor stub}

public static void Main (string[] args) {//TODO auto-Generate Method stub

ConnectionManager cm = Connectionmanager.getinstance ();

Connection conn = null; Statement stmt = null; ResultSet rs = null; String sql = "Select sysdate from Dual";

for (int i = 0; i < 10000; i++) {

try {conn = cm.getconnection (); SYSTEM.OUT.PRINTLN (conn); stmt = Conn.createstatement (); rs = stmt.executequery (SQL); 　　　　　　　　Rs.next (); System.out.println (rs.getstring (1)); } catch (Exception ex) {ex.printstacktrace ();} finally {

if (rs! = null) {try {rs.close ();} catch (Exception e) {}}

if (stmt! = null) {try {stmt.close ();} catch (Exception e) {}}

IF (conn! = null) {try {conn.close ();} catch (Exception e) {}}}

try {System.out.println (cm.ds.toString ()); Thread.Sleep (10000); } catch (Exception e) {//Todo:handle Exception}}

}

}

This article is for the network reprint

C3P0 Optimization Detail Configuration