> However we have a java-based web app in which we use connection pooling. What happens to these PreparedStatements in this scenario? Are PreparedStatements 'bound' in any way to the connection that created them? I.e after the PreparedStatement is created, can it be used as its own entity, completely independent of the connection on which it was created? If you close the original connection it'll break the PreparedStatement, won't it?
>
> E.g we create PreparedStatement1 with thread1/connection1, then connection1 gets released back to the pool. If connection1 then gets allocated to thread2 (and that is busy using the connection) then thread3 starts using the PreparedStatement1, is there going to be any problem?
>
> I ask this not because I've experienced any issues yet, but I want to avoid any corruptions (these sorts can be hard to track down) and follow 'best practice'. I've seen what can go wrong if two threads simultaneously try to execute queries on the same connection...

In theory it should work to some extent. JDBC specification requires
every driver to support multithreaded access to one connection. This is
also implemented in Jaybird, but there were also reports of some
NullPointerExceptions under heavy load, which might be related to the
multithreaded access (though none of these issues can be reproduced
under normal load, so most likely many factors are responsible for that).

But you need to consider following issues:

1. Transactions are bound to the connections. So, if you use prepared
statement in one thread, and commit/rollback are issued in another
thread, you will definitely see "funny" (i.e. non-deterministical)
behavior. If you use auto-commit, things get even worser, since prepared
statement (and underlying result set, if any) will be closed before
another statement is executed.

2. Lifetime of the prepared statements is bound by the lifetime of the
connection, which created that statement. So, any pool, that respects
the JDBC specification, will close prepared statement when the
connection is closed (i.e. returned back to the pool).

3. Jaybird will synchronize on a connection object in case of
multithreaded access, because the wire protocol needs to get reply from
the server for the first packet before it sends next one. So using two
prepared statements from one connection will be slower that using one
prepared statement per connection in case of high load, when each
prepared statement was created by a separated connection object.

So, the best practice looks like this:

Connection c = pool.getConnection();
try {
PreparedStatement stmt =
c.prepareStatement(sql);
...
} finally {
c.close();
}

If you have clever connection pool, it will also pool the prepared
statements, so you will save also time on parsing the sql statements on
the server.

Roman