Connection Pooling: To Close or Not to Close Connections?
<p>I'm using dbcp's <code>BasicDataSource</code> in my JSF Java application. Since the basic convention is to close the connection after using it, I do so in catch - finally in my code. However, the application grinds to a halt with the error, </p>

<pre><code>java.sql.SQLException: Connection is null.
at org.apache.tomcat.dbcp.dbcp2.DelegatingConnection.checkOpen(DelegatingConnection.java:611)
at org.apache.tomcat.dbcp.dbcp2.DelegatingConnection.createStatement(DelegatingConnection.java:258)
</code></pre>

<p>So <strong>I decide to not close</strong> my connections; my code almost runs okay, but then often stops with this error:</p>

<pre><code>Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
</code></pre>

<p>Below is my connection configuration:</p>

<pre><code>public class StageDB {
    public StageDB() {}
    public static Connection getConnection() {
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName(JDBC_DRIVER);
        ds.setUsername(USER);
        ds.setPassword(PASS);
        ds.setUrl(DB_URL);
        ds.setTimeBetweenEvictionRunsMillis(20*1000);
        ds.setMinIdle(0);
        ds.setMaxIdle(10);
        ds.setMaxOpenPreparedStatements(100);
        conn = ds.getConnection();
        return conn;
    }
}
</code></pre>

<p>I should mention I've tried playing around with these settings, and also using defaults, but with the same results. What could I be doing wrong?</p>