java - using Spring JdbcTemplate for multiple database operations

Question

I like the apparent simplicity of JdbcTemplate but am a little confused as to how it works. It appears that each operation (query() or update()) fetches a connection from a datasource and closes it.

Beautiful, but how do you perform multiple SQL queries within the same connection?

I might want to perform multiple operations in sequence (for example SELECT followed by an INSERT followed by a commit) or I might want to perform nested queries (SELECT and then perform a second SELECT based on result of each row).

How do I do that with JdbcTemplate. Am I using the right class?

Answer 1

how do you perform multiple SQL queries within the same connection?

The correct answer here is "use transactions". If you begin transaction and then perform multiple operations with JdbcTemplate, each of those operations will be within the scope of the transaction, and therefore are guaranteed to use the same connection.

If you don't want to get involved with transactions, then the alternative is to use the more primitive operations on JdbcTemplate, like execute(ConnectionCallback action), where you supply an instance of ConnectionCallback which is given a Connection, on which you can then perform any operations you choose. Of course, but doing this you don't get JdbcTemplate's help in any of the actual operations.

Transactions are really quite easy in Spring, you should look into using them (see above link).