Java配置数据库连接池

dbcp数据库连接池

DbcpUtils.java

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

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DbcpUtils {

	/*
	 * dbcp连接池需要创建dbcp.properties文件，并设置配置信息
	 * 需要导入的包commons-dbcp.jar & commons-logging.jar & commons-pool.jar
	 * 以及 mysql-connector-java.jar
	 */
	private static DataSource dataSource;
	
	static {
		try {
			InputStream is = DbcpUtils.class.getClassLoader().getResourceAsStream("dbcp.properties");
			
			Properties prop = new Properties();
			prop.load(is);
			dataSource = BasicDataSourceFactory.createDataSource(prop);
			
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public static DataSource getDataSource() {
		return dataSource;
	}
	
	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}
	
}

dbcp.properties
########DBCP配置文件##########
#驱动名
driverClassName=com.mysql.jdbc.Driver
#url
url=jdbc:mysql://127.0.0.1:3306/jdbc
#用户名
username=root
#密码
password=
#初试连接数
initialSize=30
#最大活跃数
maxTotal=30
#最大idle数
maxIdle=10
#最小idle数
minIdle=5
#最长等待时间(毫秒)
maxWaitMillis=1000
#程序中的连接不使用后是否被连接池回收(该版本要使用removeAbandonedOnMaintenance和removeAbandonedOnBorrow)
#removeAbandoned=true
removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true
#连接在所指定的秒数内未使用才会被删除(秒)(为配合测试程序才配置为1秒)
removeAbandonedTimeout=1

c3p0数据库连接池

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
   <!--默认配置-->
    <default-config>  
        <property name="initialPoolSize">10</property>  
        <property name="maxIdleTime">30</property>  
        <property name="maxPoolSize">100</property>  
        <property name="minPoolSize">10</property>  
        <property name="maxStatements">200</property>  
    </default-config>  
  
   <!--配置连接池mysql-->
    <named-config name="mysql">  
        <property name="driverClass">com.mysql.jdbc.Driver</property>  
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>  
        <property name="user">root</property>  
        <property name="password"></property>  
        <property name="initialPoolSize">10</property>  
        <property name="maxIdleTime">30</property>  
        <property name="maxPoolSize">100</property>  
        <property name="minPoolSize">10</property>  
        <property name="maxStatements">200</property>  
    </named-config>  

</c3p0-config>

Dbutils.java

package com.robin.dbutil;

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

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DbUtils {
/*
	 * c3p0连接池，必须创建c3p0-config.xml文件，配置相关数据库信息
	 * 需要导入的包c3p0-xxx.jar &  mysql-connector-java-xxx.jar.
	 */
	
private static ComboPooledDataSource dataSource = null;
	
	private static Connection conn = null;
	
	static {
		
		dataSource = new ComboPooledDataSource("mysql");
	}
	
	public static DataSource getDataSource() {
		return dataSource;
	}
	
	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}
	
	public static void free() {
		dataSource.close();
	}
}

DbutilsTest.java

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import com.robin.domain.Person;

public class DbUtilsTest {

	@Test
	public void findAll() throws SQLException {
		QueryRunner qr = new QueryRunner(DbUtils.getDataSource());
		String sql = "select id,name,age from person";
		List<Person> p  = (List)qr.query(sql, new BeanListHandler<Person>(Person.class));
		System.out.println(p);
	}
	
	@Test
	public void add() throws SQLException {
		QueryRunner qr = new QueryRunner(DbUtils.getDataSource());
		String sql = "insert into person(name,age)values(?,?)";
		Object[] param = {"robin2",25};
		int result = qr.update(sql, param);
		System.out.println("成功插入" + result + "条记录");
	}
	@Test
	public void update() throws SQLException {
		QueryRunner qr = new QueryRunner(DbUtils.getDataSource());
		String sql = "update person set age = 30 where id = ?";
		Object[] param = {4};
		int result = qr.update(sql, param);
		System.out.println("成功更新" + result + "条记录" );
	}
	@Test
	public void delete() throws SQLException {
		QueryRunner qr = new QueryRunner(DbUtils.getDataSource());
		String sql = "delete from person where id > ?";
		Object[] param = {4};
		int result = qr.update(sql, param);
		System.out.println("成功删除" + result + "条记录");
	}
	
}