Singleton模式和Proxy模式实现Java数据库连接池全纪录

　　学了设计模式之后，就想通过设计模式来实现一下Java数据库连接池……几天的努力，终于搞定，现在和大家分享一下（注：连接池的主要代码还是借鉴的巨人的）！

　　Singleton模式和Proxy模式在这就不多谈了。先简单介绍一下整体实现框架：通过Jsp/Servlet对自己做的数据库连接池进行应用，两个jsp页面（一个login.jsp用于登录，一个success.jsp用于显示登录成功后的用户信息）、一个servlet（Check.java用于控制整个数据流程）和两个JavaBean（SQLQuery.java用于处理业务逻辑如验证用户名密码，Userinfo.java用于获取或修改用户信息）。Connection Pool部分三个类，一个连接池管理类（DBConnectionManager），一个连接池类（DBConnectionPool），还有一个代理类（MyConnectionHandler），连接池类内嵌在连接池管理类中，连接池管理类为外覆类。

　　在此用的环境是MyEclipse8.5+Apache Tomcat6.0+MySQL5.1，先新建一个Web Project，之后就是填代码了，各代码如下：

DBConnectionManager类（包括DBConnectionPool类）代码：

 

/**  
 * 数据库连接池管理类  
 */ 
 package com.Alygle;
 import java.io.*;   
 import java.sql.*;   
 import java.util.*;   
 import java.util.Date; 

 /**  
 * 管理类DBConnectionManager支持对一个或多个由属性文件定义的数据库连接池的  
 * 访问.客户程序可以调用getInstance()方法访问本类的唯一实例  
 */  
 public class DBConnectionManager {
    //public static void main(String[] args)
    //{
    //    DBConnectionManager connectionManager = DBConnectionManager.getInstance();
     //     Connection conn =  connectionManager.getConnection("test");
     //     try 
    //  {
     //         Thread.sleep(10);
    //  } 
    //  catch (InterruptedException e)
    //  {
            // TODO Auto-generated catch block   
    //      e.printStackTrace();
    //  }
    //  Connection conn1 = connectionManager.getConnection("test");
    //  Connection conn2 = connectionManager.getConnection("test");
    //  Connection conn3 = connectionManager.getConnection("test");
    //  Connection conn4 = connectionManager.getConnection("test");
    //  Connection conn5 = connectionManager.getConnection("test");
    //  connectionManager.freeConnection("test",conn);
    //  connectionManager.freeConnection("test",conn1);
    //  connectionManager.freeConnection("test",conn2);
    //  connectionManager.freeConnection("test",conn3);
    //  connectionManager.freeConnection("test",conn4);
    //  connectionManager.freeConnection("test",conn5);
    //  Connection conn6 = connectionManager.getConnection("test");
    //  Connection conn7 = connectionManager.getConnection("test");
    //  System.out.println(" conn6 == "+conn6 +" conn7 == "+conn7);
    //}
     static private DBConnectionManager instance; // 唯一实例   
     static private int clients;
    private Vector drivers = new Vector();   
    private PrintWriter log;   
    private Hashtable pools = new Hashtable();  
    
    /**  
     * 返回唯一实例.如果是第一次调用此方法,则创建实例  
     *   
     * @return DBConnectionManager 唯一实例  
     */  
    static synchronized public DBConnectionManager getInstance() 
    {
        if (instance == null)
        {
            instance = new DBConnectionManager();   
        }
        clients++;
        return instance;
    }

    /**  
     * 建构私有函数以防止其它对象创建本类实例  
     */  
    private DBConnectionManager()
    {
        init();
    }
    
    /**  
     * 将连接对象返回给由名字指定的连接池  
     *   
     * @param name  
     *            在属性文件中定义的连接池名字  
     * @param con  
     *            连接对象  
     */  
    public void freeConnection(String name, Connection con) 
    {
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);   
            if (pool != null) 
            {
                pool.freeConnection(con);
            }
    }

    /**  
     * 获得一个可用的(空闲的)连接.如果没有可用连接,且已有连接数小于最大连接数限制,则创建并返回新连接  
     *   
     * @param name  
     *            在属性文件中定义的连接池名字  
     * @return Connection 可用连接或null  
     */  
    public Connection getConnection(String name) 
    {   
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);   
        //System.out.println(" pool == "+pool);   
         if (pool != null) 
        {
            return pool.getConnection();   
        }   
        return null;
    }

    /**
     * 获得一个可用连接.若没有可用连接,且已有连接数小于最大连接数限制, 则创建并返回新连接.否则,在指定的时间内等待其它线程释放连接.  
     *
     * @param name  
     *            连接池名字  
     * @param time  
     *            以毫秒计的等待时间  
     * @return Connection 可用连接或null  
     */  
    public Connection getConnection(String name, long time)
    {   
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);   
        if (pool != null)
        {
            return pool.getConnection(time);
        }   
        return null;   
    } 

    /**
     * 关闭所有连接,撤销驱动程序的注册  
     */  
    public synchronized void release() 
    {   
     // 等待直到最后一个客户程序调用   
         if (--clients != 0) 
        {   
            return;
        }
        Enumeration allPools = pools.elements();   
        while (allPools.hasMoreElements()) 
        {
            DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();   
            pool.release();
        }
        Enumeration allDrivers = drivers.elements();
        while (allDrivers.hasMoreElements())
        {
            Driver driver = (Driver) allDrivers.nextElement();   
            try
            { 
                DriverManager.deregisterDriver(driver);
                log("撤销JDBC驱动程序 " + driver.getClass().getName() + "的注册");   
            } 
            catch (SQLException e) 
            {
                log(e, "无法撤销下列JDBC驱动程序的注册: " + driver.getClass().getName());
            }
        }
    }

    /**  
     * 根据指定属性创建连接池实例.  
     *   
     * @param props  
     *            连接池属性  
     */  
    private void createPools(Properties props) 
    {
        Enumeration propNames = props.propertyNames();
        while (propNames.hasMoreElements())
        {
            String name = (String) propNames.nextElement();
            if (name.endsWith(".url"))
            {
                String poolName = name.substring(0, name.lastIndexOf("."));
                //System.out.println(" poolName ||"+poolName+"|");
                 String url = props.getProperty(poolName + ".url");
                if (url == null)
                {
                    log("没有为连接池" + poolName + "指定URL");
                    continue;
                }
                String user = props.getProperty(poolName + ".user");   
                String password = props.getProperty(poolName + ".password");   
                String maxconn = props.getProperty(poolName + ".maxconn", "0");   
                int max;   
                try 
                {   
                    max = Integer.valueOf(maxconn).intValue();   
                } 
                catch (NumberFormatException e) 
                {   
                    log("错误的最大连接数限制: " + maxconn + " .连接池: " + poolName);   
                    max = 0;   
                }   
                DBConnectionPool pool = new DBConnectionPool(poolName, url, user, password, max);   
                pools.put(poolName, pool);   
                log("成功创建连接池" + poolName);   
            }
        }
    }

    /**  
     * 读取属性完成初始化  
     */  
    private void init()
    {
        //InputStream fileinputstream = null;   
        //try 
        //{   
         //   fileinputstream = new FileInputStream(new File("classpath:db.properties"));   
        //} 
        //catch (FileNotFoundException e1)
        //{
         //   e1.printStackTrace();
        //}
        
        //BufferedReader bufferedreader = new BufferedReader(new InputStreamReader(fileinputstream));
         InputStream is = getClass().getResourceAsStream("/db.properties");   
        Properties dbProps = new Properties();
        try
        {
            //dbProps.load(fileinputstream);
             dbProps.load(is);
        } catch (Exception e)
        {
            e.printStackTrace();   
            System.err.println("不能读取属性文件. "+ "请确保db.properties在CLASSPATH指定的路径中");   
            return;
        }
        String logFile = dbProps.getProperty("logfile","DBConnectionManager.log");
        //System.out.println(dbProps.getProperty("logfile"));   
         try
        { 
            log = new PrintWriter(new FileWriter(logFile, true), true);
        }
        catch (IOException e)
        {   
            System.err.println("无法打开日志文件: " + logFile);   
            log = new PrintWriter(System.err);   
        }
        loadDrivers(dbProps);
        createPools(dbProps);
    }

    /**  
     * 装载和注册所有JDBC驱动程序
     *   
     * @param props  
     *            属性  
     */  
    private void loadDrivers(Properties props) 
    {   
        String driverClasses = props.getProperty("drivers");   
        StringTokenizer st = new StringTokenizer(driverClasses);   
        while (st.hasMoreElements()) 
        {   
            String driverClassName = st.nextToken().trim();   
            try 
            {   
                Driver driver = (Driver) Class.forName(driverClassName).newInstance();   
                DriverManager.registerDriver(driver);   
                drivers.addElement(driver);   
                log("成功注册JDBC驱动程序:" + driverClassName);   
            } 
            catch (Exception e) 
            {   
                log("无法注册JDBC驱动程序: " + driverClassName + ", 错误: " + e);   
            }
        }
    }

    /**  
     * 将文本信息写入日志文件  
     */  
    private void log(String msg) 
    {   
        log.println(new Date() + ": " + msg);   
    }   

    /**  
     * 将文本信息与异常写入日志文件  
     */  
    private void log(Throwable e, String msg) 
    {   
        log.println(new Date() + ": " + msg);   
        e.printStackTrace(log);   
    }   

    /** ************************************************************* */  
    /** ********************内部类DBConnectionPool******************** */  
    /** ************************************************************* */  
    /**  
     * 此内部类定义了一个连接池.它能够根据要求创建新连接,直到预定的最大连接数为止.在返回连接给客户程序之前,它能够验证连接的有效性.  
     */  
    class DBConnectionPool 
    {   
        public int checkedOut; // 当前连接数  
        public Vector freeConnections = new Vector(); // 保存所有可用连接  
        public int maxconn; // 此连接池允许建立的最大连接数  
        private String name; // 连接池名字    
        private String password; // 密码或null    
        private String url; // 数据库的JDBC URL 
        private String user; // 数据库账号或null 
        /**  
         * 创建新的连接池  
         *   
         * @param name  
         *            连接池名字  
         * @param url  
         *            数据库的JDBC URL  
         * @param user  
         *            数据库帐号或 null  
         * @param password  
         *            密码或 null  
         * @param maxconn  
         *            此连接池允许建立的最大连接数  
         */  
        public DBConnectionPool(String name, String url, String user, String password, int maxconn) 
        {
            this.name = name;
            this.url = url;
            this.user = user;
            this.password = password;
            this.maxconn = maxconn;
        }

        /**  
         * 将不再使用的连接返回给连接池  
         *   
         * @param con  
         *            客户程序释放的连接  
         */  
        public synchronized void freeConnection(Connection con) 
        {   
            // 将指定连接加入到向量末尾   
            freeConnections.addElement(con);   
            checkedOut--;
            notifyAll(); // 删除等待队列中的所有线程   
        }

        /**  
         * 从连接池获得一个可用连接.如果没有空闲的连接且当前连接数小于最大连接  
         * 数限制,则创建新连接.如原来登记为可用的连接不再有效,则从向量删除之, 然后递归调用自己以尝试新的可用连接.  
         */  
        public synchronized Connection getConnection() 
        {   
            Connection con = null;   
            //System.out.println(" freeConnections.size() "+freeConnections.size());   
            if (freeConnections.size() > 0) 
            {   
                // 获取向量中第一个可用连接   
                con = (Connection) freeConnections.firstElement();   
                freeConnections.removeElementAt(0);   
                try 
                {
                    if (con.isClosed()) 
                    {
                        log("从连接池" + name + "删除一个无效连接");   
                        // 递归调用自己,尝试再次获取可用连接   
                        con = getConnection();   
                    }   
                } 
                catch (SQLException e) 
                {   
                    log("从连接池" + name + "删除一个无效连接");   
                    // 递归调用自己,尝试再次获取可用连接   
                    con = getConnection();   
                }       
            } 
            else if (maxconn == 0 || checkedOut < maxconn) 
            {   
                con = newConnection();   
            }   
            if (con != null) 
            {   
                checkedOut++;   
            }   
            //System.out.println("con == "+con);   
            return con;   
        }   

        /**  
         * 从连接池获取可用连接.可以指定客户程序能够等待的最长时间 参见前一个getConnection()方法.  
         *
         * @param timeout  
         *            以毫秒计的等待时间限制  
         */  
        public synchronized Connection getConnection(long timeout) 
        {   
            long startTime = new Date().getTime();   
            Connection con;   
            while ((con = getConnection()) == null) 
            {   
                try 
                {   
                    wait(timeout);   
                } 
                catch (InterruptedException e) {}   
                if ((new Date().getTime() - startTime) >= timeout) 
                {   
                    // wait()返回的原因是超时   
                    return null;   
                }   
            }   
            return con;   
        }   

        /**  
         * 关闭所有连接  
         */  
        public synchronized void release() 
        {   
            Enumeration allConnections = freeConnections.elements();   
            while (allConnections.hasMoreElements()) 
            {   
                Connection con = (Connection) allConnections.nextElement();   
                try 
                {   
                    con.close();   
                    log("关闭连接池" + name + "中的一个连接");   
                } 
                catch (SQLException e)
                {
                    log(e, "无法关闭连接池" + name + "中的连接");
                }
            }
            freeConnections.removeAllElements();
        }

        /**
         * 创建新的连接
         */
        private Connection newConnection()
        {
            Connection con = null;   
            //try 
            //{   
            //        if (user == null) 
             //   {   
             //       con = DriverManager.getConnection(url);   
             //   } 
             //   else 
             //   {   
             //       con = DriverManager.getConnection(url, user, password);   
             //   }   
             //   log("连接池" + name + "创建一个新的连接");   
            //} 
            //catch (SQLException e) 
            //{   
             //   log(e, "无法创建下列URL的连接: " + url);   
              //  return null;   
            //}   
            //return con;
            try
            {
                if(user == null)
                {
                    con = DriverManager.getConnection(url);
                }
                else
                {
                    con = DriverManager.getConnection(url,user,password);
                }
                log("连接池" + name + "创建一个新的连接");
            }
            catch(SQLException e)
            {
                log(e,"无法创建下列URL的连接：" +url);
                return null;
            }
            MyConnectionHandler proxy = new MyConnectionHandler(this);
            return proxy.bind(con);
        }
    }
}

