DBCP连接池属性说明

面向JDBC驱动的基础属性

username	The connection username to be passed to our JDBC driver to establish a connection.
password	The connection password to be passed to our JDBC driver to establish a connection.
url	The connection URL to be passed to our JDBC driver to establish a connection.
driverClassName	The fully qualified Java class name of the JDBC driver to be used.

connectionProperties

The connection properties that will be sent to our JDBC driver when establishing new connections.  Format of the string must be [propertyName=property;]*  NOTE - The "user" and "password" properties will be passed explicitly, so they do not need to be included here.

 

驱动相关的属性

defaultAutoCommit	true	The default auto-commit state of connections created by this pool.
defaultReadOnly	driver default	The default read-only state of connections created by this pool. If not set then the setReadOnly method will not be called. (Some drivers don't support read only mode, ex: Informix)
defaultTransactionIsolation	driver default	The default TransactionIsolation state of connections created by this pool. One of the following: (see javadoc) NONE READ_COMMITTED READ_UNCOMMITTED REPEATABLE_READ SERIALIZABLE
defaultCatalog		The default catalog of connections created by this pool.

 

  连接相关的属性 

initialSize	0	The initial number of connections that are created when the pool is started.  Since: 1.2
maxActive	8	The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit.
maxIdle	8	The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit.
minIdle	0	The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none.
maxWait	indefinitely	The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely.

NOTE: If maxIdle is set too low on heavily loaded systems it is possible you will see connections being closed and almost immediately new connections being opened. This is a result of the active threads momentarily closing connections faster than they are opening them, causing the number of idle connections to rise above maxIdle. The best value for maxIdle for heavily loaded system will vary but the default is a good starting point.

1. initialSize ：连接池启动时创建的初始化连接数量（默认值为0）
2. maxActive ：连接池中可同时连接的最大的连接数（默认值为8，调整为20，高峰单机器在20并发左右，自己根据应用场景定）
3. maxIdle：连接池中最大的空闲的连接数，超过的空闲连接将被释放，如果设置为负数表示不限制（默认为8个，maxIdle不能设置太小，因为假如在高负载的情况下，连接的打开时间比关闭的时间快，会引起连接池中idle的个数 上升超过maxIdle，而造成频繁的连接销毁和创建，类似于jvm参数中的Xmx设置)
4. minIdle：连接池中最小的空闲的连接数，低于这个数量会被创建新的连接（默认为0，调整为5，该参数越接近maxIdle，性能越好，因为连接的创建和销毁，都是需要消耗资源的；但是不能太大，因为在机器很空闲的时候，也会创建低于minidle个数的连接，类似于jvm参数中的Xmn设置）
5. maxWait  ：最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间，超过该时间限制会抛出异常，如果设置-1表示无限等待（默认为无限，调整为60000ms，避免因线程池不够用，而导致请求被无限制挂起）

连接控测与空闲连接相关属性

Parameter Default Description

validationQuery		The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row.
testOnBorrow	true	The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another. NOTE - for a true value to have any effect, thevalidationQuery parameter must be set to a non-null string.
testOnReturn	false	The indication of whether objects will be validated before being returned to the pool.  NOTE - for a true value to have any effect, thevalidationQuery parameter must be set to a non-null string.
testWhileIdle	false	The indication of whether objects will be validated by the idle object evictor (if any). If an object fails to validate, it will be dropped from the pool.  NOTE - for a true value to have any effect, thevalidationQuery parameter must be set to a non-null string.
timeBetweenEvictionRunsMillis	-1	The number of milliseconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run.
numTestsPerEvictionRun	3	The number of objects to examine during each run of the idle object evictor thread (if any).
minEvictableIdleTimeMillis	1000 * 60 * 30	The minimum amount of time an object may sit idle in the pool before it is eligable for eviction by the idle object evictor (if any).
connectionInitSqls  NOTE: Versions 1.3 and 1.4 of DBCP incorrectly use "initConnectionSqls" as the name of this property for JNDI object factory configuration. Until 1.3.1/1.4.1 are released, "initConnectionSqls" must be used as the name for this property when using BasicDataSoureFactory to create BasicDataSource instances via JNDI.	null	A Collection of SQL statements that will be used to initialize physical connections when they are first created. These statements are executed only once - when the configured connection factory creates the connection.

 

