Apache DBCP连接数据库异常重连

         我09年负责的一个项目，有好几组P590小机提供服务，有一次，我们的小机居然集体宕机了，数据库不能服务后，所有的应用自然也没法提供服务。当数据库恢复后，所有的应用的数据库连接也是无效的，当时只有一一重启才生效。而出了问题的数据库，后续又频繁出一些小故障，因为应用的数据库连接是失效的，只能通过后续重启才能提供服务，导致应用又有了进一步的滞后性。因此后续就开始关注数据库重练。

下面这个应用场景，是用DBCP连接本地一个oracle数据库。启动应用后，把数据库关闭，然后又把数据库开启，查看connection是否能正常使用。

package test.ffm83.commons.dbcp;

import org.apache.commons.dbcp.BasicDataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import org.apache.commons.lang.StringUtils;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Connection;

import java.util.Properties;

/* 通过dbcp连接oracle数据库，模拟数据库连接成功后，数据库异常（关闭，宕机等情况）恢复

 * 使用1.4版本实现

 * @author 范芳铭

 * */

public class DbcpErrorConnection {

         privatestatic BasicDataSource dataSource = null;

         public DbcpErrorConnection(){

         }

         publicstatic void init() {

                   if(dataSource != null) {

                            try{

                                     dataSource.close();

                            }catch (Exception e) {

                                     e.printStackTrace();

                            }

                            dataSource= null;

                   }

                   try{

                            Propertiesp = new Properties();

                            p.setProperty("driverClassName","oracle.jdbc.driver.OracleDriver");

                            p.setProperty("url","jdbc:oracle:thin:@127.0.0.1:1522:orcl");

                            p.setProperty("password","sq");

                            p.setProperty("username","sq");

                            p.setProperty("maxActive","6"); //

                            p.setProperty("maxIdle","3");

                            p.setProperty("maxWait","10");

                            p.setProperty("removeAbandoned","true");// 移除不用的连接

                            p.setProperty("removeAbandonedTimeout","3");

                            p.setProperty("logAbandoned","true");

                            p.setProperty("testOnBorrow","true");

                            p.setProperty("testOnReturn","true");

                            p.setProperty("testWhileIdle","true");

                            p.setProperty("validationQuery"," select 1 from dual ");

                            dataSource= (BasicDataSource) BasicDataSourceFactory

                                               .createDataSource(p);

                   }catch (Exception e) {

                            e.printStackTrace();

                   }

         }

         publicstatic synchronized Connection getConnection() throws SQLException {

                   if(dataSource == null) {

                            init();

                   }

                   Connectionconn = null;

                   if(dataSource != null) {

                            conn= dataSource.getConnection();

                   }

                   returnconn;

         }

         publicstatic void main(String[] args) throws Exception {

                   for(int i = 0; i < 20; i++) {

                            try{

                                     getConAndNotClose(i+ 1); // 在异常放在循环中，不是个好习惯，这里只是为了模拟一些特殊情况

                                     Thread.sleep(8* 1000); // 停顿时间长一点，好停掉数据库

                            }catch (Exception e) {

                                     e.printStackTrace();

                            }

                   }

         }

         // 本方法使用数据库连接但是不释放，用来模拟连接池溢出情况

         privatestatic void getConAndNotClose(int id) throws Exception {

                   Connectioncon = null;

                   try{

                            con= DbcpErrorConnection.getConnection();

                            Stringsql = " select sysdate from dual ";

                            PreparedStatementps = con.prepareStatement(sql);

                            ResultSetrs = ps.executeQuery();

                            while(rs.next()) {

                                     Stringvalue = rs.getString("sysdate");

                                     System.out.println(StringUtils.center(value+ "," + id

                                                        +"数据库连接成功", 50, "-"));

                            }

                   }catch (Exception e) {

                            e.printStackTrace();

                   }

                   //注意，这里没有关闭数据库连接池，实际代码不要这么写。

         }

}

这些代码都不是正常的代码，在实际工作中，不要这样写。