So, Do you close a JDBC connection in EJB or not?

Discussions

EJB design: So, Do you close a JDBC connection in EJB or not?

  1. Odd question that occured to me. All over my stateless session beans, I have stacks of JDBC calls. Now Im currious, is it per spec to call connection.close() on all of those connection or am I supposed to let the server handle this. It has been working but I am interested in yankign the rougher parts back in line with the spec. Comments ?

    -- TIA
    -- Rob
  2. s
  3. i think you have to do it yourself but i've never read the specs. I've read some books though. Either one connection is created when the bean instantiates and when it is activated, (the connection is a private class variable, in both methods you assign connection = datasource.getConnection()) and closed and set to null in passivate and remove (connection.close(); connection=null;).
    The alternative is: each method gets it's connection, and closes it before the method returns. This is better because there is never an unused connection. It requires more coding (unless you use a trick: look at the patterns: method wrapper).
    The thing is, a database connectoin is a resource, you shouldn't spoil it, or hold it without using it. If you don't close your connection, the server will eventually do it when it's collection garbage.
  4. You should call close() on a connection when you're done with it. This isn't something you can find in the spec - you don't *have* to close the connection. If you won't close it, it will just be wasted - it doesn't break the spec, but its pretty silly :)
    The container may finally close the connection itself, when garbage collecting, but I wouldn't count on it. App servers pool the connection, meaning that they probably hold on to some reference to it. They could reference it using weak references to solve this problem, but that would hurt the pooling - the App server will find out about unreferenced connections after they have been marked for garbage collection, meaning it won't be able to reuse them. There may be some ways to work around this problem, but I know for a fact several App servers don't (iAS, jBoss).

    Gal

    Gal
  5. I have run into problems by not closing database connections. It is true that it not required by the spec, but if your application is be used by a large number of users and database access is frequent you may run out of db connections. This occurs because you have allocated all the connections in the pool and not released them, if you enough RAM the JVM may not need to garbage collect the objects with references to the connections thus depleting your db pool.
  6. It depends upon how the jdbc pool has been created.
    ideally you would want to close all open connection ina finally method.
    But there is concept called ideal time out and orphantimeout in jdbc pool.
    In ophantimeout(IMHO) if the connection is currently used by a client but is idel it will get time out and returned back to pool and hence there should not be any dangling connections.
  7. You should always close connections/resources when finished. Not to do so is leaving yourself up to errors and undefined behavour like transaction timeouts.

    Remember the behavour of "close" depends on what kind of database connection you have.

    If it is a stand alone connection then calling "close" closes the actually connection to the database. If it is a connection you got from a pool then calling "close" will return that connection to the pool.

    In either case your code should look something like this,

    Connection con=null;
    try {
      con = getConnection();
      ......
    } catch (...Exception) { }
    finally {
      try { con.close(); } catch (Exception e) {}
    }






  8. You should(in the finally block, to make sure) close the connection once you are done with the Connection in any case. As this is already discussed here .. I will try to add some more to it.

    Upon closing Connection obtained from the DriverManager, closes the connection with the database. So all the cursors (Statements and ResultSets associated) are cleaned up. No pooling mechnsm. here.

    Upon closing Connection obtained from DataSource goes back to pool. The Connection Object avbl to the client is only the wrapper to the actual Database Connection. So this connection will keep track of the Statements and ResultSets associated with it. While 'closing' this connection it 'may' close all the Statements and its associated ResultSet and put back the Connection Object in the pool for reuse.

    Some implementation may cache (not close) the PreparedStatements associated with the connection across usage so that it can be reused quickly. I am not sure of this implementation.

    What will happen when you are not closing?

    The Statements (Cursors to the Database) associated with that connection will keep increasing and at a point will reach the maximum allowed cursor and throws 'maximum open cursors reached'. Yes DB has restriction in the number of open cursors.

    Folks! you may reply now :)
  9. Im fully and intimately aware of how JDBC works. I just didnt know if it was in the EJB spec to close a connection. The question was answered though.