java 数据库连接池

最近要开始的项目可能要使用数据库连接池，所以我测试了使用比较多的数据库连接池，主要是C3P0，DBCP，TomcatPool

先说一下C3P0 ,C3P0相对来说比较简单，只要几个jar包就可以正常使用

jar 包：

c3p0-0.9.5-pre9.jar

c3p0-oracle-thin-extras-0.9.5-pre9.jar

mchange-commons-java-0.2.8.jar

可能还会需要一些log包，主要是为了能输出日志的。

具体代码：

package com.junl.scott.dbcp;

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

import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * @author scott
 * @E-mail: lhdscott@163.com
 * @version 1.0
 * @Date and Time：2014-11-3 上午10:35:03
 * @class explain
 */
public class C3p0Utils
{
	private static DataSource ds = null;
	private static C3p0Utils pool = null;

	C3p0Utils()
	{

	}

	static
	{
		ds = new ComboPooledDataSource();
		if (pool == null)
		{
			try
			{
				pool = new C3p0Utils();
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}
		}
	}

	public static Connection getConnection()
	{
		try
		{
			return ds.getConnection();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}

	final static void closeConnection(Connection conn) throws Exception
	{
		try
		{
			if (conn != null && !conn.isClosed())
			{
				// conn.setAutoCommit(true);
				conn.close();
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

}

配置文件：

<pre class="html" name="code"><c3p0-config>
	<!-- 默认配置，如果没有指定则使用这个配置 -->
	<default-config>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/hibernate?useUnicode=true&characterset=utf-8
		</property>
		<property name="user">root</property>
		<property name="password">123456</property>
		<property name="acquireIncrement">5</property>
		<property name="initialPoolSize">20</property>
		<property name="minPoolSize">2</property>
		<property name="maxPoolSize">500</property>
		<property name="maxStatements">0</property>
		<property name="maxStatementsPerConnection">5</property>
	</default-config>
</c3p0-config>

<span style="font-size:18px;">DBCP池:</span>

<span style="font-size:14px;">所需jar包</span>

<span style="font-size:14px;"> commons-dbcp2-2.0.1.jar </span>

<span style="font-size:14px;">commons-pool2-2.2.jar</span>

<span style="color:#ff0000;">DBCP连接池有的时候连接可能会报异常，可能是因为mysql的驱动包不兼容，可以换一个试一下。</span>

package com.junl.scott.dbcp;

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

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @author scott
 * @E-mail: lhdscott@163.com
 * @version 1.0
 * @Date and Time：2014-11-3 上午11:43:03
 * @class explain
 */
public class DBCPUtils
{
	private static DataSource ds = null;
	private static final Log log = LogFactory.getLog(DBCPUtils.class);

	static
	{
		Properties dbProperties = new Properties();
		try
		{
			dbProperties.load(DBCPUtils.class.getClassLoader().getResourceAsStream("DBCP.properties"));
			ds = BasicDataSourceFactory.createDataSource(dbProperties);
		}
		catch (Exception e)
		{
			log.error("初始化连接池失败：" + e);
		}
	}

	public static final Connection getConnection()
	{
		Connection con = null;
		try
		{
			con = ds.getConnection();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return con;
	}

	public static final void closeConn(Connection conn)
	{
		try
		{
			if (conn != null && !conn.isClosed())
			{
//				conn.setAutoCommit(true);
				conn.close();
			}
		}
		catch (SQLException e)
		{
			log.error("关闭数据库连接失败：" + e);
		}
	}


}

配置文件<pre class="plain" name="code">driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/hibernate
username=root
password=123456

maxActive=30
maxIdle=10
maxWait=1000
removeAbandoned=true
removeAbandonedTimeout=180

 

TomcatPool

所需jar包：

tomcat-jdbc.jar

tomcat-juli.jar

具体代码：

package com.junl.scott.dbcp;

import java.io.IOException;
import java.sql.Connection;
import java.util.Properties;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

/**
 * @author scott
 * @E-mail: lhdscott@163.com
 * @version 1.0
 * @Date and Time：2014-11-4 上午10:26:23
 * @class explain
 */
public class TomcatPoolUtils
{
	/*
	 * 初始化连接设置
	 */

	private static DataSource ds = new DataSource();

	static
	{
		PoolProperties poolProperties = new PoolProperties();
		Properties dbProperties = new Properties();
		try
		{
			dbProperties.load(TomcatPoolUtils.class.getClassLoader().getResourceAsStream("tomcatpool.properties"));

			poolProperties.setUrl(dbProperties.getProperty("url"));
			// 设置驱动名
			poolProperties.setDriverClassName(dbProperties.getProperty("driverClassName"));
			// 设置数据库用户名
			poolProperties.setUsername(dbProperties.getProperty("username"));
			// 设置数据库密码
			poolProperties.setPassword(dbProperties.getProperty("password"));

			/*
			 * 下面设置一些乱七八糟的属性
			 */
			poolProperties.setJmxEnabled(dbProperties.getProperty("jmxEnabled").equals("true"));
			poolProperties.setTestWhileIdle(dbProperties.getProperty("testWhileIdle").equals("true"));
			poolProperties.setTestOnBorrow(dbProperties.getProperty("testOnBorrow").equals("true"));
			poolProperties.setTestOnReturn(dbProperties.getProperty("testOnReturn").equals("true"));
			poolProperties.setValidationInterval(Integer.parseInt(dbProperties.getProperty("validationInterval")));
			poolProperties.setTimeBetweenEvictionRunsMillis(Integer.parseInt(dbProperties.getProperty("timeBetweenEvictionRunsMillis")));
			poolProperties.setMaxActive(Integer.parseInt(dbProperties.getProperty("maxActive")));
			// 设置初始化连接数
			poolProperties.setInitialSize(Integer.parseInt(dbProperties.getProperty("initialSize")));
			// 设置最大等待时间
			poolProperties.setMaxWait(Integer.parseInt(dbProperties.getProperty("maxWait")));
			poolProperties.setRemoveAbandonedTimeout(Integer.parseInt(dbProperties.getProperty("removeAbandonedTimeout")));
			poolProperties.setMinEvictableIdleTimeMillis(Integer.parseInt(dbProperties.getProperty("minEvictableIdleTimeMillis")));
			poolProperties.setMinIdle(Integer.parseInt(dbProperties.getProperty("minIdle")));
			poolProperties.setLogAbandoned(dbProperties.getProperty("logAbandoned").equals("true"));
			poolProperties.setRemoveAbandoned(dbProperties.getProperty("removeAbandoned").equals("true"));
			poolProperties.setJdbcInterceptors(dbProperties.getProperty("jdbcInterceptors"));
			ds.setPoolProperties(poolProperties);
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
	}
	static
	{

	}

	/*
	 * 获取连接
	 */
	static final Connection getConnection() throws Exception
	{
		Connection conn = null;
		conn = ds.getConnection();
		return conn;
	}

	/*
	 * 关闭连接
	 */
	static final void closeConnection(Connection conn) throws Exception
	{
		if (conn != null && !conn.isClosed())
		{
			conn.close();
		}

	}
}

配置文件：

 <pre class="plain" name="code">url=jdbc:mysql://localhost:3306/mysql
driverClassName=com.mysql.jdbc.Driver
username=root
password=123456
jmxEnabled=true
testWhileIdle=true
testOnBorrow=true
testOnReturn=false
validationInterval=30000
timeBetweenEvictionRunsMillis=30000
maxActive=100
minIdle=10
maxWait=10000
initialSize=10
removeAbandonedTimeout=60
removeAbandoned=true    
logAbandoned=true   
minEvictableIdleTimeMillis=30000
jdbcInterceptors=org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer

<span style="font-size:18px;"></span> 

<span style="font-size:18px;">以上都是具体的实现，下面给出测试的代码，是使用Junit测试的</span>

<pre class="java" name="code">package com.junl.scott.dbcp;

import java.sql.Connection;

import org.junit.Test;

/**
 * @author scott
 * @E-mail: lhdscott@163.com
 * @version 1.0
 * @Date and Time：2014-11-3 上午11:14:52
 * @class explain
 */

public class PoolTest
{
	@Test
	public void C3P0Test() throws Exception
	{
		System.out.println("C3P0数据连接池");
		long begin = System.currentTimeMillis();
		for (int i = 0; i < 10; i++)
		{
			Connection conn = C3p0Utils.getConnection();
			System.out.println(i + "   ");
			C3p0Utils.closeConnection(conn);
		}
		long end = System.currentTimeMillis();
		System.out.println("用时：" + (end - begin));
	}

	@Test
	public void DBCPTest() throws Exception
	{
		System.out.println("DBCP数据连接池");
		long begin = System.currentTimeMillis();
		for (int i = 0; i < 10; i++)
		{
			Connection conn = DBCPUtils.getConnection();
			System.out.println(i + "   ");
			DBCPUtils.closeConn(conn);
		}
		long end = System.currentTimeMillis();
		System.out.println("用时：" + (end - begin));
	}

	@Test
	public void TomcatTest() throws Exception
	{
		System.out.println("TomcatPool数据连接池");
		long begin = System.currentTimeMillis();
		for (int i = 0; i < 10; i++)
		{
			Connection conn = TomcatPoolUtils.getConnection();
			System.out.println(i + "   ");
			TomcatPoolUtils.closeConnection(conn);
		}
		long end = System.currentTimeMillis();
		System.out.println("用时：" + (end - begin));
	}

}

具体的问题要具体对待，有可能这段代码到别的机器上可能就不能正常运行了，在调试的过程中可能需要一些jar包，自己到晚上下载就可以了，不难找。