【DBCP】DBCP基本配置和重连配置+spring中配置

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，避免因线程池不够用，而导致请求被无限制挂起）
6. poolPreparedStatements：开启池的prepared（默认是false，未调整，经过测试，开启后的性能没有关闭的好。）
7. maxOpenPreparedStatements：开启池的prepared 后的同时最大连接数（默认无限制，同上，未配置）
8. minEvictableIdleTimeMillis  ：连接池中连接，在时间段内一直空闲， 被逐出连接池的时间
9. （默认为30分钟，可以适当做调整，需要和后端服务端的策略配置相关）
10. removeAbandonedTimeout  ：超过时间限制，回收没有用(废弃)的连接（默认为 300秒，调整为180）
11. removeAbandoned  ：超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收（默认为false，调整为true)

<property name="testWhileIdle"><value>true</value></property> <!-- 打开检查,用异步线程evict进行检查 -->
<property name="testOnBorrow"><value>false</value></property>
<property name="testOnReturn"><value>false</value></property>
<property name="validationQuery"><value>select sysdate from dual</value></property>
<property name="validationQueryTimeout"><value>1</value></property>
<property name="timeBetweenEvictionRunsMillis"><value>30000</value></property>
<property name="numTestsPerEvictionRun"><value>20</value></property>

public interface PoolableObjectFactory { Object makeObject() throws Exception; void destroyObject(Object obj) throws Exception; boolean validateObject(Object obj); void activateObject(Object obj) throws Exception; void passivateObject(Object obj) throws Exception;
}

public class PoolableConnectionFactory implements PoolableObjectFactory { ......
public boolean validateObject(Object obj) { //验证validateObject
if(obj instanceof Connection) {
try {
validateConnection((Connection) obj);
return true;
} catch(Exception e) {
return false;
}
} else {
return false;
}
}
public void validateConnection(Connection conn) throws SQLException {
String query = _validationQuery;
if(conn.isClosed()) {
throw new SQLException("validateConnection: connection closed");
}
if(null != query) {
Statement stmt = null;
ResultSet rset = null;
try {
stmt = conn.createStatement();
if (_validationQueryTimeout > 0) {
stmt.setQueryTimeout(_validationQueryTimeout);
}
rset = stmt.executeQuery(query);
if(!rset.next()) {
throw new SQLException("validationQuery didn't return a row");
}
} finally {
if (rset != null) {
try {
rset.close();
} catch(Exception t) {
// ignored
}
}
if (stmt != null) {
try {
stmt.close();
} catch(Exception t) {
// ignored
}
}
}
}
} .... }

protected synchronized void startEvictor(long delay) { //启动Evictor为TimerTask
if(null != _evictor) {
EvictionTimer.cancel(_evictor);
_evictor = null;
}
if(delay > 0) {
_evictor = new Evictor();
EvictionTimer.schedule(_evictor, delay, delay);
}
} for (int i=0,m=getNumTests();i<m;i++) {
final ObjectTimestampPair pair;
.......
boolean removeObject = false;
// 空闲链接处理
final long idleTimeMilis = System.currentTimeMillis() - pair.tstamp;
if ((getMinEvictableIdleTimeMillis() > 0) &&
(idleTimeMilis > getMinEvictableIdleTimeMillis())) {
removeObject = true;
} else if ((getSoftMinEvictableIdleTimeMillis() > 0) &&
(idleTimeMilis > getSoftMinEvictableIdleTimeMillis()) &&
((getNumIdle() + 1)> getMinIdle())) {
removeObject = true;
}
// testWhileIdle sql 检查处理
if(getTestWhileIdle() && !removeObject) {
boolean active = false;
try {
_factory.activateObject(pair.value);
active = true;
} catch(Exception e) {
removeObject=true;
}
if(active) {
if(!_factory.validateObject(pair.value)) {
removeObject=true;
} else {
try {
_factory.passivateObject(pair.value);
} catch(Exception e) {
removeObject=true;
}
}
}
}
// 真正关闭
if (removeObject) {
try {
_factory.destroyObject(pair.value);
} catch(Exception e) {
// ignored
}
}
........

timeoutTask = new CancelTask();
//通过TimerTask启动一定时任务
Connection.getCancelTimer().schedule(timeoutTask, this.timeoutInMillis);

class CancelTask extends TimerTask { long connectionId = 0; CancelTask() throws SQLException {
connectionId = connection.getIO().getThreadId();
} public void run() { Thread cancelThread = new Thread() { public void run() {
Connection cancelConn = null;
java.sql.Statement cancelStmt = null; try {
cancelConn = connection.duplicate();
cancelStmt = cancelConn.createStatement();
// 简单暴力，再发起一条KILL SQL，关闭先前的sql thread id
cancelStmt.execute("KILL QUERY " + connectionId);
wasCancelled = true;
} catch (SQLException sqlEx) {
throw new RuntimeException(sqlEx.toString());
} finally {
if (cancelStmt != null) {
try {
cancelStmt.close();
} catch (SQLException sqlEx) {
throw new RuntimeException(sqlEx.toString());
}
} if (cancelConn != null) {
try {
cancelConn.close();
} catch (SQLException sqlEx) {
throw new RuntimeException(sqlEx.toString());
}
}
}
}
}; cancelThread.start();
}
}

<application-policy name="EncryptDBPassword">
<authentication>
<login-module code="org.jboss.resource.security.SecureIdentityLoginModule" flag="required">
<module-option name="username">${username}</module-option>
<module-option name="password">${password_encrypt}</module-option>
<module-option name="managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=${jndiName}</module-option>
</login-module>
</authentication>
</application-policy>

private static String encode(String secret) throws NoSuchPaddingException, NoSuchAlgorithmException,
InvalidKeyException, BadPaddingException, IllegalBlockSizeException {
byte[] kbytes = "jaas is the way".getBytes();
SecretKeySpec key = new SecretKeySpec(kbytes, "Blowfish"); Cipher cipher = Cipher.getInstance("Blowfish");
cipher.init(Cipher.ENCRYPT_MODE, key);
byte[] encoding = cipher.doFinal(secret.getBytes());
BigInteger n = new BigInteger(encoding);
return n.toString(16);
} private static char[] decode(String secret) throws NoSuchPaddingException, NoSuchAlgorithmException,
InvalidKeyException, BadPaddingException, IllegalBlockSizeException {
byte[] kbytes = "jaas is the way".getBytes();
SecretKeySpec key = new SecretKeySpec(kbytes, "Blowfish"); BigInteger n = new BigInteger(secret, 16);
byte[] encoding = n.toByteArray(); Cipher cipher = Cipher.getInstance("Blowfish");
cipher.init(Cipher.DECRYPT_MODE, key);
byte[] decode = cipher.doFinal(encoding);
return new String(decode).toCharArray();
}

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
......
<property name="password"><!-- 注意多了一层转化，将密码串调用decode解密为最初的数据库密码 -->
<bean class="com.xxxxx.EncryptDBPasswordFactory">
<property name="password" value="${xxxx.password.encrypted}" />
</bean>
</property>
........
</bean>

mysql.user=root
mysql.password=root
mysql.connectionPool.maxActive=20
mysql.connectionPool.initialSize=4
mysql.connectionPool.minIdle=5
mysql.connectionPool.maxIdle=20
mysql.connectionPool.maxWait=60000
mysql.connectionPool.removeAbandoned=true