//
you're reading...
Java Programming

JAVA connect to database through connection Pool – a better approach

JAVA connect to database through connection Pool – a better approach

package connectionPoolTest;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Vector;

public class ConnectionPool {

    private String jdbcDriver = ""; // Database driver
    private String dbUrl = ""; // Database URL
    private String dbUsername = ""; // Database username
    private String dbPassword = ""; // Database password
    private String testTable = ""; // test table for database connection
    private int initialConnections = 10; // the connection pool number by default
    private int incrementalConnections = 5; // Automatically increase the number of the connection pool
    private int maxConnections = 50; // The maximum size of the connection pool
    private Vector connections = null; // the vector of the database connection store inside the connection pool, by default is "null"
// It is stored in the object "PooledConnection"

   
    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 {
        // Ensure that the connection pool does not create
        // If the connection pool has been created, save the connection vector. connections is not null
        if (connections != null) {
            return; // If the connection pool has been created, return
        }
        // Create the object of the JDBC Driver class instance specified drive
        Driver driver = (Driver) (Class.forName(this.jdbcDriver).newInstance());
        DriverManager.registerDriver(driver); // Register JDBC Driver

        // save the connection vector , by default it will have 0 number of element
        connections = new Vector();

        // Based on the value set in the initial Connections, create a connection.
        createConnections(this.initialConnections);
        System.out.println(" Database connection created successfully! ");
    }

   
    private void createConnections(int numConnections) throws SQLException {
        // Loop to create the specified number of database connections
        for (int x = 0; x < numConnections; x++) {
            // Whether the number of database connections in the connection pool has reached maximum
            // The maximum value of the Class Members maxConnections
            // Pointed out that if maxConnections is zero or negative, no limit the number of connections.
            // If the maximum number of connections has been reached, exit.
            if (this.maxConnections > 0 &&
                this.connections.size() >= this.maxConnections) {
                break;
            }

            // add a new PooledConnection object to connections vector
            // Add a connection to the pool (in vector connections)
            try {
                connections.addElement(new PooledConnection(newConnection()));
            } catch (SQLException e) {
                System.out.println(" Create database connection failed! " + e.getMessage());
                throw new SQLException();
            }
            System.out.println(" Database connection created ......");
        }
    }
   
    private Connection newConnection() throws SQLException {
        // Create a database connection
        Connection conn = DriverManager.getConnection(dbUrl, dbUsername,
                dbPassword);
        // If this is the first time create a database connection 
        // that checks the database to obtain the database promised support
        // The maximum number of client connections
        // connections.size () == 0 indicates no current connection has been created
        if (connections.size() == 0) {
            DatabaseMetaData metaData = conn.getMetaData();
            int driverMaxConnections = metaData.getMaxConnections();

            // Returned from the database driver MaxConnections; 
            // if it is 0, indicating no maximum for this database
            // The maximum connection limit connection limit, or the database does not know
            // If driver MaxConnections returns an integer, 
            // representing the number of customers connected to this database promise

            // If the maximum number of connections number of connections is greater than the database connection pool settings promised, 
            //set the maximum connection pool
            // The maximum number of number of connections to the database promised
            if (driverMaxConnections > 0 &&
                this.maxConnections > driverMaxConnections) {
                this.maxConnections = driverMaxConnections;
            }
        }
        return conn; // Returns to the new created database connection
    }

   

