Injecting datasource in EJB

Question

When you inject a datasource in your application and get a connection by invoking getConnection() on it, are you supposed to close the connection?

Answer 1

Even though the datasource itself is container managed, the API indeed requires the programmer to close connections. This is different from a couple of other container managed resources (like the entity manager), where the container takes care of closing. Note that closing here in the majority of cases doesn't actually closes the connection here, but returns the connection to a connection pool.

As a rule of thumb, if you use a factory-ish resources to obtain one or more other resources from that can be closed, you have to close them. Otherwise the container does this.

Since Connection implements AutoCloseable, you can use a try-with-resources block for this:

@Stateless
public class MyBean {

    @Resource(lookup = "java:/app/datasource")
    private DataSource dataSource;

    public void doStuff() {
        try (Connection connection = dataSource.getConnection()) {

            // Work with connection here

        } catch (SQLException e) {
            throw new SomeRuntimeException(e);
        }
    }
}

Answer 2

Of course, otherwise you'll exhaust your connection pool. It's best to do this in finally block:

@Resource(mappedName="jndi/yourDatasource")
DataSource ds;

..

Connection conn = null;
try {
     conn = ds.getConnection();
     //PERFORM QUERY, ETC..
}
catch(SQLException ex) {
     //EXCEPTION HANDLING
}
finally {
    try {
        if(conn != null)
            conn.close();
    }
    catch(SQLException ex) {..}
}