0

I'm using dbcp's BasicDataSource 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, 

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)

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

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

Below is my connection configuration:

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;
    }
}

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?

Samuel Waithaka
  • 61
  • 9
  • 3
    What's with the `ds.close()`? You create a datasource and then close it immediately? – Kayaman Nov 29 '16 at 11:01
  • This looks like a bug in dbcp. Yes you should call conn.close() when you no longer need it. If your backend is Oracle then have you tried Oracle's UCP? – Jean de Lavarene Nov 29 '16 at 11:32
  • @Kayaman, the code is in datasource class that is used by DAOs. It returns conn (see ds.getConnection()). – Samuel Waithaka Nov 29 '16 at 11:51
  • @user2194279 So each time you get a connection, you build a `BasicDataSource`, do `ds.getConnection()` and then close it with `ds.close();`? – Kayaman Nov 29 '16 at 11:54
  • @Jean-de-Laveren, a bug? how to fix that? Try a different version of dbcp perharps? Backend is mostly Postgres. – Samuel Waithaka Nov 29 '16 at 11:57
  • @Kayaman, yea seems I've been doing that...in trying different things maybe. Doesn't seem to make any difference.. but I supposed I shouldn't. – Samuel Waithaka Nov 29 '16 at 12:06
  • You're supposed to have a single `BasicDataSource` and not close it. The code in the question is completely wrong, so it's no wonder that you're having problems. I suggest reading the documentation if it's unclear to you how to use a pooling datasource. – Kayaman Nov 29 '16 at 12:07
  • @Kayaman, I have a single BasicDataSource, and DAO's call the getConnection method of StageDB class. (I've included more code.) I still have the same error though. What do you think is wrong with the approach? – Samuel Waithaka Nov 29 '16 at 14:00
  • You don't have a single `BasicDataSource`, you create one every time you call `getConnection()`. Everything is wrong with that approach. – Kayaman Nov 29 '16 at 14:07
  • I can't believe I was so ignorant of the Singleton - [link]http://stackoverflow.com/questions/70689/what-is-an-efficient-way-to-implement-a-singleton-pattern-in-java[/link] One vote down yes :(... but I got the help I needed. Thanks @Kayaman – Samuel Waithaka Nov 30 '16 at 08:13

1 Answers1

0

I found the solution to this after I realized that I was doing it all wrong. So here is what I was able to come up with, that works perfectly with my design approach.

//1. Create pooled connections datasource class using the Singleton.

/***************************
* This is the pooled datasource class
****************************/
public class PrimaryDS {
    private PrimaryDS primaryDS;

    public PrimaryDS() {
        ds = new BasicDataSource();
        ds.setDriverClassName(JDBC_DRIVER);
        ds.setUsername(USER);
        ds.setPassword(PASS);
        ds.setUrl(DB_URL);
    }

    public static PrimaryDS getInstance() {
        primaryDS = primaryDS == null ? new PrimaryDS() : primaryDS;
        return primaryDS;
    }

    public BasicDataSource getDataSource() {
        return ds;
    }

    public void setDataSource(BasicDataSource ds) {
        this.ds = ds;
    }
}

//2. DAOs make call to the datasource. Initialize DS in the DAO's constructor.

/***************************
* This is in the DAO's constructor
****************************/
ds = PrimaryDS.getInstance().getDataSource();

//3. DAOs have the database manupulation methods. In each of these methods, 
//create connection object from ds, and ensure it's closed in the catch - finally.

/***************************
* This is inside one of the DAO methods
****************************/
try {
    conn = ds.getConnection();
    stmt = null;
    rs = null;
    PreparedStatement pstmt = conn.prepareStatement(ACTIVE_ACCOUNTS_SQL);
    pstmt.setByte(1,status);
    ResultSet rs = pstmt.executeQuery();
    // TODO loop through rs
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    try {
        if(rs != null) rs.close();
        if(stmt != null) stmt.close();
        if(conn != null) conn.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}
Samuel Waithaka
  • 61
  • 9