SpringTransactionManager does not cache insert PreparedStatements

[badgerwithagun]

In SpringTransactionManager, any time Transaction.prepareBatchStatement is called, a new PreparedStatement is opened. In other implementations, the statements are cached in the transaction and re-used for the same SQL statement.

Harmless functionally, but affects performance.

[tsg21]

In most cases, the JDBC driver (or the database itself) will implement prepared statement caching, no? I am not sure you really need to do anything here.

Note Hikari CP's comments on the topic

[badgerwithagun]

This isn't really about statement caching as such. The trick that Transaction.prepareBatchStatement pulls is that any statement opened this way gets executeBatch called on it automatically at the end of the transaction, so if you write a 100 outbox records in one transaction they all get pushed out in one go in the same batch.

Because SpringTransactionManager isn't correctly caching, each insert is made in a separate request to the DB, which is indeed costly if you're doing a lot of "fan out" type behaviour (I am also planning on "fan in" capability...)