By Joel Carranza

2010-04-01 22:42:21 8 Comments

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?


@skaffman 2010-04-02 07:04:17

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

@Bill Poitras 2011-11-06 03:28:01

You actually don't need to use an actual transaction to use the same connection. However, like using transactions in Spring need to use a TransactionProxyFactoryBean or a Transaction Template. You use PROPAGATION_SUPPORTS or PROPAGATION_NEVER for the propagation, and it will reuse the same connection, but not start a real transaction.

@It Grunt 2013-10-29 19:21:08

If you are using connection pooling, don't you have to worry about not getting the same connection between statements?

@cletus 2010-04-01 23:02:24

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.

@matt b 2010-04-01 23:59:43

"the default mapping of result sets to objects uses Spring classes" can you elaborate on this? I always use RowMappers for anything that resembles querying for an object. What other option is there?

@spaaarky21 2012-04-20 21:45:10

I'm also curious what "default mapping" means here and how they use reflection. I thought RowMappers were the bread and butter of JdbcTemplate.

@Dimitry K 2014-04-13 20:18:59

@mattb , @spaaarky21 Spring can map resultset to objects for example using BeanPropertyRowMapper class (you can google examples using it). Simple query is like : List<MyOrder> orders = jt.query("SELECT * FROM orders WHERE custId=?", new BeanPropertyRowMapper<MyOrders>(MyOrder.class), id); And then you don't have to wrap your implementation of RowMapper<T> but instead fields will be mapped to the setters of the MyOrder class.

Related Questions

Sponsored Content

49 Answered Questions

[SOLVED] How to configure port for a Spring Boot application

29 Answered Questions

17 Answered Questions

[SOLVED] :: (double colon) operator in Java 8

  • 2013-11-15 12:46:32
  • Narendra Pathai
  • 328122 View
  • 956 Score
  • 17 Answer
  • Tags:   java java-8

11 Answered Questions

2 Answered Questions

3 Answered Questions

[SOLVED] Closing JDBC Connections in Pool

1 Answered Questions

[SOLVED] How can i make database connection open in JDBCTemplate spring 4

1 Answered Questions

1 Answered Questions

1 Answered Questions

[SOLVED] JDBC/JDBCTemplate Batch Operation

Sponsored Content