Re: pooled prepared statements

Thomas Finneid wrote:
> When a PreparedStatment is created by a pooled connection, as far as I
> understand if, that creation happens on the server side, and a
> reference, of sorts, is returned to the client jdbc.
>
> Is that prepared statement shared among the connections or is it only
> available to that single connection? and more importantly, can many
> connections use that prepared statement concurrently?
>
> If it is shared, then it must be usable by concurrent connections,
> otherwise it will be difficult for the client to know if the statement
> is occupied or not. So I just want to confirm that I understand how
> the JDBC driver works.
Once the query usage count exceeds the prepareThreshold parameter, then
the driver does create a "prepare statement object" on the server. This
is only valid for the time the PreparedStatement is open and only on
that connection. So to answer your question, they cannot be shared by
multiple connections.

You can create your own using the PREPARE and EXECUTE sql commands so
long as you track them across different connections...

I'll admit this would be a nice feature for things like JPA where you
end up creating lots of preparedStatements on different connections,
reusing or caching a single one may be a worthwhile performance
optimisation - although not sure about how you would implement it nicely..

JOHN