- 1.数据库连接池的概念
- 2.自定义数据库连接池
- 3.开源数据库连接池
- 4.自定义JDBC框架
数据库连接池的概念
数据库连接池
- 数据库连接池负责分配,管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是再建立一个,这项技术能明显提高对数据库操作的性能
自定义数据库连接池(了解 DateSource)
- DateSource 接口概述
1.Javax.sql.DateSource 接口: 数据源(数据库连接池). java官方提供的数据库连接池规范(接口)
2.如果想完成书库连接池技术,就必须实现DateSource
3.核心功能: 获取数据库连接对象: Connection getConnection();- 自定义数据库连接池
1.定义一个类,实现DateSource
2.定义一个容器,用于保存多个connection 连接对象
3.定义静态代码块,通过JDBC工具类获取10个连接保存到容器中
4.重写getConnection方法,从容器中获取一个连接并返回
5.定义getSize方法.用于获取容器的大小并返回
package com.itheima01;
import com.itheima.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<>());
//2.定义静态代码块,通过工具类获取10个连接对象
static {
for (int i = 1; i <= 10; i++) {
Connection con = JDBCUtils.getConnection();
pool.add(con);
}
}
//3.重写getConnection(),用于获取一个连接对象
@Override
public Connection getConnection() throws SQLException {
if (pool.size() > 0) {
Connection con = pool.remove(0);
return con;
} else {
throw new RuntimeException("连接数量用尽!");
}
}
//4.通过getSize方法,获取连接池容器的大小
public int getSize() {
return pool.size();
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@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;
}
}
- 自定义数据路库连接池的测试
package com.itheima01;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//自定义数据路库连接池的测试
public class MyDataSourceTest {
public static void main(String[] args) throws SQLException {
//1.创建连接池对象
MyDataSource dataSource = new MyDataSource();
System.out.println("使用之前连接池大小:"+dataSource.getSize());
//2.通过连接池对象的获取连接对象
Connection con = dataSource.getConnection();
//3.查询学生表的全部信息
String sql = "SELECT * FROM student";
PreparedStatement pst = con.prepareStatement(sql);
//4.执行SQL语句,接收结果集
ResultSet rs = pst.executeQuery();
//5.处理结果集
while (rs.next()){
System.out.println(rs.getInt("id")+"\t"+rs.getString("name")+"\t"+rs.getInt("age")+"\t"+rs.getDate("brithday"));
}
//6.释放资源
rs.close();
pst.close();
con.close(); //用完之后,关闭连接
/*
这里有个问题: 连接在关闭后,无法归还给连接池
*/
System.out.println("使用之前连接池大小:"+dataSource.getSize());
}
}
打印结果
归还连接
- 归还数据库连接的方式
1.继承方式
2.装饰设计模式
3.适配器设计模式
4.动态代理方式- 继承方式归还数据库连接的思想
1.通过打印连接对象,发现DriverManager获取的连接实现类时JDBC4Connection
2.我们可以自定义一个类,继承JDBCConnection这个类,重写close方法,完成连接对象的归还- 继承方式归还数据库连接的实现步骤
1.定义一个类,继承JDBCConnection
2.定义Connection连接对象和连接池容器对象的成员变量
3.通过有参构造完成对成员变量的赋值
4.重写close方法,将连接对象添加到池中- 继承方式归还数据库存在的问题
1.通过查看JDBC工具类获取连接的方法发现: 我们虽然定义了一个子类,完成了归还连接的操作,但是DriverManager获取连接的还是JDBC4Connection这个对象,并不是我们创建的子类对象,而我们又不能整体去修改驱动包中类的功能,所以继承方式行不通
package com.itheima02;
import com.mysql.jdbc.JDBC4Connection;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
/*自定义的连接对象*/
//1.定义一个类,继承JDBCConnection
public class MyConntection1 extends JDBC4Connection {
//2.定义Connection连接对象和连接池容器对象的成员变量
private Connection con;
private List<Connection> pool;
//3.通过有参构造完成对成员变量的赋值
public MyConntection1(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url,Connection con, List<Connection> pool) throws SQLException {
super(hostToConnectTo, portToConnectTo, info, databaseToConnectTo, url);
this.con=con;
this.pool=pool;
}
//4.重写close方法,完成归还连接
@Override
public void close() throws SQLException {
pool.add(con);
}
}
- 装饰设计模式归还数据库连接的思想
1.自定义一个类,实现Connection接口,这样就具备了和JDBC4Connection相同的行为了
2.重写close()方法,完成连接的归还,其余的功能还调用mysql驱动包实现类原有的方法即可- 装饰设计模式关于换数据连接的实现步骤
1.定义一个类,实现Connecction接口
2.定义Connection 连接对象和连接池容器对象的成员变量
3.通过有参构造方法完成对成员变量的赋值
4.重写close()方法,将连接对象添加到池中
5.剩余方法,只需要调用mysql驱动包的连接对象完成即可
6.在自定义连接池中, 将获取连接对象通过自定义连接对象进行包装
package com.itheima02;
import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
/*
> - 装饰设计模式关于换数据连接的实现步骤
1.定义一个类,实现Connection接口
2.定义Connection 连接对象和连接池容器对象的成员变量
3.通过有参构造方法完成对成员变量的赋值
4.重写close()方法,将连接对象添加到池中
5.剩余方法,只需要调用mysql驱动包的连接对象完成即可
6.在自定义连接池中, 将获取连接对象通过自定义连接对象进行包装
*/
//1.定义一个类,实现Connection接口
public class MyConnection2 implements Connection {
//2.定义Connection 连接对象和连接池容器对象的成员变量
private Connection con;
private List<Connection> pool;
//3.通过有参构造方法完成对成员变量的赋值
public MyConnection2(Connection con,List<Connection> pool){
this.con=con;
this.pool=pool;
}
//4.重写close()方法,将连接对象添加到池中
@Override
public void close() throws SQLException {
pool.add(con);
}
//5.剩余方法,只需要调用mysql驱动包的连接对象完成即可
@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);
}
}
自定义连接池修改的代码
package com.itheima01;
import com.itheima.utils.JDBCUtils;
import com.itheima02.MyConnection2;
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<>());
//2.定义静态代码块,通过工具类获取10个连接对象
static {
for (int i = 1; i <= 10; i++) {
Connection con = JDBCUtils.getConnection();
pool.add(con);
}
}
//3.重写getConnection(),用于获取一个连接对象
@Override
public Connection getConnection() throws SQLException {
if (pool.size() > 0) {
Connection con = pool.remove(0);
//通过自定义的连接对象,对原有的连接对象进行包装
MyConnection2 mcon = new MyConnection2(con, pool);
return mcon;
} else {
throw new RuntimeException("连接数量用尽!");
}
}
//4.通过getSize方法,获取连接池容器的大小
public int getSize() {
return pool.size();
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@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;
}
}
测试结果
- 装饰设计模式归还数据库连接存在的问题
实现Connection接口后,有大量的方法需要在自定义类中进行重写- 适配器设计模式归还数据库连接的思想
1.可以提供一个适配器,显示Connection接口,将所有方法进行实现(除了close方法)
2.自定义连接类只需要继承这个适配器,重写需要改进的close()方法进行即可- 适配器设计模式归还数据库连接的实现步骤
1.定义一个适配器类,实现Connectionm接口
2.定义Connection连接对象的成员变量
3.通过有参构造方法完成对成员变量的赋值
4.重写所有方法(除了close方法),调用mysql驱动包的连接对象完成即可
5.定义一个连接类,继承适配器类
6.定义Connection 连接对象和连接池容器对象的成员变量,并通过有参构造方法进行改造赋值
7.重写close()方法,完成归还连接
8.在自定义连接池中,将获取的连接对象通过自定义连接对象进行包装定义适配器类
package com.itheima02;
import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
/*
1.定义一个适配器类,实现Connection接口
2.定义Connection连接对象的成员变量
3.通过有参构造方法完成对成员变量的赋值
4.重写所有方法(除了close方法),调用mysql驱动包的连接对象完成即可
*/
//1.定义一个适配器类,实现Connection接口
public abstract class MyAdapter implements Connection {
//2.定义Connection 连接对象和连接池容器对象的成员变量
private Connection con;
//3.通过有参构造方法完成对成员变量的赋值
public MyAdapter(Connection con){
this.con=con;
}
//4.重写所有方法(除了close方法),调用mysql驱动包的连接对象完成即可
@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);
}
}
定义连接类
package com.itheima02;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/*
5.定义一个连接类,继承适配器类
6.定义Connection 连接对象和连接池容器对象的成员变量,并通过有参构造方法进行改造赋值
7.重写close()方法,完成归还连接
8.在自定义连接池中,将获取的连接对象通过自定义连接对象进行包装
* */
//5.定义一个连接类,继承适配器类
public class MyConnection3 extends MyAdapter {
//6.定义Connection 连接对象和连接池容器对象的成员变量,并通过有参构造方法进行改造赋值
private Connection con;
List<Connection> pool;
public MyConnection3(Connection con,List<Connection> pool){
super(con);
this.con=con;
this.pool=pool;
}
//7.重写close()方法,完成归还连接
@Override
public void close() throws SQLException {
pool.add(con);
}
}
适配器设计模式-数据库连接池代码修改
测试测试结果
- 适配器连接模式归还数据库连接存在的问题
自定义连接类虽然很简洁了,但适配器类还是自己编写的,也比较的麻烦- 动态代理
动态代理: 在不改变目标对象方法的情况下对方法进行增强
组成: 被代理对象: 真实的对象 代理对象: 内存中的对象
要求: 代理对象必须和被代理对象实现相同的接口
实现: Proxy.newProxyInstance(类加载器,接口类型Class数组,代理规则)
参数:
1.类加载器: 和被代理的对象使用相同的类加载器
2.接口类型的Class数组: 和被代理对象使用相同的数组
3.代理的规则: 完成代理增强的规则- 动态代理方式归还数据库连接的思想
1.通过Proxy来完成对Connection实现类对象的代理
2.代理过程中判断如果执行的是close方法,就将连接归还池中,如果不是则调用连接对象原来的方法即可- 动态代理方式归还数据库连接的实现步骤
1.定义一个类,实现DateSource接口
2.定义一个容器,用于保存多个Connections连接对象
3.定义静态代码块,通过JDBC工具类获取10个连接保存到容器中
4.重写getConnection()方法,从容器中获取一个连接
5.通过Proxy代理,如果是close方法,就将连接归还到池中,如果不是则调用原有功能
6.定义getSize()方法,用于获取容器的大小并返回连接池代码
package com.itheima03;
import com.itheima.utils.JDBCUtils;
import com.itheima02.MyConnection3;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
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 ProxyDateSource implements DataSource {
//1.装备容器,用于保存多个连接对象
private static List<Connection> pool = Collections.synchronizedList(new ArrayList<>());
//2.定义静态代码块,通过工具类获取10个连接对象
static {
for (int i = 1; i <= 10; i++) {
Connection con = JDBCUtils.getConnection();
pool.add(con);
}
}
/*
动态代理方式归还连接
* */
//3.重写getConnection(),用于获取一个连接对象
@Override
public Connection getConnection() throws SQLException {
if (pool.size() > 0) {
Connection con = pool.remove(0);
Connection proxyCon = (Connection) Proxy.newProxyInstance(con.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(con);
return null;
} else {
return method.invoke(con, args);
}
}
});
return proxyCon;
} else {
throw new RuntimeException("连接数量用尽!");
}
}
//4.通过getSize方法,获取连接池容器的大小
public int getSize() {
return pool.size();
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@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;
}
}
- 测试代码不变,测试结果
- 动态代理-测试结果
- 动态代理方式归还数据库连接存在的问题
自定义的连接池技术不够完善,功能也不够强大开源数据库连接池
- C3P0数据库连接池的使用步骤
1.导入jar包
2.导入配置文件到src目录下
3.创建C3P0连接池对象
4.获取数据库连接进行使用
注意: C3P0的配置文件会自动加载,但是必须叫c3p0-config.xml或者c3p0-config.propertesc3p0-config.xml的配置
<c3p0-config>
<!-- 使用默认的配置读取连接池对象 -->
<default-config>
<!-- 连接参数 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://192.168.168.10:3306/db14</property>
<property name="user">root</property>
<property name="password">Qs970216~</property>
<!-- 连接池参数 -->
<!--初始化的连接数量-->
<property name="initialPoolSize">5</property>
<!-- 最大连接数量-->
<property name="maxPoolSize">10</property>
<!-- 超时时间-->
<property name="checkoutTimeout">3000</property>
</default-config>
<named-config name="otherc3p0">
<!-- 连接参数 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://192.168.168.10:3306/db2</property>
<property name="user">root</property>
<property name="password">Qs97216~</property>
<!-- 连接池参数 -->
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">8</property>
<property name="checkoutTimeout">1000</property>
</named-config>
</c3p0-config>
获取连接测试
package com.itheima01;
import com.itheima03.ProxyDateSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//自定义数据路库连接池的测试
public class MyDataSourceTest {
public static void main(String[] args) throws SQLException {
//1.创建连接池对象
// MyDataSource dataSource = new MyDataSource();
ProxyDateSource dataSource = new ProxyDateSource();
System.out.println("使用之前连接池大小:" + dataSource.getSize());
//2.通过连接池对象的获取连接对象
Connection con = dataSource.getConnection();
//打印连接对象
System.out.println(con.getClass());
//3.查询学生表的全部信息
String sql = "SELECT * FROM student";
PreparedStatement pst = con.prepareStatement(sql);
//4.执行SQL语句,接收结果集
ResultSet rs = pst.executeQuery();
//5.处理结果集
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" + rs.getInt("age") + "\t" + rs.getDate("brithday"));
}
//6.释放资源
rs.close();
pst.close();
con.close(); //用完之后,关闭连接
System.out.println("使用之前连接池大小:" + dataSource.getSize());
}
}
注意这里有报错
Exception in thread "main" java.sql.SQLException: An attempt by a client to checkout a Connection has timed out. 导入驱动的包mysql-connector-java-5.1.37-bin.jar解决
测试结果
Druif 数据库连接池的使用步骤
1.导入jar包
2.编写配置文件,放入到src目录下
3.通过Properties 集合加载配置文件
4.通过druid连接池工厂类获取数据库连接池对象
5.获取数据库连接进行使用获取连接测试
package com.itheima04;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
/*
* 1.通过Properties集合,手动加载配置文件
* 2.通过Druid连接池工厂类获取数据库连接池对象
* 3.通过连接池对象获取数据库连接使用
* */
public class Druid {
public static void main(String[] args) throws Exception {
//获取配置文件的流对象
InputStream is = Druid.class.getClassLoader().getResourceAsStream("druid.properties");
//1.通过Properties集合,手动加载配置文件
Properties prop = new Properties();
prop.load(is);
//2.通过Druid连接池工厂类获取数据库连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//3.通过连接池对象获取数据库连接使用
Connection con = dataSource.getConnection();
//3.查询学生表的全部信息
String sql = "SELECT * FROM student";
PreparedStatement pst = con.prepareStatement(sql);
//4.执行SQL语句,接收结果集
ResultSet rs = pst.executeQuery();
//5.处理结果集
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" + rs.getInt("age") + "\t" + rs.getDate("brithday"));
}
//6.释放资源
rs.close();
pst.close();
con.close();
}
}
druid.properties配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://119.29.63.57:3306/db14
username=root
password=Qs970216~
# 初始化连接数量
initialSize=5
# 最大连接数量
maxActive=10
# 超时时间
maxWait=3000
测试结果
数据库连接池工具类
package com.itheima.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/* 数据库连接池的工具类 */
public class DataSourceUtils {
//1.私有化构造方法
private DataSourceUtils() {
}
;
//2.声明数据源变量
private static DataSource dataSource;
//3.提供静态代码块,完成配置文件的加载和获取数据库连接池对象
static {
try {
//1.完成配置文件的加载
InputStream is = DataSourceUtils.class.getClassLoader().getResourceAsStream("druid.properties");
//2.通过Properties集合,手动加载配置文件
Properties prop = new Properties();
prop.load(is);
//3.获取数据库连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
} catch (Exception e) {
e.printStackTrace();
}
}
//4.提供一个获取数据库连接的方法
public static Connection getConnection() {
Connection con = null;
try {
con = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
//5.提供一个获取数据库连接池的对象方法
public static DataSource getDataSource(){
return dataSource;
}
//6.释放资源
public void close(Connection con, ResultSet rs, Statement stat){
if (con!= null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stat != null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs!= null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void close(Connection con, Statement stat){
if (con != null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stat != null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
