jdbcpool.java

Java Database connection pool

            Driver d = (Driver) (Class.forName(info.getDriver()).newInstance());
            Properties p = new Properties();
            p.put("user", info.getUserName());
            p.put("password", info.getPassword());

            if (!d.acceptsURL(info.getURL()))
                throw new SQLException("Driver cannot accept URL: " + info.getURL());

            Connection con = d.connect(info.getURL(), p);

            // set tx isolation level
            con.setTransactionIsolation(info.getIsolationLevel());

            // set auto commit
            con.setAutoCommit(true);

            // execute initialConnectionSQL, if any
            if (info.getInitialConnectionSQL() != null) {
                try {
                    Statement s = con.createStatement();
                    s.execute(info.getInitialConnectionSQL());
                    s.close();
                } catch (SQLException sqe) {
                    throw new RuntimeException("Init SQL Suffered a SQLException: " + sqe);
                }
            }

            PoolManConnection pcon = new PoolManConnection(con, this);
            pcon.addConnectionEventListener(this);

            return pcon;

            /* OLD CODE
            Class.forName(info.getDrivername()).newInstance();
            Connection con = DriverManager.getConnection(info.getUrl(),
                                info.getUsername(),
                                info.getPassword());
            */

        } catch (ClassNotFoundException cnfex) {
            String msg = "Looks like the driver for your database was not found. Please verify that it is in your CLASSPATH and " +
                    "listed properly in the poolman.xml file.";
            log(msg, cnfex);
            throw new SQLException(msg);
        } catch (SQLException sqlex) {
            String emsg = "SQLException occurred in JDBCPool: " + sqlex.toString() + "\nparams: " + info.getDriver() + ", " + info.getURL() + ". Please check your username, password " + "and other connectivity info.";
            log(emsg, sqlex);
            throw new SQLException(emsg);
        } catch (Exception e) {
            log(e.getMessage(), e);
            throw new SQLException(e.getMessage());
        }

    }

    /** Checks to see if the current connection is valid. */
    protected boolean validate(Object o) {

        boolean valid = false;
        PoolManConnection pcon = (PoolManConnection) o;

        if (this.info.getValidationQuery() != null) {

            // execute validationSQL
            Connection con = null;
            Statement vs = null;
            ResultSet rs = null;
            try {
                // Postgres fails on raw connection...
                con = pcon.getPhysicalConnection();
                vs = con.createStatement();
                vs.execute(this.info.getValidationQuery());
                valid = true;
            } catch (Exception e) {
                debug("Failed to validate Connection using validationQuery: " + this.info.getValidationQuery(), e);
            }
            finally {
                closeResources(vs, rs);
            }
        }

        else {
            try {
                valid = !pcon.isClosed();
            } catch (SQLException e) {
            }
        }

        return valid;
    }

    /** Closes a physical database connection. */
    protected void expire(Object o) {
        try {
            PoolManConnection pcon = (PoolManConnection) o;
            pcon.removeConnectionEventListener(this);
            try {
                pcon.commit();
            } catch (SQLException se) {
            }
            pcon.close();
        } catch (Exception e) {
        }
        o = null;
    }

    /** Retrieves a PooledConnection impl and returns its Handle. */
    public Connection requestConnection()
            throws SQLException {

        try {
            PoolManConnection pcon = (PoolManConnection) super.checkOut();
            return pcon.getConnection();
        } catch (SQLException sqle) {
            log(sqle.getMessage(), sqle);
            throw new SQLException(sqle.getMessage());
        } catch (Exception e) {
            log("A non-SQL error occurred when requesting a connection:", e);
            throw new SQLException(e.getMessage());
        }
    }

    /** Returns a connection to the pool. */
    public void returnConnection(PoolManConnection pcon) {

        pcon.clean();
        super.checkIn(pcon);
    }

    public PreparedStatement requestPooledStatement(String sql) {

        PreparedStatement ps = null;

        if (preparedStatementPool.containsKey(sql)) {
            ArrayList statements = (ArrayList) preparedStatementPool.get(sql);
            synchronized (statements) {
                if (statements.size() > 0) {
                    ps = (PreparedStatement) statements.remove(0);
                }
            }
        }

        return ps;
    }

    /** Retuns a PreparedStatement to the statement pool */
    public void returnPooledStatement(PoolManPreparedStatement ps) {

        ArrayList statements = null;
        if (info.isPoolPreparedStatements()) {
            if (preparedStatementPool.containsKey(ps.getSQL())) {
                statements = (ArrayList) preparedStatementPool.get(ps.getSQL());
            }
            else {
                statements = new ArrayList();
            }

            synchronized (statements) {
                statements.add(ps.getNativePreparedStatement());
                preparedStatementPool.put(ps.getSQL(), statements);
            }
        }
        else {
            closeStatement(ps.getNativePreparedStatement());
        }
    }

    public int numStatementPools() {
        return preparedStatementPool.size();
    }

    public int numPooledStatements(String sql) {
        int num = 0;
        if (preparedStatementPool.containsKey(sql)) {
            ArrayList statements = (ArrayList) preparedStatementPool.get(sql);
            num = statements.size();
        }
        return num;
    }

    /** Overriden in order to ensure that JNDI resources are disposed of properly. */
    public void closeAllResources() {
        undeployDataSource();
        super.closeAllResources();
    }

    /** Static method that closes the Connection, Statement and ResultSets in one place. */
    public static void closeResources(Connection con, Statement statement, ResultSet resultset) {
        closeResultSet(resultset);
        closeStatement(statement);
        closeConnection(con);
    }

    /** Static method that closes the statement and result sets in one place;
     * this is here as a convenience to shorten the finally block
     * in statements.  Both arguments may be null;
     * @param statement the statement to be closed
     * @param resultSet the resultSet to be closed
     */
    public static void closeResources(Statement statement, ResultSet resultSet) {
        closeResultSet(resultSet);
        closeStatement(statement);
    }

    public static void closeConnection(Connection con) {
        try {
            if (con != null)
                con.close();
        } catch (SQLException e) {
        }
        con = null;
    }

    /** Closes the given statement.  It is here to get rid of
     * the extra try block in finally blocks that need to close statements
     * @param statement the statement to be closed. may be null
     */
    public static void closeStatement(Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException e) {
        }
        statement = null;
    }

    /** This method closes the given ResultSet.
     * @param rs the ResultSet to be closed.  May be null.
     */
    public static void closeResultSet(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
        }
        rs = null;
    }


}