    public synchronized Connection getConnection() throws SQLException {
        // Making sure the connection pool has been created
        if (connections == null) {
            return null; // If the connection hasn't been created, reture null
        }
        Connection conn = getFreeConnection(); // Get a usable database connection
        // There is no connection pool can be used, that means all connections are in use
        while (conn == null) {
            // wait for a while
            wait(250);
            conn = getFreeConnection(); // try it again,until get an usable connection,if
            // getFreeConnection() return null
            // It indicates that the connection is not available After creating a number of connections available
        }
        return conn; // Returns obtained available connections
    }

   
    private Connection getFreeConnection() throws SQLException {
        // Get a usable database connection from the connection pool
        Connection conn = findFreeConnection();
        if (conn == null) {
            // If the connection pool is currently not available for connection
            // Create some connections
            createConnections(incrementalConnections);
            // Search for available connections from the connection pool again
            conn = findFreeConnection();
            if (conn == null) {
                // If still not available after creating a connection get a connection, return null
                return null;
            }
        }
        return conn;
    }

   
    private Connection findFreeConnection() throws SQLException {
        Connection conn = null;
        PooledConnection pConn = null;

        // Connection Pooling get all vector objects
        Enumeration enumerate = connections.elements();

        // Through all of the objects to see if there is an available connection
        while (enumerate.hasMoreElements()) {
            pConn = (PooledConnection) enumerate.nextElement();
            if (!pConn.isBusy()) {
                // If this object is not busy, 
                // then get it and bring it to the database connection is busy
                conn = pConn.getConnection();
                pConn.setBusy(true);

                // This connection test is available
                if (!testConnection(conn)) {
                    // If this connection is not used again, create a new connection,
                    // And replace this connection object is not available, 
                    // if the creation fails, return null
                    try {
                        conn = newConnection();
                    } catch (SQLException e) {
                        System.out.println(" Create database connection failed " + e.getMessage());
                        return null;
                    }
                    pConn.setConnection(conn);
                }
                break; // Found an available connection, exit
            }
        }
        return conn; // Returned to find available connections
    }

   
    private boolean testConnection(Connection conn) {
        try {
            // Testing to determine whether there is a test table
            if (testTable.equals("")) {
                // If the test table is empty, try using setAutoCommit this connection () method
                // To determine whether the connection is available 
                // (this method is available only in the part of the database, if not available, an exception is thrown). 
                //Note: Use the test table method is more reliable
                conn.setAutoCommit(true);
            } else { // test passed when tested using the test table
                //check if this connection is valid
                Statement stmt = conn.createStatement();
                stmt.execute("select count(*) from " + testTable);
            }
        } catch (SQLException e) {
            // The above exception is thrown, 
            //the connection is not available, 
            // closing it, and return false;
            closeConnection(conn);
            return false;
        }
        // Connection is available, returns true
        return true;
    }

   
    public void returnConnection(Connection conn) {
        // Make sure the connection pool exists,
        // If the connection is not created (does not exist), a direct return
        if (connections == null) {
            System.out.println(" Connection pool does not exist, can not return this connection to the connection pool !");
            return;
        }
        PooledConnection pConn = null;
        Enumeration enumerate = connections.elements();
        // Go through all the connections in the connection pool, find the connection object to be returned
        while (enumerate.hasMoreElements()) {
            pConn = (PooledConnection) enumerate.nextElement();
            // First find the connection object connection pool to be returned
            if (conn == pConn.getConnection()) {
                // found, This connection is set to an idle state
                pConn.setBusy(false);
                break;
            }
        }
    }

   

    public synchronized void refreshConnections() throws SQLException {
        // Ensure that the connection pool already exists
        if (connections == null) {
            System.out.println(" Connection pool does not exist, can not be refreshed !");
            return;
        }
        PooledConnection pConn = null;
        Enumeration enumerate = connections.elements();
        while (enumerate.hasMoreElements()) {
            // Get a connection object
            pConn = (PooledConnection) enumerate.nextElement();
            // If the object is busy, wait for 5 sec
            // After 5 sec, refresh
            if (pConn.isBusy()) {
                wait(5000); // wait for 5 sec
            }
            // Close this connection, a new connection instead of using it.
            closeConnection(pConn.getConnection());
            pConn.setConnection(newConnection());
            pConn.setBusy(false);
        }
    }

   
    public synchronized void closeConnectionPool() throws SQLException {
        // Ensure that the connection pool is, if you do not exist, the return
        if (connections == null) {
            System.out.println(" Connection pool does not exist, can not be closed !");
            return;
        }
        PooledConnection pConn = null;
        Enumeration enumerate = connections.elements();
        while (enumerate.hasMoreElements()) {
            pConn = (PooledConnection) enumerate.nextElement();
            // if it is busy, wait for 5 sec
            if (pConn.isBusy()) {
                wait(5000); // wait for 5 sec
            }
            // close it after 5 sec
            closeConnection(pConn.getConnection());

            // Remove it from the connection pool vector
            connections.removeElement(pConn);
        }
        // connection is null
        connections = null;
    }

   
    private void closeConnection(Connection conn) {
        try {
            conn.close();
        } catch (SQLException e) {
            System.out.println(" Close Database Connection Error: " + e.getMessage());
        }
    }

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

   

    class PooledConnection {
        Connection connection = null; // database connection
        boolean busy = false; // Flag is being used for this connection, not using by default
        // Constructor
        public PooledConnection(Connection connection) {
            this.connection = connection;
        }

        // Returns the connection object
        public Connection getConnection() {
            return connection;
        }

        // Setting this object, the connection
        public void setConnection(Connection connection) {
            this.connection = connection;
        }

        // Get the object connection is busy
        public boolean isBusy() {
            return busy;
        }

        // Settings object connection is busy
        public void setBusy(boolean busy) {
            this.busy = busy;
        }
    }


    public static void main(String[] args) {
       
        ConnectionPool connPool
                = new ConnectionPool("oracle.jdbc.driver.OracleDriver",
                                     "jdbc:oracle:thin:@*.*.*.*"
                                     , "name", "password");

        try {
            connPool.createPool();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
            Connection conn = connPool.getConnection();
        } catch (SQLException ex1) {
            ex1.printStackTrace();
        }

    }

}
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: