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>