MyConnectionHandler类代码：

 

package com.Alygle;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;

public class MyConnectionHandler implements InvocationHandler{
    private DBConnectionManager.DBConnectionPool myds;
    private Connection con;
    private Connection proxyConn;
    MyConnectionHandler(DBConnectionManager.DBConnectionPool myds)
    {
        this.myds = myds;
    }
    Connection bind(Connection realConn)
    {
        this.con = realConn;
        //建立连接对象的代理
        this.proxyConn = (Connection) Proxy.newProxyInstance(this.getClass().getClassLoader(), new Class[] { Connection.class }, this);
        return proxyConn;
    }
    
    /**
     * @override
     */
    public Object invoke(Object proxy, Method method, Object[] args)
        throws Throwable
        {
            if("close".equals(method.getName()))  //如果是close方法
            {
                this.myds.checkedOut++;
                if(this.myds.checkedOut < this.myds.maxconn)
                {
                    this.myds.freeConnections.add(this.proxyConn);
                }
                else
                {
                    this.con.close();
                    this.myds.checkedOut--;
                }
            }
            //如果不是close方法则动态调用传入的数据库驱动的Connection对象的方法
            return method.invoke(this.con, args);
        }
}

属性配置文件db.properties:

drivers=org.gjt.mm.mysql.Driver
logfile=D\:\\Java\\Genuitec\\Workspaces\\MyEclipse 8.5\\Login\\JavaDBConnectPool-log.txt
mysql.maxconn=100
mysql.url=jdbc:mysql://localhost:3306/test
mysql.user=root
mysql.password=root

