Glassfish does not reuse JDBC Connections

Question

I'm using JDBC connection pooling on a glassfish webserver. I'm injecting the DataSource via this statement:

@Resource(lookup="database")
DataSource db;

The code which I'm using to load data looks something like this:

public ArrayList<Stuff> loadStuff()throws SQLException{
    PreparedStatement ps = db.getConnection().prepareStatement("Select * from stufftable");
    ResultSet rs = ps.executeQuery();
    ArrayList<Stuff> stuffs= new ArrayList<Stuff>();
    if(rs.next()){
        Stuff stuff = new Stuff();
        stuff.setString1(rs.getString("string1"));
        stuff.setString1(rs.getString("string1"));
        stuffs.add(stuff );
    }
    return stuffs;
}

For some reason glassfish is not reusing database connections, so I'm running out of them very fast. Sooner or later i'm always getting this error: Error in allocating a connection. Cause: In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections.

As I understood the concept of pooling on glassfish: I'm not supposed to close connections after I used them, so something else can reuse the connection when needed. Glassfish closes connection itself when there is no more demand for the connection.

Why does my program open a new connection every time? Do I have to do something with the connection when I'm done?

score 2 · Accepted Answer · edited May 23 '17 at 12:29

2

You still need to call Connection.close(). Your pool will manage your connections, so they won't really be closed, but if you don't "close" them in your code, they won't be returned to the pool.

Edit: alternatively, use a try-with-resources: https://stackoverflow.com/a/8066594/212224

edited May 23 '17 at 12:29

Community

1
1

answered Jul 22 '15 at 09:48

Mike

4,852
1
29
48

Does try-with-resources close the connection automatically when its done? Does it close the resource also when an exception is thrown and it has to abort? – Kendoha Jul 22 '15 at 10:45
Additional question: I've tried to read up on it and apparently there is a thing called suppressed Exceptions which I dont really get. Are Exceptions still thrown in that try-with-resources? – Kendoha Jul 22 '15 at 11:00

score 1 · Answer 2 · answered Jul 22 '15 at 09:52

1

Connection con = db.getConnection();
try {
    PreparedStatement ps = con.prepareStatement("Select * from stufftable");
    ...
} finally {
    // this returns the connection to the pool
    con.close();
}

answered Jul 22 '15 at 09:52

lance-java

25,497
4
59
101

Glassfish does not reuse JDBC Connections

2 Answers2