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?

Answer 1

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