【JDBC】java连接池模拟测试连接oracle

来源：这里教程网时间：2026-03-03 16:37:15 作者：

### 概述
   通过参考脚本，修改了部分参数，此处初始化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();
        }
    }
}
```