using Spring JdbcTemplate - injecting datasource vs jdbcTemplate

As per Spring [documentation](

[To see links please register here]

),
the steps to use Spring JdbcTemplate is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="

[To see links please register here]

[To see links please register here]

[To see links please register here]

[To see links please register here]

;

<!-- Scans within the base package of the application for @Components to configure as beans -->
<context:component-scan base-package="org.springframework.docs.test" />

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>

<context:property-placeholder location="jdbc.properties"/>

</beans>


And then,

@Repository
public class JdbcCorporateEventDao implements CorporateEventDao {

private JdbcTemplate jdbcTemplate;

@Autowired
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}

// JDBC-backed implementations of the methods on the CorporateEventDao follow...
}


Basically, the JdbcTemplate is created inside the Component class using the setter for datasource.

Is there anything wrong with doing it this way instead so that there is exactly ONE instance of jdbcTemplate in the application?

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
p:dataSource-ref="dataSource"
/>

And then injecting the jdbcTemplate itself directly into the Component


@Repository
public class JdbcCorporateEventDao implements CorporateEventDao {
@Resource("jdbcTemplate")
private JdbcTemplate jdbcTemplate;


// JDBC-backed implementations of the methods on the CorporateEventDao follow...
}


Is there a reason why the jdbcTemplate itself must not be injected into the component class directly?

SGB

You can do what you want. [The javadoc of JdbcTemplate][1] even clearly says it:

> Can be used within a service implementation via direct instantiation with a DataSource reference, or get prepared in an application context and given to services as bean reference.


[1]:

[To see links please register here]

In the spring-context.xml add the following and

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>

and directly you can use jdbcTemplate by autowiring like

@Autowired JdbcTemplate jdbcTemplate;

example:


this.jdbcTemplate.query("select * from ******",new RowMapper());

You can also do it like

@Configuration
@Import({PersistenceConfig.class})
@ComponentScan(basePackageClasses = {
ServiceMarker.class,
RepositoryMarker.class }
)
public class AppConfig {

/**
* To resolve ${} in @Values, you must register a static PropertySourcesPlaceholderConfigurer in either XML or
* annotation configuration file.
*/
@Bean
public static PropertySourcesPlaceholderConfigurer propertyConfigInDev() {
return new PropertySourcesPlaceholderConfigurer();
}
}

PersistenceConfig

@Configuration
@PropertySource(value = { "classpath:database/jdbc.properties" })
@EnableTransactionManagement
public class PersistenceConfig {

@Autowired
private Environment env;

/**
* The @Bean annotation is used to declare a Spring bean and the DI requirements. The @Bean annotation is equivalent to
* the <bean> tag, the method name is equivalent to the id attribute within the <bean> tag.
*
* <bean id="mySqlDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close"
p:driverClassName="${jdbc.mysql.driverClassName}"
p:url="${jdbc.mysql.url}"
p:username="${jdbc.mysql.username}"
p:password="${jdbc.mysql.password}" />
*
* @return
*/
@Bean(destroyMethod = "close")
public DataSource mySqlDataSource() {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(env.getProperty("jdbc.mysql.driverClassName"));
dataSource.setUrl(env.getProperty("jdbc.mysql.url"));
dataSource.setUsername(env.getProperty("jdbc.mysql.username"));
dataSource.setPassword(env.getProperty("jdbc.mysql.password"));
return dataSource;
}

@Bean(destroyMethod = "close")
public DataSource ls360DataSource() {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(env.getProperty("jdbc.ls360.driverClassName"));
dataSource.setUrl(env.getProperty("jdbc.ls360.url"));
dataSource.setUsername(env.getProperty("jdbc.ls360.username"));
dataSource.setPassword(env.getProperty("jdbc.ls360.password"));
return dataSource;
}
}

MySqlDaoImpl

@Repository
public class MySqlDaoImpl implements MySqlDao{

private static final Logger logger = LogManager.getLogger();

@Inject
private DataSource mySqlDataSource;
private JdbcTemplate mySqlJdbcTemplate;

@PostConstruct
public void afterPropertiesSet() throws Exception {
if (mySqlDataSource == null) {
throw new BeanCreationException("Must set mySqlDataSource on " + this.getClass().getName());
}
this.mySqlJdbcTemplate = new JdbcTemplate(mySqlDataSource);
}

@Override
public void callStoredProcedure(String storedProcedureName, Map<String, Object> inParamMap) throws Exception {

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(mySqlJdbcTemplate).withProcedureName(storedProcedureName);
SqlParameterSource in = new MapSqlParameterSource(inParamMap);

logger.info("Calling stored Procedure: " + storedProcedureName);
Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
logger.info("Stored Procedure Result: " + simpleJdbcCallResult);
}
}

Main

public static void main(String[] args ) {
try (GenericApplicationContext springContext = new AnnotationConfigApplicationContext(AppConfig.class)) {
MySQLDao mySqlDao = springContext.getBean(MySQLDaoImpl.class);
try {
Map<String, Object> inParamMap = new HashMap<String, Object>();
inParamMap.put("iCourseId", 1);
mySqlCourseRenewalDao.callStoredProcedure("usp_processCourseRenewal", inParamMap);
} catch (Exception e) {
logger.error("Exception occurs", e);
}
} catch (Exception e) {
logger.error("Exception occurs in loading Spring context: ", e);
}
}

Thanks

There's nothing technically wrong with injecting and sharing `JdbcTemplate` in place of the underlying `DataSource`.

However, there are some design drawbacks to sharing `JdbcTemplate`, which may favor injecting `DataSource` instead:

- Use of `JdbcTemplate` is an implementation detail of the DAO, and we like to keep those details hidden
- `JdbcTemplate` is lightweight, so sharing it for efficiency is likely a premature optimization
- Sharing a `JdbcTemplate` isn't risk-free as it has some mutable state (in addition to mutable state in the underlying `DataSource`)

That's assuming the typical case where `JdbcTemplate` is used with its default configuration (`fetchSize`, `maxRows`, etc). If configuration is required, that may drive the design in a particular direction, e.g. a shared pre-configured `JdbcTemplate` injected from context, or even multiple `JdbcTemplate` instances owned by a single DAO.