using Spring JdbcTemplate for multiple database op

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

I assume you want transactions? If so, take a look at Spring, JdbcTemplate and Transactions.

On a side note, I would suggest you take a look at Ibatis. Spring JDBC seems convenient but it has one major issue: the default mapping of result sets to objects uses Spring classes, which is actually really slow when dealing with large result sets. You can get around this by writing your own row mappers for these queries but personally I don't want to be writing this kind of boilerplate.

To give you an example of the difference: I had one query take 50 seconds with the Spring reflection-based row mapper that took 2 seconds with a hand coded row mapper.

Also, Spring JDBC uses inline SQL. In Java this is fairly ugly as Java (annoyingly) doesn't have a good multi-line String format.

Answer 2

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).