面试: 手写Oracle数据库连接池

Question

上周五去一家公司面试, 要求手工写oracle数据库连接池, 哪位高手能否指点一下, 怎么写? PS. 大家在项目中, 数据库连接是采用什么方式的? 加载驱动的JDBC, 还是数据库连接池多?

Answer 1

我写过一个，用一个list保存连接，关键是用代理重写close方法这公司出这种题，有点意思

Answer 2

手写Oracle数据库连接池 ?上来先连接20个，放在哈希表里慢慢用。
就行了。

Answer 3

“用代理重写close方法 ”？
重写con的close方法喽？可是用户代码用的肯定是java.sql.connection
怎么做到用户代码调用connection.close（）时肯定执行的是你定义的close（）？

Answer 4

java.sql.connection 只是一个接口，真正的实现是由各数据库厂商实现的。
是从DriverManager取出来的
有了接口，有了实现，就可以用代理了，把close重写成归还连接既可。
帖出来就是一大篇，不过我可以说下我的思路，用BlockedLinkedQueue实现一个池，WeakHashMap引用到所有的使用中连接，后台一个线程自动扫描当前的总连接数量，自动的缓慢增加到连接池的最大容量。因为用了BlockedLinkedQueue,所以归还连接和取连接都是阻塞模式的。当然归还一般不会遇到阻塞，取连接操作在连接池为空的时候会阻塞。
有了思路，自己写一个难度其实也不是很大。那天有空在博客里写一篇详细的实现方式吧。

Answer 5

让你手写的一般都是那个用JDBC连接的方法
public Connection CreateConnection()
{
  try
  {
   Connection con = null ;
   String connName = "jdbc:oracle:thin:@192.168.0.10:1521:dbname";
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con = DriverManager.getConnection(connName, "username","password");
   
   if(con!=null)
   {
    System.out.println("-------------连接成功！！！") ;
   }else{
    
    System.out.println("----------------连接失败！！！") ;
   }
   return con ;
  }catch(Exception e)
  {
   e.printStackTrace() ;
  }
  return null ;
  
}

Answer 6

import java.sql.*;
import javax.naming.Context;
import javax.naming.NamingException;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import com.aegon_cnooc.pub.Log4jInitServlet;public class ConnectionPool {    public static final String DATA_SOURCE = "java:comp/env/jdbc/aegonDB";     public static Connection getConnection()  {         Connection retval = null;
         String driverName = "oracle.jdbc.driver.OracleDriver";
         String connectionString = "jdbc:oracle:thin:@10.72.1.9:1522:ffv2dev2";
         String userName   = "uatusr";
         String password = "123456";
         //上面四行的参数改下
         try {             Class.forName(driverName);
             retval = DriverManager.getConnection(
                connectionString, userName, password);
          }
          catch (ClassNotFoundException e) {
              System.err.println("DB driver not fount");
          }
          catch (SQLException e) {
              System.err.println("Can't connect to server");
          }          return retval;
     }
    /**
   * Close a connection.
   * @param conn Pass the connection object to be closed.
   */
    public static void releaseConnection(Connection conn){        if (conn != null) {            try {
                conn.close();
            }
            catch (SQLException e) {
                LoggerUtils.recordError(Constants.AES_WEB_LOG,
                                "releaseConnection Error: " + e.toString());
            }
        }
    }
}
大概是这样  复杂点的到容器里面去做吧

Answer 7

/*
 * Created on 2003-12-23
 *
 * Copyright (c) 2003. Waboo Tech. All Rights Reserved.
 */
package com.eip.dbpool;import java.sql.*;
import javax.sql.*;
import java.lang.reflect.*;/**
 * 数据连接的代理类
 * @author BinHao
 */
