讨论1. 使用连接池后在通过连接池获取连接,进行数据库操作时, 是否需要主动关闭连接

项目中使用了apache dbcp 连接池后, 点击查询,当查询多次后,报如下错误:

?

?

org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
	at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:114)
	at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)

?

?分析:

通过测试,通过restart server后, 前10次,均可查询成功, 之后就开始报此错误. 推测是通过连接池获取数据连接后,

此连接一直占用状态, 当再次请求后,由于连接池中没有可用连接,所有请求连接时,就报了等待超时错误.

之前对数据库连接池, 有个理解: 连接池用在管理数据库连接的创建,关闭操作, 就以为无需关闭连接, 让连接池来自动关闭.

处理过程:

1 .通过查看dbcp源代码,关于连接池相关属性的设置:

1). maxActive/ maxWait: 源码如下, 意思 最大活动连接数,默认为8. 即连接池中最大连接数为8. 当程序超过此连接数再获取连接时,就处于排队状态, 当排队超高maxWait(见2, 默认为) 时间,就抛出异常.?

通过查看连接池配置,发现配置如下,即最大连接为10 ,等待时间为10ms ,通过调整maxWait时间如改为5000(即5s),程序在超过连接10次后, 请求正好5s后,抛出异常.?

		   		datasource.setMaxActive(10);
		   		datasource.setMaxWait(10);

    /**
     * The maximum number of active connections that can be allocated from
     * this pool at the same time, or negative for no limit.
     */
    protected int maxActive = GenericObjectPool.DEFAULT_MAX_ACTIVE;

?

?-->

?

    /**
     * The default cap on the total number of active instances from the pool.
     * @see #getMaxActive
     */
    public static final int DEFAULT_MAX_ACTIVE  = 8;

? /**

     * 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 <= 0 to wait indefinitely.
     */
    protected long maxWait = GenericObjectPool.DEFAULT_MAX_WAIT;

    /**
     * The default maximum amount of time (in milliseconds) the
     * {@link #borrowObject} method should block before throwing
     * an exception when the pool is exhausted and the
     * {@link #getWhenExhaustedAction "when exhausted" action} is
     * {@link #WHEN_EXHAUSTED_BLOCK}.
     * @see #getMaxWait
     * @see #setMaxWait
     */
    public static final long DEFAULT_MAX_WAIT = -1L;

?

maxWait默认为-1L ,一直等待.

maxActive和maxWait 设置需根据项目实际访问情况设置.?

?

2).?minIdle:?

    /**
     * The minimum number of active connections that can remain idle in the
     * pool, without extra ones being created, or 0 to create none.
     */
    protected int minIdle = GenericObjectPool.DEFAULT_MIN_IDLE;

    /**
     * The default minimum number of "sleeping" instances in the pool
     * before before the evictor thread (if active) spawns new objects.
     * @see #getMinIdle
     * @see #setMinIdle
     */
    public static final int DEFAULT_MIN_IDLE = 0;

?最小空闲数,即:连接池中保持连接的最小数, 此属性越接近实际访问大小,效率越高. 默认为0,即无连接.

根据本项目实际情况设置为2.(项目为个人开发)

?

3). 关于对于未关闭连接的管理处理的参数:

removeAbandoned: ?是否关闭未关闭的连接, 默认为false, 改为true ,即超过removeAbandonedTimeout的连接,进行强制关闭.

removeAbandonedTimeout: 默认为300(单位秒)即5分钟, 根据操作数据时间来判断, 由于本系统访问较小且无复杂操作,设置为30s

logAbandoned: 关闭连接是否记录日志, 默认为false,改为true.

源码如下:

?

public class AbandonedConfig {

    /**
     * Whether or not a connection is considered abandoned and eligible
     * for removal if it has been idle longer than the removeAbandonedTimeout
     */
    private boolean removeAbandoned = false;

   /**
     * Timeout in seconds before an abandoned co