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 ?
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).