手写数据库连接池

概述

不使用连接池的缺点:

1. tcp连接三次握手消耗时间
2. 消耗内存严重

使用池化思想, 达到连接的可复用, 节省时间和资源

代码

连接池接口

package jdbcPool;


public interface IPool {
    //返回我们自己封装的连接对象
    PooledConnection getConnection();

    void createConnections(int count);
}

连接类(由于jdk自己的连接对象没有状态标识 所以我们自己定义连接对象)

package jdbcPool;

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

public class PooledConnection {

    private Connection conn = null;
    //标识该连接是否被占用
    private boolean isBusy = false;

    public PooledConnection(Connection conn, boolean isBusy) {
        this.conn = conn;
        this.isBusy = isBusy;
    }

    public ResultSet queryBySql(String sql) {

        Statement sm = null;
        ResultSet rs = null;

        try {
            sm = conn.createStatement();
            rs = sm.executeQuery(sql);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public int updateBySql(String sql) {
        Statement sm = null;
        int count = -1;
        try {
            sm = conn.createStatement();
            count = sm.executeUpdate(sql);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public boolean isBusy() {
        return isBusy;
    }

    public void setBusy(boolean isBusy) {
        this.isBusy = isBusy;
    }
    //关闭连接 设置状态为空闲
    public void close() {
        isBusy = false;
    }

}

连接池接口实现类

package jdbcPool;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Vector;

public class JdbcPool implements IPool {

    private static String jdbcDriver = "";

    private static String dbUrl = "";

    private static String dbUsername = "";

    private static String dbPassword = "";

    private static int initConnCount;

    private static int maxConnnects;

    private static int incrementalcount;

    private static Vector<PooledConnection> vector = new Vector<PooledConnection>();
    //读取配置文件
    public void init() {
        InputStream in = JdbcPool.class.getClassLoader()
                .getResourceAsStream("jdbcPool/jdbc.properties");

        Properties pt = new Properties();

        try {
            pt.load(in);
        }
        catch (IOException e) {
            e.printStackTrace();
        }

        jdbcDriver = pt.getProperty("jdbcDriver");
        dbUrl = pt.getProperty("dbUrl");
        dbUsername = pt.getProperty("dbUsername");
        dbPassword = pt.getProperty("dbPassword");
        initConnCount = Integer.valueOf(pt.getProperty("initConnCount"));
        maxConnnects = Integer.valueOf(pt.getProperty("maxConnnects"));
        incrementalcount = Integer.valueOf(pt.getProperty("incrementalcount"));

        try {
            //直接反射就可以
            /* Driver driver = (Driver)(Class.forName(jdbcDriver).newInstance());
            DriverManager.registerDriver(driver); */
            Class.forName(jdbcDriver);

            //我们要创建数据库的连接，然后把连接放到vector里面
            createConnections(initConnCount);
        }
        catch (InstantiationException e) {
            e.printStackTrace();
        }
        catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //synchronized很重要 PooledConnection中的isBusy状态需要被同步修改,否则会造成多个线程拿到同一个连接的情况
    public synchronized PooledConnection getConnection() {

        if (vector.size() <= 0) {
            System.out.println("连接池中还没有连接！");
            throw new RuntimeException("连接池中还没有连接！");
        }

        PooledConnection conn = getActiveConnection();

        //等于null的情况就是连接池中的连接全部为忙碌的情况
        if (conn == null) {

            // 这时候就需要对连接池进行增长
            createConnections(incrementalcount);
            // 可能还是拿不到连接 被其他线程抢占了
            conn = getActiveConnection();
            // 拿不到连接则等待
            while (conn == null) {
                try {
                    Thread.sleep(300);
                }
                catch (InterruptedException e) {
                    e.printStackTrace();
                }
                conn = getActiveConnection();
            }

        }

        return conn;
    }

    //我们从池中拿连接
    private PooledConnection getActiveConnection() {

        for (PooledConnection conn : vector) {
            if (!conn.isBusy()) {
                Connection trueconn = conn.getConn();

                try {
                    //判断物理连接是否有效(可能存在该对象但是物理连接已经失效了的情况)
                    if (!trueconn.isValid(0)) {//该方法会真正的操作数据库的临时表来判断该连接是否有效 0不校验超时
                        //失效 创建新的物理连接 并设置到封装的连接对象中
                        Connection newconn = DriverManager.getConnection(dbUrl,
                                dbUsername,
                                dbPassword);

                        conn.setConn(newconn);
                    }
                }
                catch (SQLException e) {
                    e.printStackTrace();
                }
                //返回连接前设置该连接已被占用
                conn.setBusy(true);
                return conn;
            }
        }

        return null;
    }

    public void createConnections(int count) {
        for (int i = 0; i < count; i++) {
            //我们创建连接的时候，必须要判断这个池中的连接要小于我们的最大连接
            if (maxConnnects > 0 && vector.size() >= maxConnnects) {
                System.out.println("连接池中的连接数量已经达到了最大值！");
                throw new RuntimeException("连接池中的连接数量已经达到了最大值！");
            }

            try {
                Connection conn = DriverManager.getConnection(dbUrl,
                        dbUsername,
                        dbPassword);

                vector.add(new PooledConnection(conn, false));

            }
            catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

}

properties文件

jdbcDriver = com.mysql.jdbc.Driver

dbUrl = jdbc:mysql://localhost:3306/consult

dbUsername = myConsult

dbPassword = 123456

#数据库连接池中的初始连接数
initConnCount = 10

#最大连接数
maxConnnects = 100

#增长步长
incrementalcount = 2

连接池维护类(单例模式)

package jdbcPool;

public class DBManager {
    //静态内部类实现 n个线程同时加载一个内部类的时候 永远是线程互斥的
    private static class CreatePool {
        private static JdbcPool pool = new JdbcPool();
    }

    public static JdbcPool getInstance() {
        return CreatePool.pool;
    }

}

测试类

package jdbcPool;

import java.sql.ResultSet;

import org.junit.Before;
import org.junit.Test;

public class JdbcPoolTest {

    JdbcPool pool = DBManager.getInstance();

    @Before
    public void before() {
        pool.init();
    }

    @Test
    public void select() throws Exception {

        PooledConnection conn = pool.getConnection();

        ResultSet rs = conn.queryBySql("select * from consult_contract");

        System.out.println("线程名：" + Thread.currentThread().getName());
        if (rs.next()) {
            System.out.print(rs.getString("PSPTID") + "\t");
            System.out.print(rs.getString("CONTRACT_CODE") + "\t");
            System.out.print(rs.getString("ACTIVETIME") + "\t\n");
        }

        rs.close();
        conn.close();

    }
    // 并发测试
    @Test
    public void threadTest() {
        for (int i = 0; i < 100; i++) {
            new Thread(new Runnable() {
                public void run() {
                    try {
                        select();
                    }
                    catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }).start();
        }
    }
}