class _Connection implements InvocationHandler
{
        private final static String CLOSE_METHOD_NAME = "close";
        private Connection conn = null;
        //数据库的忙状态
        private boolean inUse = false;
        //数据库连接的没有失效的状态
        private boolean isGood = true;
        //数据库连接在返回时是否要进行自我检查
        private boolean isCheck=false;
        //数据库连接在返回时的进行自我检查的SQL串
        private String checkSql=null;
        //用户最后一次访问该连接方法的时间
        private long lastAccessTime = System.currentTimeMillis();
        private boolean supportTransaction = true;
        private boolean coding = false;        _Connection(Connection conn, boolean inUse, boolean coding,boolean isCheck,String checkSql){
                this.conn = conn;
                this.inUse = inUse;
                this.coding = coding;
                this.isCheck = isCheck;
                this.checkSql = checkSql;
                initConnectionParam(this.conn);
        }
        /**
         * 判断数据库是否支持事务操作
         * @param conn
         */
        private void initConnectionParam(Connection conn){
                DatabaseMetaData dm = null;
                try{
                        dm = conn.getMetaData();
                        supportTransaction = dm.supportsTransactions();
                }catch(Exception e){}
        }        /**
         * Returns the conn.
         * @return Connection
         */
        public Connection getConnection() {
                Class[] interfaces = conn.getClass().getInterfaces();
                if(interfaces==null||interfaces.length==0)
                        interfaces = new Class[]{Connection.class};                Connection conn2 = (Connection)Proxy.newProxyInstance(
                        conn.getClass().getClassLoader(),
                        interfaces,this);
                return conn2;
        }
        /**
         * 该方法真正的关闭了数据库的连接
         * @throws SQLException
         */
        void close() throws SQLException{
                conn.close();
        }
        /**
         * Returns the inUse.
         * @return boolean
         */
        public boolean isInUse() {
                return inUse;
        }        /**
         * Returns the isGood.
         * @return boolean
         */
        public boolean isGood() {
                return isGood;
        }
        /**
         * @see java.lang.reflect.InvocationHandler#invoke(java.lang.Object, java.lang.reflect.Method, java.lang.Object)
         */
        public Object invoke(Object proxy, Method m, Object[] args)
                throws Throwable
        {
                String method = m.getName();                if(CLOSE_METHOD_NAME.equals(method)){
                        setInUse(false);
                        checkConn();
                        return null;
                }
                //更新最近一次访问时间
                lastAccessTime = System.currentTimeMillis();
                //如果数据库不支持事务，则屏蔽任何关于事务的操作
                if((M_SETAUTOCOMMIT.equals(method) ||
                        M_COMMIT.equals(method) ||
                        M_ROLLBACK.equals(method)) &&
                        !isSupportTransaction())
                        return null;
                //调用相应的操作
                Object obj = null;
                try{
                        obj = m.invoke(conn, args);
                        if((CREATESTATEMENT.equals(method)||PREPAREDSTATEMENT.equals(method))&&coding)
                                return new _Statement((Statement)obj,true).getStatement();
                }catch(InvocationTargetException e){
                        throw e.getTargetException();
                }
                return obj;
        }        /**
         * Returns the lastAccessTime.
         * @return long
         */
        public long getLastAccessTime() {
                return lastAccessTime;
        }        /**
         * Sets the inUse.
         * @param inUse The inUse to set
         */
        public void setInUse(boolean inUse) {
                this.inUse = inUse;
        }        /**
         * Returns the supportTransaction.
         * @return boolean
         */
        public boolean isSupportTransaction() {
                return supportTransaction;
        }        /**
         * check the connect.         *
         */
        public void checkConn(){
          if (isCheck){
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {              //查询
              ps = conn.prepareStatement(checkSql);
              rs = ps.executeQuery();
              //System.out.println("该连接检查正常");
            }
            catch (Exception ex) {
              //确认这连接是失效的
              this.isGood = false;
              //System.out.println("该连接为失效的");
            }
            finally {
              try {
                if(rs!=null)
                  rs.close();
                if(ps!=null)
                  ps.close();
              }
              catch (Exception ex) {
              }            }
          }
        }
        private final static String PREPAREDSTATEMENT = "prepareStatement";
        private final static String CREATESTATEMENT = "createStatement";
        private final static String M_SETAUTOCOMMIT = "setAutoCommit";
        private final static String M_COMMIT = "commit";
        private final static String M_ROLLBACK = "rollback";}

