手写数据库连接池

说明：本实例用于学习了解数据库连接池原理，许多地方不够完善

在实际项目中可以使用开源的数据库连接池 https://github.com/alibaba/druid

一、配置文件db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
username=root
password=
min=5
max=20

二、DbUtil用于生成连接

package com.db;

import com.mysql.jdbc.Connection;

import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.util.Properties;

public class DbUtil {

    private static String PROP_PATH = "db.properties";
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    private DbUtil(){}

    static {
        try {
            InputStream in = DbUtil.class.getClassLoader().getResourceAsStream(PROP_PATH);
            Properties props = new Properties();
            props.load(in);
            driver = props.getProperty("driver");
            url = props.getProperty("url");
            username = props.getProperty("username");
            password = props.getProperty("password");
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    public static Connection getConnection(){
        try {
            Class.forName(driver);
            Connection conn = (Connection) DriverManager.getConnection(url,username,password);
            System.out.println("generate a new connection: " + conn.hashCode());
            return conn;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }
}

 三、连接池DbPool

package com.db;

import com.mysql.jdbc.Connection;

import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;
import java.util.concurrent.ConcurrentLinkedQueue;

public class DbPool {

    private static String PROP_PATH = "db.properties";
    private static String max = null;
    private static String min = null;
    private static int poolSize = 0; //记录连接池中的数量

    private DbPool(){}

    //private static List<Connection> pool = Collections.synchronizedList(new LinkedList<Connection>());//让LinkedList变成线程安全的
    private static ConcurrentLinkedQueue<Connection> pool = new ConcurrentLinkedQueue<Connection>();//更加高效的线程安全容器

    static {
        if (poolSize == 0 && pool.isEmpty()) {
            synchronized (DbPool.class) {
                if (poolSize == 0 && pool.isEmpty()) {
                    try {
                        System.out.println("连接池为空，初始化");
                        InputStream in = DbUtil.class.getClassLoader().getResourceAsStream(PROP_PATH);
                        Properties props = new Properties();
                        props.load(in);
                        max = props.getProperty("max");
                        min = props.getProperty("min");
                        for (int i = 0; i < Integer.parseInt(min); i++) {
                            pool.add(DbUtil.getConnection());
                            poolSize++;
                        }
                    } catch (IOException ex) {
                        ex.printStackTrace();
                    }
                }
            }
        }
    }

    public static Connection getConnection() {
        try {
            if (!pool.isEmpty()) {
                System.out.println("连接池非空，直接返回");
                //return (pool).removeFirst();
                return pool.remove();
            } else {
                System.out.println("连接池为空,进行扩容");
                expland(5);
                System.out.println("等待获取");
                while (true) {
                    if (!pool.isEmpty()) {
                        System.out.println("获取成功,返回");
                        //return pool.removeFirst();
                        return pool.remove();
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    /**
     * 销毁连接池
     */
    public static void distory(){
        for (Connection conn : pool) {
            try {
                conn.close();
                pool.remove(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        poolSize = 0;
        System.out.println("销毁成功，未使用：" + getAvaliableSize() + "总数：" + getPoolSize());
    }

    /**
     * 回收连接
     * @param conn
     */
    public static void recycle(Connection conn){
        pool.add(conn);
        System.out.println("回收成功，未使用：" + getAvaliableSize() + "总数：" + getPoolSize());
    }

    /**
     * 获取当前连接池有多少个连接，包括已使用和未使用
     * @return
     */
    public static int getPoolSize(){
        return poolSize;
    }

    /**
     * 获取未使用的数量
     * @return
     */
    public static int getAvaliableSize(){
        return pool.size();
    }

    /**
     * 线程池扩容
     * @num 增加的数量
     */
    private static void expland(int num) {
        if (poolSize > Integer.parseInt(max)) {
            System.out.println("总连接数量大于最大值，直接返回");
            return;
        }
        for (int i=0; i<num; i++) {
            pool.add(DbUtil.getConnection());
            poolSize++;
        }
        System.out.println("扩容成功，未使用：" + getAvaliableSize() + "总数：" + getPoolSize());
    }
}

四、测试

package com.db;


import com.mysql.jdbc.Connection;

public class Test {


    public static void main(String[] args) {

        for (int i=0;i<18;i++) {
            //DbUtil.getConnection();
            System.out.println("从连接池获取: " + DbPool.getConnection().hashCode());

        }
        DbPool.distory();

        Connection connection1 = DbPool.getConnection();
        Connection connection2 = DbPool.getConnection();
        Connection connection3 = DbPool.getConnection();

        DbPool.recycle(connection1);


    }


}