Thread: Connection and Statement
Hi All! I wonder 2 issues: 1. Are there limitations creating statements from a connection. 2. When I use a connection pool which keeps all initialized connection open, when is the earliest time I can put an already retrieved connection back. Can I put the connection back, before I have closed all created statements, or is this not good practice. Thanks for any comment. Erwin
Erwin- In reply to both of these questions, I can relate my experiences, but as to the "right" answer, the folks who actually wrote the driver will need to weigh in. > 1. Are there limitations creating statements from a connection. We have a connection pool class that has been tested with 16 open connections, each having about 30 prepared statements established. (A "PreparedConnectionPool") This doesn't seem to have broken anything and the performance is better than either prepared statements alone or a connection pool alone. (We haven't done serious benchmarking, but the difference is noticeable.) > > 2. When I use a connection pool which keeps all initialized connection > open, when is the earliest time I can put an already retrieved > connection back. Can I put the connection back, before I have closed all > created statements, or is this not good practice. Our pool class closes unused connections without closing the statements opened for the connection. Since the javadoc for close() says "Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.", I took that to imply that garbage collection would take care of this if we yanked the connection out from under the statement. So far, we've not experienced any problems with this approach. -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
Let me add in the following... (And I would love it if someone could comment on this since I'm punting somewhat...) You can return the connection to the pool even if you're not done with the statement/resultSet provided 1) You're not using a cursor while doing a fetch. (Which is due to #2) 2) You're done with your transaction. 3) You don't use the statement for more querrying anymore. Say that you completed a select statement, and are about the interate through the result set. In theory you can return the connection to the pool. (Though I don't do this myself) But if you have the transaction open for any reason, and you return the connection you'll find odd things happening. If you're just doing a select, the most common times this would happen is with a fetch since you'll call it multiple times. Also, if you have the statement, you can get the connection from the statement via the getConnection() method. You prob shouldn't use that either. Now, is this good practices? I don't do this personally out of habit. But I'm very interested in others opinions on if this returning the connection when you just have to iterate over the results of a select. --- Nick Fankhauser <nickf@ontko.com> wrote: > > 2. When I use a connection pool which keeps all initialized connection > > open, when is the earliest time I can put an already retrieved > > connection back. Can I put the connection back, before I have closed > > all created statements, or is this not good practice. > > Our pool class closes unused connections without closing the statements > opened for the connection. Since the javadoc for close() says "Releases > this Statement object's database and JDBC resources immediately instead > of waiting for this to happen when it is automatically closed.", I took > that to imply that garbage collection would take care of this if we > yanked the connection out from under the statement. So far, we've not > experienced any problems with this approach. ===== Virtually, | "Must you shout too?" Ned Wolpert | -Dante wolpert@yahoo.com | _________________/ "Who watches the watchmen?" 4e75 -Juvenal, 120 AD -- Place your commercial here -- fnord __________________________________________________ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com
Erwin Ambrosch wrote: > Hi All! > > I wonder 2 issues: > > 1. Are there limitations creating statements from a connection. I don't understand what you are asking here. Can you explain a little bit more? > > 2. When I use a connection pool which keeps all initialized connection > open, when is the earliest time I can put an already retrieved > connection back. Can I put the connection back, before I have closed all > created statements, or is this not good practice. This would not be a good idea. If you return the connection to the pool before you are done with all of the statements then the connection is available for other processes to use. If someother process uses the connection while you are still using your statements then that other process calls commit, it will commit your statements as well since they all belong to the same connection. thanks, --Barry