Answer 8

/*
 * Created on 2003-10-23
 *
 * Copyright (c) 2003. Waboo Tech. All Rights Reserved.
 */
package com.eip.dbpool;import java.sql.*;
import java.lang.reflect.*;/**
 * 数据库语句的代理类
 * @author BinHao
 */
class _Statement implements InvocationHandler, _Base
{
        private Statement statement ; //保存所接管对象的实例
        private boolean decode = false; //指定是否进行字符串转码        public _Statement(Statement stmt,boolean decode) {
                this.statement = stmt;
                this.decode = decode;
        }
        /**
         * 获取一个接管后的对象实例
         * @return
         */
        public Statement getStatement() {
                Class[] interfaces = statement.getClass().getInterfaces();
                if(interfaces==null||interfaces.length==0)
                        interfaces = new Class[]{Statement.class};                Statement stmt = (Statement)Proxy.newProxyInstance(
                        statement.getClass().getClassLoader(),
                        interfaces,this);
                return stmt;
        }
        /**
         * 方法接管
         */
        public Object invoke(Object proxy, Method m, Object[] args)
                throws Throwable {
                String method = m.getName();
                //接管setString方法
                if(decode && SETSTRING.equals(method)){
                        try{
                                String param = (String)args[1];
                                if(param!=null)
                                        param = new String(param.getBytes(),DECODE);
                                return m.invoke(statement,new Object[]{args[0],param});
                        }catch(InvocationTargetException e){
                                throw e.getTargetException();
                        }
                }
                //接管executeQuery方法
                if(decode && EXECUTEQUERY.equals(method)){
                        try{
                                ResultSet rs = (ResultSet)m.invoke(statement,args);
                                return new _ResultSet(rs,decode).getResultSet();
                        }catch(InvocationTargetException e){
                                throw e.getTargetException();
                        }
                }
                try{
                        return m.invoke(statement, args);
                }catch(InvocationTargetException e){
                        throw e.getTargetException();
                }
        }
        //两个要接管的方法名
        private final static String SETSTRING = "setString";
        private final static String EXECUTEQUERY = "executeQuery";
}

Answer 9

package com.eip.dbpool;import java.sql.SQLException;
import java.util.Hashtable;import javax.naming.NameAlreadyBoundException;
import javax.naming.NameNotFoundException;
import javax.sql.DataSource;/**
 * 连接池类厂，该类用来保存多个数据源名称和数据库连接池对应的哈希
 * 是使用连接池的入口
 * @author BinHao
 */
