JDBC Cache statement per connection

0
votes

i know when using preparestatement the statement will be cache for the next time use , but i've read the docs which said the Cache is made per connection , from my understanding this means that each connection maintain it's own Cache . i.e. Connection A can't use the statement cached in Connection B even those two connections are in the same connection pool .

i'm wondering why can't the connection pool manage the Cache for all connections in it , so the statement could be reused by all connections.

my question : am i right about this ? or i just misunderstand this. and if i'm right , how about my wondering mentioned above . can it be implemented that way ?

1
jdbcprepared-statementconnection-pooling
The Connection Pool has little to do with it. The remote database (if cachable statements are supported and enabled) will maintain a statement that has already been query plan'd. - Elliott Frisch
@ElliottFrisch connection pools can also implement a statement cache (eg store a number of prepared statements, even if the logical statement handle was closed) - Mark Rotteveel

1 Answers

0
votes

A statement handle is - usually - linked to the physical connection that created it (not only at the JDBC side, but also at the database side). It is also deleted/closed/disposed when the connection is closed. As it is linked to the connection, the handle can't be used from a different connection, therefor the statement cache - if any - is per connection.

Even if this were technically possible, there could be additional problems (eg privilege leaks if connection have different rights, etc).