web.xml配置代码：

 

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee   http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
 <servlet>
  <servlet-name>Check</servlet-name>
  <servlet-class>mypack.Check</servlet-class>
 </servlet>
 <servlet-mapping>
  <servlet-name>Check</servlet-name>
  <url-pattern>/Check</url-pattern>
 </servlet-mapping>

</web-app>

 下面是用jsp/servlet测试连接池的代码。

Check.java代码：

 

package mypack;
import java.io.IOException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class Check extends HttpServlet {
    /**
     * 
     */
    private static final long serialVersionUID = 7261651425231069629L;
    public Check()
    {
        super();
    }
    public void destroy()
    {
        super.destroy();
    }
    public void doGet(HttpServletRequest request,HttpServletResponse response)
                throws ServletException,IOException{}
    public void doPost(HttpServletRequest request,HttpServletResponse response)
                throws ServletException,IOException
    {
        String username = (String) request.getParameter("username");
        String password = (String) request.getParameter("password");
        
        if(!SQLQuery.validate(username,password))
        {
            request.setAttribute("error.message","非法登录！");
            RequestDispatcher requestDispatcher = request.getRequestDispatcher("login.jsp");
            requestDispatcher.forward(request, response);
        }
        else
        {
            request.getSession().setAttribute("username",username);
            RequestDispatcher requestDispatcher = request.getRequestDispatcher("success.jsp");
            requestDispatcher.forward(request, response);
        }
    }

}

