proxool数据连接池总结

数据库连接池总结----Proxool

1、所需jar包: proxool-0.9.1.jar 和 proxool-cglib.jar
下载地址: http://sourceforge.net/projects/proxool/

2、通过 ProxoolDriver 取得 connection

	public Connection getConnectThrowProxoolDriver () {
		Connection connection =  null ; 
		try  { 
			Class.forName( "org.logicalcobwebs.proxool.ProxoolDriver" ); 
			connection = DriverManager.getConnection( 
			"proxool.test:org.gjt.mm.mysql.Driver:jdbc:mysql://localhost:3306/test" , 
			"root" , 
			"1234" 
			); 
		}  catch  (Exception e) { 
			System.out.println(e);
		} 
		return connection;
	}

3、使用 properties 或者 xml 来设置 DB URL 及 Driver
可以采用 xml 配置文件, 在程序中使用 JAXPConfigurator.configure("proxool.xml", false); 配置文件设置如下

<?xml version="1.0" encoding="UTF-8"?>  
    <!--   
        the proxool configuration can be embedded within your own   
        application's. Anything outside the "proxool" tag is ignored.   
    -->  
<something-else-entirely>  
    <proxool>  
        <alias>dbname</alias> <!--数据源的别名-->  
        <driver-url>jdbc:mysql://localhost:3306/test</driver-url><!--url连接串-->  
        <driver-class>org.gjt.mm.mysql.Driver</driver-class> <!--驱动类-->  
        <driver-properties>  
            <property name="user" value="root" /> <!--用户名-->  
            <property name="password" value="1234" /><!--密码-->  
        </driver-properties>    
        <!--最大连接数(默认5个),超过了这个连接数,再有请求时,就排在队列中等候,最大的等待请求数由maximum-new-connections决定 -->  
        <maximum-connection-count>100</maximum-connection-count>    
        <!--最小连接数(默认2个)-->  
        <minimum-connection-count>10</minimum-connection-count>    
        <!--proxool自动侦察各个连接状态的时间间隔(毫秒),侦察到空闲的连接就马上回收,超时的销毁 默认30秒-->  
        <house-keeping-sleep-time>90000</house-keeping-sleep-time>  
        <!--没有空闲连接可以分配而在队列中等候的最大请求数,超过这个请求数的用户连接就不会被接受-->  
        <maximum-new-connections>10</maximum-new-connections>    
        <!--最少保持的空闲连接数(默认2个)-->  
        <prototype-count>5</prototype-count>    
        <!--在使用之前测试-->  
        <test-before-use>true</test-before-use>  
        <!--用于保持连接的测试语句 -->  
        <house-keeping-test-sql>select name from test</house-keeping-test-sql>  
    </proxool>  
</something-else-entirely>

也可以采用 properties, 在程序 PropertyConfigurator.configure("proxool.properties"); 文件设置如下:
WEB-INF/proxool.properties
    jdbc-0.proxool.alias=property-test
    jdbc-0.proxool.driver-url=jdbc:mysql://localhost:3306/test
    jdbc-0.proxool.driver-class=org.gjt.mm.mysql.Driver
    jdbc-0.user=root
    jdbc-0.password=password
    jdbc-0.proxool.maximum-connection-count=10
    jdbc-0.proxool.house-keeping-test-sql=select CURRENT_DATE


可以直接在Java程式中使用java.util.Properties设定

Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
Properties info = new Properties();
info.setProperty("proxool.maximum-connection-count", "20");
info.setProperty("proxool.house-keeping-test-sql", "select CURRENT_DATE");
info.setProperty("user", "root");
info.setProperty("password", "1234");
Connection conn = DriverManager.getConnection("proxool.example:com.mysql.jdbc.Driver:jdbc:mysql://localhost:3306/test", info);

4、如果在 Web 使用, 在 web.xml 也可以设置 ServletConfigurator Servlet 来注册

