Обсуждение: 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