JDBC 线程池

public class ConnectionPool {

    private String driver;
    private String url;
    private String user;
    private String password;
    private String testTable;
    private int initalConNum = 1;//连接池的初始大小
    private int increatmentalConNum = 3;//自动增长的数量
    private int maxConn = 20;        //连接池最大的大小
    private Vector<PooledConnection> connections = null;//存放连接池中链接数据库的向量

    public ConnectionPool(String driver, String url, String user, String password) {
        this.driver = driver;
        this.url = url;
        this.user = user;
        this.password = password;
        try {
            createPool();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public int getInitalConNum() {
        return initalConNum;
    }

    public int getIncreatmentalConNum() {
        return increatmentalConNum;
    }

    public int getMaxConn() {
        return maxConn;
    }

    public String getTestTable() {
        return testTable;
    }

    public void setTestTable(String testTable) {
        this.testTable = testTable;
    }

    /**
     * 创建一个数据库连接池
     *
     * @throws Exception
     */
    public synchronized void createPool() throws Exception {
        if (connections != null) {
            return;
        }
        //实例化数据库驱动
        Driver _driver = (Driver) Class.forName(driver).newInstance();
        //注册驱动
        DriverManager.registerDriver(_driver);
        //创建保存连接的向量
        this.connections = new Vector<PooledConnection>();
        //根据初始化中设置的值来创建连接
        createConnections(initalConNum);
    }

    /**
     * 创建连接
     *
     * @param numConn 连接数量
     */
    private void createConnections(int numConn) {
        for (int i = 0; i < numConn; ++i) {
            if (connections.size() >= maxConn) {
                break;
            }
            try {
                connections.addElement(new PooledConnection(newConnection()));
            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
    }

    /**
     * 创建一个新的数据库连接
     *
     * @return
     * @throws SQLException
     */
    private Connection newConnection() throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, password);
        //如果是第一次创建数据库,即检查数据库,获取此数据库允许支持的最大连接数目
        if (connections.size() == 0) {
            DatabaseMetaData metaData = conn.getMetaData();
            int driverMaxConn = metaData.getMaxConnections();
            if (driverMaxConn > 0 && maxConn > driverMaxConn) {
                maxConn = driverMaxConn;
            }
        }
        return conn;
    }

    public synchronized PooledConnection getConnection() {
        if (connections == null) {
            return null;
        }
        PooledConnection conn = getFreeConnection();
        while (conn == null) {//conn==null表示没有可用的连接,表示所有连接正在使用当中
            _wait(250);
            conn = getFreeConnection();
        }
        return conn;
    }

    private PooledConnection getFreeConnection() {
        PooledConnection conn = findFreeConnection();
        if (conn == null) {
            System.out.println("目前数据库中没有可用的连接,创建一些连接");
            createConnections(increatmentalConNum);
            conn = findFreeConnection();
            if (conn == null) {
                return null;
            }
        }
        return conn;
    }

    /**
     * 从所有的连接中查找一个可用的连接
     *
     * @return
     */
    private PooledConnection findFreeConnection() {
        for (int i = 0, size = connections.size(); i < size; ++i) {
            PooledConnection pc = connections.elementAt(i);
            if (!pc.getIsBusy()) {
                Connection conn = pc.getConnection();
                pc.setIsBusy(true);
                if (!isValid(conn)) {
                    try {
                        conn = newConnection();
                        pc.setConnection(conn);
                    } catch (SQLException sqle) {
                        sqle.printStackTrace();
                        connections.remove(i--);
                        continue;
                    }
                }
                return pc;
            }
        }
        return null;
    }

    /**
     * 判断连接是否可用
     *
     * @param conn
     * @return
     */
    public boolean isValid(Connection conn) {
        try {
            return conn.isValid(3000);
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            return false;
        }
    }

    /**
     * 返回一个数据库连接到连接池中，并把此连接置为空闲。
     * 所有使用连接池获得的数据库连接均应在不使用此连接时返回它。
     * @param conn
     */
    public void returnConnection(Connection conn) {
        if (connections == null) {
            return;
        }
        PooledConnection pConn;
        Enumeration<PooledConnection> enumera = connections.elements();
        while (enumera.hasMoreElements()) {
            pConn = enumera.nextElement();
            if (conn == pConn.getConnection()) {
                pConn.setIsBusy(false);
                break;
            }
        }
    }

    /**
     * 刷新所有连接池的对象
     */
    public synchronized void refreshConnections() {
        if (connections == null) {
            return;
        }
        PooledConnection pConn;
        Enumeration<PooledConnection> enumera = connections.elements();
        while (enumera.hasMoreElements()) {
            pConn = enumera.nextElement();
            while (pConn.getIsBusy()) {
                _wait(1000);
            }
            closeConnctionPool();
            try {
                pConn.setConnection(newConnection());
                pConn.setIsBusy(false);
            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
    }

    /**
     * 关闭池中所有连接并清空连接池
     */
    public void closeConnctionPool() {
        if (connections == null) {
            return;
        }
        PooledConnection pConn;
        Enumeration<PooledConnection> enumera = connections.elements();
        while (enumera.hasMoreElements()) {
            pConn = enumera.nextElement();
            if (pConn.getIsBusy()) {
                _wait(5000);
            }
            closeConnction(pConn.getConnection());
            connections.removeElement(pConn);
        }
        connections = null;
    }

    /**
     * 关闭一个数据库连接
     *
     * @param conn
     */
    private void closeConnction(Connection conn) {
        try {
            conn.close();
            conn = null;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }

    /**
     * 使程序等待指定的秒数
     *
     * @param seconds
     */
    private void _wait(int seconds) {
        try {
            Thread.sleep(seconds);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }

    /**
     * 打印连接池中所有连接的状态
     */
    public void _print() {
        System.out.println("total connections: " + connections.size());
        for (int i = 0, size = connections.size(); i < size; i++) {
            System.out.println(i + ": " + connections.get(i).getIsBusy());
        }
    }

    /**
     * 保存连接池对象的类
     */
//    public class PooledConnection {
//
//        private Connection conn = null;
//        private boolean isBusy;
//
//        public PooledConnection(Connection conn) {
//            this.conn = conn;
//        }
//
//        public Connection getConnection() {
//            return conn;
//        }
//
//        public void setConnection(Connection conn) {
//            this.conn = conn;
//        }
//
//        public boolean isBusy() {
//            return isBusy;
//        }
//
//        public void setIsBusy(boolean isBusy) {
//            this.isBusy = isBusy;
//        }
//
//        public ResultSet executeQuery(String sql) throws SQLException {
//            return conn.createStatement().executeQuery(sql);
//        }
//
//        public int executeUpdate(String sql) throws SQLException {
//            return conn.createStatement().executeUpdate(sql);
//        }
//    }
}

public class DBManager {

    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/cloud_ap";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    private static PooledConnection pConn;
    private static ConnectionPool connPool;
    private static DBManager dbManager;

    private DBManager() {
        if (dbManager != null) {
            return;
        }
        connPool = new ConnectionPool(DRIVER, URL, USER, PASSWORD);
        try {
            connPool.createPool();
            dbManager = this;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取一个数据库连接对象
     * @return
     */
    public static PooledConnection getConnection() {
        if (dbManager == null) {
            synchronized (DBManager.class) {
                dbManager =new DBManager();
            }
        }
        pConn = connPool.getConnection();
        return pConn;
    }

    public static DBManager getDbManager(){
        if (dbManager == null) {
            synchronized (DBManager.class) {
                new DBManager();
            }
        }
        return dbManager;
    }

    /**
     * 关闭连接池
     */
    public void close() {
        connPool.closeConnctionPool();
    }
}

 public boolean getMac(String mac) {
        boolean flag = false;
        String sql = "select * from ap_info where macaddr='" + mac + "'";
        PooledConnection pConn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            pConn = DBManager.getConnection();
            stmt = pConn.getConnection().createStatement();
            rs = stmt.executeQuery(sql);
            flag = rs.next();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            sqle.printStackTrace();
        } finally {
            if (pConn != null) {
                pConn.setIsBusy(false);
                System.out.println("conn:"+pConn.getConnection());
            }
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException sqle) {
                System.out.println(sqle.getMessage());
                sqle.printStackTrace();
            }
        }
        return flag;
    }