java的mysql连接池
可从官网下载最新版本http://proxool.sourceforge.net
让Proxool连接池支持自动重连接
项目使用的连接池是Proxool，有一个问题，如果连接池在运行当中，出现网络或者数据库故障而无法连接到数据库，在恢复正常以后，由于连接是在连接池中持久保存的，会出现连接仍然不可用的情况，这时连接池里的连接实际上都是坏连接，怎么让连接池可以自动重连清除这些坏连接呢？
让Proxool连接池支持自动重连接
其实只要配置了test-before-use 参数，即每次取出连接都检查连接是否可用，就可以做到让连接池实现在故障恢复后自动重连接。实例配置文件如下：
<proxool>
<alias>pool</alias>
<driver-url> jdbc:mysql://localhost:3306/cds </driver-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<driver-properties>
<property name="user" value="vcom" />
<property name="password" value="vcom" />
<property name="autoReconnect" value="true" />
</driver-properties>
<minimum-connection-count>1</minimum-connection-count>
<maximum-connection-count>8</maximum-connection-count>
<prototype-count>1</prototype-count>
<test-before-use>true</test-before-use>
<house-keeping-sleep-time>60000</house-keeping-sleep-time>
<house-keeping-test-sql>select CURRENT_DATE from dual</house-keeping-test-sql>
< /proxool>
需要注意一点，对于Mysql数据库还必须在连接参数里加上autoReconnect=true 参数，否则即使打开了test-before-use 参数，仍然不能重连接！
===========================================
首先在src目录下创建属性配置文件
proxool.properties
jdbc-0.proxool.alias=test
jdbc-0.proxool.driver-url=jdbc:mysql://localhost:3306/test
jdbc-0.proxool.driver-class=com.mysql.jdbc.Driver
jdbc-0.user=root
jdbc-0.password=coship
jdbc-0.proxool.house-keeping-test-sql=select *
jdbc-0.proxool.maximum-connection-count=10
jdbc-0.proxool.minimum-connection-count=5
在工程中导入一下几个包
proxool-0.9.1.jar
proxool-cglib.jar
commons-logging.jar
mysql-connector-java-5.1.6-bin.jar
编写下面的应用程序
package test.pool;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.ResourceBundle;
import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.configuration.PropertyConfigurator;
public class mysqlcon {
/**
* @param args
*/
public static void main(String[] args) {
Connection conn = null;
Statement sta=null;
ResultSet rs=null;
String sql="insert into test values ('a','a')";
try {
try {
//PropertyConfigurator.configure(mysqlcon.class.getResource("/")+"proxool.properties");
Properties pro=new Properties();
ResourceBundle resource = ResourceBundle.getBundle("proxool");
pro.put("jdbc-0.proxool.alias",resource.getString("jdbc-0.proxool.alias"));
pro.put("jdbc-0.proxool.driver-url",resource.getString("jdbc-0.proxool.driver-url"));
pro.put("jdbc-0.proxool.driver-class",resource.getString("jdbc-0.proxool.driver-class"));
pro.put("jdbc-0.user",resource.getString("jdbc-0.user"));
pro.put("jdbc-0.password",resource.getString("jdbc-0.password"));
pro.put("jdbc-0.proxool.house-keeping-test-sql",resource.getString("jdbc-0.proxool.house-keeping-test-sql"));
pro.put("jdbc-0.proxool.maximum-connection-count",resource.getString("jdbc-0.proxool.maximum-connection-count"));
pro.put("jdbc-0.proxool.minimum-connection-count",resource.getString("jdbc-0.proxool.minimum-connection-count"));
PropertyConfigurator.configure(pro);
} catch (ProxoolException e) {
e.printStackTrace();
}
conn = DriverManager.getConnection("proxool.test");
sta=conn.createStatement();
rs=sta.executeQuery("select * from test");
while(rs.next()) {
System.out.println(rs.getString("name")+" "+rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
rs.close();
sta.close();
//conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
======================================
<?xml version="1.0" encoding="UTF-8"?>
<!--
the proxool configuration can be embedded within your own
application's. Anything outside the "proxool" tag is ignored.
-->
<something-else-entirely>
<proxool>
<alias>dbname</alias> <!--数据源的别名-->
<driver-url>jdbc:oracle:thin:@127.0.0.1:1521:testdb</driver-url><!--url连接串-->
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class> <!--驱动类-->
<driver-properties>
<property name="user" value="username" /> <!--用户名-->
<property name="password" value="password" /><!--密码-->
</driver-properties>
<!--最大连接数(默认5个),超过了这个连接数,再有请求时,就排在队列中等候,最大的等待请求数由maximum-new-connections决定 -->
<maximum-connection-count>100</maximum-connection-count>
<!--最小连接数(默认2个)-->
<minimum-connection-count>10</minimum-connection-count>
<!--proxool自动侦察各个连接状态的时间间隔(毫秒),侦察到空闲的连接就马上回收,超时的销毁 默认30秒-->
<house-keeping-sleep-time>90000</house-keeping-sleep-time>
<!--没有空闲连接可以分配而在队列中等候的最大请求数,超过这个请求数的用户连接就不会被接受-->
<maximum-new-connections>10</maximum-new-connections>
<!--最少保持的空闲连接数(默认2个)-->
<prototype-count>5</prototype-count>
<!--在使用之前测试-->
<test-before-use>true</test-before-use>
<!--用于保持连接的测试语句 -->
<house-keeping-test-sql>select sysdate from dual</house-keeping-test-sql>
</proxool>
< /something-else-entirely>