public class ConnectionFactory {
        //该哈希表用来保存数据源名和连接池对象的关系表
        static Hashtable connectionPools = null;
        static {
                connectionPools = new Hashtable(5, 0.75F);
        }
        /**
         * 从连接池工厂中获取指定名称对应的连接池对象
         * @param dataSource 连接池对象对应的名称
         * @return DataSource 返回名称对应的连接池对象
         * @throws NameNotFoundException 无法找到指定的连接池
         */
        public static DataSource lookup(String dataSource)
                throws NameNotFoundException {
                Object ds = null;
                ds = connectionPools.get(dataSource);
                if (ds == null || !(ds instanceof DataSource))
                        throw new NameNotFoundException(dataSource);
                return (DataSource) ds;
        }        /**
         * 将指定的名字和数据库连接配置绑定在一起并初始化数据库连接池
         * @param name 对应连接池的名称
         * @param param 连接池的配置参数，具体请见类ConnectionParam
         * @return DataSource 如果绑定成功后返回连接池对象
         * @throws NameAlreadyBoundException 一定名字name已经绑定则抛出该异常
         * @throws ClassNotFoundException 无法找到连接池的配置中的驱动程序类
         * @throws IllegalAccessException 连接池配置中的驱动程序类有误
         * @throws InstantiationException 无法实例化驱动程序类
         * @throws SQLException 无法正常连接指定的数据库
         */
        public static DataSource bind(String name, ConnectionParam param)
                throws
                        NameAlreadyBoundException,
                        ClassNotFoundException,
                        IllegalAccessException,
                        InstantiationException,
                        SQLException {
                DataSourceImpl source = null;
                try {
                        lookup(name);
                        throw new NameAlreadyBoundException(name);
                } catch (NameNotFoundException e) {
                        source = new DataSourceImpl(param);
                        source.initConnection();
                        connectionPools.put(name, source);
                }
                return source;
        }
        /**
         * 重新绑定数据库连接池
         * @param name 对应连接池的名称
         * @param param 连接池的配置参数，具体请见类ConnectionParam
         * @return DataSource 如果绑定成功后返回连接池对象
         * @throws NameAlreadyBoundException 一定名字name已经绑定则抛出该异常
         * @throws ClassNotFoundException 无法找到连接池的配置中的驱动程序类
         * @throws IllegalAccessException 连接池配置中的驱动程序类有误
         * @throws InstantiationException 无法实例化驱动程序类
         * @throws SQLException 无法正常连接指定的数据库
         */
        public static DataSource rebind(String name, ConnectionParam param)
                throws
                        NameAlreadyBoundException,
                        ClassNotFoundException,
                        IllegalAccessException,
                        InstantiationException,
                        SQLException {
                try {
                        unbind(name);
                } catch (Exception e) {
                }
                return bind(name, param);
        }
        /**
         * 删除一个数据库连接池对象
         * @param name
         * @throws NameNotFoundException
         */
        public static void unbind(String name) throws NameNotFoundException {
                DataSource dataSource = lookup(name);
                if (dataSource instanceof DataSourceImpl) {
                        DataSourceImpl dsi = (DataSourceImpl) dataSource;
                        dsi.stop();
                        try{
                                dsi.close();
                        }catch(SQLException e){}
                        dsi = null;
                }
                connectionPools.remove(name);
        }}

Answer 10

package com.eip.dbpool;import java.io.Serializable;/**
 * 数据库的初始化参数
 * @author BinHao
 */
