Is it better to instantiate a new JdbcTemplate in every query or to inject a single one everywhere?

Question

I have a Java library where I access the DB via JDBC using Spring's JDBC support. This library contains approximately a DAO class for each table I need to access, which are over a hundred. Currently I instantiate a new JdbcTemplate, or one of its variants, each time I need to perform a new query. Is this considered good practice or should I reuse a single JdbcTemplate as much as I can? I've actually seen examples of both approaches in either books or online documentation.

The context is a J2EE application, but ideally the code should be usable in different contexts, e.g. in offline tests or command line support tools.

Answer 1

Although there isn't a lot of overhead involved in creating a new JdbcTemplate, there isn't much of a point. The JdbcDaoSupport class, an abstract class useful for handling JdbcTemplate based DAOs consistently allows each DAO to either inject a DataSource (and undernear the covers instantiates a JdbcTemplate based on that DataSource) or inject a JdbcTemplate. Since you can do either you would only do the latter if you were looking to customize your JdbcTemplate by setting one or more of the following properties:

• fetchSize
• ignoreWarnings
• maxRows
• nativeJdbcExtractor
• queryTimeout
• skipResultsProcessing
• exceptionTranslator

You would likely have one JdbcTemplate for each combination of these properties. All of these do have defaults, so its only necessary to set them if you are going to override them. Depending on the diversity of your DAOs, you may have one or many. Or in the case of extending JdbcDaoSupport, you may have none, having each DAO just wrap the datasource in a default JdbcTemplate undernearth the covers.