3.JDBC高级之数据库连接池.md

1.数据库连接池的概念

1.1 数据库连接池的背景

数据库连接是一种关键的、有限的、昂贵的资源，这一点在多用户的页面应用程序中体现的尤为突出
对数据库连接的管理能显著影响到整个应用程序的性能指标，数据库连接池正式针对整个问题提出的

1.2 数据库连接池

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不用重新建立一个。这项技术能明显提高对数据库操作的性能。

2.自定义数据库连接池

2.1DataSource

1）DataSource接口概述

• javax.sql.DataSource接口：数据源（数据库连接池）。Java官方提供的数据库连接池规范（接口）
• 如果想要完成数据库连接池技术，就必须实现DataSource接口
• 核心方法：获取数据库连接对象：Connection getConnection();

2）自定义数据库连接池
① 定义一个类，实现DataSource接口
② 定义一个容器，用于保存多个Connection连接对象
③ 定义静态代码块，通过JDBC工具类获取10个连接保存到容器中
④ 重写getConnection方法，从容器中获取一个连接并返回
⑤ 定义getSize方法，用于获取容器的大小和返回

package jdbc02;

import jdbc01.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.Collection;
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 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;
    }
}

3）自定义数据库连接池的测试

package jdbc02;

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

public class MyDataSourceTest {
    public static void main(String[] args) throws Exception{
        //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("sid") + '\t' + rs.getString("name") + '\t' + rs.getInt("age") + '\t' + rs.getDate("birthday"));
        }

        //释放资源
        rs.close();
        pst.close();
        con.close(); //用完以后，关闭连接
        
        System.out.println("使用之后的数量：" + dataSource.getSize());
    }
}

2.2 归还连接

归还数据库连接的方式：

• 继承方式
• 装饰设计模式
• 适配器设计模式
• 动态代理方式

2.2.1 归还连接：继承方式

1）继承方式归还数据库连接的思想

• 通过答应连接对象，发现DriverManager获取的连接实现类是class com.mysql.cj.jdbc.ConnectionImpl
• 那我们就可以自定义一个类，继承ConnectionImpl这个类，重写close()方法，完成连接对象的归还

2）继承归还数据库连接的实现步骤
① 定义一个类，继承ConnectionImpl
② 定义Connection连接对象和连接池对象的成员变量
③ 通过有参构造方法完成对成员变量的赋值
④ 重写close方法，将连接对象添加到连接池中

3）继承方式归还数据库连接存在问题
通过查看JDBC工具类获取连接的方法发现；我们虽然定义了一个子类，完成了归还连接的操作。但是DriverManager获取的还是ConnectionImpl这个对象，并不是我们的子类对象，而我们不能整体去修改驱动包中类的用跟那个，所以继承这种方式行不通！

package jdbc03;

import com.mysql.cj.conf.HostInfo;
import com.mysql.cj.jdbc.ConnectionImpl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/*
* 自定义的连接对象
* */
public class MyConnection1 extends ConnectionImpl {//1 定义一个类，继承ConnectionImpl
    //2 定义Connection连接对象和连接池对象的成员变量
    private Connection con;
    private List<Connection> pool;

    //3 通过有参构造方法完成对成员变量的赋值
    public MyConnection1(HostInfo hostInfo, Connection con, List<Connection> pool) throws SQLException {
        super(hostInfo);
        this.con = con;
        this.pool = pool;
    }

    //4 重写close方法，将连接对象添加到连接池中
    @Override
    public void close() throws SQLException {
        pool.add(con);
    }
}

2.2.2 归还连接=装饰设计模式

1）装饰设计模式归还数据库连接的思想

• 我们自己定义一个类，实现Connection接口，这样就具备了和ConnectionImpl相同的行为了
• 重写close()，完成连接的归还。其余的功能还调用mysql驱动包实现类原有的方法即可

2）装饰设计模式归还数据库连接的实现步骤
① 定义一个类，实现COnnection接口
② 定义Connection连接对象和连接池容器对象的成员变量
③ 通过有参构造方法完成对成员变量的赋值
④ 重写close()方法，将连接对象添加到池中
⑤ 剩余方法，只需要调用mysql驱动包的连接对象完成即可
⑥ 在自定义连接池中，将获取的连接对象通过自定义连接对象进行包装

3）装饰设计模式归还数据库连接存在的问题
实现Connection接口后，有大量的方法需要在自定义类中进行重写

修改MyDataSource

    //3 重写getConnection()，用于获取一个连接对象
    @Override
    public Connection getConnection() throws SQLException {
        if(pool.size() > 0){
            Connection con = pool.remove(0);
            //通过自定义的连接对象对原有的连接对象进行包装
            MyConnection2 myCon = new MyConnection2(con,pool);
            return myCon;
        }else{
            throw new RuntimeException("连接数量已用尽");
        }
    }

