基于proxool的数据库连接池

proxool是一个使用方便的数据库连接池.
适应于一个应用需要同时连接多种或多个数据库的场合.
该连接池能够按照别名获取数据库连接.
别名的生成为 url加username,因为这两个组合能够区分不同数据库和统一数据库不同用户的连接.

连接池代码:

import java.sql.Connection;
import java.sql.SQLException;

import java.util.HashMap;

import java.util.Map;

import org.apache.log4j.Logger;
import org.logicalcobwebs.proxool.ProxoolConstants;
import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.ProxoolFacade;
import org.logicalcobwebs.proxool.admin.SnapshotIF;

import com.hedes.exception.cp.ConnectionPoolException;
import com.hedes.exception.cp.ConnectionPoolNotExistAliasException;
import com.hedes.exception.cp.InitConnectionPoolException;
import com.hedes.exception.cp.NullConnectionException;

import java.sql.DriverManager;

import java.util.Properties;

/**
 * 基于proxool的数据库连接池
 * 
 * @author AXIN
 */
public class ConnectionPool {

	private static Logger log = Logger.getLogger(ConnectionPool.class);

	private static Map idList = new HashMap();// 别名映射

	private static int ids = 0;// 别名序列

	private static ConnectionPool c = null;// 唯一实例

	/**
	 * 初始化数据库连接池
	 * 
	 * @throws InitConnectionPoolException
	 *             初始化失败异常
	 */
	private ConnectionPool() throws InitConnectionPoolException {
		try {
			Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
		} catch (ClassNotFoundException e) {
			throw new InitConnectionPoolException(e);
		}
	}

	/**
	 * 获取数据库连接池实例
	 * 
	 * @return 数据库连接池实例
	 * @throws InitConnectionPoolException
	 *             初始化失败异常
	 */
	public final static ConnectionPool getConnectionPool()
			throws InitConnectionPoolException {
		if (c == null) {
			try {
				c = new ConnectionPool();
			} catch (InitConnectionPoolException e) {
				throw e;
			}
		}
		return c;
	}

	/**
	 * 获取别名 普通的访问方式 在别名不存在的情况下不创建数据库连接
	 * 
	 * @param prop
	 *            连接参数
	 * @return
	 * @throws ConnectionPoolNotExistAliasException
	 */
	public final synchronized String getId(java.util.Properties prop)
			throws ConnectionPoolNotExistAliasException {
		if (prop == null) {
			throw new ConnectionPoolNotExistAliasException(
					"无效的入参 Properties.--" + prop);
		}
		String URL = prop.getProperty("URL");
		String USER = prop.getProperty("USER");
		if (URL == null || "".compareTo(URL.trim()) == 0) {
			throw new ConnectionPoolNotExistAliasException("无效的入参 URL.--" + URL);
		}
		if (USER == null || "".compareTo(USER.trim()) == 0) {
			throw new ConnectionPoolNotExistAliasException("无效的入参 USER.--"
					+ URL);
		}
		String alias = (String) idList.get(URL.toLowerCase() + "-"
				+ USER.toLowerCase());
		if (alias == null) {
			throw new ConnectionPoolNotExistAliasException("null");
		}
		return alias;

	}

	/**
	 * 获取别名 特殊的访问方式 在别名不存在的情况下尝试创建数据库连接
	 * 
	 * @param prop
	 *            连接参数
	 * @return
	 * @throws ConnectionPoolNotExistAliasException
	 */
	public final synchronized String getIdSpecial(java.util.Properties prop)
			throws ConnectionPoolNotExistAliasException {
		if (prop == null) {
			throw new ConnectionPoolNotExistAliasException(
					"无效的入参 Properties.--" + prop);
		}
		String URL = prop.getProperty("URL");
		String USER = prop.getProperty("USER");
		if (URL == null || "".compareTo(URL.trim()) == 0) {
			throw new ConnectionPoolNotExistAliasException("无效的入参 URL.--" + URL);
		}
		if (USER == null || "".compareTo(USER.trim()) == 0) {
			throw new ConnectionPoolNotExistAliasException("无效的入参 USER.--"
					+ URL);
		}
		String alias = (String) idList.get(URL.toLowerCase() + "-"
				+ USER.toLowerCase());
		if (alias == null) {
			try {
				this.add(prop);
			} catch (ConnectionPoolException e) {
				e.printStackTrace();
				throw new ConnectionPoolNotExistAliasException(e.getMessage());
			}
		}
		alias = (String) idList.get(URL.toLowerCase() + "-"
				+ USER.toLowerCase());
		return alias;

	}

	/**
	 * 数据库连接参数 字符串类型校验
	 * 
	 * @param value
	 *            值
	 * @param key
	 *            关键字
	 * @throws ConnectionPoolException
	 */
	private void checkPropValueString(final String value, final String key)
			throws ConnectionPoolException {
		if (value == null || "".compareTo(value.trim()) == 0) {
			throw new ConnectionPoolException("无效的入参 " + key + ".--" + key);
		}
	}