SQLQuery.java代码：

 

package mypack;
import java.sql.Connection;
//import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import com.mysql.jdbc.Statement;
import com.Alygle.*;

public class SQLQuery {   //用于处理业务逻辑的JavaBean
    public static boolean validate(String username,String password)
    {   //用于验证用户名密码是否正确
        boolean bool = false;
        //try
        //{
        //    Class.forName("org.gjt.mm.mysql.Driver");
        //}
        //catch (java.lang.ClassNotFoundException e)
        //{
        //    System.out.print("Class not found exception occur. Message is:");
        //    System.out.print(e.getMessage());
        //}
        try
        {
            Statement stmt;
            ResultSet rs;
            DBConnectionManager connectionManager = DBConnectionManager.getInstance();
            //String url = "jdbc:mysql://localhost:3306/test";
            //String user = "root";
            //String passwordOfDB = "root";
            Connection conn = null;
            conn = connectionManager.getConnection("mysql");
            //try
            //{
            //    conn = DriverManager.getConnection(url,user,passwordOfDB);
            //}
            //catch (Exception ex)
            //{
            //    ex.printStackTrace();
            //}
            stmt = (Statement) conn.createStatement();
            rs = (ResultSet) stmt.executeQuery(
                    "select * from userinfo where username = '"+username+"'and password = '"+password+"'");
            if(rs.next()) bool = true;
            else bool = false;
        }
        catch (SQLException e)
        {
            System.out.print("SQL Exception occur. Message is:");
            System.out.print(e.getMessage());
        }
        return bool;
    }
    public static Userinfo userinfoQuery(String username)
    {
        Userinfo ui = new Userinfo();
        //try
        //{
        //    Class.forName("org.gjt.mm.mysql.Driver");
        //}
        //catch (java.lang.ClassNotFoundException e)
        //{
        //    System.out.print("Class not found exception occur. Message is:");
        //    System.out.print(e.getMessage());
        //}
        //连接数据库并获取数据
        try
        {
            Statement stmt;
            ResultSet rs;
            DBConnectionManager connectionManager = DBConnectionManager.getInstance();
            //String url = "jdbc:mysql://localhost:3306/test";
            //String user = "root";
            //String passwordOfDB = "root";
            //连接数据库
            Connection conn = null;
            try
            {
                //conn = DriverManager.getConnection(url,user,passwordOfDB);
                conn = connectionManager.getConnection("mysql");
            }
            catch (Exception ex) {ex.printStackTrace();}
            stmt = (Statement) conn.createStatement();
            rs = (ResultSet) stmt.executeQuery(
                    "select * from userinfo where username = '"+username+"'");
            while(rs.next())
            {
                ui.setName(rs.getString(2));
                ui.setAge(rs.getInt(4));
                ui.setGender(rs.getString(5));
                ui.setEmail(rs.getString(6));
            }
        }
        catch (SQLException e)
        {
            System.out.print("SQL Exception occur. Message is:");
            System.out.print(e.getMessage());
        }
        return ui;
    }

}

 

