一、数据库连接池概念
- 数据库连接是一种关键的、有限的、昂贵的资源，对数据库连接的管理能显著影响到程序的性能指标。数据库连接池正是针对这个问题提出来的。
- 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个，能明显提高对数据库操作的性能。
二、自定义数据库连接池
Java官方提供了数据库连接池接口：java.sql.DataSource接口，可以通过实现此接口自定义数据库连接池。
1.最简单的自定义连接池，不能归还连接
- 新建自定义连接池类，实现DataSource接口：
import org.example4.utils.JDBCUtils;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.logging.Logger;
public class MyDataSource implements DataSource {
//1.准备容器，保存多个连接对象
private static List<Connection> pool = Collections.synchronizedList(new ArrayList<Connection>());
//2.定义静态代码块 通过工具类获取10个连接对象
static {
for (int i = 1; i <= 10; i++) {
Connection connection = JDBCUtils.getConnection();
pool.add(connection);
}
}
//3.重写getSize方法，获取连接池容器大小
public int getSize(){
return pool.size();
}
//4.重写getConnection(),用于获取一个连接对象
@Override
public Connection getConnection() throws SQLException {
if(pool.size() > 0){
Connection connection = pool.remove(0);
return connection;
//MyConnection myConnection = new MyConnection(connection, pool);//装饰器自定义连接类
//return myConnection;
//MyConnection2 myConnection = new MyConnection2(connection, pool);//适配器自定义连接类
//return myConnection2;
}else{
throw new RuntimeException("连接数量用尽。");
}
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
}
- 测试类：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DataSourceTest {
public static void main(String[] args) throws SQLException {
//1.创建连接池对象
MyDataSource dataSource = new MyDataSource();
System.out.println("使用之前的数量：" + dataSource.getSize());
//2.通过连接池对象获取连接对象
Connection connection = dataSource.getConnection();
System.out.println(connection.getClass());
//3.查询学生表的信息
String sql = "SELECT * FROM student";
PreparedStatement pst = connection.prepareStatement(sql);
//4.执行sql语句 接收结果集
ResultSet resultSet = pst.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getInt("sid") + "\t" + resultSet.getString("name") + "\t" + resultSet.getString("age") );
}
//5.释放资源
resultSet.close();
pst.close();
connection.close();
System.out.println("使用之后的数量：" + dataSource.getSize());
}
}
2.装饰器模式归还连接
- 定义一个类，实现Connection接口
- 定义连接对象和连接池容器的成员变量
- 通过有参构造方法为成员变量赋值
- 重写close方法，完成归还连接
- 剩余方法，调用原有连接对象中的功能
装饰者模式实现Connection所有的方法，重写包括close方法的全部方法，只有close()方法需要自定义。
- 在自定义类中重写所有的方法：
// 1. 定义一个类，实现Connection接口
public class MyConnection implements Connection {
//2.定义连接对象和连接池容器的成员变量
private Connection con;
private List<Connection> pool;
//3.通过有参构造方法为成员变量赋值
public MyConnection(Connection con, List<Connection> pool) {
this.con = con;
this.pool = pool;
}
// 4. 重写close方法，完成归还连接
@Override
public void close() throws SQLException {
pool.add(con);
}
// 5. 剩余方法，调用原有连接对象中的功能
@Override
public Statement createStatement() throws SQLException {
return con.createStatement();
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return con.prepareStatement(sql);
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
return con.prepareCall(sql);
}
@Override
public String nativeSQL(String sql) throws SQLException {
return con.nativeSQL(sql);
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
con.setAutoCommit(autoCommit);
}
@Override
public boolean getAutoCommit() throws SQLException {
return con.getAutoCommit();
}
@Override
public void commit() throws SQLException {
con.commit();
}
@Override
public void rollback() throws SQLException {
con.rollback();
}
@Override
public boolean isClosed() throws SQLException {
return con.isClosed();
}
@Override
public DatabaseMetaData getMetaData() throws SQLException {
return con.getMetaData();
}
@Override
public void setReadOnly(boolean readOnly) throws SQLException {
con.setReadOnly(readOnly);
}
@Override
public boolean isReadOnly() throws SQLException {
return con.isReadOnly();
}
@Override
public void setCatalog(String catalog) throws SQLException {
con.setCatalog(catalog);
}
@Override
public String getCatalog() throws SQLException {
return con.getCatalog();
}
@Override
public void setTransactionIsolation(int level) throws SQLException {
con.setTransactionIsolation(level);
}
@Override
public int getTransactionIsolation() throws SQLException {
return con.getTransactionIsolation();
}
@Override
public SQLWarning getWarnings() throws SQLException {
return con.getWarnings();
}
@Override
public void clearWarnings() throws SQLException {
con.clearWarnings();
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
return con.createStatement(resultSetType,resultSetConcurrency);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
return con.prepareStatement(sql,resultSetType,resultSetConcurrency);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
return con.prepareCall(sql,resultSetType,resultSetConcurrency);
}
@Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
return con.getTypeMap();
}
@Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
con.setTypeMap(map);
}
@Override
public void setHoldability(int holdability) throws SQLException {
con.setHoldability(holdability);
}
@Override
public int getHoldability() throws SQLException {
return con.getHoldability();
}
@Override
public Savepoint setSavepoint() throws SQLException {
return con.setSavepoint();
}
@Override
public Savepoint setSavepoint(String name) throws SQLException {
return con.setSavepoint(name);
}
@Override
public void rollback(Savepoint savepoint) throws SQLException {
con.rollback(savepoint);
}
@Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
con.releaseSavepoint(savepoint);
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return con.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return con.prepareStatement(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return con.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
return con.prepareStatement(sql,autoGeneratedKeys);
}
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
return con.prepareStatement(sql,columnIndexes);
}
@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
return con.prepareStatement(sql,columnNames);
}
@Override
public Clob createClob() throws SQLException {
return con.createClob();
}
@Override
public Blob createBlob() throws SQLException {
return con.createBlob();
}
@Override
public NClob createNClob() throws SQLException {
return con.createNClob();
}
@Override
public SQLXML createSQLXML() throws SQLException {
return con.createSQLXML();
}
@Override
public boolean isValid(int timeout) throws SQLException {
return con.isValid(timeout);
}
@Override
public void setClientInfo(String name, String value) throws SQLClientInfoException {
con.setClientInfo(name,value);
}
@Override
public void setClientInfo(Properties properties) throws SQLClientInfoException {
con.setClientInfo(properties);
}
@Override
public String getClientInfo(String name) throws SQLException {
return con.getClientInfo(name);
}
@Override
public Properties getClientInfo() throws SQLException {
return con.getClientInfo();
}
@Override
public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
return con.createArrayOf(typeName,elements);
}
@Override
public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
return con.createStruct(typeName,attributes);
}
@Override
public void setSchema(String schema) throws SQLException {
con.setSchema(schema);
}
@Override
public String getSchema() throws SQLException {
return con.getSchema();
}
@Override
public void abort(Executor executor) throws SQLException {
con.abort(executor);
}
@Override
public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
con.setNetworkTimeout(executor,milliseconds);
}
@Override
public int getNetworkTimeout() throws SQLException {
return con.getNetworkTimeout();
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return con.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return con.isWrapperFor(iface);
}
}
- 在MyDataSource类中创建适配器模式的自定义类进行测试。见上面MyDataSource注释的部分。
3.适配器模式归还连接
适配器模式在装饰器模式的基础上，将继承Connection接口后需要重写的大量方法单独放到一个适配器类中，重写所有的方法（除close方法），然后自定义连接类继承此适配器类，并重写close()方法即可。
- 创建适配器类。使用抽象类，可以包含抽象方法、普通方法、构造方法。
import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
//1.定义一个适配器类，实现Connection接口
//2.定义连接对象的成员变量
//3.通过有参构造为变量赋值
//4.重写除close之外的所有的抽象方法
public abstract class MyAdapter implements Connection{
//2.定义连接对象的成员变量
private Connection con;
//3.通过有参构造为变量赋值
public MyAdapter(Connection con) {
this.con = con;
}
//4.重写除close之外的所有的抽象方法
@Override
public Statement createStatement() throws SQLException {
return con.createStatement();
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return con.prepareStatement(sql);
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
return con.prepareCall(sql);
}
@Override
public String nativeSQL(String sql) throws SQLException {
return con.nativeSQL(sql);
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
con.setAutoCommit(autoCommit);
}
@Override
public boolean getAutoCommit() throws SQLException {
return con.getAutoCommit();
}
@Override
public void commit() throws SQLException {
con.commit();
}
@Override
public void rollback() throws SQLException {
con.rollback();
}
@Override
public boolean isClosed() throws SQLException {
return con.isClosed();
}
@Override
public DatabaseMetaData getMetaData() throws SQLException {
return con.getMetaData();
}
@Override
public void setReadOnly(boolean readOnly) throws SQLException {
con.setReadOnly(readOnly);
}
@Override
public boolean isReadOnly() throws SQLException {
return con.isReadOnly();
}
@Override
public void setCatalog(String catalog) throws SQLException {
con.setCatalog(catalog);
}
@Override
public String getCatalog() throws SQLException {
return con.getCatalog();
}
@Override
public void setTransactionIsolation(int level) throws SQLException {
con.setTransactionIsolation(level);
}
@Override
public int getTransactionIsolation() throws SQLException {
return con.getTransactionIsolation();
}
@Override
public SQLWarning getWarnings() throws SQLException {
return con.getWarnings();
}
@Override
public void clearWarnings() throws SQLException {
con.clearWarnings();
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
return con.createStatement(resultSetType,resultSetConcurrency);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
return con.prepareStatement(sql,resultSetType,resultSetConcurrency);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
return con.prepareCall(sql,resultSetType,resultSetConcurrency);
}
@Override
public Map<String, Class<?>> getTypeMap() throws SQLException {
return con.getTypeMap();
}
@Override
public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
con.setTypeMap(map);
}
@Override
public void setHoldability(int holdability) throws SQLException {
con.setHoldability(holdability);
}
@Override
public int getHoldability() throws SQLException {
return con.getHoldability();
}
@Override
public Savepoint setSavepoint() throws SQLException {
return con.setSavepoint();
}
@Override
public Savepoint setSavepoint(String name) throws SQLException {
return con.setSavepoint(name);
}
@Override
public void rollback(Savepoint savepoint) throws SQLException {
con.rollback(savepoint);
}
@Override
public void releaseSavepoint(Savepoint savepoint) throws SQLException {
con.releaseSavepoint(savepoint);
}
@Override
public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return con.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return con.prepareStatement(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return con.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
}
@Override
public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
return con.prepareStatement(sql,autoGeneratedKeys);
}
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
return con.prepareStatement(sql,columnIndexes);
}
@Override
public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
return con.prepareStatement(sql,columnNames);
}
@Override
public Clob createClob() throws SQLException {
return con.createClob();
}
@Override
public Blob createBlob() throws SQLException {
return con.createBlob();
}
@Override
public NClob createNClob() throws SQLException {
return con.createNClob();
}
@Override
public SQLXML createSQLXML() throws SQLException {
return con.createSQLXML();
}
@Override
public boolean isValid(int timeout) throws SQLException {
return con.isValid(timeout);
}
@Override
public void setClientInfo(String name, String value) throws SQLClientInfoException {
con.setClientInfo(name,value);
}
@Override
public void setClientInfo(Properties properties) throws SQLClientInfoException {
con.setClientInfo(properties);
}
@Override
public String getClientInfo(String name) throws SQLException {
return con.getClientInfo(name);
}
@Override
public Properties getClientInfo() throws SQLException {
return con.getClientInfo();
}
@Override
public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
return con.createArrayOf(typeName,elements);
}
@Override
public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
return con.createStruct(typeName,attributes);
}
@Override
public void setSchema(String schema) throws SQLException {
con.setSchema(schema);
}
@Override
public String getSchema() throws SQLException {
return con.getSchema();
}
@Override
public void abort(Executor executor) throws SQLException {
con.abort(executor);
}
@Override
public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
con.setNetworkTimeout(executor,milliseconds);
}
@Override
public int getNetworkTimeout() throws SQLException {
return con.getNetworkTimeout();
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return con.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return con.isWrapperFor(iface);
}
}
- 创建自定义连接类，重写close()方法。
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
//适配器设计模式
//1.定义一个类，继承适配器类
//2.定义连接对象和连接池容器对象的成员变量
//3.通过有参构造为变量赋值
//4.重写close方法
public class MyConnection2 extends MyAdapter{
//2.定义连接对象和连接池容器对象的成员变量
private Connection con;
private List<Connection> pool;
public MyConnection2(Connection con, List<Connection> pool) {
super(con);//接收到con对象以后给父类传递
this.con = con;
this.pool = pool;
}
@Override
public void close() throws SQLException {
pool.add(con);
}
}
- 同样在MyDataSource类中创建适配器模式的自定义类进行测试。见上面MyDataSource注释的部分。
4.动态代理模式归还连接
使用动态代理模式归还连接，可以通过Proxy代理Connection的实现类，判断如果执行的是close()方法，则重写close()的功能，将连接归还连接池中，其他方法依旧调用原来的方法。
下面重写getConnection()方法：
//动态代理方式
@Override
public Connection getConnection() throws SQLException {
if(pool.size() > 0){
Connection connection = pool.remove(0);
Connection proxyConn = (Connection) Proxy.newProxyInstance(connection.getClass().getClassLoader(), new Class[]{Connection.class},
new InvocationHandler() {
//执行Connection实现类所有的方法都会经过invoke
//如果是close()，则重写；否则执行原对象功能
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
if (method.getName().equals("close")) {
pool.add(connection);
return null;
}else return method.invoke(connection, args);
}
});
return proxyConn;
}else{
throw new RuntimeException("连接数量用尽。");
}
}
177
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
