Apache DBCP串連資料庫異常重連

標籤：apache   java   資料庫連接池   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();

                   }

                   //注意，這裡沒有關閉資料庫連接池，實際代碼不要這麼寫。

         }

}

這些代碼都不是正常的代碼，在實際工作中，不要這樣寫。

Apache DBCP串連資料庫異常重連