Reusing a Prepared Statement with another conneciton object

Question

Here is my setup:

I have two databases, lets say DB A and DB B.

I have a Connection to DB A called conn_a. I create a PreparedStatement such as

PreparedStatement pStmt = conn_a.prepareStatement(sql,
    Statement.RETURN_GENERATED_KEYS);

Then I do a bunch of 'set' operation on the pStmt; such as setInt, setString etc.

Finally, I execute the statement with pStmt.execute();

Now I want to run the SAME prepared statement (pStmt) on conn_b (hooked up to DB B). How can I do this?

Thanks!

Answer 1

I don't think you can - you prepare the statement against a connection and, by extension, against a database. The intention is that the query execution plan is cached at the server side so that subsequent executions of the prepared statement don't have to re-evaluate the query plan again. Running the same PreparedStatement against a different database doesn't make sense in that context.

You can prepare the statement again, against the other connection, but it would be a different prepared statement, albeit one that was extremely similar to the first one. So it's not hard to envisage a method where you pass the connection to it, and prepare the statement against the provided connection.