Detailed introduction to database connection pool c3p0 configuration in Java

This article mainly introduces the relevant information of database connection pool c3p0 configuration in detail, which has certain reference value. Interested friends can refer to it

The configuration methods of c3p0 are divided into three types , respectively

1. Setters set each configuration item one by one
2. Provide a c3p0.properties file under the class path
3. Provide a c3p0-config under the class path. xml file

1.setters set each configuration item one by one

This method is the most cumbersome, and the form is generally like this:

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, it is not suitable to use, so the document provides another method.

2. Provide a c3p0.properties file under the class path

The name of the file must be c3p0.properties, and the format of the configuration items is ：

c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/jdbc
c3p0.user=root
c3p0.password=java

Only the most basic configuration items are provided above. Other configuration items refer to the document configuration. Remember to c3p0. Just add the attribute name after it, and finally initialize it. The data source method 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. Provide a c3p0-config.xml file under the class path

This The first method is similar to the second one, but has more advantages
(1). It is more intuitive and obvious, very similar to the configuration of hibernate and spring
(2). It can serve multiple data sources and provide There are two configuration methods: default-config and named-config
The following 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, the method of initializing the data source is the same as The two are the same. If you want to use the initialized data source configured in named-config, you only need to use a ComboPooledDataSource constructor with parameters.

private static ComboPooledDataSource ds = new ComboPooledDataSource("myApp");
Let’s sort out the understanding of c3p0 configuration learned from documents and online (user, password, driverClass, jdbcUrl is not necessary)

1. Basic configuration items

acquireIncrement

default : 3

The number of new database connections created at one time by the connection pool when no idle connections are available

initialPoolSize

default : 3

The number of connections created when the connection pool is initialized

maxPoolSize

default : 15

The maximum number of connections in the connection pool, if a new When connecting, if the total number of connections exceeds this value, new connections will not be obtained, but will wait for

other connections to be released, so this value may be designed to be very large

maxIdleTime

default: 0 unit s
The maximum idle time of the connection. If this time is exceeded and a database connection has not been used, the connection will be disconnected.
If it is 0, it will never be disconnected. Open a connection

minPoolSize
default: 3
The minimum number of connections maintained by the connection pool, and the subsequent maxIdleTimeExcessConnections are used in conjunction with this to reduce the load on the connection pool

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

maxConnectionAge

default: 0 units

Configure the lifetime of the connection, if it exceeds Connections during this time will be automatically disconnected and discarded by the connection pool. Of course, the connection in use will not be disconnected immediately, but will wait for it to close before disconnecting. When configured to 0, there will be no limit on the connection lifetime.

maxIdleTimeExcessConnections

default : 0 unit s

This configuration is mainly to reduce the load of the connection pool. For example, the number of connections in the connection pool is created due to a certain data access peak. There are many data connections

but the number of database connections required in the subsequent time period is very small. At this time, there is no need for the connection pool to maintain so many connections, so it is necessary to

disconnect and discard them. Some connections to reduce load, must be less than maxIdleTime. If the configuration is not 0, the number of connections in the connection pool will be maintained to minPoolSize. If

is 0, it will not be processed.

maxIdleTime can also fall into this category, which has been written before.

3. Configure connection test: Because the database connection in the connection pool is likely to last for several hours, it is very likely that the actual connection has become invalid due to database server problems, network problems, etc., but the connection pool The connection inside is still valid. If the connection is obtained at this time, an exception will definitely occur, so it is necessary to confirm the validity of the connection by testing the connection.

The first three items below are used to configure how to test the connection, and the last three items are used to configure the timing of testing 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 use your own test sql statement to test the database connection on this empty table

This table can only be accessed by c3p0 If used, the user cannot operate, and the preferredTestQuery configured by the user will be ignored.

preferredTestQuery

default : null

Another way to configure a test connection. You can only choose one from the automaticTestTable above.

If you want to use it to test the connection, do not set it to null, otherwise the testing process will be very time-consuming. At the same time, you must ensure that the table in the sql statement must exist in the database.

connectionTesterClassName