Userinfo.java代码：

 

package mypack;

public class Userinfo {  //用于获取或修改用户信息的JavaBean
    private String name = null;
    private String password = null;
    private int age = 0;
    private String gender = null;
    private String email = null;
    public String getName()
    {
        return name;
    }
    public String getPassword()
    {
        return password;
    }
    public int getAge()
    {
        return age;
    }
    public String getGender()
    {
        return gender;
    }
    public String getEmail()
    {
        return email;
    }
    
    public void setName(String name)
    {
        this.name = name;
    }
    public void setPassword(String password)
    {
        this.password = password;
    }
    public void setAge(int age)
    {
        this.age = age;
    }
    public void setGender(String gender)
    {
        this.gender = gender;
    }
    public void setEmail(String email)
    {
        this.email = email;
    }
    
    public void init(String username)
    {
        Userinfo ui = SQLQuery.userinfoQuery(username);
        this.name = ui.name;
        this.password = ui.password;
        this.age = ui.age;
        this.gender = ui.gender;
        this.email = ui.email;
    }

}

jsp页面

login.jsp代码：

<%@ page language="java" import="java.util.*" pageEncoding="GB2312"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>Login Page</title>
    <meta http-equiv="Content-Type" content="text/html; charset=gb2312">
  </head>
  <body>
  <div align="center">
   <form name="regForm" method="post" action="Check">
    Name<input name="username" type="text" id="username"><br/>
    Password<input name="password" type="password" id="password"><br/>
   <input type="Submit" name="submit" value="Submit"/>
   <input type="Reset" name="reset" value="reset"/>
   <br/>
   <br/>
   <font color="red">
   <%
   if(!((String)request.getAttribute("error.message") == null)){
    out.print(request.getAttribute("error.message"));
   }
    %>
    </font>
    </form>
  </div>
  </body>
