C3P0 Configuration Detailed

Database connection Pool C3P0 Framework is a very good open source jar, high-performance management of the data source, here only to discuss the program itself is responsible for the data source, do not discuss container management.

First, the realization way:

C3P0 is implemented in three ways:

1. Write your own code to implement the data source

For example: Configure a property file under the Classpath, Config.properties, with the following:

Driverclass=xxx

Jdbcurl=xxx

User=xxx

Password=xxx

...

The code then implements the

Properties Props = new properties ();

InputStream in = Thread.class.getResourceAsStream ("config.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"));

...

A data source is implemented here.

This can also be configured to configure an XML file under the Classpath

<config>

<source name= "Source1" >

<property name= "User" >root</property>

<property name= "Password" >xxx</property>

<property name= "url" >xxx</property>

<property name= "Driverclass" >xxx</property>

</source>

<source name= "Source2" >

...

</source>

</config>

Then parse the XML file yourself so that you can configure multiple data sources

2. Configure the default familiar files

A c3p0.properties file is provided under the Classpath (cannot be renamed)

The configuration is as follows:

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. After the property name is, the last way to initialize the data source is this simple: ... DataSource ds = new Combopooleddatasource (); return ds; .... Then you can use the data source, C3P0 will automatically parse the C3p0.properties 3. A c3p0-config.xml file is provided under the path

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

<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= "MySource" >

<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>

...

DataSource ds = new Combopooleddatasource ("MySource");

return DS;

...

This makes it possible to use the data source.

Second, some parameter configuration description:

1. Most commonly used configurations

Initialpoolsize: Number of connections created when connection pooling is initialized, Default:3 (recommended)

Minpoolsize: The minimum number of connections maintained by the connection pool, Default:3 (recommended)

Maxpoolsize: The maximum number of connections that are available in the connection pool, and if a new connection is made, the total number of connections exceeds this value no longer acquires new connections, but waits for other connections to be released, so this value may be designed to be large, default:15 (recommended)

Acquireincrement: Connection pool number of new database connections created one time when no idle connection is available, Default:3 (recommended)

2. Manage connection pool size and the lifetime of the connection

Maxconnectionage: 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 disconnected, but instead waits for it to close and then disconnect. When configured to 0, the lifetime of the connection is not limited. default:0 Unit S (not recommended)

MaxIdleTime: The maximum idle time for a connection, and if a database connection has not been used at this time, the connection will be disconnected. If it is 0, the connection is never disconnected, that is, the connection is reclaimed. default:0 Unit s (recommended)

Maxidletimeexcessconnections: This configuration is mainly to reduce the load of the connection pool, such as connection pool connection number because of the peak of data access caused a lot of data connections, but the subsequent time period requires a small number of database connections, need to quickly release, Must be less than maxidletime. In fact, this is not necessary configuration, MaxIdleTime has been configured. default:0 Unit S (not recommended)

3. Configure the connection test:

Automatictesttable: Configure a table name, the connection pool uses its own test SQL statement to test the database connection on this empty table based on the name of the table, which can only be used by C3P0 and cannot be manipulated by the user. Default:null (not recommended)

Preferredtestquery: With the above automatictesttable can only choose one. Implement a SQL instrumentation statement yourself. Default:null (recommended)

Idleconnectiontestperiod: The time interval used to configure the test idle connection. The test method is also one of the two above, can be used to solve the problem of MySQL8 hours disconnection. Because it guarantees that the connection pool will test the idle connection at a certain time, so that a valid idle connection can access the database at a certain time, it will break in a MySQL8-hour session-Free State. The 0 is not tested. default:0 (recommended)

Testconnectiononcheckin: If true, the validity of the connection is tested at close. Default:false (not recommended)

Testconnectiononcheckout: High performance consumption. If true, it will be tested every time getconnection, in order to improve performance, try not to use. Default:false (not recommended)

4. Configure the PreparedStatement cache:

Maxstatements: 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. The maxstatementsperconnection configuration is not valid at the same time. Default:0 (not recommended)

Maxstatementsperconnection: 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. If the settings are good, you can certainly improve performance. is not cached at 0. default:0 (see case study)

5. Re-connect the relevant configuration

Acquireretryattempts: The number of times the connection pool retries to obtain a new connection failure, and an infinite retry if less than or equal to 0 until the connection succeeds. DEFAULT:30 (recommended)

Acquireretrydelay: The time between connection pooling when a new connection is obtained. default:1000 Unit MS (recommended)

Breakafteracquirefailure: True if the data source is automatically closed when connection acquisition fails, unless the application is restarted. So generally not. Default:false (not recommended)

Checkouttimeout: Configures the wait time for application getconnection when all connection pooling connections are exhausted. An infinite wait of 0 waits until another connection is released or a new connection is created, and no 0 is thrown sqlexception if the connection is still not obtained when the time is up. is actually acquireretryattempts*acquireretrydelay. Default:0 (with two above, repeat, select two of them are OK)

6. Customizing the lifecycle of Management connection

Connectioncustomizerclassname: Used to customize the management of connection, such as connection acquire when setting the connection isolation level, Or when the connection is dropped, the resource is closed,

