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

  • xysoul_云龙

    2021-04-08 11:13:45
  • Oracle
  • 原创

概述

通过参考脚本，修改了部分参数，此处初始化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();
        }
    }
}
请使用浏览器的分享功能分享到微信等