数据库连接池java实现小结 - JAVA 技术文档 - 安全技术

目前的很多方法都是要求用户只能按规定方法使用连接，不能使用直接关闭数据连接的方法。解决办法就是使用代理类，来中间解决。可以参考<A href="http://www-900.ibm.com/developerWorks/cn/java/l-connpoolproxy/index.shtml">http://www-900.ibm.com/developerWorks/cn/java/l-connpoolproxy/index.shtml</A>

<UL>

<LI>能维护连接的正常状态</LI></UL>

因为针对数据库连接创建的资源，如果不能及时的释放，就会影响下一次数据连接的使用。例如在sql 2k中，一个连接不同创建多条Statement否则操作时会有数据连接占线的异常，所以必须在归还连接以后释放这些资源。

<TBODY>

<TR>

<A href="file://判">//判</A>断是使用了createStatement语句 if (CREATESTATE.equals(method.getName())) { obj = method.invoke(conn, args); statRef = (Statement)obj;//记录语句 return obj; }

</CODE></PRE></TD></TR></TBODY></TABLE>

<TBODY>

<TR>

<TD><PRE><CODE> <A href="file://判">//判</A>断是否调用了close的方法，如果调用close方法则把连接置为无用状态 if(CLOSE.equals(method.getName())) { <A href="file://设">//设</A>置不使用标志 setIsFree(false); <A href="file://检">//检</A>查是否有后续工作，清除该连接无用资源 if (statRef != null) statRef.close(); if (prestatRef != null) prestatRef.close(); return null; } </CODE></PRE></TD></TR></TBODY></TABLE>

<UL>

<LI>正确保护类不被违例使用</LI></UL>

一个考虑就是不能让用户随便使用代理类，而只能自己使用，一个就是用内部私有类，一个就是使用只有指定类才能调用的标志。我的实现就是采用后者。

<TBODY>

<TR>

