Using the JdbcTemplate in Spring

Outline

1. Why use the JDBC Template?
2. Configure the JDBC Template
3. Perform CRUD Operations with the JDBC Template
4. Map Result Sets to Objects using RowMapper and ResultSetExtractor

Benefits of using the JDBC Template

1. No connection management
2. No exception handling required
1. Exceptions are unchecked, which means you can catch them if you like, but you are not obligated to
2. Checked vs Unchecked Exceptions

The JDBC Template is one of the core features of Spring's data access support. Using the JDBC support allows us to avoid try/catch block, support vfor iterating through result sets, mapping to sets, under trad JDBC approach.

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;  
 ...  
   
 @Repository
 public class RentalLocationRepositoryImpl implements RentalLocationRepository {  
   
      private JdbcTemplate          template;  
   
      @Autowired  
      public 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());  
 }  

Map Result Sets to Objects using RowMapper and ResultSetExtractor