### 概述

透過參考指令碼，修改了部分引數，此處初始化10個連線，發起該java時，會與資料庫建立10個連線，如中介軟體連線池，與資料庫建立10個連線後，其他業務訪問連線池，中介軟體的併發數並不代表資料庫中process併發數。 如本例，與資料庫建立了10個連線，中介軟體可對外提供100個使用者連線（此處的併發數才是業務訪問併發量）。
   
#### 建立連線
```
# 5秒輸出一個，最後統計總共建立多少會話，與process，語句參考
            PreparedStatement preparedStatement=conn.prepareStatement("select (select count(*) from v$session where username='MYTEST') as session_count, (select count(*) as cnt from v$session s,v$process p where s.paddr=p.addr and s.username='MYTEST') as sp_count from dual");
            ResultSet result=preparedStatement.executeQuery();
            while (result.next()) {
                System.out.println("session count :" + result.getString(1));
                System.out.println("session_process count :" + result.getString(2));

#輸出示例
[oracle@myasm test]$ javac  ConnectionPool.java 
[oracle@myasm test]$ java ConnectionPool
2021-04-08 11:09:42 第 0個資料庫連線己建立 ......
2021-04-08 11:09:45 第 1個資料庫連線己建立 ......
2021-04-08 11:09:47 第 2個資料庫連線己建立 ......
2021-04-08 11:09:49 第 3個資料庫連線己建立 ......
2021-04-08 11:09:51 第 4個資料庫連線己建立 ......
2021-04-08 11:09:53 第 5個資料庫連線己建立 ......
2021-04-08 11:09:55 第 6個資料庫連線己建立 ......
2021-04-08 11:09:57 第 7個資料庫連線己建立 ......
2021-04-08 11:09:59 第 8個資料庫連線己建立 ......
2021-04-08 11:10:02 第 9個資料庫連線己建立 ......
 資料庫連線池建立成功 ！ 
session count :10
session_process count :10
```
#### 監聽日誌輸出
```
Thu Apr 08 11:09:42 2021
08-APR-2021 11:09:42 * (CONNECT_DATA=(SID=mydb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.116)(PORT=11266)) * establish * mydb * 0
08-APR-2021 11:09:45 * (CONNECT_DATA=(SID=mydb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.116)(PORT=11268)) * establish * mydb * 0
08-APR-2021 11:09:47 * (CONNECT_DATA=(SID=mydb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.116)(PORT=11269)) * establish * mydb * 0
08-APR-2021 11:09:49 * (CONNECT_DATA=(SID=mydb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.116)(PORT=11271)) * establish * mydb * 0
08-APR-2021 11:09:51 * (CONNECT_DATA=(SID=mydb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.116)(PORT=11272)) * establish * mydb * 0
WARNING: Subscription for node down event still pending
08-APR-2021 11:09:51 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=myasm)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647552)) * status * 0
Thu Apr 08 11:09:53 2021
08-APR-2021 11:09:53 * (CONNECT_DATA=(SID=mydb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.116)(PORT=11273)) * establish * mydb * 0
08-APR-2021 11:09:54 * service_update * mydb * 0
08-APR-2021 11:09:55 * (CONNECT_DATA=(SID=mydb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.116)(PORT=11275)) * establish * mydb * 0
08-APR-2021 11:09:57 * service_update * mydb * 0
08-APR-2021 11:09:57 * (CONNECT_DATA=(SID=mydb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.116)(PORT=11276)) * establish * mydb * 0
08-APR-2021 11:10:00 * (CONNECT_DATA=(SID=mydb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.116)(PORT=11278)) * establish * mydb * 0
08-APR-2021 11:10:00 * service_update * mydb * 0
08-APR-2021 11:10:02 * (CONNECT_DATA=(SID=mydb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.80.116)(PORT=11279)) * establish * mydb * 0
Thu Apr 08 11:10:06 2021
08-APR-2021 11:10:06 * service_update * mydb * 0
Thu Apr 08 11:10:18 2021
08-APR-2021 11:10:18 * service_update * mydb * 0
```

