I would recommend using something like Spring's jdbc support, so that this stuff happens for you automatically if you want to do straight JDBC. You can use DBCP with it, too!
On Mon, Dec 14, 2009 at 12:45 AM, Pawan Singh <[email protected]> wrote: > Hi > > Can someone with Apache DBCP expertise clarify this question? This code > snippet assumes that Database connection pools have been established using > Apache DBCP package. > > public ResultSet getResults(String sql) > { > > Connection conn = pooledDataSource.getConnection(); <-- gets > connection from pool > PreparedStatement stmt = conn.prepareStatement(sql,...); > ResultSet rs = stmt.executeQuery(); > conn.close(); <--- return to pool > return rs; > } > > public uicode_in_someotherproject() { > ResultSet rs = getResults("SELECT * from foo"); > bindDataToTableControl(rs); > } > > My problem is simply controlling the lifetime of the ResultSet. Since it is a > J2SE app, and we return ResultSet back to the user interface to bind data to > user controls, I cannot close the connection. This is because > PoolableConnection actually "passivates" the connection on returning to pool > which closes the statement and result set. > > My only other choice is to make the top layer control the lifetime of > connection and statement - but that is ugly. I though that DBCP's connection > pool will simply return the connection without "passivating" it and therefore > everything will work properly. > > I just want advice from experts on how to control the lifetime of ResultSet. > Do I have to keep a reference to connection object till I am ready to discard > ResultSet? > > Another question is related to "garbage collection". Why does DBCP not use > "finalize" to return these connections and statements back to pool? It would > seem to me that it will solve some of the problems related to having to call > "close" on all these objects properly - otherwise one can cause a leak. > > -Pawan > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
