oracle connection pool

[Ryan Tracy]

I am using the Oracle jdbc driver and I am having some issues where connections are not being released. I am making sure that the close is being called in a finally clause. I am calling the datasource.getConnection() in a static class. Does anyone know what could be causing the the connections to max out in oracle?

[Jeanne Boyarsky]

Is it possible those are idle connections? Oracle doesn't "empty" the pool unless you set a preferred pool size parameter. The idea is that once a connection is sued, it stays in the pool available to others.

[Ryan Tracy]

I thought that at first, but then the application starts not being able to connect to oracle once the oracle sessions get too high. We keep raising the connection max parameter, but they just fill up and once they fill up I get unable to connect exceptions. I have the max parameter set to default initially. I incremented it 50 each time a changed it. I am at 350 currently.

[Martin Vashko]

It really sounds like a connection leak to me.

I am making sure that the close is being called in a finally clause.

I'd check this one again. Make sure that the finally clause is always completed - that is, that any exception raised in the finally clause itself is properly handled, otherwise the finally clause might not run completely.

[Ryan Tracy]

Here is my static connection class

public class SQLUtil {
    private static Logger logger = Logger.getLogger("com.xxx.xxx.util.SQLUtil");
        
    private SQLUtil() {
        
    }
    
    public static Connection getConnection() throws SQLException {
        return getConnection("jdbc/OracleDS");
    }
    
    public static Connection getConnection(String jndiName) throws SQLException {
        try {
            InitialContext ic = new InitialContext();
            DataSource ds = (DataSource)ic.lookup(jndiName);
            return ds.getConnection();
        } catch (NamingException e) {
            logger.log(Level.SEVERE, "Unable to find datasource with name [" + jndiName + "]");
        }
        throw new SQLException("Unable to get connection.");
    }
    
    public static void closeDBObjects(Connection conn, PreparedStatement ps, ResultSet rs) {
        closeDBObject(rs);
        closeDBObject(ps);
        closeDBObject(conn);
    }
    
    public static void closeDBObject(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                logger.info("Problem closing ResultSet. cause: " + ex.getMessage());
            }
            rs = null;
        }
    }
    
    public static void closeDBObject(PreparedStatement ps) {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException ex) {
                logger.info("Problem closing PreparedStatement. cause: " + ex.getMessage());
            }
            ps = null;
        }
    }
    
    public static void closeDBObject(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                logger.info("Problem closing Connection. cause: " + ex.getMessage());
            }
            conn = null;
        } 
    }
}

Here is the use:

Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "";
        try {
            conn = SQLUtil.getConnection();

            sql = "SELECT myfield FROM myTable";

            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                System.out.println(rs.getString("myfield"));
            }
        } catch (SQLException ce) {
            ce.printStackTrace();
            throw new EJBException(ce);
        } finally {
            SQLUtil.closeDBObjects(conn, pstmt, rs);
        }

Do you see any problems with the way I have written this code that could cause a connection leak?

Thanks

[Ryan Tracy]

Anyone have some suggestions on how I might find the connection leak?

Thanks