新建类MyConnection2

package jdbc03;

import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
//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驱动包的连接对象完成即可
    //6 在自定义连接池中，将获取的连接对象通过自定义连接对象进行包装
    @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);
    }
}

2.2.3 归还连接-适配器设计模式

1）适配器设计模式归还数据库连接思想

• 我们可以提供一个适配器类，实现Connection接口，将所有方法进行实现（除了close方法）
• 自定义连接类只需继承这个适配器，重写需要改进的close()方法即可

2）适配器设计模式归还数据库连接实现步骤
① 定义一个适配器类，实现Connection接口
② 定义Connection连接对象的成员变量
③ 通过有参构造方法完成对成员变量的赋值
④ 重写所有方法（除了close），调用mysql驱动包的连接对象完成即可
⑤ 定义一个连接类，继承适配器类
⑥ 定义Connection连接对象和连接池容器对象的成员变量，并通过有参构造器进行赋值
⑦ 重写close()方法，完成归还
⑧ 在自定义连接池中，将获取的连接对象通过自定义连接对象进行包装

3）适配器设计模式归还数据库连接存在的问题
自定义连接类虽然很简洁了，但适配器类还是我们自己编写的，也比较的麻烦

修改MyDataSource

    //3 重写getConnection()，用于获取一个连接对象
    @Override
    public Connection getConnection() throws SQLException {
        if(pool.size() > 0){
            Connection con = pool.remove(0);
            //通过自定义的连接对象对原有的连接对象进行包装
            MyConnection3 myCon = new MyConnection3(con,pool);
            return myCon;
        }else{
            throw new RuntimeException("连接数量已用尽");
        }
    }

MyAdapter.java

package jdbc03;

import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

public abstract class MyAdapter implements Connection {//1 定义一个适配器类，实现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);
    }
}

MyConnection3.java

package jdbc03;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class MyConnection3 extends MyAdapter{//5 定义一个连接类，继承适配器类
    //6 定义Connection连接对象和连接池容器对象的成员变量，并通过有参构造器进行赋值
    private Connection con;
    private 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);
    }
}

2.2.4 动态代理

动态代理：在不改变目标对象方法的情况下对方法进行增强

组成：
被代理对象：真实的对象
代理对象：内存中的一个对象

要求：代理对象必须和被代理对象实现相同的接口

实现：
Proxy.newProxyInstance()

StudentInterface

package proxy;

public interface StudentInterface {
    void eat(String name);
    void study();
}

Student

package proxy;

public class Student implements StudentInterface{
    public void eat(String name){
        System.out.println("学生吃" + name);
    }

    public void study(){
        System.out.println("在家自学");
    }
}

Test

package proxy;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;

public class Test {
    public static void main(String[] args) {
        Student stu = new Student();
//        stu.eat("米饭");
//        stu.study();
        /*
        * 要求：在不改动Student类中任何的代码的前提下，通过study方法输出一句话：大爷，来玩呀
        * 类加载器：和被代理对象使用相同的类加载器
        * 接口类型Class数组：和被代理对象使用相同的接口
        * 代理规则：完成代理增强的功能
        * */
        StudentInterface proxyStu = (StudentInterface) Proxy.newProxyInstance(stu.getClass().getClassLoader(), new Class[]{StudentInterface.class}, new InvocationHandler() {
            /*
            * 执行Student类中所有的方法都会经过invoke方法
            * 对method方法进行判断
            *   如果是study，则对其增强
            *   如果不是，还去调用学生对象原有的功能即可
            * */

            @Override
            public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
                if(method.getName().equals("study")){
                    System.out.println("大爷，来玩呀！");
                    return null;
                }else{
                    return method.invoke(stu,args);
                }
            }
        });

        proxyStu.eat("米饭");
        proxyStu.study();
    }
}

2.2.5 归还连接-动态代理方式

1）动态代理方式归还数据库连接的思想

• 我们可以通过Proxy来完成对Connection实现类对象的代理
• 代理过程中判断如果执行的是close方法，我们就将连接归还池中，如果是其他方法则调用连接对象原来的功能即可

2）动态代理归还数据库连接的实现步骤
① 定义一个类，实现DataSource接口
② 定义一个容器，用于保存多个Connection连接对象
③ 定义静态代码块，通过JDBC工具类获取10个连接保存到容器汇总
④ 重写getConnection方法，从容器中获取一个连接
⑤ 通过Proxy代理，如果是close方法，就将连接归还池中。如果是其他方法则调用原有功能
⑥ 定义getSize方法，用于获取容器的大小并返回

3）动态代理方式归还数据库存在的问题
我们自己写的连接池技术不够完善，功能也不够强大

