数据库连接池
所谓的数据库连接池技术,就是用来分配,管理,释放数据库连接的。我们以前用 JDBC 操作数据库的时候,每次操作完都会将连接关闭。数据库连接是极其宝贵的资源,频繁的创建和销毁会极大地降低服务器的性能。因此,我们可以利用池化技术,重复利用数据库资源,避免没必要的开销。
1. 手写数据库连接池
首先,我们来手写一个基本的数据库连接池,加深对数据库连接池原理的理解。
MyConnectionPool:
package com.cskaoyan.DataSource;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.LinkedList;
import java.util.Map;
import java.util.Properties;
import java.util.Queue;
import java.util.concurrent.Executor;
import java.util.concurrent.LinkedBlockingDeque;
/**
* @author shihao
* @create 2020-06-06 16:17
*/
public class MyConnectionPool {
//属性
//MAX_SIZE、INIT_SIZE这两个属性通过pool.properties配置文件传入
//最大连接数目
private static int MAX_SIZE;
//初始连接数目
private static int INIT_SIZE;
//配置文件
private static Properties info = new Properties();
//实际连接的数目
private int size;
//用队列存放空闲连接,使用时来这里拿,用完了放回来
private Queue<Connection> queue = new LinkedList<>();
static {
try (Reader reader = new FileReader("pool.properties")) {
info.load(reader);
MAX_SIZE = Integer.parseInt(info.getProperty("MAX_SIZE"));
INIT_SIZE = Integer.parseInt(info.getProperty("INIT_SIZE"));
} catch (IOException e) {
e.printStackTrace();
}
}
//构造方法
//我们希望在创建对象之前就对MAX_SIZE和INIT_SIZE赋值,所以应在静态代码块中对这两个参数初始化
public MyConnectionPool() {
//创建INIT_SIZE个空闲连接,并放在队列中
for (int i = 0; i < INIT_SIZE; i++) {
Connection connec = createConnection();
queue.offer(connec);
}
}
//方法
//获取连接
public Connection getConnection(){
//如果队列非空,就从队列中取出一个连接
if (!queue.isEmpty()){
return queue.poll();
}
//走到这就说明没有空闲的连接了
//如果没有达到最大值,就创建一个连接给外界使用
if (size<MAX_SIZE){
return createConnection();
}
//走到这说明达到最大值了,就抛出异常
throw new RuntimeException("连接数目已经达到最大值!");
}
//我们希望这些连接在没有用的时候把它放回到池子中,调用close方法后,让连接回到连接池中,而不是把它销毁
private Connection createConnection() {
String url = info.getProperty("url");
String user = info.getProperty("user");
String password = info.getProperty("password");
Connection conn = null;
try {
conn = new WorkConnection(DriverManager.getConnection(url, user, password));
size++;
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//我们希望WorkConnection可以访问私有成员queue等,就需要写一个内部类
//其实我们只需要重写其close方法
private class WorkConnection extends ConnectionWrapper {
WorkConnection(Connection connec) {
super(connec);
}
@Override
public void close() throws SQLException {
//用完这条连接后就放到队列中去,而不是销毁
queue.offer(this);
}
}
}
装饰者模式在流里面也经常用,比如说,对一个流进行包装就会得到另一个流。
package com.cskaoyan.DataSource;
import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
/**
* @author shihao
* @create 2020-06-06 17:08
*/
//装饰者模式
public abstract class ConnectionWrapper implements Connection {
//这些方法我们都不会重写,我们让connec来帮我们做事情
//组合
//组合定义
//在新类里面创建原有类的对象,重复利用已有类的功能。(has-a关系)
protected Connection connec;
ConnectionWrapper(Connection connec) {
this.connec = connec;
}
@Override
public Statement createStatement() throws SQLException {
return connec.createStatement();
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return connec.prepareStatement(sql);
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
return connec.prepareCall(sql);
}
@Override
public String nativeSQL(String sql) throws SQLException {
return connec.nativeSQL(sql);
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
connec.setAutoCommit(autoCommit);
}
@Override
public boolean getAutoCommit() throws SQLException {
return connec.getAutoCommit();
}
@Override
public void commit() throws SQLException {
connec.commit();
}
@Override
public void rollback() throws SQLException {
connec.rollback();
}
//这个方法让继承者们实现,因为要重写的只有这有方法
@Override
public abstract void close() throws SQLException;
@Override
public boolean isClosed() throws SQLException {
return connec.isClosed();
}
@Override
public DatabaseMetaData getMetaData() throws SQLException {
return connec.getMetaData();
}
@Override
public void setReadOnly(boolean readOnly) throws SQLException {
connec.setReadOnly(readOnly);
}
@Override
public boolean isReadOnly() throws SQLException {
return connec.isReadOnly();
}
@Override
public void setCatalog(String catalog) throws SQLException {
connec.setCatalog(catalog);
}
@Override
public String getCatalog() throws SQLException {
return connec.getCatalog();
}
@Override
public void setTransactionIsolation(int level) throws SQLException {
connec.setTransactionIsolation(level);
}
@Override
public int getTransactionIsolation() throws SQLException {
return connec.getTransactionIsolation();
}
@Override
public SQLWarning getWarnings() throws SQLException {
return connec.getWarnings();
}
@Override
public void clearWarnings() throws SQLException {
connec.clearWarnings();
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
return connec.createStatement(resultSetType, resultSetConcurrency);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
return connec.prepareCall(sql, resultSetType, resultSetConcurrency);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
return connec.prepareCall(sql, resultSetType, resultSetConcurrency);
}
@Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
return connec.getTypeMap();
}
@Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
connec.setTypeMap(map);
}
@Override
public void setHoldability(int holdability) throws SQLException {
connec.setHoldability(holdability);
}
@Override
public int getHoldability() throws SQLException {
return connec.getHoldability();
}
@Override
public Savepoint setSavepoint() throws SQLException {
return connec.setSavepoint();
}
@Override
public Savepoint setSavepoint(String name) throws SQLException {
return connec.setSavepoint(name);
}
@Override
public void rollback(Savepoint savepoint) throws SQLException {
connec.rollback(savepoint);
}
@Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
connec.releaseSavepoint(savepoint);
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return connec.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return connec.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return connec.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
return connec.prepareStatement(sql, autoGeneratedKeys);
}
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
return connec.prepareStatement(sql,columnIndexes);
}
@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
return connec.prepareStatement(sql,columnNames);
}
@Override
public Clob createClob() throws SQLException {
return connec.createClob();
}
@Override
public Blob createBlob() throws SQLException {
return connec.createBlob();
}
@Override
public NClob createNClob() throws SQLException {
return connec.createNClob();
}
@Override
public SQLXML createSQLXML() throws SQLException {
return connec.createSQLXML();
}
@Override
public boolean isValid(int timeout) throws SQLException {
return connec.isValid(timeout);
}
@Override
public void setClientInfo(String name, String value) throws SQLClientInfoException {
connec.setClientInfo(name,value);
}
@Override
public void setClientInfo(Properties properties) throws SQLClientInfoException {
connec.setClientInfo(properties);
}
@Override
public String getClientInfo(String name) throws SQLException {
return connec.getClientInfo(name);
}
@Override
public Properties getClientInfo() throws SQLException {
return connec.getClientInfo();
}
@Override
public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
return connec.createArrayOf(typeName,elements);
}
@Override
public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
return connec.createStruct(typeName,attributes);
}
@Override
public void setSchema(String schema) throws SQLException {
connec.setSchema(schema);
}
@Override
public String getSchema() throws SQLException {
return connec.getSchema();
}
@Override
public void abort(Executor executor) throws SQLException {
connec.abort(executor);
}
@Override
public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
connec.setNetworkTimeout(executor, milliseconds);
}
@Override
public int getNetworkTimeout() throws SQLException {
return connec.getNetworkTimeout();
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return connec.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return connec.isWrapperFor(iface);
}
}
···················································································································································································································
2. 优秀的数据库连接池
Sun 公司规定:Java 中所有的数据库连接池都需要实现 DataSource 接口。该接口定义如下:
其中,定义了两个获取连接的方法。
2.1 DBCP
DBCP 是由 Apache 开发的一个 Java 数据库连接池项目。Tomcat 内置的连接池组件就是 DBCP。单独使用 DBCP需要3个包:common-dbcp.jar,common-pool.jar,common-logging.jar。它预先将数据库连接放在内存中,应用程序需要建立数据库连接时直接到连接池中申请一个就行,用完再放回。单线程,并发量低,性能不好,适用于小型系统。
使用的方式主要有两种,一种是硬编码方式,就是在代码中设置各种参数 (不推荐) 。
第二种方式就是配置相应的配置文件 (推荐)。
package com.cskaoyan.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.junit.Assert;
import org.junit.Test;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author shihao
* @create 2020-06-06 22:05
*/
public class DBCPDemo {
//硬编码方式
@Test
public void testGetConnection() throws SQLException {
BasicDataSource ds = new BasicDataSource();
ds.setInitialSize(3);
ds.setMaxTotal(10);
ds.setMaxIdle(1800);
ds.setUrl("jdbc:mysql://localhost:3306/jdbc_db");
ds.setUsername("root");
ds.setPassword("069583");
Connection conn = ds.getConnection();
Assert.assertNotNull(conn);
}
//配置文件方式
@Test
public void testGetConnection2() {
Properties info = new Properties();
Connection conn = null;
try (Reader reader = new FileReader("dbcp.properties")) {
info.load(reader);
BasicDataSource ds = BasicDataSourceFactory.createDataSource(info);
conn = ds.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
Assert.assertNotNull(conn);
}
}
···················································································································································································································
2.2 C3P0
开源的 JDBC 连接池,支持 JDBC3 规范和 JDBC2 的标准扩展。目前使用它的开源项目有Hibernate、Spring等。单线程,性能较差,适用于小型系统。使用 C3P0 需要 c3p0-0.9.5.2.jar 和 mchangecommons-java-0.2.11.jar。
c3p0 会默认从 src 根目录下读取文件名为 c3p0-config.xml 的文件。在该文件中,我们可以配置我们自己需要的参数,如下所示,更多的参数可以自己查阅相关参数。
package com.cskaoyan.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Assert;
import org.junit.Test;
import java.sql.Connection;
import java.sql.SQLException;
/**
* @author shihao
* @create 2020-06-06 22:22
*/
public class C3P0Demo {
@Test
public void testGetConnection() {
ComboPooledDataSource ds = new ComboPooledDataSource();
Connection conn = null;
try {
conn = ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
Assert.assertNotNull(conn);
}
}
···················································································································································································································
2.3 Druid
Druid 是 Java 语言中最好的数据库连接池,Druid 能够提供强大的监控和扩展功能,是一个可用于大数据实时查询和分析的高容错、高性能的开源分布式系统,尤其是当发生代码部署、机器故障以及其他产品系统遇到宕机等情况时,Druid 仍能够保持100% 正常运行。
主要特色: 为分析监控设计;快速的交互式查询;高可用;可扩展;
package com.cskaoyan.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Assert;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.util.Properties;
/**
* @author shihao
* @create 2020-06-06 22:22
*/
public class DruidDemo {
@Test
public void testGetConnection() {
Properties info = new Properties();
Connection conn = null;
try (Reader reader = new FileReader("druid.properties")) {
info.load(reader);
DataSource ds = DruidDataSourceFactory.createDataSource(info);
conn = ds.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
Assert.assertNotNull(conn);
}
}
···················································································································································································································
数据库连接池的优点:
- 数据库连接得以重用,避免了频繁创建和销毁引起的大量性能开销,同时也增加了系统的稳定性。
- 数据库连接池在初始化过程中,往往就已经创建了若干个连接。因此可以提高系统的反应速度。
- 如果有多个应用共用一个 DBMS,由于 DBMS 的连接是有限的,因此我们可以通过数据库连接池
限制一个应用最大的连接数目,避免某一应用独占所有的连接。 - 在较为完善的数据库连接池中,可以设置占用超时,强制回收被占用的连接,从而避免了常规数据
库连接操作中可能出现的资源泄露问题。
···················································································································································································································
作业:
. 用Druid数据库连接池创建连接。在test数据库中,创建user表(id, username, password, balance).
批量插入10000条记录, 然后查询 balance>10000 的用户有哪些?将username和balance输出到test.txt文件中。
package com.cskaoyan.Homework;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Assert;
import javax.sql.DataSource;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.PrintWriter;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Properties;
import java.util.Random;
/**
* @author shihao
* @create 2020-06-06 22:41
* <p>
* 2. 用Druid数据库连接池创建连接。
* 在test数据库中,创建user表(id, username, password, balance).
* 批量插入10000条记录, 然后查询 balance>10000 的用户有哪些?
* 将username和balance输出到test.txt文件中。
*/
public class Homework {
public static void main(String[] args) throws SQLException, FileNotFoundException {
// 获取连接
Connection conn = GetConnection();
//获取执行平台
Statement stmt = conn.createStatement();
//创建user表
String sql = "create table user(id int primary key auto_increment, " +
"username varchar(255) not null, password varchar(255) not null, " +
"balance decimal(18,2) default 0)";
stmt.execute(sql);
//插入1000条数据
sql = "insert into user(username,password,balance) values (?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
Random r = new Random();
for (int i = 0; i < 100; i++) {
pstmt.setString(1, "user" + i);
pstmt.setString(2, "pwd" + i);
pstmt.setBigDecimal(3, new BigDecimal(r.nextInt(15000)));
pstmt.addBatch();
}
pstmt.executeBatch();
// 查询balance>10000的记录,并写入到test.txt文件中
sql = "select * from user where balance > 10000";
ResultSet rs = pstmt.executeQuery(sql);
PrintWriter pw = new PrintWriter("homework.txt");
pw.println("id" + "\t" + "username" + "\t" + "balance");
while (rs.next()) {
int id = rs.getInt("id");
String username = rs.getString("username");
BigDecimal balance = rs.getBigDecimal("balance");
pw.println(id + "\t" + username + "\t" + balance);
}
pw.close();
conn.close();
}
//连接数据库
public static Connection GetConnection() {
Properties info = new Properties();
Connection conn = null;
try (Reader reader = new FileReader("druid.properties")) {
info.load(reader);
DataSource ds = DruidDataSourceFactory.createDataSource(info);
conn = ds.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}