1、validateQuery， 代表检查的sql，要求可执行并能返回至少一条记录。
2、testOnBorrow：设定在借出对象时是否进行有效性检查。 
3、testOnBorrow：设定在还回对象时是否进行有效性检查。 
4、timeBetweenEvictionRunsMillis，设定间隔每过多少毫秒进行一次后台对象清理的行动。如果这个值不是正数，则实际上不会进行后台对象清理。 
5、参数numTestsPerEvictionRun：设定在进行后台对象清理时，每次检查几个对象。如果这个值不是正数，则每次检查的对象数是检查时池内对象的总数乘以这个值的负倒数再向上取整的结果――也就是说，如果这个值是-2（-3、-4、-5……）的话，那么每次大约检查当时池内对象总数的1/2（1/3、1/4、1/5……）左右。 
6、minEvictableIdleTimeMillis：设定在进行后台对象清理时，视休眠时间超过了多少毫秒的对象为过期。过期的对象将被回收。如果这个值不是正数，那么对休眠时间没有特别的约束。 
7、testWhileIdle：设定在进行后台对象清理时，是否还对没有过期的池内对象进行有效性检查。不能通过有效性检查的对象也将被回收。 

---

 

Parameter Default Description

poolPreparedStatements	false	Enable prepared statement pooling for this pool.
maxOpenPreparedStatements	unlimited	The maximum number of open statements that can be allocated from the statement pool at the same time, or zero for no limit.

 This component has also the ability to pool PreparedStatements. When enabled a statement pool will be created for each Connection and PreparedStatements created by one of the following methods will be pooled:

• public PreparedStatement prepareStatement(String sql)
• public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)

 

NOTE - Make sure your connection has some resources left for the other statements. Pooling PreparedStatements may keep their cursors open in the database, causing a connection to run out of cursors, especially if maxOpenPreparedStatements is left at the default (unlimited) and an application opens a large number of different PreparedStatements per connection. To avoid this problem, maxOpenPreparedStatements should be set to a value less than the maximum number of cursors that can be open on a Connection.

---

未释放资源的连接相关的属性

1. 代码未在finally释放connection ,　不过我们都用sqlmapClientTemplate，底层都有链接释放的过程
2. 遇到数据库死锁。以前遇到过后端存储过程做了锁表操作，导致前台集群中连接池全都被block住，后续的业务处理因为拿不到链接所有都处理失败了。

Parameter Default Description

removeAbandoned	false	Flag to remove abandoned connections if they exceed the removeAbandonedTimout. If set to true a connection is considered abandoned and eligible for removal if it has been idle longer than the removeAbandonedTimeout. Setting this to true can recover db connections from poorly written applications which fail to close a connection.
removeAbandonedTimeout	300	Timeout in seconds before an abandoned connection can be removed.
logAbandoned	false	Flag to log stack traces for application code which abandoned a Statement or Connection. Logging of abandoned Statements and Connections adds overhead for every Connection open or new Statement because a stack trace has to be generated.

 If you have enabled "removeAbandoned" then it is possible that a connection is reclaimed by the pool because it is considered to be abandoned. This mechanism is triggered when (getNumIdle() < 2) and (getNumActive() > getMaxActive() - 3)

 For example maxActive=20 and 18 active connections and 1 idle connection would trigger the "removeAbandoned". But only the active connections that aren't used for more then "removeAbandonedTimeout" seconds are removed, default (300 sec). Traversing a resultset doesn't count as being used.

removeAbandoned参数解释：

1. removeAbandoned  ：超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收（默认为false，调整为true)。如果开启了removeAbandoned，当getNumIdle() < 2) and (getNumActive() > getMaxActive() - 3)时被触发。举例当maxActive=20, 活动连接为18,空闲连接为1时可以触发"removeAbandoned".但是活动连接只有在没有被使用的时间超 过"removeAbandonedTimeout"时才被回收;
2. removeAbandonedTimeout  ：超过时间限制，回收没有用(废弃)的连接（默认为 300秒，调整为180）
3. logAbandoned： 标记当连接被回收时是否打印程序的stack traces日志（默认为false，未调整）