public class ConnectionParam implements Serializable
{
        private String driver; //数据库驱动程序
        private String url; //数据连接的URL
        private String user; //数据库用户名
        private String password; //数据库密码
        private int minConnection = 0; //初始化连接数
        private int maxConnection = 50; //最大连接数
        private long timeoutValue = 60000; //连接的最大空闲时间
        private long waitTime = 80000; //取连接的时候如果没有可用连接最大的等待时间
        private boolean coding = false; //是否对字符串数据进行编码和解码
        private boolean Checkdb=true;           //设置是否检查数据库连接池中失效的连接
        private String Checksql="select '1' from tab"; //设置检查失效连接的SQL语句        public ConnectionParam(){
                this(null,null,null,null);
        }
        public ConnectionParam(String driver,String url,String user,String password)
        {
                this.driver = driver;
                this.url = url;
                this.user = user;
                this.password = password;
        }
        /**
         * Returns the driver.
         * @return String
         */
        public String getDriver() {
                return driver;
        }        /**
         * Returns the password.
         * @return String
         */
        public String getPassword() {
                return password;
        }        /**
         * Returns the url.
         * @return String
         */
        public String getUrl() {
                return url;
        }        /**
         * Returns the user.
         * @return String
         */
        public String getUser() {
                return user;
        }        /**
         * Sets the driver.
         * @param driver The driver to set
         */
        public void setDriver(String driver) {
                this.driver = driver;
        }        /**
         * Sets the password.
         * @param password The password to set
         */
        public void setPassword(String password) {
                this.password = password;
        }        /**
         * Sets the url.
         * @param url The url to set
         */
        public void setUrl(String url) {
                this.url = url;
        }        /**
         * Sets the user.
         * @param user The user to set
         */
        public void setUser(String user) {
                this.user = user;
        }        /**
         * Returns the maxConnection.
         * @return int
         */
        public int getMaxConnection() {
                return maxConnection;
        }        /**
         * Returns the minConnection.
         * @return int
         */
        public int getMinConnection() {
                return minConnection;
        }        /**
         * Returns the timeoutValue.
         * @return int
         */
        public long getTimeoutValue() {
                return timeoutValue;
        }        /**
         * Sets the maxConnection.
         * @param maxConnection The maxConnection to set
         */
        public void setMaxConnection(int maxConnection) {
                this.maxConnection = maxConnection;
        }        /**
         * Sets the minConnection.
         * @param minConnection The minConnection to set
         */
        public void setMinConnection(int minConnection) {
                this.minConnection = minConnection;
        }        /**
         * Returns the waitTime.
         * @return long
         */
        public long getWaitTime() {
                return waitTime;
        }        /**
         * Sets the timeoutValue.
         * @param timeoutValue The timeoutValue to set
         */
        public void setTimeoutValue(long timeoutValue) {
                this.timeoutValue = timeoutValue;
        }        /**
         * Sets the waitTime.
         * @param waitTime The waitTime to set
         */
        public void setWaitTime(long waitTime) {
                this.waitTime = waitTime;
        }        public boolean isCoding() {
                return coding;
        }        public void setCoding(boolean coding) {
                this.coding = coding;
        }        /**
         * /**
          * Sets the checkdb
          * @param checkdb The checkdb to set
        */       public void setCheckdb(boolean checkdb){
                 this.Checkdb=checkdb;
       }      /**
        * /**
         * Gets the checkdb
         * @return checkdb The checkdb to get
       */      public boolean getCheckdb(){
                return Checkdb;
      }     /**
       * /**
        * Sets the checksql
        * @param checksql The checksql to set
      */     public void setChecksql(String checksql){
               this.Checksql=checksql;
     }    /**
      * /**
       * Gets the checksql
       * @return checksql The checksql to get
     */    public String getChecksql(){
             return Checksql;
    }        /**
         * @see java.lang.Object#clone()
         */
        public Object clone(){
                ConnectionParam param = new ConnectionParam(driver,url,user,password);
                param.setMaxConnection(maxConnection);
                param.setMinConnection(minConnection);
                param.setTimeoutValue(timeoutValue);
                param.setWaitTime(waitTime);
                return param;
        }        /**
         * @see java.lang.Object#equals(java.lang.Object)
         */
        public boolean equals(Object obj) {
                if(obj instanceof ConnectionParam){
                        ConnectionParam param = (ConnectionParam)obj;
                        return compare(driver, param.getDriver()) &&
                                compare(url, param.getUrl()) &&
                                compare(user, param.getUser()) &&
                                compare(password, param.getPassword());
                }
                return false;
        }
        /**
         * 比较两个对象是否相等
         * @param obj1
         * @param obj2
         * @return
         */
        private static boolean compare(Object obj1, Object obj2){
                boolean bResult = false;
                if(obj1 != null)
                        bResult = obj1.equals(obj2);
                else if(obj2 !=null)
                        bResult = obj2.equals(obj1);
                else
                        bResult = true;
                return bResult;
        }
}

Answer 11

/*
 * Created on 2003-12-23
 *
 * Copyright (c) 2003. Waboo Tech. All Rights Reserved.
 */
package com.eip.dbpool;import java.sql.ResultSet;import java.lang.reflect.InvocationHandler;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;/**
 * 数据库结果集的代理类
 * @author BinHao
 */