default :  com.mchange.v2.c3p0.impl.DefaultConnectionTester

连接池用来支持automaticTestTable和preferredTestQuery测试的类，必须是全类名，就像默认的那样，

可以通过实现UnifiedConnectionTester接口或者继承AbstractConnectionTester来定制自己的测试方法

idleConnectionTestPeriod

default : 0

用来配置测试空闲连接的间隔时间。测试方式还是上面的两种之一，可以用来解决MySQL8小时断开连接的问题。因为它

保证连接池会每隔一定时间对空闲连接进行一次测试，从而保证有效的空闲连接能每隔一定时间访问一次数据库，将于MySQL

8小时无会话的状态打破。为0则不测试。

testConnectionOnCheckin

default : false

如果为true，则在close的时候测试连接的有效性。为了提高测试性能，可以与idleConnectionTestPeriod搭配使用，

配置preferredTestQuery或automaticTestTable也可以加快测试速度。

testConnectionOnCheckout

default : false
性能消耗大。如果为true，在每次getConnection的时候都会测试，为了提高性能，

可以与idleConnectionTestPeriod搭配使用，

配置preferredTestQuery或automaticTestTable也可以加快测试速度。

4.配置PreparedStatement缓存

maxStatements
default : 0
连接池为数据源缓存的PreparedStatement的总数。由于PreparedStatement属于单个Connection,所以
这个数量应该根据应用中平均连接数乘以每个连接的平均PreparedStatement来计算。为0的时候不缓存，
同时maxStatementsPerConnection的配置无效。

maxStatementsPerConnection
default : 0
连接池为数据源单个Connection缓存的PreparedStatement数，这个配置比maxStatements更有意义，因为
它缓存的服务对象是单个数据连接，如果设置的好，肯定是可以提高性能的。为0的时候不缓存。

5.重连相关配置

acquireRetryAttempts

default : 30

连接池在获得新连接失败时重试的次数，如果小于等于0则无限重试直至连接获得成功

acquireRetryDelay

default : 1000 单位ms

连接池在获得新连接时的间隔时间

breakAfterAcquireFailure
default : false

如果为true，则当连接获取失败时自动关闭数据源，除非重新启动应用程序。所以一般不用。
个人觉得上述三个没有更改的必要，但可以将acquireRetryDelay配置地更短一些

6.定制管理Connection的生命周期

connectionCustomizerClassName
default : null
用来定制Connection的管理，比如在Connection acquire 的时候设定Connection的隔离级别，或者在
Connection丢弃的时候进行资源关闭，就可以通过继承一个AbstractConnectionCustomizer来实现相关

方法，配置的时候使用全类名。有点类似监听器的作用。
例如：

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.配置未提交的事务处理

autoCommitOnClose

default : false

连接池在回收数据库连接时是否自动提交事务

如果为false，则会回滚未提交的事务

如果为true，则会自动提交事务

forceIgnoreUnresolvedTransactions

default : false

这个配置强烈不建议为true。
一般来说事务当然由自己关闭了，为什么要让连接池来处理这种不细心问题呢？

8.配置debug和回收Connection 一般来说事务当然由自己关闭了，为什么要让连接池来处理这种不细心问题呢？

unreturnedConnectionTimeout
default : 0 单位 s
为0的时候要求所有的Connection在应用程序中必须关闭。如果不为0，则强制在设定的时间到达后回收
Connection，所以必须小心设置，保证在回收之前所有数据库操作都能够完成。这种限制减少Connection未关闭
情况的不是很适用。为0不对connection进行回收，即使它并没有关闭。

debugUnreturnedConnectionStackTraces
default : false
如果为true并且unreturnedConnectionTimeout设为大于0的值，当所有被getConnection出去的连接
unreturnedConnectionTimeout时间到的时候，就会打印出堆栈信息。只能在debug模式下适用，因为
打印堆栈信息会减慢getConnection的速度
同第七项一样的，连接用完当然得close了，不要通过unreturnedConnectionTimeout让连接池来回收未关闭的连接。