You can implement related methods by inheriting a abstractconnectioncustomizer, using the full class name when configuring. A bit like the role of a listener. Default:null (not recommended)

7. Configuration of uncommitted transactions

Autocommitonclose: Whether the connection pool commits transactions automatically when the database connection is reclaimed. If False, the uncommitted transaction is rolled back, and if true, the transaction is automatically committed. Default:false (not recommended)

Forceignoreunresolvedtransactions: This configuration is strongly not recommended for true. Default:false (not recommended)

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: When 0 is required, all connection must be closed in the application. If not 0, the connection is forced to be reclaimed after the set time has arrived, so care must be taken to set it to ensure that all database operations are completed before recycling. This limitation does not apply to reducing the connection of the closed case. Manual shutdown is recommended. default:0 Unit S (not recommended)

Debugunreturnedconnectionstacktraces: If True and Unreturnedconnectiontimeout is set to a value greater than 0, When all the connections that are getconnection out unreturnedconnectiontimeout time, the stack information is printed. Only applicable in debug mode because the print stack information slows down the speed of getconnection Default:false (not recommended)

Other configuration items: Because some configuration items are almost not necessary for their own configuration, using the default value is good, so it is not written again.

Three, example:

The example takes the second approach:

1.c3p0.properties:

Java code

2. #驱动
4. C3p0.driverclass=com.mysql.jdbc.driver
6. #地址
8. C3p0.jdbcurl=jdbc:mysql://localhost:3306/jdbc
10. #用户名
12. C3p0.user=root
14. #密码
16. C3p0.password=lovejava
18. #-------------------------------
20. #连接池初始化时创建的连接数
22. C3p0.initialpoolsize=3
24. #连接池保持的最小连接数
26. C3p0.minpoolsize=3
28. #连接池在无空闲连接可用时一次性创建的新数据库连接数,default:3
30. c3p0.acquireincrement=3
32. #连接池中拥有的最大连接数, if a new connection is made, the total number of connections will exceed this value and no new connections will be obtained, but instead wait for other connections to be released, so this value may be designed to be large,default:
34. C3p0.maxpoolsize=
36. #连接的最大空闲时间, if a database connection has not been used for more than this time, the connection will be disconnected, in seconds
38. C3p0.maxidletime=
40. #连接池在获得新连接失败时重试的次数, if it is less than or equal to 0, retry indefinitely until the connection succeeds
42. c3p0.acquireretryattempts=
44. #连接池在获得新连接时的间隔时间
46. c3p0.acquireretrydelay=

2.ConnectionPool

Java code

2. Package com.study.pool;
6. Import java.sql.Connection;
8. Import java.sql.SQLException;
12. Import Javax.sql.DataSource;
16. Import Com.mchange.v2.c3p0.ComboPooledDataSource;
20. Public class ConnectionPool {
22. Private DataSource ds;
24. private static ConnectionPool pool;
26. Private ConnectionPool () {
28. ds = new Combopooleddatasource ();
30. }
32. public static final ConnectionPool getinstance () {
34. if (pool==null) {
36. try{
38. Pool = new ConnectionPool ();
40. }catch (Exception e) {
42. E.printstacktrace ();
44. }
46. }
48. return pool;
50. }
52. public synchronized final Connection getconnection () {
54. try {
56. return ds.getconnection ();
58. } catch (SQLException e) {
60. E.printstacktrace ();
62. }
64. return null;
66. }
70. }

3.PoolThread

Java code

2. Package com.study.pool;
6. Import java.sql.Connection;
8. Import java.sql.PreparedStatement;
10. Import Java.sql.ResultSet;
12. Import java.sql.SQLException;
16. Public class Poolthread extends Thread {
18. @Override
20. public Void Run () {
22. ConnectionPool pool = connectionpool.getinstance ();
24. Connection con = null;
26. PreparedStatement stmt= null;
28. ResultSet rs = null;
30. try{
32. con = pool.getconnection ();
34. stmt = con.preparestatement ("Select Sysdate as Nowtime from dual");
36. rs = Stmt.executequery ();
38. While (Rs.next ()) {
40. System.out.println (Thread.CurrentThread (). GetId () +"---------------start" +rs.getstring ("Nowtime"));
42. }
44. } catch (Exception e) {
46. E.printstacktrace ();
48. }finally{
50. try {
52. Rs.close ();
54. Stmt.close ();
56. Con.close ();
58. } catch (SQLException e) {
60. E.printstacktrace ();
62. }
64. }
66. System.out.println (Thread.CurrentThread (). GetId () +"--------End");
68. }
70. }

4.PoolMain

Java code

2. Package com.study.pool;
6. Public class Poolmain {
10. /** 
12. * Data source buffer Pool instance Practice
14. */
16. public static void Main (string[] args) {
18. SYSTEM.OUT.PRINTLN ("Buffer pool simulation start");
20. poolthread[] Threads = new poolthread[50];
22. For (int i=0;i<threads.length;i++) {
24. Threads[i] = new Poolthread ();
26. }
28. For (int i=0;i<threads.length;i++) {
30. Threads[i].start ();
32. }
34. }
38. }

C3P0 Configuration Detailed