AI专业者开发社区 1 号成员
  •  0 回帖  •  3 浏览  •  5 个月前
 

Java数据库连接池封装与用法

修改于抄袭版本，那货写的有点 BUG, 两个类，一个用法

ConnectionPool 类:
package com.vl.sql;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Vector;
public class ConnectionPool {

<span style="color: rgba(0, 0, 255, 1)">private</span> String jdbcDriver = "<span style="color: rgba(139, 0, 0, 1)"></span>"; <span style="color: rgba(0, 128, 0, 1)">// 数据库驱动</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> String dbUrl = "<span style="color: rgba(139, 0, 0, 1)"></span>"; <span style="color: rgba(0, 128, 0, 1)">// 数据 URL</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> String dbUsername = "<span style="color: rgba(139, 0, 0, 1)"></span>"; <span style="color: rgba(0, 128, 0, 1)">// 数据库用户名</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> String dbPassword = "<span style="color: rgba(139, 0, 0, 1)"></span>"; <span style="color: rgba(0, 128, 0, 1)">// 数据库用户密码</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> String testTable = "<span style="color: rgba(139, 0, 0, 1)"></span>"; <span style="color: rgba(0, 128, 0, 1)">// 测试连接是否可用的测试表名，默认没有测试表</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">int</span> initialConnections = 1; <span style="color: rgba(0, 128, 0, 1)">// 连接池的初始大小</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">int</span> incrementalConnections = 5;<span style="color: rgba(0, 128, 0, 1)">// 连接池自动增加的大小</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">int</span> maxConnections = 20; <span style="color: rgba(0, 128, 0, 1)">// 连接池最大的大小</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> Vector&lt;PooledConnection&gt; connections = <span style="color: rgba(0, 0, 255, 1)">null</span>; <span style="color: rgba(0, 128, 0, 1)">// 存放连接池中数据库连接的向量 ,</span>

<span style="color: rgba(0, 128, 0, 1)">// 初始时为 null</span>

<span style="color: rgba(0, 128, 0, 1)">// 它中存放的对象为 PooledConnection 型</span>

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 构造函数
 * 
 * 
 * 
 * @param jdbcDriver
 *            String JDBC 驱动类串
 * 
 * @param dbUrl
 *            String 数据库 URL
 * 
 * @param dbUsername
 *            String 连接数据库用户名
 * 
 * @param dbPassword
 *            String 连接数据库用户的密码
 * 
 * 
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> ConnectionPool(String jdbcDriver, String dbUrl, String dbUsername, String dbPassword) {

	<span style="color: rgba(0, 0, 255, 1)">this</span>.jdbcDriver = jdbcDriver;

	<span style="color: rgba(0, 0, 255, 1)">this</span>.dbUrl = dbUrl;

	<span style="color: rgba(0, 0, 255, 1)">this</span>.dbUsername = dbUsername;

	<span style="color: rgba(0, 0, 255, 1)">this</span>.dbPassword = dbPassword;

	<span style="color: rgba(0, 0, 255, 1)">try</span> {
		createPool();
	} <span style="color: rgba(0, 0, 255, 1)">catch</span> (Exception e) {
		e.printStackTrace();
	}

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 返回连接池的初始大小
 * 
 * 
 * 
 * @return 初始连接池中可获得的连接数量
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span> getInitialConnections() {

	<span style="color: rgba(0, 0, 255, 1)">return</span> <span style="color: rgba(0, 0, 255, 1)">this</span>.initialConnections;

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 设置连接池的初始大小
 * 
 * 
 * 
 * @param 用于设置初始连接池中连接的数量
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setInitialConnections(<span style="color: rgba(0, 0, 255, 1)">int</span> initialConnections) {

	<span style="color: rgba(0, 0, 255, 1)">this</span>.initialConnections = initialConnections;

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 返回连接池自动增加的大小 、
 * 
 * 
 * 
 * @return 连接池自动增加的大小
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span> getIncrementalConnections() {

	<span style="color: rgba(0, 0, 255, 1)">return</span> <span style="color: rgba(0, 0, 255, 1)">this</span>.incrementalConnections;

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 设置连接池自动增加的大小
 * 
 * @param 连接池自动增加的大小
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setIncrementalConnections(<span style="color: rgba(0, 0, 255, 1)">int</span> incrementalConnections) {

	<span style="color: rgba(0, 0, 255, 1)">this</span>.incrementalConnections = incrementalConnections;

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 返回连接池中最大的可用连接数量
 * 
 * @return 连接池中最大的可用连接数量
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span> getMaxConnections() {

	<span style="color: rgba(0, 0, 255, 1)">return</span> <span style="color: rgba(0, 0, 255, 1)">this</span>.maxConnections;

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 设置连接池中最大可用的连接数量
 * 
 * 
 * 
 * @param 设置连接池中最大可用的连接数量值
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setMaxConnections(<span style="color: rgba(0, 0, 255, 1)">int</span> maxConnections) {

	<span style="color: rgba(0, 0, 255, 1)">this</span>.maxConnections = maxConnections;

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 获取测试数据库表的名字
 * 
 * 
 * 
 * @return 测试数据库表的名字
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> String getTestTable() {

	<span style="color: rgba(0, 0, 255, 1)">return</span> <span style="color: rgba(0, 0, 255, 1)">this</span>.testTable;

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 设置测试表的名字
 * 
 * @param testTable
 *            String 测试表的名字
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setTestTable(String testTable) {

	<span style="color: rgba(0, 0, 255, 1)">this</span>.testTable = testTable;

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 
 * 
 * 创建一个数据库连接池，连接池中的可用连接的数量采用类成员
 * 
 * initialConnections 中设置的值
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">synchronized</span> <span style="color: rgba(0, 0, 255, 1)">void</span> createPool() <span style="color: rgba(0, 0, 255, 1)">throws</span> Exception {

	<span style="color: rgba(0, 128, 0, 1)">// 确保连接池没有创建</span>

	<span style="color: rgba(0, 128, 0, 1)">// 如果连接池己经创建了，保存连接的向量 connections 不会为空</span>

	<span style="color: rgba(0, 0, 255, 1)">if</span> (connections != <span style="color: rgba(0, 0, 255, 1)">null</span>) {

		<span style="color: rgba(0, 0, 255, 1)">return</span>; <span style="color: rgba(0, 128, 0, 1)">// 如果己经创建，则返回</span>

	}

	<span style="color: rgba(0, 128, 0, 1)">// 实例化 JDBC Driver 中指定的驱动类实例</span>

	Driver driver = (Driver) (Class.forName(<span style="color: rgba(0, 0, 255, 1)">this</span>.jdbcDriver).newInstance());

	DriverManager.registerDriver(driver); <span style="color: rgba(0, 128, 0, 1)">// 注册 JDBC 驱动程序</span>

	<span style="color: rgba(0, 128, 0, 1)">// 创建保存连接的向量 , 初始时有 0 个元素</span>

	connections = <span style="color: rgba(0, 0, 255, 1)">new</span> Vector&lt;PooledConnection&gt;();

	<span style="color: rgba(0, 128, 0, 1)">// 根据 initialConnections 中设置的值，创建连接。</span>

	createConnections(<span style="color: rgba(0, 0, 255, 1)">this</span>.initialConnections);

	System.out.println("<span style="color: rgba(139, 0, 0, 1)">create pool</span>");

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 创建由 numConnections 指定数目的数据库连接 , 并把这些连接
 * 
 * 放入 connections 向量中
 * 
 * 
 * 
 * @param numConnections
 *            要创建的数据库连接的数目
 */</span>
<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">void</span> createConnections(<span style="color: rgba(0, 0, 255, 1)">int</span> numConnections) <span style="color: rgba(0, 0, 255, 1)">throws</span> SQLException {

	<span style="color: rgba(0, 128, 0, 1)">// 循环创建指定数目的数据库连接</span>

	<span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">int</span> x = 0; x &lt; numConnections; x++) {

		<span style="color: rgba(0, 128, 0, 1)">// 是否连接池中的数据库连接的数量己经达到最大？最大值由类成员 maxConnections</span>

		<span style="color: rgba(0, 128, 0, 1)">// 指出，如果 maxConnections 为 0 或负数，表示连接数量没有限制。</span>

		<span style="color: rgba(0, 128, 0, 1)">// 如果连接数己经达到最大，即退出。</span>
		System.out.println(<span style="color: rgba(0, 0, 255, 1)">this</span>.connections.size() + "<span style="color: rgba(139, 0, 0, 1)">,</span>" + <span style="color: rgba(0, 0, 255, 1)">this</span>.maxConnections);
		<span style="color: rgba(0, 0, 255, 1)">if</span> (<span style="color: rgba(0, 0, 255, 1)">this</span>.maxConnections &gt; 0 &amp;&amp; <span style="color: rgba(0, 0, 255, 1)">this</span>.connections.size() &gt;= <span style="color: rgba(0, 0, 255, 1)">this</span>.maxConnections) {
			System.out.println("<span style="color: rgba(139, 0, 0, 1)">连接数己经达到最大</span>");
			<span style="color: rgba(0, 0, 255, 1)">break</span>;

		}

		<span style="color: rgba(0, 128, 0, 1)">// add a new PooledConnection object to connections vector</span>

		<span style="color: rgba(0, 128, 0, 1)">// 增加一个连接到连接池中（向量 connections 中）</span>

		<span style="color: rgba(0, 0, 255, 1)">try</span> {

			connections.addElement(<span style="color: rgba(0, 0, 255, 1)">new</span> PooledConnection(newConnection()));

		} <span style="color: rgba(0, 0, 255, 1)">catch</span> (SQLException e) {

			System.out.println("<span style="color: rgba(139, 0, 0, 1)"> 创建数据库连接失败！ </span>" + e.getMessage());

			<span style="color: rgba(0, 0, 255, 1)">throw</span> <span style="color: rgba(0, 0, 255, 1)">new</span> SQLException();

		}

		System.out.println("<span style="color: rgba(139, 0, 0, 1)"> 数据库连接己创建 ......</span>");

	}

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 创建一个新的数据库连接并返回它
 * 
 * 
 * 
 * @return 返回一个新创建的数据库连接
 */</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> Connection newConnection() <span style="color: rgba(0, 0, 255, 1)">throws</span> SQLException {

	<span style="color: rgba(0, 128, 0, 1)">// 创建一个数据库连接</span>
	
	Connection conn = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);

	<span style="color: rgba(0, 128, 0, 1)">// 如果这是第一次创建数据库连接，即检查数据库，获得此数据库允许支持的</span>

	<span style="color: rgba(0, 128, 0, 1)">// 最大客户连接数目</span>

	<span style="color: rgba(0, 128, 0, 1)">// connections.size()==0 表示目前没有连接己被创建</span>

	<span style="color: rgba(0, 0, 255, 1)">if</span> (connections.size() == 0) {

		DatabaseMetaData metaData = conn.getMetaData();

		<span style="color: rgba(0, 0, 255, 1)">int</span> driverMaxConnections = metaData.getMaxConnections();

		<span style="color: rgba(0, 128, 0, 1)">// 数据库返回的 driverMaxConnections 若为 0 ，表示此数据库没有最大</span>

		<span style="color: rgba(0, 128, 0, 1)">// 连接限制，或数据库的最大连接限制不知道</span>

		<span style="color: rgba(0, 128, 0, 1)">// driverMaxConnections 为返回的一个整数，表示此数据库允许客户连接的数目</span>

		<span style="color: rgba(0, 128, 0, 1)">// 如果连接池中设置的最大连接数量大于数据库允许的连接数目 , 则置连接池的最大</span>

		<span style="color: rgba(0, 128, 0, 1)">// 连接数目为数据库允许的最大数目</span>

		<span style="color: rgba(0, 0, 255, 1)">if</span> (driverMaxConnections &gt; 0 &amp;&amp; <span style="color: rgba(0, 0, 255, 1)">this</span>.maxConnections &gt; driverMaxConnections) {

			<span style="color: rgba(0, 0, 255, 1)">this</span>.maxConnections = driverMaxConnections;

		}

	}

	<span style="color: rgba(0, 0, 255, 1)">return</span> conn; <span style="color: rgba(0, 128, 0, 1)">// 返回创建的新的数据库连接</span>

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 通过调用 getFreeConnection() 函数返回一个可用的数据库连接 ,
 * 
 * 如果当前没有可用的数据库连接，并且更多的数据库连接不能创
 * 
 * 建（如连接池大小的限制），此函数等待一会再尝试获取。
 * 
 * 
 * 
 * @return 返回一个可用的数据库连接对象
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">synchronized</span> PooledConnection getConnection() <span style="color: rgba(0, 0, 255, 1)">throws</span> SQLException {

	<span style="color: rgba(0, 128, 0, 1)">// 确保连接池己被创建</span>

	<span style="color: rgba(0, 0, 255, 1)">if</span> (connections == <span style="color: rgba(0, 0, 255, 1)">null</span>) {

		<span style="color: rgba(0, 0, 255, 1)">return</span> <span style="color: rgba(0, 0, 255, 1)">null</span>; <span style="color: rgba(0, 128, 0, 1)">// 连接池还没创建，则返回 null</span>

	}

	PooledConnection conn = getFreeConnection(); <span style="color: rgba(0, 128, 0, 1)">// 获得一个可用的数据库连接</span>

	<span style="color: rgba(0, 128, 0, 1)">// 如果目前没有可以使用的连接，即所有的连接都在使用中</span>

	<span style="color: rgba(0, 0, 255, 1)">while</span> (conn == <span style="color: rgba(0, 0, 255, 1)">null</span>) {

		<span style="color: rgba(0, 128, 0, 1)">// 等一会再试</span>

		wait(250);

		conn = getFreeConnection(); <span style="color: rgba(0, 128, 0, 1)">// 重新再试，直到获得可用的连接，如果</span>

		<span style="color: rgba(0, 128, 0, 1)">// getFreeConnection() 返回的为 null</span>

		<span style="color: rgba(0, 128, 0, 1)">// 则表明创建一批连接后也不可获得可用连接</span>

	}
	
	<span style="color: rgba(0, 0, 255, 1)">return</span> conn;<span style="color: rgba(0, 128, 0, 1)">// 返回获得的可用的连接</span>

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 本函数从连接池向量 connections 中返回一个可用的的数据库连接，如果
 * 
 * 当前没有可用的数据库连接，本函数则根据 incrementalConnections 设置
 * 
 * 的值创建几个数据库连接，并放入连接池中。
 * 
 * 如果创建后，所有的连接仍都在使用中，则返回 null
 * 
 * @return 返回一个可用的数据库连接
 */</span>
<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> print() {
	System.out.println("<span style="color: rgba(139, 0, 0, 1)">total connection:</span>" + connections.size());
	<span style="color: rgba(0, 0, 255, 1)">int</span> i = 1;
	<span style="color: rgba(0, 0, 255, 1)">for</span> (PooledConnection conn : connections) {
		System.out.println("<span style="color: rgba(139, 0, 0, 1)">---</span>" + i + "<span style="color: rgba(139, 0, 0, 1)">:</span>" + conn.isBusy());
	}
}

<span style="color: rgba(0, 0, 255, 1)">private</span> PooledConnection getFreeConnection() <span style="color: rgba(0, 0, 255, 1)">throws</span> SQLException {

	<span style="color: rgba(0, 128, 0, 1)">// 从连接池中获得一个可用的数据库连接</span>

	PooledConnection conn = findFreeConnection();

	<span style="color: rgba(0, 0, 255, 1)">if</span> (conn == <span style="color: rgba(0, 0, 255, 1)">null</span>) {

		<span style="color: rgba(0, 128, 0, 1)">// 如果目前连接池中没有可用的连接</span>
		<span style="color: rgba(0, 128, 0, 1)">// 创建一些连接</span>
		System.out.println("<span style="color: rgba(139, 0, 0, 1)">目前连接池中没有可用的连接,创建一些连接 </span>");
		createConnections(incrementalConnections);
		<span style="color: rgba(0, 128, 0, 1)">// 重新从池中查找是否有可用连接</span>
		conn = findFreeConnection();

		<span style="color: rgba(0, 0, 255, 1)">if</span> (conn == <span style="color: rgba(0, 0, 255, 1)">null</span>) {
			<span style="color: rgba(0, 128, 0, 1)">// 如果创建连接后仍获得不到可用的连接，则返回 null</span>
			<span style="color: rgba(0, 0, 255, 1)">return</span> <span style="color: rgba(0, 0, 255, 1)">null</span>;
		}

	}

	<span style="color: rgba(0, 0, 255, 1)">return</span> conn;

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 查找连接池中所有的连接，查找一个可用的数据库连接，
 * 
 * 如果没有可用的连接，返回 null
 * 
 * 
 * 
 * @return 返回一个可用的数据库连接
 */</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> PooledConnection findFreeConnection() <span style="color: rgba(0, 0, 255, 1)">throws</span> SQLException {

	
	<span style="color: rgba(0, 128, 0, 1)">// 获得连接池向量中所有的对象</span>
	<span style="color: rgba(0, 0, 255, 1)">for</span> (<span style="color: rgba(0, 0, 255, 1)">int</span> i = 0; i &lt; connections.size(); i++) {
		PooledConnection pc = connections.elementAt(i);
		<span style="color: rgba(0, 128, 0, 1)">// System.out.println("pConn.isBusy():"+pConn.isBusy());</span>
		<span style="color: rgba(0, 0, 255, 1)">if</span> (!pc.isBusy()) {
			<span style="color: rgba(0, 128, 0, 1)">// 如果此对象不忙，则获得它的数据库连接并把它设为忙</span>
			Connection conn = pc.getConnection();
			pc.setBusy(<span style="color: rgba(0, 0, 255, 1)">true</span>);
			<span style="color: rgba(0, 128, 0, 1)">// 测试此连接是否可用</span>
			<span style="color: rgba(0, 0, 255, 1)">if</span> (!isValid(conn)) {

				<span style="color: rgba(0, 128, 0, 1)">// 如果此连接不可再用了，则创建一个新的连接，</span>

				<span style="color: rgba(0, 128, 0, 1)">// 并替换此不可用的连接对象，如果创建失败，删除该无效连接，遍历下一个不忙连接</span>

				<span style="color: rgba(0, 0, 255, 1)">try</span> {
					conn = newConnection();
					pc.setConnection(conn);
				} <span style="color: rgba(0, 0, 255, 1)">catch</span> (SQLException e) {
					e.printStackTrace();
					connections.remove(i--);
					<span style="color: rgba(0, 0, 255, 1)">continue</span>;
				}
			}
	
			<span style="color: rgba(0, 0, 255, 1)">return</span> pc; <span style="color: rgba(0, 128, 0, 1)">// 己经找到一个可用的连接，退出</span>

		}
	}

	<span style="color: rgba(0, 0, 255, 1)">return</span> <span style="color: rgba(0, 0, 255, 1)">null</span>;<span style="color: rgba(0, 128, 0, 1)">// 返回找到到的可用连接</span>

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 测试一个连接是否可用，如果不可用，关掉它并返回 false
 * 
 * 否则可用返回 true
 * 
 * 
 * 
 * @param conn
 *            需要测试的数据库连接
 * 
 * @return 返回 true 表示此连接可用， false 表示不可用
 */</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">boolean</span> isValid(Connection conn) {
	

	<span style="color: rgba(0, 0, 255, 1)">try</span> {
		<span style="color: rgba(0, 0, 255, 1)">return</span> conn.isValid(3000);
	} <span style="color: rgba(0, 0, 255, 1)">catch</span> (SQLException e) {
		<span style="color: rgba(0, 128, 0, 1)">// TODO Auto-generated catch block</span>
		e.printStackTrace();
		<span style="color: rgba(0, 0, 255, 1)">return</span> <span style="color: rgba(0, 0, 255, 1)">false</span>;
	}
	<span style="color: rgba(0, 128, 0, 1)">// try {</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// // 判断测试表是否存在</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// if (testTable.equals("")) {</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// // 如果测试表为空，试着使用此连接的 setAutoCommit() 方法</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// // 来判断连接否可用（此方法只在部分数据库可用，如果不可用 ,</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// // 抛出异常）。注意：使用测试表的方法更可靠</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// conn.setAutoCommit(true);</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// } else {// 有测试表的时候使用测试表测试</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// // check if this connection is valid</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// Statement stmt = conn.createStatement();</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// stmt.execute("select count(*) from " + testTable);</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// }</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// } catch (SQLException e) {</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// // 上面抛出异常，此连接己不可用，关闭它，并返回 false;</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// closeConnection(conn);</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// return false;</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// }</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// // 连接可用，返回 true</span>
	<span style="color: rgba(0, 128, 0, 1)">//</span>
	<span style="color: rgba(0, 128, 0, 1)">// return true;</span>

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 此函数返回一个数据库连接到连接池中，并把此连接置为空闲。
 * 
 * 所有使用连接池获得的数据库连接均应在不使用此连接时返回它。
 * 
 * 
 * 
 * @param 需返回到连接池中的连接对象
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> returnConnection(Connection conn) {

	<span style="color: rgba(0, 128, 0, 1)">// 确保连接池存在，如果连接没有创建（不存在），直接返回</span>

	<span style="color: rgba(0, 0, 255, 1)">if</span> (connections == <span style="color: rgba(0, 0, 255, 1)">null</span>) {

		System.out.println("<span style="color: rgba(139, 0, 0, 1)"> 连接池不存在，无法返回此连接到连接池中 !</span>");

		<span style="color: rgba(0, 0, 255, 1)">return</span>;

	}

	PooledConnection pConn = <span style="color: rgba(0, 0, 255, 1)">null</span>;

	Enumeration&lt;PooledConnection&gt; enumerate = connections.elements();

	<span style="color: rgba(0, 128, 0, 1)">// 遍历连接池中的所有连接，找到这个要返回的连接对象</span>

	<span style="color: rgba(0, 0, 255, 1)">while</span> (enumerate.hasMoreElements()) {

		pConn = (PooledConnection) enumerate.nextElement();

		<span style="color: rgba(0, 128, 0, 1)">// 先找到连接池中的要返回的连接对象</span>

		<span style="color: rgba(0, 0, 255, 1)">if</span> (conn == pConn.getConnection()) {

			<span style="color: rgba(0, 128, 0, 1)">// 找到了 , 设置此连接为空闲状态</span>

			pConn.setBusy(<span style="color: rgba(0, 0, 255, 1)">false</span>);

			<span style="color: rgba(0, 0, 255, 1)">break</span>;

		}

	}

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 刷新连接池中所有的连接对象
 * 
 * 
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">synchronized</span> <span style="color: rgba(0, 0, 255, 1)">void</span> refreshConnections() <span style="color: rgba(0, 0, 255, 1)">throws</span> SQLException {

	<span style="color: rgba(0, 128, 0, 1)">// 确保连接池己创新存在</span>

	<span style="color: rgba(0, 0, 255, 1)">if</span> (connections == <span style="color: rgba(0, 0, 255, 1)">null</span>) {

		System.out.println("<span style="color: rgba(139, 0, 0, 1)"> 连接池不存在，无法刷新 !</span>");

		<span style="color: rgba(0, 0, 255, 1)">return</span>;

	}

	PooledConnection pConn = <span style="color: rgba(0, 0, 255, 1)">null</span>;

	Enumeration&lt;PooledConnection&gt; enumerate = connections.elements();

	<span style="color: rgba(0, 0, 255, 1)">while</span> (enumerate.hasMoreElements()) {

		<span style="color: rgba(0, 128, 0, 1)">// 获得一个连接对象</span>

		pConn = (PooledConnection) enumerate.nextElement();

		<span style="color: rgba(0, 128, 0, 1)">// 如果对象忙则等 5 秒 ,5 秒后直接刷新</span>

		<span style="color: rgba(0, 0, 255, 1)">if</span> (pConn.isBusy()) {

			wait(5000); <span style="color: rgba(0, 128, 0, 1)">// 等 5 秒</span>

		}

		<span style="color: rgba(0, 128, 0, 1)">// 关闭此连接，用一个新的连接代替它。</span>

		closeConnection(pConn.getConnection());

		pConn.setConnection(newConnection());

		pConn.setBusy(<span style="color: rgba(0, 0, 255, 1)">false</span>);

	}

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 关闭连接池中所有的连接，并清空连接池。
 */</span>

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">synchronized</span> <span style="color: rgba(0, 0, 255, 1)">void</span> closeConnectionPool() <span style="color: rgba(0, 0, 255, 1)">throws</span> SQLException {

	<span style="color: rgba(0, 128, 0, 1)">// 确保连接池存在，如果不存在，返回</span>

	<span style="color: rgba(0, 0, 255, 1)">if</span> (connections == <span style="color: rgba(0, 0, 255, 1)">null</span>) {

		System.out.println("<span style="color: rgba(139, 0, 0, 1)"> 连接池不存在，无法关闭 !</span>");

		<span style="color: rgba(0, 0, 255, 1)">return</span>;

	}

	PooledConnection pConn = <span style="color: rgba(0, 0, 255, 1)">null</span>;

	Enumeration&lt;PooledConnection&gt; enumerate = connections.elements();

	<span style="color: rgba(0, 0, 255, 1)">while</span> (enumerate.hasMoreElements()) {

		pConn = (PooledConnection) enumerate.nextElement();

		<span style="color: rgba(0, 128, 0, 1)">// 如果忙，等 5 秒</span>

		<span style="color: rgba(0, 0, 255, 1)">if</span> (pConn.isBusy()) {

			wait(5000); <span style="color: rgba(0, 128, 0, 1)">// 等 5 秒</span>

		}

		<span style="color: rgba(0, 128, 0, 1)">// 5 秒后直接关闭它</span>

		closeConnection(pConn.getConnection());

		<span style="color: rgba(0, 128, 0, 1)">// 从连接池向量中删除它</span>

		connections.removeElement(pConn);

	}

	<span style="color: rgba(0, 128, 0, 1)">// 置连接池为空</span>

	connections = <span style="color: rgba(0, 0, 255, 1)">null</span>;

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 关闭一个数据库连接
 * 
 * 
 * 
 * @param 需要关闭的数据库连接
 */</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">void</span> closeConnection(Connection conn) {

	<span style="color: rgba(0, 0, 255, 1)">try</span> {

		conn.close();

	} <span style="color: rgba(0, 0, 255, 1)">catch</span> (SQLException e) {

		System.out.println("<span style="color: rgba(139, 0, 0, 1)"> 关闭数据库连接出错： </span>" + e.getMessage());

	}

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 使程序等待给定的毫秒数
 * 
 * 
 * 
 * @param 给定的毫秒数
 */</span>

<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">void</span> wait(<span style="color: rgba(0, 0, 255, 1)">int</span> mSeconds) {

	<span style="color: rgba(0, 0, 255, 1)">try</span> {

		Thread.sleep(mSeconds);

	} <span style="color: rgba(0, 0, 255, 1)">catch</span> (InterruptedException e) {

	}

}

<span style="color: rgba(0, 128, 0, 1)">/**
 * 
 * 内部使用的用于保存连接池中连接对象的类
 * 
 * 此类中有两个成员，一个是数据库的连接，另一个是指示此连接是否
 * 
 * 正在使用的标志。
 */</span>

<span style="color: rgba(0, 0, 255, 1)">class</span> PooledConnection {

	<span style="color: rgba(0, 0, 255, 1)">private</span> Connection connection = <span style="color: rgba(0, 0, 255, 1)">null</span>;<span style="color: rgba(0, 128, 0, 1)">// 数据库连接</span>

	<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">boolean</span> busy ; <span style="color: rgba(0, 128, 0, 1)">// 此连接是否正在使用的标志，默认没有正在使用</span>

	<span style="color: rgba(0, 128, 0, 1)">// 构造函数，根据一个 Connection 构告一个 PooledConnection 对象</span>

	<span style="color: rgba(0, 0, 255, 1)">private</span> PooledConnection(Connection connection) {

		<span style="color: rgba(0, 0, 255, 1)">this</span>.connection = connection;

	}

	<span style="color: rgba(0, 0, 255, 1)">public</span> ResultSet executeQuery(String sql) <span style="color: rgba(0, 0, 255, 1)">throws</span> SQLException {
		<span style="color: rgba(0, 0, 255, 1)">return</span> connection.createStatement().executeQuery(sql);
	}
	
	<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">int</span> executeUpdate(String sql) <span style="color: rgba(0, 0, 255, 1)">throws</span> SQLException {
		<span style="color: rgba(0, 0, 255, 1)">return</span> connection.createStatement().executeUpdate(sql);
	}

	<span style="color: rgba(0, 128, 0, 1)">// 返回此对象中的连接</span>

	<span style="color: rgba(0, 0, 255, 1)">private</span> Connection getConnection() {

		<span style="color: rgba(0, 0, 255, 1)">return</span> connection;

	}

	<span style="color: rgba(0, 128, 0, 1)">// 设置此对象的，连接</span>

	<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setConnection(Connection connection) {

		<span style="color: rgba(0, 0, 255, 1)">this</span>.connection = connection;

	}

	<span style="color: rgba(0, 128, 0, 1)">// 获得对象连接是否忙</span>

	<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">boolean</span> isBusy() {

		<span style="color: rgba(0, 0, 255, 1)">return</span> busy;

	}

	<span style="color: rgba(0, 128, 0, 1)">// 设置对象的连接正在忙</span>

	<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">void</span> setBusy(<span style="color: rgba(0, 0, 255, 1)">boolean</span> busy) {
		<span style="color: rgba(0, 128, 0, 1)">// System.out.println("set to busy:"+busy);</span>
		<span style="color: rgba(0, 0, 255, 1)">this</span>.busy = busy;

	}

	<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> close() {
		busy = <span style="color: rgba(0, 0, 255, 1)">false</span>;
	}



}



}

DBManager 类:
package com.vl.sql;
import java.sql.SQLException;
import com.vl.Config;
import com.vl.sql.ConnectionPool.PooledConnection;

public class DBManager {


<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> PooledConnection conn;
<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> ConnectionPool connectionPool;
<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> DBManager inst;

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span> close() {
	<span style="color: rgba(0, 0, 255, 1)">try</span> {
		connectionPool.closeConnectionPool();
	} <span style="color: rgba(0, 0, 255, 1)">catch</span> (SQLException e) {
		<span style="color: rgba(0, 128, 0, 1)">// TODO Auto-generated catch block</span>
		e.printStackTrace();
	}
}

<span style="color: rgba(0, 0, 255, 1)">public</span> DBManager() {
	<span style="color: rgba(0, 0, 255, 1)">if</span> (inst != <span style="color: rgba(0, 0, 255, 1)">null</span>)
		<span style="color: rgba(0, 0, 255, 1)">return</span>;

	<span style="color: rgba(0, 128, 0, 1)">// TODO Auto-generated constructor stub</span>

	String connStr = String.format("<span style="color: rgba(139, 0, 0, 1)">jdbc:mysql://%s:%d/%s</span>", Config.getInstance().mysqlHost, Config.getInstance().mysqlPort,
			Config.getInstance().mysqlDB);
	connectionPool = <span style="color: rgba(0, 0, 255, 1)">new</span> ConnectionPool("<span style="color: rgba(139, 0, 0, 1)">com.mysql.jdbc.Driver</span>", connStr, Config.getInstance().mysqlUser, Config.getInstance().mysqlPassword);
	<span style="color: rgba(0, 0, 255, 1)">try</span> {
		connectionPool.createPool();
		inst = <span style="color: rgba(0, 0, 255, 1)">this</span>;
		

	} <span style="color: rgba(0, 0, 255, 1)">catch</span> (Exception e) {
		<span style="color: rgba(0, 128, 0, 1)">// TODO Auto-generated catch block</span>
		e.printStackTrace();
	}

}

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span> PooledConnection getConnection() {
	<span style="color: rgba(0, 0, 255, 1)">if</span> (inst == <span style="color: rgba(0, 0, 255, 1)">null</span>)
		<span style="color: rgba(0, 0, 255, 1)">new</span> DBManager();

	<span style="color: rgba(0, 0, 255, 1)">try</span> {
		
		conn = connectionPool.getConnection();
		
	} <span style="color: rgba(0, 0, 255, 1)">catch</span> (SQLException e) {
		<span style="color: rgba(0, 128, 0, 1)">// TODO Auto-generated catch block</span>
		e.printStackTrace();
	}

	<span style="color: rgba(0, 0, 255, 1)">return</span> conn;
}

<span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span> main(String[] args) {

}



}


用法：
                String sql = "....";
		ResultSet rs;
		PooledConnection conn = null;
		try {conn = DBManager.getConnection();
			rs = conn.executeQuery(sql);

		<span style="color: rgba(0, 0, 255, 1)">if</span> (rs.next())
		<span style="color: rgba(0, 0, 255, 1)">return</span> rs.getInt(1);
	} <span style="color: rgba(0, 0, 255, 1)">catch</span> (SQLException e) {
		<span style="color: rgba(0, 128, 0, 1)">// TODO Auto-generated catch block</span>
		e.printStackTrace();
	} <span style="color: rgba(0, 0, 255, 1)">finally</span> {
		conn.close();
	}
	<span style="color: rgba(0, 0, 255, 1)">return</span> 0;</pre>