Overview of Spring JDBC

Traditional JDBC operations require manual handling of connection lifecycle: establishing a connection, executing SQL statements, processing results, and explicitly closing resources. This leads to boilerplate code and increases the risk of resource leaks. Spring’s JDBC module abstracts this complexity by managing connections, handling exceptions, and providing reusable components, enabling developers to focus on business logic rather than low-level data access details.

JdbcTemplate Core Concepts

The JdbcTemplate class is the central component of Spring’s JDBC abstraction. It simplifies common operations ( CRUD ) and centralizes exception translation and connection management.

• Extends JdbcAccessor, which manages shared infrastructure such as:
• DataSource: Provides database connections, often with pooling and transaction support.
• SQLExceptionTranslator: Converts vendor-specific SQLExceptions into Spring’s consistent DataAccessException hierarchy.
• Implements the JdbcOperations interface, ensuring a unified API for data access.

Spring JDBC Package Structure

Configuraton Setup

Configuration is typically defined in applicationContext.xml. Below is a minimal setup using DriverManagerDataSource (suitable for development, not production).

DataSource Bean

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost:3306/springjdbc?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC"/>
    <property name="username" value="appuser"/>
    <property name="password" value="secret"/>
</bean>

JdbcTemplate Bean

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

DAO Delegation

<bean id="accountDao" class="com.example.repository.AccountRepositoryImpl">
    <property name="template" ref="jdbcTemplate"/>
</bean>

Domain Model and DAO Implementation

Entity: Account

package com.example.model;

public class Account {
    private Integer id;
    private String holderName;
    private BigDecimal currentBalance;

    // Constructors, getters, setters, toString()
}

Repository Interface

package com.example.repository;

import com.example.model.Account;
import java.util.List;

public interface AccountRepository {
    int save(Account acc);
    int modify(Account acc);
    int remove(int identifier);
    Account getById(int id);
    List<Account> listAll();
}

Implementation Using JdbcTemplate

package com.example.repository;

import com.example.model.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.util.List;

public class AccountRepositoryImpl implements AccountRepository {

    private JdbcTemplate template;

    public void setTemplate(JdbcTemplate template) {
        this.template = template;
    }

    @Override
    public int save(Account acc) {
        String sql = "INSERT INTO accounts (holderName, currentBalance) VALUES (?, ?)";
        return template.update(sql, acc.getHolderName(), acc.getCurrentBalance());
    }

    @Override
    public int modify(Account acc) {
        String sql = "UPDATE accounts SET holderName = ?, currentBalance = ? WHERE id = ?";
        return template.update(sql, acc.getHolderName(), acc.getCurrentBalance(), acc.getId());
    }

    @Override
    public int remove(int identifier) {
        String sql = "DELETE FROM accounts WHERE id = ?";
        return template.update(sql, identifier);
    }

    @Override
    public Account getById(int id) {
        String sql = "SELECT * FROM accounts WHERE id = ?";
        RowMapper<Account> mapper = new BeanPropertyRowMapper<>(Account.class);
        return template.queryForObject(sql, mapper, id);
    }

    @Override
    public List<Account> listAll() {
        String sql = "SELECT * FROM accounts";
        RowMapper<Account> mapper = new BeanPropertyRowMapper<>(Account.class);
        return template.query(sql, mapper);
    }
}

Common JdbcTemplate Query Methods

Testing Example

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:context.xml")
public class JdbcRepositoryTest {

    @Autowired
    private AccountRepository repository;

    @Test
    public void shouldInsertNewAccount() {
        Account acc = new Account();
        acc.setHolderName("Ana Silva");
        acc.setCurrentBalance(new BigDecimal("2500.00"));
        int rows = repository.save(acc);
        assertTrue(rows > 0);
    }

    @Test
    public void shouldFetchAllAccounts() {
        List<Account> list = repository.listAll();
        assertFalse(list.isEmpty());
    }
}