<TD><PRE><CODE> /** * 创建连接的工厂，只能让工厂调用 * @param factory 要调用工厂，并且一定被正确初始化 * @param param 连接参数 * @return 连接 */ static public _Connection getConnection(ConnectionFactory factory, ConnectionParam param) { if (factory.isCreate())//判断是否正确初始化的工厂 { _Connection _conn = new _Connection(param); return _conn; } else return null; } </CODE></PRE></TD></TR></TBODY></TABLE>

<UL>

<LI>提供良好的用户接口，简单实用</LI></UL>

使用静态方法创建工厂，然后来得到连接，使用完全和普通的Connection方法一样，没有限制。同时为了方便，设置了连接参数类和工厂参数类。

<UL>

<LI>

<TBODY>

<TR>

<TD><PRE><CODE> ConnectionParam param = new ConnectionParam(driver,url,user,password); ConnectionFactory cf = null;//new ConnectionFactory(param, new FactoryParam()); try{ cf = new ConnectionFactory(param,new FactoryParam()); Connection conn1 = cf.getFreeConnection(); Connection conn2 = cf.getFreeConnection(); Connection conn3 = cf.getFreeConnection(); Statement stmt = conn1.createStatement(); ResultSet rs = stmt.executeQuery("select * from requests"); if (rs.next()) { System.out.println("conn1 y"); } else { System.out.println("conn1 n"); } stmt.close(); conn1.close(); </CODE></PRE></TD></TR></TBODY></TABLE>

<LI>为了实现连接池的正常运作，使用了单态模式</LI></UL>

<TBODY>

<TR>

<TD><PRE><CODE> /** * 使用指定的参数创建一个连接池 */ public ConnectionFactory(ConnectionParam param, FactoryParam fparam) throws SQLException { <A href="file://不">//不</A>允许参数为空 if ((param == null)||(fparam == null)) throw new SQLException("ConnectionParam和FactoryParam不能为空"); if (m_instance == null) { synchronized(ConnectionFactory.class){ if (m_instance == null) { <A href="file://new">//new</A> instance <A href="file://参">//参</A>数定制 m_instance = new ConnectionFactory(); m_instance.connparam = param; m_instance.MaxConnectionCount = fparam.getMaxConn(); m_instance.MinConnectionCount = fparam.getMinConn(); m_instance.ManageType = fparam.getType(); m_instance.isflag = true; <A href="file://初">//初</A>始化，创建MinConnectionCount个连接 System.out.println("connection factory 创建！"); try{ for (int i=0; i < m_instance.MinConnectionCount; i++) { _Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam); if (_conn == null) continue; System.out.println("connection创建"); m_instance.FreeConnectionPool.add(_conn);//加入空闲连接池 m_instance.current_conn_count ++; <A href="file://标">//标</A>志是否支持事务 m_instance.supportTransaction = _conn.isSupportTransaction(); } } catch(Exception e) { e.printStackTrace(); } <A href="file://根">//根</A>据策略判断是否需要查询 if (m_instance.ManageType != 0) { Thread t = new Thread(new FactoryMangeThread(m_instance)); t.start(); } } } } } </CODE></PRE></TD></TR></TBODY></TABLE>

<UL>

<LI>连接池的管理</LI></UL>

对于连接池的管理，我是设想使用静态管理和动态管理两种策略，设置了最大限制，和恒定的连接数。使用了2个池，一个空闲池，一个使用池。静态就是使用的时候发现空闲连接不够再去检查。动态就是使用了一个线程定时检查。

<TBODY>

<TR>

<TD><PRE><CODE> //根据策略判断是否需要查询 if (m_instance.ManageType != 0) { Thread t = new Thread(new FactoryMangeThread(m_instance)); t.start(); }</CODE></PRE><PRE><CODE> //连接池调度线程 public class FactoryMangeThread implements Runnable { ConnectionFactory cf = null; long delay = 1000; public FactoryMangeThread(ConnectionFactory obj) { cf = obj; } /* (non-Javadoc) * @see java.lang.Runnable#run() */ public void run() { while(true){ try{ Thread.sleep(delay); } catch(InterruptedException e){} System.out.println("eeeee"); <A href="file://判">//判</A>断是否已经关闭了工厂，那就退出监听 if (cf.isCreate()) cf.schedule(); else System.exit(1); } } }</CODE></PRE></TD></TR></TBODY></TABLE>

最后给出完整的源代码：

<HR>

_Connectio.java

<TBODY>

<TR>

<TD><PRE>package scut.ailab.connectionpool;</PRE><PRE>import java.lang.reflect.*; import java.sql.*;</PRE><PRE>/** * @author youyongming * 定义数据库连接的代理类 */ public class _Connection implements InvocationHandler { <A href="file://定">//定</A>义连接 private Connection conn = null; <A href="file://定">//定</A>义监控连接创建的语句 private Statement statRef = null; private PreparedStatement prestatRef = null; <A href="file://是">//是</A>否支持事务标志 private boolean supportTransaction = false; <A href="file://数">//数</A>据库的忙状态 private boolean isFree = false; <A href="file://最">//最</A>后一次访问时间 long lastAccessTime = 0; <A href="file://定">//定</A>义要接管的函数的名字 String CREATESTATE = "createStatement"; String CLOSE = "close"; String PREPARESTATEMENT = "prepareStatement"; String COMMIT = "commit"; String ROLLBACK = "rollback";</PRE><PRE> /** * 构造函数，采用私有，防止被直接创建 * @param param 连接参数 */ private _Connection(ConnectionParam param) { <A href="file://记">//记</A>录日至 try{ <A href="file://创">//创</A>建连接 Class.forName(param.getDriver()).newInstance(); conn = DriverManager.getConnection(param.getUrl(),param.getUser(), param.getPassword()); DatabaseMetaData dm = null; dm = conn.getMetaData(); <A href="file://判">//判</A>断是否支持事务 supportTransaction = dm.supportsTransactions(); } catch(Exception e) { e.printStackTrace(); } }</PRE><PRE> /* (non-Javadoc) * @see java.lang.reflect.InvocationHandler#invoke(java.lang.Object, java.lang.reflect.Method, java.lang.Object[]) */ public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { Object obj = null; <A href="file://判">//判</A>断是否调用了close的方法，如果调用close方法则把连接置为无用状态 if(CLOSE.equals(method.getName())) { <A href="file://设">//设</A>置不使用标志 setIsFree(false); <A href="file://检">//检</A>查是否有后续工作，清除该连接无用资源 if (statRef != null) statRef.close(); if (prestatRef != null) prestatRef.close(); return null; } <A href="file://判">//判</A>断是使用了createStatement语句 if (CREATESTATE.equals(method.getName())) { obj = method.invoke(conn, args); statRef = (Statement)obj;//记录语句 return obj; } <A href="file://判">//判</A>断是使用了prepareStatement语句 if (PREPARESTATEMENT.equals(method.getName())) { obj = method.invoke(conn, args); prestatRef = (PreparedStatement)obj; return obj; } <A href="file://如">//如</A>果不支持事务，就不执行该事物的代码 if ((COMMIT.equals(method.getName())||ROLLBACK.equals(method.getName())) && (!isSupportTransaction())) return null; obj = method.invoke(conn, args); <A href="file://设">//设</A>置最后一次访问时间，以便及时清除超时的连接 lastAccessTime = System.currentTimeMillis(); return obj; }</PRE><PRE> /** * 创建连接的工厂，只能让工厂调用 * @param factory 要调用工厂，并且一定被正确初始化 * @param param 连接参数 * @return 连接 */ static public _Connection getConnection(ConnectionFactory factory, ConnectionParam param) { if (factory.isCreate())//判断是否正确初始化的工厂 { _Connection _conn = new _Connection(param); return _conn; } else return null; } public Connection getFreeConnection() { <A href="file://返">//返</A>回数据库连接conn的接管类，以便截住close方法 Connection conn2 = (Connection)Proxy.newProxyInstance( conn.getClass().getClassLoader(), conn.getClass().getInterfaces(),this); return conn2; }</PRE><PRE> /** * 该方法真正的关闭了数据库的连接 * @throws SQLException */ void close() throws SQLException{ <A href="file://由">//由</A>于类属性conn是没有被接管的连接，因此一旦调用close方法后就直接关闭连接 conn.close(); } public void setIsFree(boolean value) { isFree = value; } public boolean isFree() { return isFree; } /** * 判断是否支持事务 * @return boolean */ public boolean isSupportTransaction() { return supportTransaction; } } </PRE></TD></TR></TBODY></TABLE>

<HR>

ConnectionFactory.java

<TBODY>

<TR>

<TD><PRE> package scut.ailab.connectionpool;</PRE><PRE>/** * @author youyongming * */ import java.util.LinkedHashSet; import java.sql.*; import java.util.Iterator;</PRE><PRE>public class ConnectionFactory { private static ConnectionFactory m_instance = null; <A href="file://在">//在</A>使用的连接池 private LinkedHashSet ConnectionPool = null; <A href="file://空">//空</A>闲连接池 private LinkedHashSet FreeConnectionPool = null; <A href="file://最">//最</A>大连接数 private int MaxConnectionCount = 4; <A href="file://最">//最</A>小连接数 private int MinConnectionCount = 2; <A href="file://当">//当</A>前连接数 private int current_conn_count = 0; <A href="file://连">//连</A>接参数 private ConnectionParam connparam = null; <A href="file://是">//是</A>否创建工厂的标志 private boolean isflag = false; <A href="file://是">//是</A>否支持事务 private boolean supportTransaction = false; <A href="file://定">//定</A>义管理策略 private int ManageType = 0;</PRE><PRE> private ConnectionFactory() { ConnectionPool = new LinkedHashSet(); FreeConnectionPool = new LinkedHashSet(); } /** * 使用指定的参数创建一个连接池 */ public ConnectionFactory(ConnectionParam param, FactoryParam fparam) throws SQLException { <A href="file://不">//不</A>允许参数为空 if ((param == null)||(fparam == null)) throw new SQLException("ConnectionParam和FactoryParam不能为空"); if (m_instance == null) { synchronized(ConnectionFactory.class){ if (m_instance == null) { <A href="file://new">//new</A> instance <A href="file://参">//参</A>数定制 m_instance = new ConnectionFactory(); m_instance.connparam = param; m_instance.MaxConnectionCount = fparam.getMaxConn(); m_instance.MinConnectionCount = fparam.getMinConn(); m_instance.ManageType = fparam.getType(); m_instance.isflag = true; <A href="file://初">//初</A>始化，创建MinConnectionCount个连接 System.out.println("connection factory 创建！"); try{ for (int i=0; i < m_instance.MinConnectionCount; i++) { _Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam); if (_conn == null) continue; System.out.println("connection创建"); m_instance.FreeConnectionPool.add(_conn);//加入空闲连接池 m_instance.current_conn_count ++; <A href="file://标">//标</A>志是否支持事务 m_instance.supportTransaction = _conn.isSupportTransaction(); } } catch(Exception e) { e.printStackTrace(); } <A href="file://根">//根</A>据策略判断是否需要查询 if (m_instance.ManageType != 0) { Thread t = new Thread(new FactoryMangeThread(m_instance)); t.start(); } } } } } /** * 标志工厂是否已经创建 * @return boolean */ public boolean isCreate() { return m_instance.isflag; } /** * 从连接池中取一个空闲的连接 * @return Connection * @throws SQLException */ public synchronized Connection getFreeConnection() throws SQLException { Connection conn = null; <A href="file://获">//获</A>取空闲连接 Iterator iter = m_instance.FreeConnectionPool.iterator(); while(iter.hasNext()){ _Connection _conn = (_Connection)iter.next(); <A href="file://找">//找</A>到未用连接 if(!_conn.isFree()){ conn = _conn.getFreeConnection(); _conn.setIsFree(true); <A href="file://移">//移</A>出空闲区 m_instance.FreeConnectionPool.remove(_conn); <A href="file://加">//加</A>入连接池 m_instance.ConnectionPool.add(_conn); break; } } <A href="file://检">//检</A>查空闲池是否为空 if (m_instance.FreeConnectionPool.isEmpty()) { <A href="file://再">//再</A>检查是否能够分配 if (m_instance.current_conn_count < m_instance.MaxConnectionCount) { <A href="file://新">//新</A>建连接到空闲连接池 int newcount = 0 ; <A href="file://取">//取</A>得要建立的数目 if (m_instance.MaxConnectionCount - m_instance.current_conn_count >=m_instance.MinConnectionCount) { newcount = m_instance.MinConnectionCount; } else { newcount = m_instance.MaxConnectionCount - m_instance.current_conn_count; } /<A href="file://创">/创</A>建连接 for (int i=0;i <newcount; i++) { _Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam); m_instance.FreeConnectionPool.add(_conn); m_instance.current_conn_count ++; } } else {//如果不能新建，检查是否有已经归还的连接 iter = m_instance.ConnectionPool.iterator(); while(iter.hasNext()){ _Connection _conn = (_Connection)iter.next(); if(!_conn.isFree()){ conn = _conn.getFreeConnection(); _conn.setIsFree(false); m_instance.ConnectionPool.remove(_conn); m_instance.FreeConnectionPool.add(_conn); break; } } } }//if (FreeConnectionPool.isEmpty()) <A href="file://再">//再</A>次检查是否能分配连接 if(conn == null){ iter = m_instance.FreeConnectionPool.iterator(); while(iter.hasNext()){ _Connection _conn = (_Connection)iter.next(); if(!_conn.isFree()){ conn = _conn.getFreeConnection(); _conn.setIsFree(true); m_instance.FreeConnectionPool.remove(_conn); m_instance.ConnectionPool.add(_conn); break; } } if(conn == null)//如果不能则说明无连接可用 throw new SQLException("没有可用的数据库连接"); } System.out.println("get connection"); return conn; } /** * 关闭该连接池中的所有数据库连接 * @throws SQLException */ public synchronized void close() throws SQLException { this.isflag = false; SQLException excp = null; <A href="file://关">//关</A>闭空闲池 Iterator iter = m_instance.FreeConnectionPool.iterator(); while(iter.hasNext()){ try{ ((_Connection)iter.next()).close(); System.out.println("close connection:free"); m_instance.current_conn_count --; }catch(Exception e){ if(e instanceof SQLException) excp = (SQLException)e; } } <A href="file://关">//关</A>闭在使用的连接池 iter = m_instance.ConnectionPool.iterator(); while(iter.hasNext()){ try{ ((_Connection)iter.next()).close(); System.out.println("close connection:inused"); m_instance.current_conn_count --; }catch(Exception e){ if(e instanceof SQLException) excp = (SQLException)e; } } if(excp != null) throw excp; } /** * 返回是否支持事务 * @return boolean */ public boolean isSupportTransaction() { return m_instance.supportTransaction; } /** * 连接池调度管理 * */ public void schedule() { Connection conn = null; <A href="file://再">//再</A>检查是否能够分配 Iterator iter = null; <A href="file://检">//检</A>查是否有已经归还的连接 { iter = m_instance.ConnectionPool.iterator(); while(iter.hasNext()){ _Connection _conn = (_Connection)iter.next(); if(!_conn.isFree()){ conn = _conn.getFreeConnection(); _conn.setIsFree(false); m_instance.ConnectionPool.remove(_conn); m_instance.FreeConnectionPool.add(_conn); break; } } } if (m_instance.current_conn_count < m_instance.MaxConnectionCount) { <A href="file://新">//新</A>建连接到空闲连接池 int newcount = 0 ; <A href="file://取">//取</A>得要建立的数目 if (m_instance.MaxConnectionCount - m_instance.current_conn_count >=m_instance.MinConnectionCount) { newcount = m_instance.MinConnectionCount; } else { newcount = m_instance.MaxConnectionCount - m_instance.current_conn_count; } <A href="file://创">//创</A>建连接 for (int i=0;i <newcount; i++) { _Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam); m_instance.FreeConnectionPool.add(_conn); m_instance.current_conn_count ++; } } } }</PRE></TD></TR></TBODY></TABLE>

<HR>

ConnectionParam.java

<TBODY>

<TR>

<TD><PRE> package scut.ailab.connectionpool;</PRE><PRE>import java.io.Serializable;</PRE><PRE>/** * @author youyongming * 实现数据库连接的参数类 */ public class ConnectionParam implements Serializable { private String driver; <A href="file://数">//数</A>据库驱动程序 private String url; <A href="file://数">//数</A>据连接的URL private String user; <A href="file://数">//数</A>据库用户名 private String password; <A href="file://数">//数</A>据库密码 /** * 唯一的构造函数，需要指定连接的四个必要参数 * @param driver 数据驱动 * @param url 数据库连接url * @param user 用户名 * @param password 密码 */ public ConnectionParam(String driver,String url,String user,String password) { this.driver = driver; this.url = url; this.user = user; this.password = password; }</PRE><PRE> public String getDriver() { return driver; }</PRE><PRE> public String getPassword() { return password; }</PRE><PRE> public String getUrl() { return url; }</PRE><PRE> public String getUser() { return user; }</PRE><PRE> public void setDriver(String driver) { this.driver = driver; }</PRE><PRE> public void setPassword(String password) { this.password = password; }</PRE><PRE> public void setUrl(String url) { this.url = url; }</PRE><PRE> public void setUser(String user) { this.user = user; }</PRE><PRE> /** * @see java.lang.Object#clone() */ public Object clone(){ ConnectionParam param = new ConnectionParam(driver,url,user,password); return param; }</PRE><PRE> /** * @see java.lang.Object#equals(java.lang.Object) */ public boolean equals(Object obj) { if(obj instanceof ConnectionParam){ ConnectionParam param = (ConnectionParam)obj; return ((driver.compareToIgnoreCase(param.getDriver()) == 0)&& (url.compareToIgnoreCase(param.getUrl()) == 0)&& (user.compareToIgnoreCase(param.getUser()) == 0)&& (password.compareToIgnoreCase(param.getPassword()) == 0)); } return false; } } </PRE></TD></TR></TBODY></TABLE>

<HR>

FactoryMangeThread.java

<TBODY>

<TR>

<HR>

FactoryParam.java

<TBODY>

<TR>

<HR>

testmypool.java

<TBODY>

<TR>

<TD><PRE> /* * Created on 2003-5-13 * * To change the template for this generated file go to * Window>Preferences>Java>Code Generation>Code and Comments */ package scut.ailab.connectionpool;</PRE><PRE>/** * @author youyongming * */ import java.sql.*;</PRE><PRE>public class testmypool {</PRE><PRE> public void test1() { String user = "DevTeam"; String password = "DevTeam"; String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String url = "jdbc:odbc:gfqh2"; ConnectionParam param = new ConnectionParam(driver,url,user,password); ConnectionFactory cf = null;//new ConnectionFactory(param, new FactoryParam()); try{ cf = new ConnectionFactory(param,new FactoryParam()); Connection conn1 = cf.getFreeConnection(); Connection conn2 = cf.getFreeConnection(); Connection conn3 = cf.getFreeConnection(); Statement stmt = conn1.createStatement(); ResultSet rs = stmt.executeQuery("select * from requests"); if (rs.next()) { System.out.println("conn1 y"); } else { System.out.println("conn1 n"); } stmt.close(); conn1.close(); Connection conn4 = cf.getFreeConnection(); Connection conn5 = cf.getFreeConnection(); stmt = conn5.createStatement(); rs = stmt.executeQuery("select * from requests"); if (rs.next()) { System.out.println("conn5 y"); } else { System.out.println("conn5 n"); } conn2.close(); conn3.close(); conn4.close(); conn5.close(); } catch(Exception e) { e.printStackTrace(); } finally{ try{ cf.close(); } catch(Exception e) { e.printStackTrace(); } } } public static void main(String[] args) { String user = "DevTeam"; String password = "DevTeam"; String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String url = "jdbc:odbc:gfqh2"; ConnectionParam param = new ConnectionParam(driver,url,user,password); ConnectionFactory cf = null;//new ConnectionFactory(param,new FactoryParam()); try{ cf = new ConnectionFactory(param,new FactoryParam()); ConnectionFactory cf1= new ConnectionFactory(param,new FactoryParam()); Connection conn1 = null; long time = System.currentTimeMillis(); for (int i=0; i <10;i++) { conn1 = cf.getFreeConnection(); Statement stmt = conn1.createStatement(); ResultSet rs = stmt.executeQuery("select * from requests"); if (rs.next()) { System.out.println("conn1 y"); } else { System.out.println("conn1 n"); } conn1.close(); } System.out.println("pool:" + (System.currentTimeMillis()-time)); time = System.currentTimeMillis(); Class.forName(param.getDriver()).newInstance(); for (int i=0; i <10;i++) { conn1 = DriverManager.getConnection(param.getUrl(),param.getUser(), param.getPassword()); Statement stmt = conn1.createStatement(); ResultSet rs = stmt.executeQuery("select * from requests"); if (rs.next()) { System.out.println("conn1 y"); } else { System.out.println("conn1 n"); } conn1.close(); } System.out.println("no pool:" + (System.currentTimeMillis()-time)); } catch(Exception e) { e.printStackTrace(); } finally{ try{ cf.close(); } catch(Exception e) { e.printStackTrace(); } } } } </PRE></TD></TR></TBODY></TABLE>

<UL>

<LI>关于作者：游永明，人工智能实验室在读研究生，爱好Linux和Java的开发，研究方向是时态数据库。希望能和大家进行探讨和交流，<A href="mailto:chinascutface@hotmail.com">chinascutface@hotmail.com</A></LI></UL>

【返回顶部】【打印本页】【关闭窗口】