JDBC：数据库连接池

数据库连接池原理

• 当有多个线程访问数据库，每个线程都会创建一个连接
• 创建和关闭连接需要消耗资源
• 连接池在使用前，就会创建好一定数量的连接

ConnectionPool构造方法和初始化

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> cs = new ArrayList<Connection>();
    int size;
    public ConnectionPool(int size) {
        this.size = size;
        init();
    }
    public void init() {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            for (int i = 0; i < size; i++) {
                Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin");
                cs.add(c);
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    public synchronized Connection getConnection() {
        while (cs.isEmpty()) {
            try {
                this.wait();
            } catch (InterruptedException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        Connection c = cs.remove(0);
        return c;
    }
    
    public synchronized void returnConnection(Connection c) {
        cs.add(c);
        this.notifyAll();
    }
}

测试类

• 首先初始化一个有3条连接的数据库连接池
• TraditonalWorkingThread文件

package jdbc;

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

public class TraditonalWorkingThread extends Thread {
    @Override
    public void run() {
        // 加载数据库驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        try (
                Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin");
                Statement st = c.createStatement();
            ) 
            {
                for (int i = 0; i < TestConnectionPool.insertTimes; i++) {
                    String sql = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")";
                    st.execute(sql);
                }
            }
            catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
}

• ConnectionpoolWorkingThread文件

package jdbc;

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


public class ConnectionpoolWorkingThread extends Thread {
    private ConnectionPool cp;
    
    public ConnectionpoolWorkingThread(ConnectionPool cp) {
        this.cp = cp;
    }
    
    @Override
    public void run() {
        Connection c = cp.getConnection();
        try (Statement st = c.createStatement()) {
            for (int i = 0; i < TestConnectionPool.insertTimes; i++) {
                String sql = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")";
                st.execute(sql);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        cp.returnConnection(c);
    }
}

• TestConnectionPool文件

package jdbc;

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

public class TestConnectionPool {
    
    private static int threadNumber = 100;
    public static int insertTimes = 1;
    
    public static void main(String[] args) {
        traditionalWay();
        connectionPoolWay();
    }

    private static void connectionPoolWay() {
        ConnectionPool cp = new ConnectionPool(10);
        System.out.println("开始连接池方式插入数据测试：");
        long start = System.currentTimeMillis();
        List<Thread> ts = new ArrayList<>();
        for (int i = 0; i < threadNumber; i++) {
            Thread t = new ConnectionpoolWorkingThread(cp);
            t.start();
            ts.add(t);
        }
        // 等待所有线程结束
        for (Thread t : ts) {
            try {
                t.join();
            } catch (InterruptedException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        long end = System.currentTimeMillis();
        
        System.out.printf("使用连接池方式，启动%d条线程，每个线程插入%d条数据，一共耗时%d 毫秒%n", threadNumber, insertTimes, end-start);
    }

    private static void traditionalWay() {
        System.out.println("开始传统方式插入数据测试：");
        long start = System.currentTimeMillis();
        List<Thread> ts = new ArrayList<>();
        for (int i = 0; i < threadNumber; i++) {
            Thread t = new TraditonalWorkingThread();
            t.start();
            ts.add(t);
        }
        // 等待所有线程结束
        for (Thread t : ts) {
            try {
                t.join();
            } catch (InterruptedException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        long end = System.currentTimeMillis();
        
        System.out.printf("使用传统方式，启动%d条线程，每个线程插入%d条数据，一共耗时%d 毫秒%n", threadNumber, insertTimes, end-start);
    }
}

image.png