Java数据库连接池

import java.sql.*;
import java.util.*;
import java.io.*;

/**
 * <p>
 * Title:数据库连接池
 * </p>
 * <p>
 * Description:本连接池支持对失效连接重连、重建连接池，并监控调用者及调用时间
 * 本类需要在类路径根中存在db.properties数据库配置文件，文件内容示例：
 * drivers=net.sourceforge.jtds.jdbc.Driver
 * defaultpoolname.url=jdbc:jtds:sqlserver://192.167.1.107:1433/wapplat;charset=gb2312
 * defaultpoolname.user=sa
 * defaultpoolname.password=lutong
 * defaultpoolname.maxconn=20
 * </p>
 * <p>
 * Copyright: Copyright (c) 2005
 * </p>
 * <p>
 * Company:lutong
 * </p>
 *
 * @author <a href="mailto:hshwebsite@21cn.com">DBoy</a>
 * @version 1.0
 */

public class ConnectPool extends Thread
{  
    /* 连接池，存放已创建但未使用的连接 */
    private Stack pool = new Stack();

    /* 被返回的数据库连接,将由线程检测并返回到池中 */
    private Stack returnedPool = new Stack();

    /* 已被取走、正在使用的连接 */
    private Map using = new HashMap();

    /* 已经创建连接池计数 */
    private int created = 0;

    /* JDBC Driver类 */
    String drivers = null;

    /* 数据库连接字符串 */
    String url = null;

    /* 数据库连接用户名 */
    String user = null;

    /* 数据库连接密码 */
    String password = null;

    /* 连接池最大数 */
    int max = 100;

    /* 连接池最小数 （还未实现）*/
    int min = 10;

    /* 空闲连接等待释放的时间(秒) ，（还未实现）*/
    int idleTime = 1800;
   
    /*是否需要停止线程*/
    boolean stopThread = false;

    /*各种同步锁对象，据说byte[]对象创建时间最短，占资料最少*/
    private byte[] createdLock = new byte[0];
    private byte[] usingLock = new byte[0];
    private byte[] poolLock = new byte[0];
    private byte[] returnedPoolLock = new byte[0];
   
    /*单实例*/
    private static ConnectPool instance = new ConnectPool();

    /**
     * 私有的构造方法，防止从外部直接实例化
     * 
     */
    private ConnectPool()
    {
        /* 初始化数据库连接参数 */
        init();

        /* 启动服务线程 */
        start();
    }

    /**
     * 从外部取得本类实例的唯一方法
     *
     * @return ConnectPool
     */
    public static ConnectPool getInstance()
    {
        return instance;
    }

    /**
     * 从连接池中取得一个数据库连接
     * 如果池中已没有连接，则新创建一个连接
     * 被使用的连接放到using对象里
     * @param caller
     * @return
     */
    public Connection getConnection(String poolname, String caller)
    {
        if(null==caller || caller.length()==0)
        {           
            StackTraceElement[] callerStackTrace = new Throwable().getStackTrace();
            caller = callerStackTrace[1].toString();
        }
        Connection conn = null;
        try
        {
            synchronized(poolLock)
            {
                conn = (Connection) pool.pop();  
            }
        }
        catch (EmptyStackException e)
        {
            conn = newConnection();
        }

        if (null != conn)
        {
            synchronized(usingLock)
            {
                using.put(conn, new UsingConnection(conn, caller));
            }
        }
        return conn;
    }

