Outline
- Why use the JDBC Template?
- Configure the JDBC Template
- Perform CRUD Operations with the JDBC Template
- Map Result Sets to Objects using RowMapper and ResultSetExtractor
Benefits of using the JDBC Template
- No connection management
- No exception handling required
- Exceptions are unchecked, which means you can catch them if you like, but you are not obligated to
- Checked vs Unchecked Exceptions
Using JDBC Template doesn't have the auto-magicallity of a full JPA stack-implementation, but in my experience, what you lose in code in shifting to JPA, you typically pick up in configuration. Use of the JDBC Template loses just enough of the boilerplate code, yet retains enough simplicity, that a developer accustomed to traditional JDBC development can pick it up quickly, and the slightly higher learning curve with JPA is avoided.
Proposed Architecture
Package Structure and Files:
- src/
- main/
- java
- com.mycompany.project
- data
- entities
- Media.java
- Rental.java
- RentalLocation.java
- repositories
- RentalLocationRepository.java
- RentalLocationRepositoryImpl.java
- RentalLocationResultsExtractor.java
- Repo.java
- service
- RentalService.java
- resources
- config
- application-config.xml
- test
- java
- com.mycompany.project
- RentalLocationRepositoryTest
- pom.xml
Configure the JDBC Template
src/main/resources/config/application-context.xml
<?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="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">
<context:component-scan base-package="com.mycompany.project.data" />
<bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="url" value="jdbc:mysql://localhost:3306/myschema" />
<property name="username" value="myuser" />
<property name="password" value="mypassword" />
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
</bean>
</beans>
Repository Interface
public interface RentalLocationRepository extends Repo<RentalLocation> {// add custom methods here} public interface Repo<T> { void insert(T entity); void update(T entity); void delete(T entity); T findById(); }
Concrete Repository Implementation
package com.mycompany.project.data.repositories; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.List; ...@Repositorypublic class RentalLocationRepositoryImpl implements RentalLocationRepository { private JdbcTemplate template;@Autowiredpublic RentalLocationRepositoryImpl(DataSource dataSource) { this.template = new JdbcTemplate(dataSource); } ... }
Notice the basic design here.
In a jUnit test case, the RentalLocationRepositoryImpl is constructed like this:
ApplicationContext context = new ClassPathXmlApplicationContext("config/application-context.xml");
assertNotNull(context);
DataSource dataSource = context.getBean("datasource", DataSource.class);
assertNotNull(dataSource);
RentalLocationRepositoryImpl repo = new RentalLocationRepositoryImpl(dataSource);
assertNotNull(repo);
The Datasource is retrieved from the configured application-context and passed into the Repository class. That's it. Everything else is managed by Spring. No further annotation is needed
Perform CRUD Operations with the JDBC Template
Create
@Override
public void insert(final RentalLocation entity) {
KeyHolder keyHolder = new GeneratedKeyHolder();
this.template.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement statement = con.prepareStatement(INSERT_STATEMENT, Statement.RETURN_GENERATED_KEYS);
statement.setString(1, entity.getName());
statement.setString(2, entity.getAddress1());
statement.setString(3, entity.getAddress2());
statement.setString(4, entity.getCity());
statement.setString(5, entity.getState());
statement.setString(6, entity.getPostalCode());
return statement;
}
}, keyHolder);
entity.setRentalLocationId(keyHolder.getKey().intValue());
}
...
@Test
public void insert() throws Throwable {
ApplicationContext context = new ClassPathXmlApplicationContext("config/application-context.xml");
DataSource dataSource = context.getBean("datasource", DataSource.class);
RentalLocationRepositoryImpl repo = new RentalLocationRepositoryImpl(dataSource);
RentalLocation entity = dummy();
repo.insert(entity);
System.err.println("Auto Generated ID (retrieved by-reference): " + entity.getRentalLocationId());
assertNotNull(repo.findById(entity.getRentalLocationId()));
((ConfigurableApplicationContext) context).registerShutdownHook();
}
The insert statement is
insert into rental_location (rental_location_id, name, address1, address2, city, state, postal_code) values (null, ?, ?, ?, ?, ?, ?)
Read
public RentalLocation findById(Integer id) {
try {
return this.template.queryForObject(FIND_BY_ID_QUERY, new Object[] { id }, new RentalLocationRowMapper());
} catch (EmptyResultDataAccessException e) {
return null;
}
}
public List<RentalLocation> findLocationsByState1(String state) {
return this.template.query(FIND_BY_STATE_QUERY_01, new Object[] { state }, new RentalLocationRowMapper());
}
public List<RentalLocation> findLocationsByState2(String state) {
return this.template.query(FIND_BY_STATE_QUERY_02, new Object[] { state }, new RentalLocationResultExtractor());
}
