sql - Is it possible to execute a CallableStament on a different Connection? -

it considered practice reuse callablestatement's instances (check here).

but when creating callablestatement, statement (from understanding) bound specific connection. do:

connection con = pool.getconnection(); callablestatement st = con.preparecall("{ stmt; }"); st.executequery(); st.close(); con.close();             

from checked, following not going execute query:

connection con = pool.getconnection(); callablestatement st = con.preparecall("{ stmt; }"); con.close(); con = pool.getconnection(); // possibly new connection, different 1 used create callablestatement instance st.executequery(); st.close(); 

my question is: if want reuse callablestatement instances, on other hand still being able new connections , close old ones (not have have same connection open) can do?

preparedstatements (or ought be) cached jdbc driver. see e.g. http://www.mchange.com/projects/c3p0/

this means should not hold on 1 , use between connections, don't worry, driver manage caching you. happens each connection cache own, if have 5 connections, you'll have 5 cached copies lying around, sufficiently small.

calling preparestatement retrieve cache if cached , allocate if not. repeated calls preparestatement lightweight. correct use of api.

see e.g. oracle's docs technically oracle-specific believe information standard.