#### 詳細指令碼參考
```
/*
 *https://www.cnblogs.com/tobey/articles/5810682.html
 *
 */
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Vector;
import java.text.SimpleDateFormat;
import java.util.Date;
@SuppressWarnings("rawtypes")

public class ConnectionPool {

private String jdbcDriver = "oracle.jdbc.driver.OracleDriver"; 
    private String dbUrl = ""; 
    private String dbUsername = "";
    private String dbPassword = "";
    private String testTable = "";
    private int initialConnections = 10;
    private int incrementalConnections = 5;
    private int maxConnections = 50;

private Vector connections = null;

public ConnectionPool(String jdbcDriver, String dbUrl, String dbUsername,
                          String dbPassword) {

this.jdbcDriver = jdbcDriver;
        this.dbUrl = dbUrl;
        this.dbUsername = dbUsername;
        this.dbPassword = dbPassword;
    }

public int getInitialConnections() {
        return this.initialConnections;
    }

public void setInitialConnections(int initialConnections) {
        this.initialConnections = initialConnections;
    }

public int getIncrementalConnections() {
        return this.incrementalConnections;
    }

public void setIncrementalConnections(int incrementalConnections) {
        this.incrementalConnections = incrementalConnections;
    }

public int getMaxConnections() {
        return this.maxConnections;
    }

public void setMaxConnections(int maxConnections) {
        this.maxConnections = maxConnections;
    }

public String getTestTable() {
        return this.testTable;
    }

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

public synchronized void createPool() throws Exception {

if (connections != null) {
            return; 
        }

Driver driver = (Driver) (Class.forName(this.jdbcDriver).newInstance());
        DriverManager.registerDriver(driver);

connections = new Vector();

createConnections(this.initialConnections);
        System.out.println(" 資料庫連線池建立成功 ！ ");
    }

@SuppressWarnings("unchecked")
    private void createConnections(int numConnections) throws SQLException {

for (int x = 0; x < numConnections; x++) {
        
        Date date = new Date();
        String strDateFormat = "yyyy-MM-dd HH:mm:ss";
        SimpleDateFormat sdf = new SimpleDateFormat(strDateFormat); 
           
            if (this.maxConnections > 0 &&
                this.connections.size() >= this.maxConnections) {
                break;
            }
         
            try {
                connections.addElement(new PooledConnection(newConnection()));
            } catch (SQLException e) {
                System.out.println(" 建立資料庫連線失敗！ " + e.getMessage());
                throw new SQLException();
            }
            System.out.println(sdf.format(date) + " 第 " + x + "個資料庫連線己建立 ......");
			wait(2000);
        }
    }

private Connection newConnection() throws SQLException {
     
        Connection conn = DriverManager.getConnection(dbUrl, dbUsername,
                dbPassword);

if (connections.size() == 0) {
            DatabaseMetaData metaData = conn.getMetaData();
            int driverMaxConnections = metaData.getMaxConnections();

if (driverMaxConnections > 0 &&
                this.maxConnections > driverMaxConnections) {
                this.maxConnections = driverMaxConnections;
            }
        }
        return conn; 
    }

public synchronized Connection getConnection() throws SQLException {
      
        if (connections == null) {
            return null;
        }
        Connection conn = getFreeConnection(); 
        while (conn == null) {
         
            wait(250);
            conn = getFreeConnection(); 
        }
        return conn; 
    }

private Connection getFreeConnection() throws SQLException {
   
        Connection conn = findFreeConnection();
        if (conn == null) {

createConnections(incrementalConnections);

conn = findFreeConnection();
            if (conn == null) {
            
                return null;
            }
        }
        return conn;
    }

@SuppressWarnings("resource")
    private Connection findFreeConnection() throws SQLException {
        Connection conn = null;
        PooledConnection pConn = null;

Enumeration enumerate = connections.elements();
    
        while (enumerate.hasMoreElements()) {
            pConn = (PooledConnection) enumerate.nextElement();
            if (!pConn.isBusy()) {
            
                conn = pConn.getConnection();
                pConn.setBusy(true);

if (!testConnection(conn)) {
               
                    try {
                        conn = newConnection();
                    } catch (SQLException e) {
                        System.out.println(" 建立資料庫連線失敗！ " + e.getMessage());
                        return null;
                    }
                    pConn.setConnection(conn);
                }
                break;
            }
        }
        return conn;
    }

private boolean testConnection(Connection conn) {
        try {
            
            if (testTable.equals("")) {

conn.setAutoCommit(true);
            } else {
                Statement stmt = conn.createStatement();
                stmt.execute("select count(*) from " + testTable);
            }
        } catch (SQLException e) {
      //      closeConnection(conn);
            return false;
        }
        return true;
    }

public void returnConnection(Connection conn) {
    
        if (connections == null) {
            System.out.println(" 連線池不存在，無法返回此連線到連線池中 !");
            return;
        }
        PooledConnection pConn = null;
        Enumeration enumerate = connections.elements();

while (enumerate.hasMoreElements()) {
            pConn = (PooledConnection) enumerate.nextElement();

if (conn == pConn.getConnection()) {

pConn.setBusy(false);
                break;
            }
        }
    }

public synchronized void refreshConnections() throws SQLException {

if (connections == null) {
            System.out.println(" 連線池不存在，無法重新整理 !");
            return;
        }
        PooledConnection pConn = null;
        Enumeration enumerate = connections.elements();
        while (enumerate.hasMoreElements()) {
    
            pConn = (PooledConnection) enumerate.nextElement();
  
            if (pConn.isBusy()) {
                wait(5000); 
            }

//           closeConnection(pConn.getConnection());
            pConn.setConnection(newConnection());
            pConn.setBusy(false);
        }
    }

public synchronized void closeConnectionPool() throws SQLException {
  
        if (connections == null) {
            System.out.println(" 連線池不存在，無法關閉 !");
            return;
        }
        PooledConnection pConn = null;
        Enumeration enumerate = connections.elements();
        while (enumerate.hasMoreElements()) {
            pConn = (PooledConnection) enumerate.nextElement();
            if (pConn.isBusy()) {
                wait(5000); 
            }
  //          closeConnection(pConn.getConnection());
            connections.removeElement(pConn);
        }
        connections = null;
    }

private void closeConnection(Connection conn) {
        try {
            conn.close();
        } catch (SQLException e) {
            System.out.println(" 關閉資料庫連線出錯： " + e.getMessage());
        }
    }

private void wait(int mSeconds) {
        try {
            Thread.sleep(mSeconds);
        } catch (InterruptedException e) {
        }
    }

class PooledConnection {
        Connection connection = null; 
        boolean busy = false;
        public PooledConnection(Connection connection) {
            this.connection = connection;
        }

public Connection getConnection() {
            return connection;
        }

public void setConnection(Connection connection) {
            this.connection = connection;
        }

public boolean isBusy() {
            return busy;
        }

public void setBusy(boolean busy) {
            this.busy = busy;
        }
    }

public static void main(String[] args) {

ConnectionPool connPool= new ConnectionPool("oracle.jdbc.OracleDriver",
                                     "jdbc:oracle:thin:@192.168.80.116:1521:mydb"
                                     , "mytest", "oracle");
        try {
            connPool.createPool();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
            Connection conn = connPool.getConnection();
            PreparedStatement preparedStatement=conn.prepareStatement("select (select count(*) from v$session where username='MYTEST') as session_count, (select count(*) as cnt from v$session s,v$process p where s.paddr=p.addr and s.username='MYTEST') as sp_count from dual");
            ResultSet result=preparedStatement.executeQuery();
            while (result.next()) {
                System.out.println("session count :" + result.getString(1));
                System.out.println("session_process count :" + result.getString(2));
            }
//            System.out.println(conn);
        } catch (SQLException ex1) {
            ex1.printStackTrace();
        }

}

}
```

【JDBC】java連線池模擬測試連線oracle

相關文章

【JDBC】java連線池模擬測試 連線oracle

相關文章

【JDBC】java連線池模擬測試連線oracle