web.xml文件配置如下：

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" 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>ServletConfigurator</servlet-name>
		<servlet-class>
			org.logicalcobwebs.proxool.configuration.ServletConfigurator
		</servlet-class>
		<init-param>
			<param-name>xmlFile</param-name>
			<param-value>WEB-INF/proxool.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>
	<!-- 
		配置连接池的监视路径
		检视 pooling 现在状态 
	-->
	<servlet>
		<servlet-name>Admin</servlet-name>
		<servlet-class>
			org.logicalcobwebs.proxool.admin.servlet.AdminServlet
		</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>Admin</servlet-name>
		<url-pattern>/admin</url-pattern>
	</servlet-mapping>
	<!-- 
	配置受保护域，只有Tomcat管理员才能察看连接池的信息 
	<security-constraint>
		<web-resource-collection>
			<web-resource-name>proxool</web-resource-name>
			<url-pattern>/admin</url-pattern>
		</web-resource-collection>
		<auth-constraint>
			<role-name>manager</role-name>
		</auth-constraint>
	</security-constraint>
	<login-config>
		<auth-method>BASIC</auth-method>
		<realm-name>proxool manager Application</realm-name>
	</login-config>
	<security-role>
		<description>
			The role that is required to log in to the Manager
			Application
		</description>
		<role-name>manager</role-name>
	</security-role>
	<error-page>
		<error-code>401</error-code>
		<location>/401.jsp</location>
	</error-page>
	-->
</web-app>

获取Connection的程序代码如下：

public Connection getConnect() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection("proxool.dbname");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

贴上自己的测试类：

package com.royzhou.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.configuration.JAXPConfigurator;

public class DBUtil {
	
	public Connection getConnectThrowProxoolDriver () {
		Connection connection =  null ; 
		try  { 
			Class.forName( "org.logicalcobwebs.proxool.ProxoolDriver" ); 
			connection = DriverManager.getConnection( 
			"proxool.test:org.gjt.mm.mysql.Driver:jdbc:mysql://localhost:3306/test" , 
			"root" , 
			"1234" 
			); 
		}  catch  (Exception e) { 
			System.out.println(e);
		} 
		return connection;
	}
	
	/**
	 * 通过XML配置文件读取配置
	 * @return
	 */
	public Connection getConnectThrowXMLConfig() {
		Connection connection =  null ; 
		try {
			Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
			/**
			 * 需要使用绝对路径来设置,
			 * 因为proxool源代码没有做判断,
			 * 如果使用相对路径结果会报FileN
			 * otFoundException
			 */
			JAXPConfigurator.configure("F:\\workspace\\proxool\\proxool\\WEB-INF\\proxool.xml", false); // false 表示不验证 
			connection = DriverManager.getConnection("proxool.test"); //proxool.test这里的test必须与配置文件配置的别名相同
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (ProxoolException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
	
	/**
	 * 通过properties配置文件读取配置
	 * @return
	 */
	public Connection getConnectThrowPropConfig() {
		Connection connection =  null ; 
		try {
			Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
			/**
			 * 需要使用绝对路径来设置,
			 * 因为proxool源代码没有做判断,
			 * 如果使用相对路径结果会报FileN
			 * otFoundException
			 */
			org.logicalcobwebs.proxool.configuration.PropertyConfigurator.configure("F:\\workspace\\proxool\\proxool\\WEB-INF\\proxool.properties");
			connection = DriverManager.getConnection("proxool.test"); //proxool.test这里的test必须与配置文件配置的别名相同
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (ProxoolException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
	
	/**
	 * 直接在代码中设置props
	 * @return
	 */
	public Connection getConnectThrowProp() {
		Connection conn = null;
		Properties info = new Properties();
		info.setProperty("proxool.alias", "test");
		info.setProperty("proxool.maximum-connection-count", "20");
		info.setProperty("proxool.house-keeping-test-sql", "select CURRENT_DATE");
		info.setProperty("user", "root");
		info.setProperty("password", "1234");
		try {
			Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
			conn = DriverManager.getConnection("proxool.test:com.mysql.jdbc.Driver:jdbc:mysql://localhost:3306/test", info);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * WEB应用
	 * Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
	 * JAXPConfigurator.configure("WEB-INF/proxool.xml",false);
	 * Connection conn = DriverManager.getConnection("proxool.dbname");
	 * 报FileNotFoundException,找不到原因
	 * 所以使用ServletConfigurator,查看proxool源代码发现
	 * ServletConfigurator加载配置文件的时候做了判断
	 * String appDir = servletConfig.getServletContext().getRealPath("/");
	 * File file = new File(value);
     * if (file.isAbsolute()) {
     * 	  JAXPConfigurator.configure(value, false);
     * } else {
     *    JAXPConfigurator.configure(appDir + File.separator + value, false);
     * }
	 * @return
	 */
	public Connection getConnectThrowWeb() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection("proxool.dbname");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void main(String[] args) {
		System.out.println(new DBUtil().getConnectThrowXMLConfig());
	}
}

以上代码仅供测试使用,实际开发中Connection使用完成之后都必须做相应的关闭操作。。