JDBC連接池原理、自定義連接池代碼實現

首先自己實現一個簡單的連接池：數據準備： CREATE DATABASE mybase; USE mybase; CREATE TABLE users( uid INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(64), upassword VARC ...

首先自己實現一個簡單的連接池：

數據準備：

CREATE DATABASE mybase;
USE mybase;
CREATE TABLE users(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(64),
upassword VARCHAR(64)
);
INSERT INTO users (username,upassword) VALUES("zhangsan","123"),("lisi","456"),("wangwu","789");
SELECT * FROM users;

View Code

提取JDBC工具類：

package demo;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class JDBCUtils3 {
    public static String driver;
    public static String url;
    public static String username;
    public static String password;

    static {
        try {
            ClassLoader classLoader = JDBCUtils3.class.getClassLoader();
            InputStream is = classLoader.getResourceAsStream("db.properties");
            Properties props = new Properties();
            props.load(is);
            driver = props.getProperty("driver");
            url = props.getProperty("url");
            username = props.getProperty("username");
            password = props.getProperty("password");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return conn;
    }

    public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }
}

View Code

配置文件db.properties：

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybase
username=root
password=xuyiqing

View Code

自定義連接池：

package demo1;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger;

import javax.sql.DataSource;

import demo.JDBCUtils3;

public class MyDataSource implements DataSource{
    //1.創建1個容器用於存儲Connection對象
    private static LinkedList<Connection> pool = new LinkedList<Connection>();
    
    //2.創建5個連接放到容器中去
    static{
        for (int i = 0; i < 5; i++) {
            Connection conn = JDBCUtils3.getConnection();
            pool.add(conn);
        }
    }
    
    /**
     * 重寫獲取連接的方法
     */
    @Override
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        //3.使用前先判斷
        if(pool.size()==0){
            //4.池子裡面沒有，我們再創建一些
            for (int i = 0; i < 5; i++) {
                conn = JDBCUtils3.getConnection();
                pool.add(conn);
            }
        }
        //5.從池子裡面獲取一個連接對象Connection
        conn = pool.remove(0);
        return conn;
    }

    /**
     * 歸還連接對象到連接池中去
     */
    public void backConnection(Connection conn){
        pool.add(conn);
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {
        // TODO Auto-generated method stub
        
    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {
        // TODO Auto-generated method stub
        
    }

    @Override
    public int getLoginTimeout() throws SQLException {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

}

View Code

測試：

package demo1;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.junit.Test;

public class TestMyDataSource {
    @Test
    public void testAddUser() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        // 1.創建自定義連接池對象
        MyDataSource dataSource = new MyDataSource();
        try {
            // 2.從池子中獲取連接
            conn = dataSource.getConnection();
            String sql = "insert into users values(null,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "呂布");
            pstmt.setString(2, "貂蟬");
            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("添加成功!");
            } else {
                System.out.println("添加失敗!");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            dataSource.backConnection(conn);
        }
    }
}

View Code

測試：輸出：添加成功!

接下來要做到放回連接的時候，調用了close方法卻不是關閉資源，而是放回到池中

這時候需要一個自定義類實現Connection介面

package demo1;

import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.LinkedList;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

//1.實現同一個介面Connection
public class MyConnection implements Connection {
    //3.定義一個變數
    private Connection conn;
    
    private LinkedList<Connection> pool;
    
    // 2.編寫一個構造方法(參數使用了面相對象的多態特性)
    public MyConnection(Connection conn,LinkedList<Connection> pool) {
        this.conn=conn;
        this.pool=pool;
    }
    
    //4.書寫需要增強的方法
    @Override
    public void close() throws SQLException {
        pool.add(conn);
    }

    /**
     * 此方法必須覆蓋！否則會出現空指針異常！！！
     */
    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }
    
    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public Statement createStatement() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public CallableStatement prepareCall(String sql) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public String nativeSQL(String sql) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void setAutoCommit(boolean autoCommit) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public boolean getAutoCommit() throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public void commit() throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public void rollback() throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public boolean isClosed() throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public DatabaseMetaData getMetaData() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void setReadOnly(boolean readOnly) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public boolean isReadOnly() throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public void setCatalog(String catalog) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public String getCatalog() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void setTransactionIsolation(int level) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public int getTransactionIsolation() throws SQLException {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public SQLWarning getWarnings() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void clearWarnings() throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
            throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Map<String, Class<?>> getTypeMap() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public void setHoldability(int holdability) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public int getHoldability() throws SQLException {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public Savepoint setSavepoint() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Savepoint setSavepoint(String name) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void rollback(Savepoint savepoint) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public void releaseSavepoint(Savepoint savepoint) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
            throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency,
            int resultSetHoldability) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency,
            int resultSetHoldability) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Clob createClob() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Blob createBlob() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public NClob createNClob() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public SQLXML createSQLXML() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public boolean isValid(int timeout) throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public void setClientInfo(String name, String value) throws SQLClientInfoException {
        // TODO Auto-generated method stub

    }

    @Override
    public void setClientInfo(Properties properties) throws SQLClientInfoException {
        // TODO Auto-generated method stub

    }

    @Override
    public String getClientInfo(String name) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Properties getClientInfo() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void setSchema(String schema) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public String getSchema() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void abort(Executor executor) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public int getNetworkTimeout() throws SQLException {
        // TODO Auto-generated method stub
        return 0;
    }

}

View Code

修改自定義連接池類：

package demo1;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger;

import javax.sql.DataSource;

import demo.JDBCUtils3;

public class MyDataSource implements DataSource {
    // 1.創建1個容器用於存儲Connection對象
    private static LinkedList<Connection> pool = new LinkedList<Connection>();

    // 2.創建5個連接放到容器中去
    static {
        for (int i = 0; i < 5; i++) {
            Connection conn = JDBCUtils3.getConnection();
            // 放入池子中connection對象已經經過改造了
            MyConnection myconn = new MyConnection(conn, pool);
            pool.add(myconn);
        }
    }

    /**
     * 重寫獲取連接的方法
     */
    @Override
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        // 3.使用前先判斷
        if (pool.size() == 0) {
            // 4.池子裡面沒有，我們再創建一些
            for (int i = 0; i < 5; i++) {
                conn = JDBCUtils3.getConnection();
                // 放入池子中connection對象已經經過改造了
                MyConnection myconn = new MyConnection(conn, pool);
                pool.add(myconn);
            }
        }
        // 5.從池子裡面獲取一個連接對象Connection
        conn = pool.remove(0);
        return conn;
    }

    /**
     * 歸還連接對象到連接池中去
     */
    public void backConnection(Connection conn) {
        pool.add(conn);
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public int getLoginTimeout() throws SQLException {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

}

View Code

測試：

package demo1;

import java.sql.Connection;
import java.sql.PreparedStatement;

import javax.sql.DataSource;

import org.junit.Test;

import demo.JDBCUtils3;

public class TestMyDataSource {
    @Test
    public void testAddUser1() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        // 1.創建自定義連接池對象
        DataSource dataSource = new MyDataSource();
        try {
            // 2.從池子中獲取連接
            conn = dataSource.getConnection();
            String sql = "insert into users values(null,?,?)";
            //3.必須在自定義的connection類中重寫prepareStatement(sql)方法
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "呂布1");
            pstmt.setString(2, "貂蟬1");
            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("添加成功!");
            } else {
                System.out.println("添加失敗!");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils3.release(conn, pstmt, null);
        }
    }
}

View Code

添加成功！