Luc Lieber wrote:What I really want is a link to some authoritative documentation that makes guarantees about what causes 'automatic closing'. Perhaps it's driver specific, but I haven't even found any driver documentation that gives the required information.
Yeah, now I understand your problem. Unfortunately, I don't know of any such authoritative source. I know some authorities (eg. Tom Kyte of Oracle) mentioned that applications can keep prepared statements open for an indeterminate amount of time, though that probably wasn't specifically about Java.
As you say, this could be
JDBC driver specific. Perhaps your best option would be to read the documentation for your driver, even if you don't find a definitive answer, you might find some important hints. For example, Oracle's JDBC driver caches prepared statements and reuses them. This means that closing and opening a prepared statement has probably just a small overhead and, if I wanted to be on the safe side, I'd close and re-open the statements for each transaction.
In any case, a good defensive strategy might be to close and reopen everything after any SQLException is encountered. Errors should be uncommon, so the additional overhead should be small.
* More info: It is important that I can distinguish between a 'temporary condition' like a network error and a 'permanent' condition' like a malformed query. Transactions need to be re-queued if the database simply isn't reachable, but aborted if there's something wrong with the queries themselves.
In my opinion, this is unreasonably difficult even for database-specific code. I don't know other databases in detail, but in Oracle there are thousands of error codes. This might be entirely unfeasible in a database-agnostic way.
Perhaps you might retry transactions a few times and then put them into a list of failed jobs and have some human review them and decide which ones to run again and which ones to ditch. There shouldn't be many malformed queries or permanent errors, if you tested the application properly. And even if there are (hopefully just a few) transactions failed due to bugs, they'll be retried, but they'll fail again.