class _ResultSet implements InvocationHandler, _Base
{
        private ResultSet rs = null;
        private boolean decode = false;        public _ResultSet(ResultSet rs,boolean decode) {
                this.rs = rs;
                this.decode = decode;
        }        public ResultSet getResultSet(){
                Class[] interfaces = rs.getClass().getInterfaces();
                if(interfaces==null||interfaces.length==0)
                        interfaces = new Class[]{ResultSet.class};                ResultSet rs2 = (ResultSet)Proxy.newProxyInstance(
                        rs.getClass().getClassLoader(),
                        interfaces,this);
                return rs2;
        }        /**
         * 结果getString方法
         */
        public Object invoke(Object proxy, Method m, Object[] args) throws Throwable
        {
                String method = m.getName();
                if(decode && GETSTRING.equals(method)){
                        try{
                                String result = (String)m.invoke(rs,args);
                                if(result!=null)
                                        return new String(result.getBytes(DECODE));
                                return null;
                        }catch(InvocationTargetException e){
                                throw e.getTargetException();
                        }
                }
                try{
                        return m.invoke(rs, args);
                }catch(InvocationTargetException e){
                        throw e.getTargetException();
                }
        }
        private final static String GETSTRING = "getString";
}

Answer 12

/*
 * Created on 2003-12-23
 *
 * Copyright (c) 2003. Waboo Tech. All Rights Reserved.
 */
package com.eip.dbpool;/**
 * 所有代理对象必须实现的接口，定义了一些常量
 * @author BinHao
 */
public interface _Base {        public final static String DECODE = "8859_1";}

Answer 13

package com.eip.dbpool;import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Iterator;
import javax.sql.DataSource;/**
 * 对应一个数据库连接池的数据源定义。
 * 同一个进程中对于同一个connParam应该保证只有一个DataSourceImpl的实例
 * @author BinHao
 */
