数据库连接池的基本使用

首先创建一个类管理连接池,对连接进行获取以及分配

ConnectionPool.java

package jdbc;

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

public class ConnectionPool {
    
    List<Connection> connections=new ArrayList<Connection>();
    int size;//连接池容量
    public ConnectionPool(int size) {
        this.size=size;
        init();
    }
    
    
    private void init() {

        // 不能使用try-with-resource的方式创建连接,因为这些连接要放到连接池中备用
        try {
            Class.forName("com.mysql.jdbc.Driver");
            for(int i=0; i<size;i++) {
                Connection connection=DriverManager.getConnection
                        ("jdbc:mysql://127.0.0.1:3306/study?characterEncoding=UTF-8", "root", "joey");
                connections.add(connection);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 从连接池中获取连接
     * @return
     */
    public synchronized Connection getConnection() {
        while(connections.isEmpty()) {
            // 如果连接池中没有可用连接, 让线程等待
            try {
                this.wait();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        // 从连接池中获取一个连接
        Connection connection=connections.remove(0);
        return connection;
    }
    
    /**
     * 返回连接给连接池,并通知其它线程(有可用连接)
     * @param connection
     */
    public synchronized void returnConnection(Connection connection) {
        connections.add(connection);
        this.notifyAll();
    }
}

完~

测试类中创建多线程虚拟任务,利用连接池中的连接完成

ConnectionPoolTest.java

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectionPoolTest {

    public static void main(String[] args) {
        long start = System.currentTimeMillis();
        
        // 通过连接池的管理,让多线程任务可以循环利用连接资源
        ConnectionPool connectionPool = new ConnectionPool(10);
        for (int i = 0; i < 100; i++) {
            new WorkingThread("working thread" + i, connectionPool).start();
        }

        // 直接发起多线程任务
/*        for (int i = 0; i < 100; i++) {
            new WorkingThread2("working thread" + i).start();
        }*/
        
        long end = System.currentTimeMillis();
        System.out.println("耗时: " + (end - start));
    }
}

class WorkingThread extends Thread {

    private ConnectionPool connectionPool;

    public WorkingThread(String name, ConnectionPool connectionPool) {
        super(name);
        this.connectionPool = connectionPool;
    }

    @Override
    public void run() {
        Connection connection = connectionPool.getConnection();
        System.out.println(this.getName() + ":\t获取了一个连接,并开始工作");
        try (Statement statement = connection.createStatement()) {
            // 模拟耗时一秒的数据库SQL语句
            Thread.sleep(1000);
            statement.execute("select * from employee;");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        connectionPool.returnConnection(connection);
        System.out.println(this.getName() + ":\t结束工作,并返回连接");
    }
}

class WorkingThread2 extends Thread {

    public WorkingThread2(String name) {
        super(name);
    }

    @Override
    public void run() {
        System.out.println(this.getName() + ":\t获取了一个连接,并开始工作");
        try (Connection connection = DriverManager
                .getConnection("jdbc:mysql://127.0.0.1:3306/study?characterEncoding=UTF-8", "root", "joey");
                Statement statement = connection.createStatement()) {
            // 模拟耗时一秒的数据库SQL语句
            Thread.sleep(1000);
            statement.execute("select * from employee;");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        System.out.println(this.getName() + ":\t结束工作,并返回连接");
    }
}

--主线程中的耗时计算不准确??