Simplify Database Access in Spring Boot with JdbcTemplate: A Step‑by‑Step Guide

JDBC Overview

JDBC is Java's API for connecting to relational databases, providing classes and interfaces for executing queries and updates.

Typical usage requires loading the driver, establishing a connection, creating statements, executing SQL, processing the ResultSet, and finally closing resources.

public class DbUtil {
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/spring";
    private static final String USER = "root";
    private static final String PASSWORD = "123_123";
    public static void main(String[] args) {
        Connection conn = null;
        try {
            // 1. Load driver
            Class.forName("com.mysql.jdbc.Driver");
            // 2. Establish connection
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            // 3. Create statement
            Statement stmt = conn.createStatement();
            // 4. Define SQL
            ResultSet rs = stmt.executeQuery("SELECT order_no, amount FROM tb_order");
            // 5. Process results
            while (rs.next()) {
                System.out.println("订单号：" + rs.getString("order_no") + " 金额：" + rs.getInt("amount"));
            }
        } catch (Exception e) {
            // log error
        } finally {
            // 7. Close resources
            if (conn != null) {
                try { conn.close(); } catch (SQLException e1) { e1.printStackTrace(); }
            }
        }
    }
}

JdbcTemplate Introduction

Spring wraps JDBC in JdbcTemplate to eliminate repetitive boilerplate code. The fully‑qualified class name is org.springframework.jdbc.core.JdbcTemplate.

Key Methods

execute – runs any SQL statement, commonly used for DDL.

update and batchUpdate – execute INSERT, UPDATE, DELETE and batch operations.

query and queryForXXX – perform SELECT queries and map results.

call – invoke stored procedures or functions.

Integrating JdbcTemplate with Spring Boot

Adding Dependencies

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

The starter pulls in spring-jdbc; the MySQL connector provides the driver implementation.

Entity Class

@Data
public class Order {
    private int id;
    private String orderNo;
    private int amount;
}

DataSource Configuration (application.properties)

spring.datasource.url=jdbc:mysql://localhost:3306/spring?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.username=root
spring.datasource.password=123_123
# Spring Boot 2.1.0 deprecated com.mysql.jdbc.Driver
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Service Interface

public interface OrderService {
    int save(Order order);
    int update(Order order);
    int delete(int id);
    Order findById(int id);
    List<Order> findAll();
}

Service Implementation

@Service("orderService")
public class OrderServiceImpl implements OrderService {
    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public int save(Order order) {
        return jdbcTemplate.update("insert into tb_order(order_no, amount) values(?, ?)",
                order.getOrderNo(), order.getAmount());
    }

    @Override
    public int update(Order order) {
        return jdbcTemplate.update("update tb_order set amount = ? where id = ?",
                order.getAmount(), order.getId());
    }

    @Override
    public int delete(int id) {
        return jdbcTemplate.update("delete from tb_order where id = ?", id);
    }

    @Override
    public Order findById(int id) {
        return jdbcTemplate.queryForObject("select * from tb_order where id = ?",
                new Object[]{id}, new BeanPropertyRowMapper<>(Order.class));
    }

    @Override
    public List<Order> findAll() {
        return jdbcTemplate.query("select * from tb_order", new OrderRowMapper());
    }

    class OrderRowMapper implements RowMapper<Order> {
        @Override
        public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
            Order order = new Order();
            order.setId(rs.getInt("id"));
            order.setOrderNo(rs.getString("order_no"));
            order.setAmount(rs.getInt("amount"));
            return order;
        }
    }
}

Unit Tests (JUnit 5)

@SpringBootTest
class OrderServiceTest {
    @Resource
    private OrderService orderService;

    @Test
    void save() {
        Order order = new Order();
        order.setOrderNo("N003");
        order.setAmount(10000);
        orderService.save(order);
    }

    @Test
    void update() {
        Order order = new Order();
        order.setId(1);
        order.setOrderNo("N001");
        order.setAmount(8888);
        orderService.update(order);
    }

    @Test
    void delete() { orderService.delete(2); }

    @Test
    void findById() {
        Order order = orderService.findById(3);
        // log order info
    }

    @Test
    void findAll() {
        List<Order> list = orderService.findAll();
        // log list info
    }
}

Conclusion

Injecting JdbcTemplate with @Resource enables concise CRUD operations in Spring Boot, eliminating the verbosity of raw JDBC. The next article will explore common integration errors and deeper source‑code analysis.