public class DataSourceImpl implements DataSource, Runnable
{
        ConnectionParam connParam; //数据库连接的详细参数信息
        HashSet conns; //连接池对象：存放所有的数据库连接
        boolean bStop = false; //是否停止监控线程
        long checkInterval = 1000L; //监控线程的监控间隔
        private Thread hMonitor = null; //监控线程的对象
        private Driver dbDriver = null; //数据库驱动程序缓存，用于注销驱动        /**
         * 构造连接池所必须的参数
         * @param connParam
         */
        DataSourceImpl(ConnectionParam connParam) {
                this.connParam = connParam;
                conns = new HashSet(connParam.getMaxConnection());
                hMonitor = new Thread(this);
                hMonitor.start();
        }
        /**
         * 初始化连接参数,加载数据库驱动程序并初始化最小连接数
         * @throws SQLException 初始化最小连接数所产生的异常
         * @throws ClassNotFoundException 驱动程序没找到的异常
         * @throws IllegalAccessException 非法驱动程序
         * @throws InstantiationException 非法驱动程序
         */
        synchronized void initConnection()
                throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException
        {                dbDriver = (Driver)Class.forName(connParam.getDriver()).newInstance();                int min = Math.min(connParam.getMinConnection(),connParam.getMaxConnection());
                if(min > 0){
                        Connection[] conns = new Connection[min];
                        for(int i=0;i<min;i++)
                                conns[i] = getConnection();
                        for(int i=0;i<min;i++)
                                conns[i].close();
                        conns= null;
                }
        }
        /**
         * 关闭该连接池中的所有数据库连接
         * @return int 返回被关闭连接的个数
         * @throws SQLException 关闭连接时发送的数据库异常
         */
        synchronized int close() throws SQLException
        {
                int cc = 0;
                SQLException excp = null;
                Iterator iter = conns.iterator();
                while(iter.hasNext()){
                        try{
                                ((_Connection)iter.next()).close();
                                cc ++;
                        }catch(Exception e){
                                if(e instanceof SQLException)
                                        excp = (SQLException)e;
                        }
                }
                //取消数据库驱动程序的登记
                DriverManager.deregisterDriver(dbDriver);
                if(excp != null)
                        throw excp;
                return cc;
        }
        /**
         * 停止对连接池中各个连接监控的线程
         */
        synchronized void stop(){
                bStop = true;
                //停止监控线程
                try{
                        hMonitor.join(checkInterval, 100);
                }catch(Exception e){
                }finally{
                        if(hMonitor.isAlive()){
                                hMonitor.interrupt();
                                hMonitor = null;
                        }
                }
        }
        /**
         * @see javax.sql.DataSource#getConnection(java.lang.String, java.lang.String)
         */
        public synchronized Connection getConnection(String user, String password)
                throws SQLException
        {
                Connection conn = null;
                //首先从连接池中找出空闲的对象
                conn = getFreeConnection(0);
                if(conn == null){
                        //判断是否超过最大连接数,如果超过最大连接数
                        //则等待一定时间查看是否有空闲连接,否则抛出异常告诉用户无可用连接
                        if(getConnectionCount() >= connParam.getMaxConnection()){
                                conn = getFreeConnection(connParam.getWaitTime());
                        }
                        else{
                                //没有超过连接数，重新获取一个数据库的连接
                                connParam.setUser(user);
                                connParam.setPassword(password);                                Connection conn2 = DriverManager.getConnection(connParam.getUrl(),user, password);
                                //判断数据库是否支持事务                                _Connection _conn = new _Connection(conn2,true,connParam.isCoding(),connParam.getCheckdb(),connParam.getChecksql());
                                //返回数据库连接的代理
                                conn = _conn.getConnection();
                                conns.add(_conn);
                        }
                }
                return conn;
        }
        /**
         * 从连接池中取一个空闲的连接
         * @param nTimeout 如果该参数值为0则没有连接时只是返回一个null
         * 否则的话等待nTimeout毫秒看是否还有空闲连接，如果没有抛出异常
         * @return Connection 返回可用的数据库连接
         * @throws SQLException 数据库异常
         */
        Connection getFreeConnection(long nTimeout)
                throws SQLException
        {
                Connection conn = null;
                Iterator iter = conns.iterator();
                while(iter.hasNext()){
                        _Connection _conn = (_Connection)iter.next();
                        if(!_conn.isInUse()){
                                //判断当前数据库连接是否失效
                                if(!_conn.isGood()){
                                  try {
                                    //如果失效就尝试关闭回收资源
                                    _conn.close();
                                  }
                                  catch (Exception ex) {                                  }
                                  finally {
                                    //从池中删除这个失效的数据库连接对象
                                    iter.remove();
                                  }
                                  continue;
                                }
                                conn = _conn.getConnection();
                                //如果数据库联机因为某种原因而关闭则删除其所在连接中的对象
                                if(conn.isClosed()){
                                        iter.remove();
                                        continue;
                                }
                                _conn.setInUse(true);
                                break;
                        }
                }
                if(conn == null && nTimeout > 0){
                        //等待nTimeout毫秒以便看是否有空闲连接
                        int COUNT = 30;
                        int i=0;
                        do{
                                try{
                                        Thread.sleep(nTimeout / COUNT);
                                }catch(Exception e){}
                                conn = getFreeConnection(0);
                                i++;
                        }while(conn==null && i<COUNT);
                        if(conn == null)
                                throw new SQLException("没有可用的数据库连接");
                }
                return conn;
        }

Answer 14

这个类分两贴:        /**
         * 该线程用来检查连接池中超时的对象
         * @see java.lang.Runnable#run()
         */
        public void run() {
                long startTime = System.currentTimeMillis();
                int  nHour = 0;
                int nMaxConn = 0;
                int usecount = 0;
                while(!bStop){
                        int i = 0;
                        for(;!bStop && i<100;i++){
                                try{
                                        Thread.sleep(checkInterval/100);
                                }catch(Exception e){}
                        }
                        if(i<100)
                                break;                        //检查超时连接
                        Iterator iter = conns.iterator();
                        usecount = 0; //清除计数标志
                        while(!bStop && iter.hasNext()){
                                _Connection conn = (_Connection)iter.next();
                                long freeTime = System.currentTimeMillis() - conn.getLastAccessTime();
                                if(freeTime > connParam.getTimeoutValue() && conn.isInUse()){
                                        try{
                                                conn.getConnection().close();
                                        }catch(Exception e){}
                                }
                                if(conn.isInUse())
                                        usecount ++;
                        }
                        //检查一个时间段内正在使用中的最大连接数，关闭并删除无用的连接对象
                        //这样做是为了防止由于某段时间特别大量的连接后，这些连接就不再可用
                        //的时候，这些连接往往浪费很多的系统资源，将最近最少使用的连接对象
                        //从队列中删除掉
                        int hour = (int)((System.currentTimeMillis() - startTime) / 3600000);
                        if(hour == nHour){
                                nMaxConn = Math.max(nMaxConn, usecount);
                                nMaxConn = Math.max(nMaxConn, connParam.getMinConnection());
                        }
                        else{
                                int idx = 0;
                                synchronized(conns){
                                        Iterator it = conns.iterator();
                                        while(!bStop && it.hasNext()){
                                                _Connection conn = (_Connection)it.next();
                                                idx ++;
                                                if(idx > nMaxConn){
                                                        try{
                                                                conn.close();
                                                        }catch(Exception e){}
                                                        it.remove();
                                                }//End of if(idx > nMaxConn)
                                        }//End of while(it.hasNext())
                                }//End of synchronized(conns)
                                //重新置新时间的最大连接数计数器为0
                                nMaxConn = 0;
                                nHour = hour;
                        }
                }
        }        /**
         * 获取连接池中的连接数
         * @return int
         */
        public int getConnectionCount(){
                return conns.size();
        }
        /**
         * @see javax.sql.DataSource#getConnection()
         */
        public Connection getConnection() throws SQLException {
                return getConnection(connParam.getUser(), connParam.getPassword());
        }        /**
         * @see javax.sql.DataSource#getLogWriter()
         */
        public PrintWriter getLogWriter() throws SQLException {
                return DriverManager.getLogWriter();
        }        /**
         * @see javax.sql.DataSource#getLoginTimeout()
         */
        public int getLoginTimeout() throws SQLException {
                return DriverManager.getLoginTimeout();
        }        /**
         * @see javax.sql.DataSource#setLogWriter(java.io.PrintWriter)
         */
        public void setLogWriter(PrintWriter arg0) throws SQLException {
                DriverManager.setLogWriter(arg0);
        }        /**
         * @see javax.sql.DataSource#setLoginTimeout(int)
         */
        public void setLoginTimeout(int arg0) throws SQLException {
                DriverManager.setLoginTimeout(arg0);
        }        /**
         * Returns the connParam.
         * @return ConnectionParam
         */
        public ConnectionParam getConnParam() {
                return connParam;
        }}

