开源数据库连接池的使用

上一篇文章简单介绍了数据库连接池的基本原理实现，链接：这里我们介绍两种常用数据库连接池的使用：

1.DBCP数据库连接池：

package com.itheima.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;

public class DBCPDemo {

	public static void main(String[] args) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			
			BasicDataSource source = new BasicDataSource();
			source.setDriverClassName("com.mysql.jdbc.Driver");
			source.setUrl("jdbc:mysql://localhost:3306/day11");
			source.setUsername("root");
			source.setPassword("root");
			conn = source.getConnection();
			ps = conn.prepareStatement("select * from account");
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString(2);
				String salary = rs.getString(3);
				System.out.println(name + " : " + salary);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					rs = null;
				}
			}
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					ps = null;
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					conn = null;
				}
			}
		}
	}
}

这里需要导入相应的dbcp jar包，这里我就不提供了。

以上代码是通过set方法设置类加载路径、url、数据库用户名和密码，此外我们还可以通过properties文件进行配置。

properties文件内的属性名称和setXXX方法对应，只是首字母小写。

dbcp-config.properties文件内容如下：

#类加载路径
driverClassName=com.mysql.jdbc.Driver
#url数据库访问路径
url=jdbc:mysql://localhost:3306/day11
#用户名
username=root
#密码
password=root

package com.itheima.jdbc;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DBCPDemo {
	private static Properties properties = new Properties();
	static {
		try {
			properties.load(new FileReader("dbcp-config.properties"));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	public static void main(String[] args) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			
			BasicDataSourceFactory factory = new BasicDataSourceFactory();
			DataSource source = factory.createDataSource(properties);
			conn = source.getConnection();
			
			ps = conn.prepareStatement("select * from account");
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString(2);
				String salary = rs.getString(3);
				System.out.println(name + " : " + salary);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					rs = null;
				}
			}
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					ps = null;
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					conn = null;
				}
			}
		}
	}
}

#数据库连接池初始化连接数
initialSize=10
#数据库连接池最大连接数
maxActive=50
#数据库连接池最小空闲连接数
minIdle=5
#数据库连接池最大空闲连接数
maxIdle=20
#等待超时，当一个链接空闲时间超过该时间，该链接定义为空闲连接
maxWait=60000

package com.itheima.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Demo {

	public static void main(String[] args) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			
			ComboPooledDataSource source = new ComboPooledDataSource();
			source.setDriverClass("com.mysql.jdbc.Driver");
			source.setJdbcUrl("jdbc:mysql://localhost:3306/day11");
			source.setUser("root");
			source.setPassword("root");
			conn = source.getConnection();
			
			ps = conn.prepareStatement("select * from account");
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString(2);
				String salary = rs.getString(3);
				System.out.println(name + " : " + salary);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					rs = null;
				}
			}
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					ps = null;
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					conn = null;
				}
			}
		}
	}
}

以下是官方文档的叙述：

By default, c3p0 will look for an XML configuration file in its classloader‘s resource path under the name "/c3p0-config.xml". That means the XML file should be placed in a directly or jar file directly named in your applications CLASSPATH, in WEB-INF/classes, or some similar location.

（也就是说：名字必须为c3p0-config.xml，文件位置：类加载路径下，即eclipse的src目录下）

package com.itheima.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Demo {

	public static void main(String[] args) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			//如果不通过setXXX方法设置属性，则会自动到类加载目录下查找c3p0-config.xml文件
			//查找到后默认使用<default-config>标签下的配置信息，也可以在构造source对象时传入
			//配置的名称，即ComboPooledDataSource source = new ComboPooledDataSource("myconfig");
			//当然c3p0-config.xml文件里配置了<default-config>和<named-config>,一般情况下使用其中一个就行了。
			ComboPooledDataSource source = new ComboPooledDataSource();
			conn = source.getConnection();
			
			ps = conn.prepareStatement("select * from account");
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString(2);
				String salary = rs.getString(3);
				System.out.println(name + " : " + salary);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			if(rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					rs = null;
				}
			}
			if(ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					ps = null;
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					conn = null;
				}
			}
		}
	}
}

<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/day11</property>
    <property name="user">root</property>
    <property name="password">root</property>
  </default-config>

  <named-config name="myconfig">
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/day11</property>
    <property name="user">root</property>
    <property name="password">root</property>
  </named-config>
</c3p0-config>

此外xml文件还可以配置其他参数，这里就不一一赘述了，官方文档写的很清楚。