1) 使用数据库连接池的原因:
• 传统方式创建和销毁连接都需要消耗系统资源
• 传统方式创建和销毁连接都需要消耗时间
2) 使用数据库连接池的目的:
• 为了复用连接,代替传统的频繁占用系统资源和耗费时间的方式
• 便于管理连接,可以规定最大的连接数(控制应用服务器对数据库的并发访问)
我们可以通过链表,实现自己的连接池。
public class PersonalConnectionPool {
/**
* 用户名
*/
private static String user;
/**
* 密码
*/
private static String password;
/**
* 连接数据库的URL
*/
private static String url;
/**
* 连接池
* 规定最大连接数为3
*/
private static LinkedList<Connection> pool;
/**
* 从属性文件中加载数据库驱动,初始化连接池
*/
static{
try {
Properties properties = new Properties();
pool = new LinkedList<Connection>();
Class.forName("com.mysql.jdbc.Driver");
ClassLoader classLoader = PersonalConnectionPool.class.getClassLoader();
InputStream iStream = classLoader.getResourceAsStream("mysqlCongfig.properties");
properties.load(iStream);
user = properties.getProperty("user");
password = properties.getProperty("password");
url = properties.getProperty("url");
//创建三个连接对象(包装类对象)放到池子中
for (int i = 0; i < 3; i++) {
Connection connection = DriverManager.getConnection(url, user, password);
Connection connectionWrapper = new ConnectionWapper(connection,pool);
pool.add(connectionWrapper);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @throws SQLException
* @method 向外提供连接对象
*/
public Connection getConnection() throws SQLException {
Connection connection;
if(pool.size()>0)
{
connection = pool.removeFirst();
}
else
{
//等待超时,返回一个新创建的对象
connection = DriverManager.getConnection(url, user, password);
}
System.out.println("当前池子中有 "+pool.size()+" 个对象");
return connection;
}
/**
* 归还连接对象
* 直接简化在包装类的close方法中
*/
}
但是基于统一,JAVA为数据库连接池提供了公共接口,要求所有项目开发的连接池必须实现DataSource接口,可一统一用一套接口的方法使用不同开发商的数据库连接池。
3) 主流的连接池有:
(1)C3p0
(2)dbcp
(3)Tomcat jdbc pool
(4)BoneCP
(5)Druid
4) C3P0连接池示例代码:
(0)需要导入c3p0-0.9.1.2.jar
(1)C3P0连接池代码
package com.ambow.pool;
/*
* C3P0连接池
*/
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Util {
//1.创建连接池对象
static DataSource c3p0Pool;
static {
c3p0Pool = new ComboPooledDataSource();
}
//2.获取数据库连接(从连接池中获取)
public static Connection getConnection() {
Connection conn = null;
try {
conn = c3p0Pool.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//3.关闭连接,释放资源
public static void closeAll(Connection conn,Statement stmt,ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//4.获取连接池
public static DataSource getPool() {
return c3p0Pool;
}
}
(2)C3P0配置文件【方式一】c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/studb</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>
</default-config>
</c3p0-config>
(3)C3P0配置文件【方式二】c3p0.properties
c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/studb?useUnicode=true&characterEncoding=UTF-8
c3p0.user=root
c3p0.password=root
c3p0.maxPoolSize=20
c3p0.minPoolSize=2
c3p0.initialPoolSize=5
c3p0.maxStatements=30
c3p0.maxIdleTime=100
参考资料:
JDBC连接池的基本原理及实现方式:
https://blog.csdn.net/u010028461/article/details/78932109
C3P0连接池使用教程
https://www.cnblogs.com/ygj0930/p/6405861.html
主流Java数据库连接池比较与开发配置实战
https://blog.csdn.net/fysuccess/article/details/66972554
jar包下载地址:
https://sourceforge.net/projects/c3p0/
http://www.java2s.com/Code/Jar/c/Downloadc3p0jar.htm