9.其他配置项：因为有些配置项几乎没有自己配置的必要，使用默认值就好，所以没有再写出来

checkoutTimeout
default : 0
配置当连接池所有连接用完时应用程序getConnection的等待时间。为0则无限等待直至有其他连接释放
或者创建新的连接，不为0则当时间到的时候如果仍没有获得连接，则会抛出SQLException

三、示例：

示例采用第二种方式：

1.c3p0.properties:

#驱动 
c3p0.driverClass=com.mysql.jdbc.Driver 
#地址 
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/jdbc 
#用户名 
c3p0.user=root 
#密码 
c3p0.password=lovejava 
#------------------------------- 
#连接池初始化时创建的连接数 
c3p0.initialPoolSize=3 
#连接池保持的最小连接数 
c3p0.minPoolSize=3 
#连接池在无空闲连接可用时一次性创建的新数据库连接数,default:3 
c3p0.acquireIncrement=3 
#连接池中拥有的最大连接数，如果获得新连接时会使连接总数超过这个值则不会再获取新连接，而是等待其他连接释放，所以这个值有可能会设计地很大,default : 15 
c3p0.maxPoolSize=15 
#连接的最大空闲时间，如果超过这个时间，某个数据库连接还没有被使用，则会断开掉这个连接,单位秒 
c3p0.maxIdleTime=100 
#连接池在获得新连接失败时重试的次数，如果小于等于0则无限重试直至连接获得成功 
c3p0.acquireRetryAttempts=30 
#连接池在获得新连接时的间隔时间 
c3p0.acquireRetryDelay=1000

 2.ConnectionPool

package com.study.pool; 
 
import java.sql.Connection; 
import java.sql.SQLException; 
 
import javax.sql.DataSource; 
 
import com.mchange.v2.c3p0.ComboPooledDataSource; 
 
public class ConnectionPool { 
 private DataSource ds; 
 private static ConnectionPool pool; 
 private ConnectionPool(){ 
  ds = new ComboPooledDataSource(); 
 } 
 public static final ConnectionPool getInstance(){ 
  if(pool==null){ 
   try{ 
    pool = new ConnectionPool(); 
   }catch (Exception e) { 
    e.printStackTrace(); 
   } 
  } 
  return pool; 
 } 
 public synchronized final Connection getConnection() { 
  try { 
   return ds.getConnection(); 
  } catch (SQLException e) {  
   e.printStackTrace(); 
  } 
  return null; 
 } 
  
}

 3.PoolThread

package com.study.pool; 
 
import java.sql.Connection; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
 
public class PoolThread extends Thread { 
 @Override 
 public void run(){ 
  ConnectionPool pool = ConnectionPool.getInstance(); 
  Connection con = null; 
  PreparedStatement stmt= null; 
  ResultSet rs = null; 
  try{ 
   con = pool.getConnection(); 
   stmt = con.prepareStatement("select sysdate as nowtime from dual"); 
   rs = stmt.executeQuery(); 
   while(rs.next()){ 
    System.out.println(Thread.currentThread().getId()+"---------------开始"+rs.getString("nowtime")); 
   } 
  } catch (Exception e) { 
   e.printStackTrace(); 
  }finally{ 
   try { 
    rs.close(); 
    stmt.close(); 
    con.close(); 
   } catch (SQLException e) { 
    e.printStackTrace(); 
   } 
  } 
  System.out.println(Thread.currentThread().getId()+"--------结束"); 
 } 
}

 4.PoolMain

package com.study.pool; 
 
public class PoolMain { 
 
 /** 
  * 数据源缓冲池 实例练习 
  */ 
 public static void main(String[] args) { 
  System.out.println("缓冲池模拟开始"); 
  PoolThread[] threads = new PoolThread[50]; 
  for(int i=0;i<threads.length;i++){ 
   threads[i] = new PoolThread(); 
  } 
  for(int i=0;i<threads.length;i++){ 
   threads[i].start(); 
  } 
 } 
 
}

The above is the detailed content of Detailed introduction to database connection pool c3p0 configuration in Java. For more information, please follow other related articles on the PHP Chinese website!