修改MyDataSource

    //动态代理的方式
    @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("连接数量已用尽");
        }
    }

3.开源数据库连接池

3.1 C3P0

C3P0数据库连接池的使用步骤
① 导入jar包
② 导入配置文件到src目录下
③ 创建C3P0连接池对象
④ 获取数据库连接进行使用

注意：C3P0的配置文件会自动加载，但是必须叫c3p0-config.xml或c3p0-config.properties

下载地址：https://mvnrepository.com/artifact/com.mchange/c3p0/0.9.5.5
依赖jar包下载地址：https://mvnrepository.com/artifact/com.mchange/mchange-commons-java/0.2.20
导入jar包

创建c3p0-config.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- 这是默认配置信息 -->
    <default-config>
        <!-- 连接四大参数配置 -->
        <property name="jdbcUrl">jdbc:mysql://192.168.1.224:3306/db14</property>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <!-- 池参数配置 -->
        <!--连接池用完自动增量3-->
        <property name="acquireIncrement">3</property>
        <!--初始化的连接数量-->
        <property name="initialPoolSize">10</property>
        <!--最小连接数量-->
        <property name="minPoolSize">2</property>
        <!--最大连接数量-->
        <property name="maxPoolSize">10</property>
        <!--超时时间-->
        <property name="checkoutTimeout">3000</property>
    </default-config>
</c3p0-config>

测试c3p0

package c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class C3P0Test1 {
    public static void main(String[] args) throws SQLException {
        //1 创建c3p0的数据库连接池对象
        DataSource dataSource = new ComboPooledDataSource();

        //2 通过连接池对象获取数据库连接
        Connection con = dataSource.getConnection();

        //3 执行操作
        //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("sid") + '\t' + rs.getString("name") + '\t' + rs.getInt("age") + '\t' + rs.getDate("birthday"));
        }

        //释放资源
        rs.close();
        pst.close();
        con.close();

    }
}

测试2

package c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0Test2 {
    public static void main(String[] args) throws SQLException {
        //1 创建C3P0的数据库连接池对象
        DataSource dataSource = new ComboPooledDataSource();

        //2 测试
        for(int i = 1;i <= 11;i++){
            Connection con = dataSource.getConnection();
            System.out.println(i + ":" + con);

            if(i == 5){
                con.close();
            }
        }
    }
}

3.2 Druid数据库连接池

1）Druid数据库连接池的使用步骤
① 导入jar包
② 编写配置文件，放在src目录下
③ 通过Properties集合加载配置文件
④ 通过Druid连接池工厂类获取数据库连接池对象
⑤ 获取数据库连接进行使用

注意：Druid不会自动加载配置文件，需要我们手动加载，但是文件的名称可以自定义

下载地址：https://mvnrepository.com/artifact/com.alibaba/druid
导入jar包

创建druid.properties文件

driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://192.168.1.224:3306/db14
username=root
password=123456
#初始化连接数量
initialSize=5
#最大的连接数量
maxActive=10
#超时时间
maxWait=3000

测试Druid

package druid;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class DruidTest1 {
    public static void main(String[] args) throws Exception {
        //获取配置文件的流对象
        InputStream is = DruidTest1.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("sid") + '\t' + rs.getString("name") + '\t' + rs.getInt("age") + '\t' + rs.getDate("birthday"));
        }

        //释放资源
        rs.close();
        pst.close();
        con.close();
    }
}

3.3 连接池的工具类

创建工具类

package druid;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.support.spring.stat.annotation.Stat;

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 {
            //完成配置文件的加载
            InputStream is = DataSourceUtils.class.getClassLoader().getResourceAsStream("druid.properties");

            Properties prop = new Properties();
            prop.load(is);

            //获取数据库连接池对象
            dataSource = DruidDataSourceFactory.createDataSource(prop);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //4.提供一个获取数据库连接的方法
    public static Connection getConnection(){
        Connection con = null;
        try {
            con = dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return con;
    }

    //5.提供一个获取数据库连接池对象的方法
    public static DataSource getDataSource(){
        return dataSource;
    }

    //6.释放资源
    public static void close(Connection con, Statement stat, ResultSet rs){
        if(con != null){
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    public static void close(Connection con, Statement stat){
        if(con != null){
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

测试

package druid;

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

public class DruidTest2 {
    public static void main(String[] args) throws SQLException {
        //1.通过连接池工具类获取一个数据库连接
        Connection con = DataSourceUtils.getConnection();

        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("sid") + '\t' + rs.getString("name") + '\t' + rs.getInt("age") + '\t' + rs.getDate("birthday"));
        }

        //释放资源
        DataSourceUtils.close(con,pst,rs);
    }
}