    /**
     * 创建一个新的数据库连接
     *
     * @return
     */
    private Connection newConnection()
    {
        Connection conn = null;
        try
        {
            if (created < max)
            {
                Class.forName(drivers);
                conn = DriverManager.getConnection(url, user, password);
                if (null != conn)
                {
                    synchronized(createdLock)
                    {
                        created++;
                    }
                }
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 返回一个数据库连接到池中，再由线程返回连接池
     *
     * @param conn
     */
    public void freeConnection(Connection conn)
    {
        freeConnection(null, conn);
    }

    public void freeConnection(String poolName, Connection conn)
    {
        if (null != conn)
        {
            synchronized(returnedPoolLock)
            {
                returnedPool.push(conn);
            }
        }
    }

    /**
     * 初始化数据库连接使用的参数
     * 
     */
    private void init()
    {
        InputStream is = getClass().getResourceAsStream("/db.properties");
        Properties dbProps = new Properties();
        try
        {
            dbProps.load(is);
        }
        catch (Exception e)
        {
            System.err.println("Can't read the properties file. " + "Make sure db.properties is in the CLASSPATH");
            return;
        }
        drivers = dbProps.getProperty("drivers");
        url = dbProps.getProperty("defaultpoolname.url");
        user = dbProps.getProperty("defaultpoolname.user");
        password = dbProps.getProperty("defaultpoolname.password");
        max = Integer.parseInt(dbProps.getProperty("defaultpoolname.maxconn"));       
    }

    /**
     * 连接服务线程，主要作用： 记录已取走的连接 测试返回的连接是否可用
     */
    public void run()
    {
        Connection conn = null;
        UsingConnection uc = null;
        while (true  && !stopThread)
        {
            /*
             * 处理被返回的数据库连接 判断连接是否由本池创建并发出的，如果不是则直接关闭
             * 如果是则测试连接是否有效，无效从池中删除，有效则返回池中
             */
            while (!returnedPool.empty())
            {
                synchronized(returnedPoolLock)
                {
                    conn = (Connection) returnedPool.pop();
                }
                synchronized(usingLock)
                {
                    uc = (UsingConnection) using.get(conn);
                }
                if (null == uc)
                {
                    try
                    {
                        conn.close();
                    }
                    catch (Exception e)
                    {
                    }
                }
                synchronized(usingLock)
                {
                    using.remove(conn);
                }

                if (testOK(conn))
                {
                    synchronized(poolLock)
                    {
                        pool.add(conn);
                    }
                }
                else
                {
                    try
                    {
                        conn.close();
                    }
                    catch (Exception e)
                    {
                    }
                    synchronized(createdLock)
                    {
                        created--;
                    }
                }
            }

            conn = null;
            uc = null;
            /* 避免循环太快 */
            try
            {
                Thread.sleep(50);
            }
            catch (InterruptedException ie)
            {
            }
        }
        stopThread = false;
    }

    /**
     * 测试连接是否正常
     *
     * @param conn
     * @return
     */
    public boolean testOK(Connection conn)
    {
        boolean result = false;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try
        {
            pstmt = conn.prepareStatement("select 1");
            rs = pstmt.executeQuery();
            result = true;
        }
        catch (Exception e)
        {
        }
        finally
        {
            if (null != rs)
            {
                try
                {
                    rs.close();
                }
                catch (Exception e)
                {
                }
            }
            if (null != pstmt)
            {
                try
                {
                    pstmt.close();
                }
                catch (Exception e)
                {
                }
            }
        }
        return result;
    }

    /**
     * 取得当前正使用的连接信息（HTML格式）
     *
     * @return
     */
    public String getUsingHTMLInfo()
    {

        StringBuffer info = new StringBuffer();
        info.append("Driver:" + drivers + "<br>");
        info.append("Connect url:" + url + "<br>");
        synchronized(createdLock)
        {
            info.append("Created connection count:" + created + "<br>");
        }
        synchronized(usingLock)
        {
         info.append("Using connection count:" + using.values().size() + "<br><br>");
         info.append("<table border=1><tr><td>Caller</td><td>Using Time(ms)</td></tr>");
         Iterator it = using.values().iterator();
        
         UsingConnection uc = null;
         while (it.hasNext())
         {
             uc = (UsingConnection) it.next();
             info.append("<tr><td>");
             info.append(uc.getCaller());
             info.append("</td><td>");
             info.append(uc.getUsingTime());
             info.append("</td></tr>");
         }
        }
        info.append("</table>");
        return info.toString();
    }

    /**
     * 释放所有连接,创建新池
     */
    public void release()
    {  
        /*要求停止线程*/
        stopThread = true;
       
        /*停等待线程结束,线程结束时会把stopThread置为false*/
        int timeout = 0;
        while(stopThread)
        {  
            if(++timeout>600)
            {
                break;
            }
            try
            {
                Thread.sleep(1000);
            }
            catch (InterruptedException ie)
            {
            }
        }
       
        /*创建新连接池实例*/
        instance = new ConnectPool();
       
        /*释放所有连接，除了Using，Using是正在使用的且会在新的池里被释放*/
        synchronized(poolLock)
        {
         while(!pool.isEmpty())
         {  
             try
             {
                 Connection conn = (Connection)pool.pop();
                 conn.close();
             }
             catch(Exception e)
             {               
             }
         }
        }
    
        synchronized(returnedPoolLock)
        {
         while(!returnedPool.isEmpty())
         {  
             try
             {
                 Connection conn = (Connection)returnedPool.pop();
                 conn.close();
             }
             catch(Exception e)
             {               
             }
         }
        }
    }
}

/**
 * 用户存储当前被使用的数据库连接信息
 */
class UsingConnection
{
    private Connection conn = null;

    private String caller = null;

    long time = 0;

    public UsingConnection(Connection conn, String caller)
    {
        this.conn = conn;
        this.caller = caller;
        time = System.currentTimeMillis();
    }

    public String getCaller()
    {
        return caller;
    }

    public long getUsingTime()
    {
        return System.currentTimeMillis() - time;
    }
}