I'm trying to assess how a large in-house app will behave with PG. The
obvious and most correct way to handle the situation is to re-code their
apps to /not/ send dynamic queries to Connection.prepareStatement() at
all :-) I'm curious though, about how PG and/or the JDBC driver
generally handles prepared statements (now, and future/ideally):
1) Are statements cached server side or driver?
2) If client side, are statements cached per-connection, or pooled in a
classloader (or even jvm classloader)?
(their connection pool impl allows creation of prepared statements
bound to pooled Connections -- caching only at the connection level * 50
connections with prepareStatement(dynamicSql) has implications since the
list can be several thousand items long and in random order)
3) Can we set a maximum time-to-live so that only frequently reused
statements stay cached, so that if there is dynamic sql sent to
Con.prepareStatement, will not be a memory leak (see next)?
4) They want parameter escaping but in the context of lists:
WHERE foo IN ('a','b','c',...dynamic list).
Is this possible in a database neutral way?
String[] ar = new String[]{"a","b"};
"WHERE foo IN ?",
ps.setObject(1,ar,Types.ARRAY);
(I've never tried this snippet but presume it won't work due to the
zero-len case which should failfast according to sql, I believe)
Thanks,
Ken