在独立的应用程序中创建和使用连接池

在独立的应用程序中创建和使用连接池

很多时候我们都需要独立开发应用程序来处理业务，这个时候也需要利用连接也来进行数据库的访问。这里我们用Apache的DBCP来构造连接池。

DBConnPool.java

package jead.chap4;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;

public class DBConnPool
{
	static {
		setupPool();
	}

	public static void setupPool()
	{
		System.out.println("初始化线程池");
		String strDBConnUrl = "jdbc:mysql://localhost:3306/test";
		// 连接名称
		String strPoolName = "testPool";
		String strJdbcDriverName = "com.mysql.jdbc.Driver";
		String strPoolingDriverName = "org.apache.commons.dbcp.PoolingDriver";
		// 接连数据库用户
		String strUser = "root";
		// 连接数据库密码
		String strPassword = "111111";
		String strUseUnicode = "true";
		// 数据库使用编辑
		String strCharacterEncoding = "utf-8";
		// 最大活动连接
		int intMaxAction = 10;
		// 最大空闲连接数
		int intMaxIdle = 5;
		// 最大等待连接数
		int intMaxWait = 100;
		try {
			// 加载JDBC到JVM
			Class.forName(strJdbcDriverName);

			// 创建通用对象池
			GenericObjectPool connectionPool = new GenericObjectPool(null);

			// 设置通用对象池参数
			connectionPool.setMaxActive(intMaxAction);
			connectionPool.setMaxIdle(intMaxIdle);
			connectionPool.setMaxWait(intMaxWait);

			// 接下来创建连接工厂池，对象会利用连接工产生成数据库连接
			// 这里使用的是DriverManagerConnectionFactory连接工产，利用
			// 连接参数来构造这个连接工产

			Properties prop = new Properties();

			prop.setProperty("user", strUser);
			prop.setProperty("password", strPassword);
			prop.setProperty("useUnicode", strUseUnicode);
			prop.setProperty("characterEncoding", strCharacterEncoding);

			ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
					strDBConnUrl, prop);

			// 现在创建PoolableConnectionFactory对象，安包装了连接工产创建 的连接 使用其池的功能
			@SuppressWarnings("unused")
			PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
					connectionFactory, connectionPool, null, null, false, true);

			// 最后，创建连接也驱动
			Class.forName(strPoolingDriverName);
			PoolingDriver driver = (PoolingDriver) DriverManager
					.getDriver("jdbc:apache:commons:dbcp:");

			// 用连接池驱动注册连接池到JVM
			driver.registerPool(strPoolName, connectionPool);

			// 完成以上步骤以后就可以使用连接池了，我们利用getConnection从池是取得一个返回

		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("连接池：" + strPoolName + "建立成功！");

	}

	//获取连接
	public static Connection getConnection(String strPoolName)
			throws SQLException, ClassNotFoundException
	{
		Connection conn = null;
		conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:"
				+ strPoolName);

		return conn;
	}
	
	//关闭连接池
	public static void closePool(String strPoolName)
	{
		try {
			PoolingDriver driver=(PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
			driver.closePool(strPoolName);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		System.out.println("连接池"+strPoolName+" 成功关闭");
	}
	
	
	//打印连接状态
	public static void printDriverStats(String strPoolName)
	{
		try {
			PoolingDriver driver=(PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
			
			ObjectPool connectionPool= driver.getConnectionPool(strPoolName);
			System.out.println("NumActive:"+connectionPool.getNumActive());
			System.out.println("NumIdle:"+connectionPool.getNumIdle());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

 

TestConnPool.java

package jead.chap4;
import java.sql.*;
public class TestConnPool
{
	private boolean stop = false;	
	public void testQuery()
		{
			Connection conn = null;
			Statement smt = null;
			ResultSet rs = null;
			try {
				conn = DBConnPool.getConnection("testPool");
				smt = conn.createStatement();
				String sql = "select * from login";
				rs = smt.executeQuery(sql);
				while (rs.next()) {
					System.out.println("id:" + rs.getInt("_id") + " username:"
							+ rs.getString("_username") + " password:"
							+ rs.getString("_password") + " desc:"
							+ rs.getString("_desc"));
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if (rs != null)
						rs.close();
					if (smt != null)
						smt.close();
					if (conn != null)
						conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	
	public static void main(String[] args)
	{
		try {
			Class.forName("jead.chap4.DBConnPool");
		} catch (Exception e) {
			e.printStackTrace();
		}

		TestConnPool tcp = new TestConnPool();		
		tcp.testQuery();

	}
}

 

运行结果：

初始化线程池 连接池：testPool建立成功！ id:1 username:张三 password:111111 desc:一个人 id:2 username:李四 password:111111 desc:一个人 id:3 username:王五 password:111111 desc:一个人 id:4 username:马拉多纳 password:10 desc:阿根足球运动员 id:5 username:坎通纳 password:10 desc:法国足球运动员 id:6 username:罗马里奥 password:10 desc:法国足球运动员 id:7 username:古利特 password:10 desc:荷兰足球运动员