首页 > 代码库 > Apache DBCP连接数据库异常重连

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 = http://www.mamicode.com/rs.getString("sysdate");

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

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

                                    

                            }

                   }catch (Exception e) {

                            e.printStackTrace();

                   }

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

         }

}

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

Apache DBCP连接数据库异常重连