Answer 15

package com.eip.dbpool;/*
 * Created on 2003-12-23
 *
 * Copyright (c) 2003. Waboo Tech. All Rights Reserved.
 */
import java.sql.*;
import javax.sql.*;public class TestConnectionPool {
/**
 * 数据库连接池的测试类
 * @author BinHao
 */public final static String POOL_NAME = "pool1";       public static void main(String[] args) throws Exception
       {
               //初始化连接池
               ConnectionParam param = new ConnectionParam();
               param.setDriver("com.microsoft.jdbc.sqlserver.SQLServerDriver");
               param.setUrl("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs");
               param.setUser("sa");
               param.setPassword("");
               param.setMinConnection(3);
               //param.setCoding(true);
               param.setCheckdb(false);
               ConnectionFactory.bind(POOL_NAME,param);               //开始测试
               DataSource ds = null;
               DataSourceImpl source=null;
               Connection conn = null;
               PreparedStatement ps = null;
               ResultSet rs = null;
               try{
                       ds = ConnectionFactory.lookup(POOL_NAME);
                       conn = ds.getConnection();
                       //查询
                       ps = conn.prepareStatement("SELECT * FROM authors");
                       rs = ps.executeQuery();
                       while(rs.next()){
                               System.out.println("au_id:"+rs.getString(1)+",address="+rs.getString(5));
                       }
                       source=(DataSourceImpl)ds;
                       System.out.println(source.getConnectionCount());
               }finally{
                       if(rs!=null)
                               rs.close();
                       if(ps!=null)
                               ps.close();
                       if(conn!=null)
                               conn.close();                       ConnectionFactory.unbind(POOL_NAME);
               }
       }
}