</html>

 

success.jsp代码：

<%@ page language="java" import="java.util.*" pageEncoding="GB2312"%>

<html>
  <head>
    <title>登陆成功</title>
    <meta http-equiv="Content-Type" content="text/html; charset=gb2312">
  </head>
  <jsp:useBean id="userinfo" scope="page" class="mypack.Userinfo"></jsp:useBean>
 
  <body>
  <div align="center">
   登陆成功！<br>
   <%userinfo.init((String)session.getAttribute("username")); %>
   用户：<jsp:getProperty name="userinfo" property="name"/><br>
   年龄：<jsp:getProperty name="userinfo" property="age" /><br>
   性别：<jsp:getProperty name="userinfo" property="gender"/><br>
   邮箱：<jsp:getProperty name="userinfo" property="email"/><br>
  </div>
  </body>
</html>

在MySql中创建数据库，代码如下:(MySql的用户名和密码都是root，如果不是，就在属性文件里改一下）

create database test;

use test

create table userinfo

(id int primary key,

 userinfo varchar(16),

 password varchar(16),

 age int,

 gender varchar(8),

 email varchar(45));

insert into userinfo values(10001,'Alygle','123456',23,'male','Alygle2009@hotmail.com');

insert into userinfo values(10002,'admin','admin',25,'female','Alygle.chang@gmail.com');

　　最后记得加入MySql数据库驱动mysql-connector-java-5.1.10-bin.jar，启动tomcat服务器，在地址栏输入http://localhost:8080/Login/login.jsp就行了。对数据库连接池的操作记录在根目录下的JavaDBConnectPool-log.txt里有记录，如果属性文件里没有指明这个文件，就会默认记录在DBConnectionManager.log这一文件里。

　　以上就是Connection Pool的全过程了，如有不足之处望高人纠正批评。