	/**
	 * 数据库连接参数 数字类型校验
	 * 
	 * @param value
	 *            值
	 * @param key
	 *            关键字
	 * @param def
	 *            默认值
	 * @return
	 * @throws ConnectionPoolException
	 */
	private String checkPropValueNumber(final String value, final String key,
			final String def) throws ConnectionPoolException {
		if (value == null || "".compareTo(value.trim()) == 0) {
			return def;
		}
		try {
			int temp = Integer.parseInt(value);
			if (temp < 1) {
				throw new ConnectionPoolException("无效的入参 " + key + ".--" + key);
			}
		} catch (java.lang.NumberFormatException e) {
			throw new ConnectionPoolException("无效的入参 " + key + ".--" + key);
		}
		return value;

	}

	/**
	 * 添加一个数据库连接池
	 * 
	 * @param prop
	 *            连接参数
	 * @throws ConnectionPoolException
	 */
	public final synchronized void add(java.util.Properties prop)
			throws ConnectionPoolException {
		if (prop == null) {
			throw new ConnectionPoolException("无效的入参 Properties.--" + prop);
		}
		String DRIVER = prop.getProperty("DRIVER");
		checkPropValueString(DRIVER, "DRIVER");
		String URL = prop.getProperty("URL");
		checkPropValueString(URL, "URL");
		String USER = prop.getProperty("USER");
		checkPropValueString(USER, "USER");
		String PASSWORD = prop.getProperty("PASSWORD");

		String MIN = prop.getProperty("MIN");
		String MAX = prop.getProperty("MAX");
		String INIT = prop.getProperty("INIT");
		String WATITIME = prop.getProperty("WATITIME");

		MIN = checkPropValueNumber(MIN, "MIN", "5");
		MAX = checkPropValueNumber(MAX, "MAX", "20");
		INIT = checkPropValueNumber(INIT, "INIT", "5");
		WATITIME = checkPropValueNumber(WATITIME, "WATITIME", "50000");

		if (Integer.parseInt(INIT) < Integer.parseInt(MIN)) {
			throw new ConnectionPoolException("最小连接数必须小于初始化连接数!");
		}
		Properties info = new Properties();
		info.setProperty(ProxoolConstants.USER_PROPERTY, USER);
		info.setProperty(ProxoolConstants.PASSWORD_PROPERTY, PASSWORD);
		info.setProperty(ProxoolConstants.VERBOSE_PROPERTY, Boolean.TRUE
				.toString());
		info.setProperty(ProxoolConstants.MINIMUM_CONNECTION_COUNT_PROPERTY,
				MIN);
		info.setProperty(ProxoolConstants.MAXIMUM_CONNECTION_COUNT_PROPERTY,
				MAX);
		info.setProperty(ProxoolConstants.PROTOTYPE_COUNT_PROPERTY, INIT);
		info.setProperty(ProxoolConstants.HOUSE_KEEPING_SLEEP_TIME_PROPERTY,
				WATITIME);
		String alias = "HEDES-" + ids;
		idList.put(URL.toLowerCase() + "-" + USER.toLowerCase(), alias);
		ids = ids + 1;
		String url = "proxool." + alias + ":" + DRIVER + ":" + URL;
		try {
			ProxoolFacade.registerConnectionPool(url, info);
		} catch (ProxoolException e) {
			throw new ConnectionPoolException("创建连接池错误.--" + e.getMessage());
		}
	}

	/**
	 * 获取连接
	 * 
	 * @param key
	 *            别名
	 * @return 一个可用的数据库连接
	 * @throws NullConnectionException
	 */
	public final Connection getConnection(String key)
			throws NullConnectionException {
		try {
			Connection conn = DriverManager.getConnection("proxool." + key);
			// wacth(key);
			if (conn == null) {
				throw new NullConnectionException("无法获取连接" + key + ".");
			}
			return conn;
		} catch (SQLException e) {
			throw new NullConnectionException("获取连接异常.", e);
		}
	}

	/**
	 * 查看指定别名下的连接池情况
	 * 
	 * @param key
	 *            别名
	 */
	public final void wacth(String key) {
		SnapshotIF snapshot;
		try {
			snapshot = ProxoolFacade.getSnapshot(key, true);

			int curActiveCount = snapshot.getActiveConnectionCount();
			int availableCount = snapshot.getAvailableConnectionCount();
			int maxCount = snapshot.getMaximumConnectionCount();

			log.info("----------------------------------");
			log.info(curActiveCount + "(当前活动连接)  " + availableCount

			+ "(可用连接)  " + maxCount + "(最大连接)");
			log.info("----------------------------------");
		} catch (ProxoolException e) {
			e.printStackTrace();
		}
	}
}

调用方式:

             

Properties prop = new Properties(); 
		prop.put("URL", "");
		prop.put("DRIVER", "");
		prop.put("USER", "");
		prop.put("PASSWORD", "");
		prop.put("MIN", "5");
		prop.put("MAX", "10");
		prop.put("INIT", "6");
		prop.put("WATITIME", "10000");

		ConnectionPool cp;
		try {
			cp = ConnectionPool.getConnectionPool();
		} catch (InitConnectionPoolException e) {
			//TODO 
		}

		try {
			id = cp.getIdSpecial(prop);
		} catch (ConnectionPoolNotExistAliasException e) {
			//TODO 
		}
		try {
			Connection cn=cp.getConnection(id);
                        //TODO 
		} catch (NullConnectionException e) {
			//TODO 
		}

虽然调用麻烦,但是在一定程度上能够保证安全性和稳定性.在获取到别名以后获取连接的方式就简单很多了.
注:连接在使用完毕后请记得要关闭.