JIVE论坛分析 － 简单数据库连接池设计

首先给出设计图

 

DataSourceConnectionProvider

 

    这个类就不用讲了，直接绑定JNDI数据源，执行代码是

 

Context context = new InitialContext(contextProperties);dataSource = (DataSource) context.lookup( name );

 

首先DataSource名字的绑定记得灵活配置放在XML或者Properties中。是重要的是运行InitialContext是要环境支持JNDI。

 

 

DbConnectionDefaultPool

 

呵呵，这个类者是重头戏。

第一步，基本的数据库配置。包括最大连接数，最小连接池，连接超时时间等。

第二步，先创建一个最小的连接的大小，代码段如下：

 

  try { for(int i=1; i < dbLoop; i++) { try { for(int j=0; j < currConnections; j++) { createConn(j); } connectionsSucceeded=true; break; } catch (SQLException e){ log.println("--->Attempt (" + String.valueOf(i) + " of " + String.valueOf(dbLoop) + ") failed to create new connections set at startup: "); log.println(" " + e); log.println(" Will try again in 15 seconds..."); try { Thread.sleep(15000); } catch(InterruptedException e1) {} } } if(!connectionsSucceeded) { // All attempts at connecting to db exhausted log.println("/r/nAll attempts at connecting to Database exhausted"); throw new IOException(); } } catch (Exception e) { e.printStackTrace(); throw new IOException(); }

private void createConn(int i) throws SQLException { Date now = new Date(); try { Class.forName (dbDriver); connPool[i] = DriverManager.getConnection (dbServer,dbLogin,dbPassword); connStatus[i]=0; connID[i]=connPool[i].toString(); connLockTime[i]=0; connCreateDate[i] = now.getTime(); log.println(now.toString() + " Opening connection " + String.valueOf(i) + " " + connPool[i].toString() + ":"); } catch (ClassNotFoundException e2) { e2.printStackTrace(); throw new SQLException(e2.getMessage()); } }

 

这边要说明一个就是for currConnections这年循环就是创建初始化的连接数了，但是，为什么还要再加上一个外循环for dbLoop呢。

因为数据库当中可能出现已经连接满了，我们创建不了所要初始化的连接数，比如说3，当你创建了2个后，此时数据库连接已满，或者此时网络堵塞，你可能连接超时，创建不了刚刚好的3个连接，于是就会抛出异常,解决的办法是先等待一下。再次进入循环尝试下次的连接。

数据库连接。

这边地方要说一下关于创建时间这边有一个建议就是写一个Timer。每一秒去执行一次获取系统时间。实际上执行

System.currentTimeMillis()是比较消耗资源的。像这边需要轮询如果经常要获取系统时间。允许时间差的情况下。建议使用Timer去更新系统时间。

上面代码段是关于数据库连接池的创建的。数据库连接池的调用哪下

public Connection getConnection() { Connection conn=null; if(available){ boolean gotOne = false; for(int outerloop=1; outerloop<=10; outerloop++) { try { int loop=0; int roundRobin = connLast + 1; if(roundRobin >= currConnections) roundRobin=0; do { synchronized(connStatus) { if((connStatus[roundRobin] < 1) && (! connPool[roundRobin].isClosed())) { conn = connPool[roundRobin]; connStatus[roundRobin]=1; connLockTime[roundRobin] = System.currentTimeMillis(); connLast = roundRobin; gotOne = true; break; } else { loop++; roundRobin++; if(roundRobin >= currConnections) roundRobin=0; } } } while((gotOne==false)&&(loop < currConnections)); } catch (SQLException e1) {} if(gotOne) { break; } else { synchronized(this) { // Add new connections to the pool if(currConnections < maxConns) { try { createConn(currConnections); currConnections++; } catch(SQLException e) { log.println("Unable to create new connection: " + e); } } } try { Thread.sleep(2000); } catch(InterruptedException e) {} log.println("-----> Connections Exhausted! Will wait and try " + "again in loop " + String.valueOf(outerloop)); } } // End of try 10 times loop } else { log.println("Unsuccessful getConnection() request during destroy()"); } // End if(available) return conn; }

 

connLast这个变量是用来计算上次取得连接的位置。因为这边是用数据库连接池缓存对象是数组。这边有一个for outerloop主要是考虑一个情况，你所创建的所有连接都被使用，并且无法再次创建新连接的时候怎么办。这个时候就要考虑一下使用外循环去尝试一下连接。

 

再接下来是轮询数据库连接池，检测状态，将使用过长的连接要去掉。代码如下：

while(forever) { // Make sure the log file is the one this instance opened // If not, clean it up! try { BufferedReader in = new BufferedReader(new FileReader(logFileString + "pid")); String curr_pid = in.readLine(); if(curr_pid.equals(pid)) { //log.println("They match = " + curr_pid); } else { //log.println("No match = " + curr_pid); log.close(); // Close all connections silently - they are definitely dead. for(int i=0; i < currConnections; i++) { try { connPool[i].close(); } catch (SQLException e1) {} // ignore } // Returning from the run() method kills the thread return; } in.close(); } catch (IOException e1) { log.println("Can't read the file for pid info: " + logFileString + "pid"); } // Get any Warnings on connections and print to event file for(int i=0; i < currConnections; i++) { try { currSQLWarning = connPool[i].getWarnings(); if(currSQLWarning != null) { log.println("Warnings on connection " + String.valueOf(i) + " " + currSQLWarning); connPool[i].clearWarnings(); } } catch(SQLException e) { log.println("Cannot access Warnings: " + e); } } for(int i=0; i < currConnections; i++) { // Do for each connection long age = System.currentTimeMillis() - connCreateDate[i]; synchronized(connStatus) { if(connStatus[i] > 0) { // In use, catch it next time! continue; } connStatus[i] = 2; // Take offline (2 indicates housekeeping lock) } try { // Test the connection with createStatement call if(age > maxConnMSec) { // Force a reset at the max conn time throw new SQLException(); } stmt = connPool[i].createStatement(); connStatus[i] = 0; // Connection is O.K. //log.println("Connection confirmed for conn = " + // String.valueOf(i)); // Some DBs return an object even if DB is shut down if(connPool[i].isClosed()) { throw new SQLException(); } // Connection has a problem, restart it } catch(SQLException e) { try { log.println(new Date().toString() + " ***** Recycling connection " + String.valueOf(i) + ":" ); connPool[i].close(); createConn(i); } catch(SQLException e1) { log.println("Failed: " + e1); connStatus[i] = 0; // Can't open, try again next time } } finally { try { if(stmt != null) { stmt.close(); } } catch(SQLException e1){}; } } try { Thread.sleep(20000); } // Wait 20 seconds for next cycle catch(InterruptedException e) { // Returning from the run method sets the internal // flag referenced by Thread.isAlive() to false. // This is required because we don't use stop() to // shutdown this thread. return; } }

这边学习还是那个时间计算问题。最好写个Timer去更新时间，并将那个时间做为系统时间。

这边用了代码connPool[i].createStatement()去判定系统的连接正常执行，connPool[i].isClosed()这边多了个注释就是

// Some DBs return an object even if DB is shut down

这一句还不是很明白他的意思

 

这边使用ConnectionWrapper这个类去代理数据库的close事件。继承代理的效率可要比动态代理的效率高多了。