JDBC-Backed CRUD App

Build a full CRUD console app using Spring JDBC and JdbcTemplate.

Spring JDBCJdbcTemplateTesting

Overview

Spring JDBC's JdbcTemplate sits one level below an ORM like Spring Data JPA: it does not generate SQL for you, and it does not map your object model onto tables automatically. What it removes is the boilerplate JDBC has always demanded — opening a Connection, creating a PreparedStatement, remembering to close both in a finally block, and converting a checked SQLException into something you actually have to handle. You still write every SQL statement yourself and you still tell it exactly how to turn a ResultSet row into a Java object, which is precisely why this project is worth building before reaching for an ORM: it shows what an ORM is actually automating away underneath.

This project is a console CRUD (Create, Read, Update, Delete) app for managing tasks, backed by an in-memory H2 database that Spring builds automatically when the app starts. You will write a TaskRepository around JdbcTemplate's three core methods — query(), queryForObject()-style single-row reads, and update() for INSERT/UPDATE/DELETE — plus a RowMapper that is the one place in the whole project aware of the tasks table's column names. You will also write a genuine unit test that mocks JdbcTemplate itself with Mockito, to see why the repository layer is exactly where SQL typos most often hide from the compiler.

What You'll Build

A Task model and a schema.sql script creating the tasks table.
A TaskRowMapper converting one JDBC ResultSet row into one Task.
A JdbcTaskRepository using JdbcTemplate.query() for reads and JdbcTemplate.update() for writes.
Generated-key retrieval on create() using a KeyHolder.
An embedded H2 DataSource bean built with EmbeddedDatabaseBuilder, with JdbcTemplate wired on top of it.
A unit test that mocks JdbcTemplate with Mockito to verify the repository's SQL and arguments without touching a real database.

Prerequisites

Project 1 of this course (@Configuration, @Bean, constructor injection).
Basic SQL — CREATE TABLE, INSERT, SELECT ... WHERE, UPDATE, DELETE.
What a JDBC ResultSet and PreparedStatement are, at least conceptually.
JUnit 5 basics (@Test, assertions) and, briefly, Mockito's mock()/verify().
Maven basics.

Project Structure

The project lives under src/main/java/com/programinds/taskcrud/ for application code and src/test/java/com/programinds/taskcrud/ for the Step 7 test, with schema.sql under src/main/resources/ so Spring can find it on the classpath at startup. Task is a plain model, TaskRowMapper and JdbcTaskRepository do the actual JDBC work, AppConfig wires the embedded database and JdbcTemplate together, and TaskCrudApplication holds the console menu loop.

Read and write concerns are kept in the same JdbcTaskRepository class here (there are only five operations total), but the same repository → service → controller layering from Project 1 would apply the moment real business rules — validation, authorization, cross-table logic — needed to sit above raw persistence. This project deliberately stays at the persistence layer alone so JdbcTemplate itself is the entire focus.

pom.xml (dependencies)

<dependencies>
    <!-- spring-jdbc provides JdbcTemplate, RowMapper, and EmbeddedDatabaseBuilder. -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>6.1.13</version>
    </dependency>

    <!-- H2 is a pure-Java, in-memory database — no separate server process to
         install or configure, which is what makes EmbeddedDatabaseBuilder possible. -->
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>2.2.224</version>
    </dependency>

    <dependency>
        <groupId>org.junit.jupiter</groupId>
        <artifactId>junit-jupiter</artifactId>
        <version>5.10.3</version>
        <scope>test</scope>
    </dependency>

    <!-- Used in Step 7 to create a fake JdbcTemplate that records how it was called,
         instead of a real one that would need a real database connection. -->
    <dependency>
        <groupId>org.mockito</groupId>
        <artifactId>mockito-core</artifactId>
        <version>5.12.0</version>
        <scope>test</scope>
    </dependency>
</dependencies>

Step 1: Add the Spring JDBC and H2 Dependencies

Four dependencies cover this entire project: spring-jdbc for JdbcTemplate itself, h2 for the embedded in-memory database the app runs against, and junit-jupiter/mockito-core (both scoped test, since they are never needed at runtime) for Step 7's unit test. Add the block above to your pom.xml before continuing.

Step 2: Define the Task Model and Schema

Task is a plain model with two constructors — one for a task freshly loaded from the database (with a known id), and a convenience one for a brand-new task that has not been saved yet (id defaults to 0 until create() in Step 5 assigns the database-generated one). schema.sql is picked up automatically by EmbeddedDatabaseBuilder in Step 6, run once against the fresh H2 database at startup, before the app ever queries it.

src/main/resources/schema.sql

CREATE TABLE tasks (
    id    INT AUTO_INCREMENT PRIMARY KEY,   -- H2 generates this automatically on INSERT
    title VARCHAR(255) NOT NULL,
    done  BOOLEAN NOT NULL DEFAULT FALSE      -- Every new task starts not done
);

package com.programinds.taskcrud;

public class Task {
    private int id;
    private String title;
    private boolean done;

    public Task(int id, String title, boolean done) {
        this.id = id;
        this.title = title;
        this.done = done;
    }

    public Task(String title, boolean done) {   // For a not-yet-saved task; id is assigned by create() in Step 5
        this(0, title, done);
    }

    public int getId() { return id; }
    public void setId(int id) { this.id = id; }

    public String getTitle() { return title; }
    public void setTitle(String title) { this.title = title; }

    public boolean isDone() { return done; }
    public void setDone(boolean done) { this.done = done; }

    @Override
    public String toString() {
        return String.format("#%d [%s] %s", id, done ? "x" : " ", title);
    }
}

Step 3: Build a RowMapper for Task

A RowMapper answers exactly one question: how does one row from a JDBC ResultSet become one Java object? Spring calls mapRow() once per row returned by a query and collects every result into the List that JdbcTemplate.query() hands back — which makes TaskRowMapper the single place in this entire project that knows the tasks table's actual column names.

package com.programinds.taskcrud;

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class TaskRowMapper implements RowMapper<Task> {
    @Override
    public Task mapRow(ResultSet rs, int rowNum) throws SQLException {
        // rowNum (the row's 0-based position in the result set) is part of
        // RowMapper's contract but unused here — some mappers need it, this one doesn't.
        int id = rs.getInt("id");
        String title = rs.getString("title");
        boolean done = rs.getBoolean("done");
        return new Task(id, title, done);
    }
}

Step 4: Build TaskRepository's Read Methods

findAll() uses query(), which runs the SQL and calls the RowMapper once per returned row, collecting the results into a List. findById() also uses query() here rather than queryForObject() — queryForObject() throws EmptyResultDataAccessException when nothing matches, which would force every caller into a try/catch just to handle "not found"; checking results.isEmpty() after query() keeps the same null-returning contract the earlier plain-Java course projects used.

JdbcTemplate Method	Returns	Used For
query(sql, RowMapper, args...)	List<T> (possibly empty)	Any SELECT returning zero or more rows
queryForObject(sql, RowMapper, args...)	A single T; throws if not exactly one row	A SELECT guaranteed to match exactly one row
update(sql, args...)	int (rows affected)	Any INSERT, UPDATE, or DELETE

package com.programinds.taskcrud;

import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class JdbcTaskRepository implements TaskRepository {
    private final JdbcTemplate jdbcTemplate;
    private final TaskRowMapper rowMapper = new TaskRowMapper();   // Reused by every read method below

    public JdbcTaskRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;   // Constructor injection; wired in Step 6's AppConfig
    }

    @Override
    public Task findById(int id) {
        List<Task> results = jdbcTemplate.query(
            "SELECT id, title, done FROM tasks WHERE id = ?", rowMapper, id);
        return results.isEmpty() ? null : results.get(0);   // Avoids queryForObject()'s thrown exception on "not found"
    }

    @Override
    public List<Task> findAll() {
        return jdbcTemplate.query("SELECT id, title, done FROM tasks ORDER BY id", rowMapper);
    }
}

interface TaskRepository {
    Task findById(int id);
    List<Task> findAll();
}

Step 5: Add Create, Update, and Delete Operations

create() needs the database-generated id back, which requires a different update() overload than a plain UPDATE or DELETE does: passing a PreparedStatementCreator plus a KeyHolder lets JdbcTemplate capture whatever id H2 auto-generated for the new row. update() and deleteById(), by contrast, use the simplest update(String sql, Object... args) overload, since neither needs anything back beyond a row-affected count.

package com.programinds.taskcrud;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.PreparedStatement;
import java.sql.Statement;

// (continuing the same JdbcTaskRepository class from Step 4)
public Task create(String title) {
    KeyHolder keyHolder = new GeneratedKeyHolder();   // Captures the id the database auto-generates for this INSERT

    // This update(PreparedStatementCreator, KeyHolder) overload exists
    // specifically for the "I need the generated key back" case — the plain
    // update(String, Object...) overload used below does not expose it.
    jdbcTemplate.update(connection -> {
        PreparedStatement ps = connection.prepareStatement(
            "INSERT INTO tasks (title, done) VALUES (?, ?)",
            Statement.RETURN_GENERATED_KEYS);   // Tells the JDBC driver to make the generated id retrievable
        ps.setString(1, title);
        ps.setBoolean(2, false);   // A brand-new task always starts not done
        return ps;
    }, keyHolder);

    int generatedId = keyHolder.getKey().intValue();
    return new Task(generatedId, title, false);
}

public void update(Task task) {
    // update(String sql, Object... args) is JdbcTemplate's plain "run this DML
    // and bind these ? placeholders in order" method — used for UPDATE and
    // DELETE alike, since neither returns rows the way SELECT does.
    jdbcTemplate.update("UPDATE tasks SET title = ?, done = ? WHERE id = ?",
        task.getTitle(), task.isDone(), task.getId());
}

public void deleteById(int id) {
    jdbcTemplate.update("DELETE FROM tasks WHERE id = ?", id);
}

Step 6: Configure the DataSource and JdbcTemplate Beans

EmbeddedDatabaseBuilder spins up an in-process H2 database that lives only for the lifetime of the JVM running it — perfect for a self-contained demo project, since there is no external database server to install, configure, or clean up between runs. JdbcTemplate itself is stateless and thread-safe, which is why it is registered as one shared singleton bean here rather than created fresh per repository call.

package com.programinds.taskcrud;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;

import javax.sql.DataSource;

@Configuration
public class AppConfig {

    @Bean
    public DataSource dataSource() {
        return new EmbeddedDatabaseBuilder()
            .setType(EmbeddedDatabaseType.H2)
            .addScript("schema.sql")   // Run once at startup, from src/main/resources, to create the tasks table
            .build();
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);   // A single, stateless, thread-safe JdbcTemplate shared by every repository call
    }

    @Bean
    public TaskRepository taskRepository(JdbcTemplate jdbcTemplate) {
        return new JdbcTaskRepository(jdbcTemplate);   // Constructor injection: the repository never touches DataSource directly
    }
}

Step 7: Write a Unit Test With a Mocked JdbcTemplate

The repository layer is exactly where SQL mistakes most often hide from the compiler — a typo in a column name, or two ? placeholders bound in the wrong order, compiles perfectly fine, because to javac the SQL is just a String. Mocking JdbcTemplate with Mockito turns this into a true UNIT test: it verifies deleteById()'s own logic — the exact SQL text and the exact argument passed — without needing any real (even embedded) database running, which keeps it fast, deterministic, and immune to leftover data from a previous test affecting this one.

package com.programinds.taskcrud;

import org.junit.jupiter.api.Test;
import org.mockito.ArgumentCaptor;
import org.springframework.jdbc.core.JdbcTemplate;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.verify;

class JdbcTaskRepositoryTest {

    @Test
    void deleteByIdRunsTheCorrectSqlWithTheGivenId() {
        JdbcTemplate mockJdbcTemplate = mock(JdbcTemplate.class);   // A fake JdbcTemplate that only records how it was called
        JdbcTaskRepository repository = new JdbcTaskRepository(mockJdbcTemplate);

        repository.deleteById(42);

        // ArgumentCaptor inspects EXACTLY what deleteById() passed to
        // JdbcTemplate.update() — not just that some call happened, but the
        // precise SQL string and the precise id value.
        ArgumentCaptor<String> sqlCaptor = ArgumentCaptor.forClass(String.class);
        ArgumentCaptor<Object> idCaptor = ArgumentCaptor.forClass(Object.class);
        verify(mockJdbcTemplate).update(sqlCaptor.capture(), idCaptor.capture());

        assertEquals("DELETE FROM tasks WHERE id = ?", sqlCaptor.getValue());
        assertEquals(42, idCaptor.getValue());
    }
}

Why Not Test Against Real H2 Instead?

A test that spins up the real embedded H2 database and actually deletes a row is also valuable — but it is an INTEGRATION test, not a unit test: it verifies the whole stack (SQL syntax, H2's own behavior, JdbcTemplate's translation of results) together. Both kinds of test are worth having; mocking JdbcTemplate here specifically isolates JdbcTaskRepository's own code from everything underneath it, which is what makes a failure here point at exactly one class.

Step 8: Build the Console Menu and Run

main() builds the container (which, per Step 6, also builds and schema-initializes the embedded H2 database as a side effect of creating the dataSource() bean), retrieves the fully-wired TaskRepository, and loops on a numbered menu — the same shape used by the very first Java projects in this course, just backed by a real (if embedded) SQL database instead of an ArrayList this time.

package com.programinds.taskcrud;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import java.util.Scanner;

public class TaskCrudApplication {
    public static void main(String[] args) {
        AnnotationConfigApplicationContext context =
            new AnnotationConfigApplicationContext(AppConfig.class);
        TaskRepository taskRepository = context.getBean(TaskRepository.class);

        Scanner scanner = new Scanner(System.in);
        int choice;

        do {
            System.out.println("\n===== TASK CRUD APP =====");
            System.out.println("1. Add Task");
            System.out.println("2. List Tasks");
            System.out.println("3. Mark Task Done");
            System.out.println("4. Delete Task");
            System.out.println("5. Exit");
            System.out.print("Enter your choice: ");
            choice = scanner.nextInt();
            scanner.nextLine();   // Discard the leftover newline left behind by nextInt()

            if (choice == 1) {
                System.out.print("Enter task title: ");
                String title = scanner.nextLine();
                Task created = ((JdbcTaskRepository) taskRepository).create(title);
                System.out.println("Added: " + created);
            } else if (choice == 2) {
                System.out.println("\n--- All Tasks ---");
                for (Task t : taskRepository.findAll()) {
                    System.out.println(t);
                }
            } else if (choice == 3) {
                System.out.print("Enter task id to mark done: ");
                int id = scanner.nextInt();
                scanner.nextLine();
                Task task = taskRepository.findById(id);
                if (task == null) {
                    System.out.println("No task with that id.");
                } else {
                    task.setDone(true);
                    ((JdbcTaskRepository) taskRepository).update(task);
                    System.out.println("Task marked done.");
                }
            } else if (choice == 4) {
                System.out.print("Enter task id to delete: ");
                int id = scanner.nextInt();
                scanner.nextLine();
                ((JdbcTaskRepository) taskRepository).deleteById(id);
                System.out.println("Task deleted (if it existed).");
            } else if (choice == 5) {
                System.out.println("Goodbye!");
            } else {
                System.out.println("Invalid choice, try again.");
            }
        } while (choice != 5);

        scanner.close();
        context.close();
    }
}

The casts to JdbcTaskRepository above exist only because TaskRepository, as defined inline in Step 4, does not declare create()/update()/deleteById() on the interface itself; the Complete Code section below shows the cleaner version with all five operations on TaskRepository, matching how the interface should actually be written.

Complete Code

This is the interface, corrected to declare all five operations, together with the full JdbcTaskRepository implementation. Save each class under its matching file, add schema.sql under src/main/resources/, and run with mvn compile exec:java -Dexec.mainClass=com.programinds.taskcrud.TaskCrudApplication.

TaskRepository.java, JdbcTaskRepository.java (complete)

package com.programinds.taskcrud;

import java.util.List;

public interface TaskRepository {
    Task create(String title);
    Task findById(int id);
    List<Task> findAll();
    void update(Task task);
    void deleteById(int id);
}

// (separate file: JdbcTaskRepository.java)
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.PreparedStatement;
import java.sql.Statement;

class JdbcTaskRepositoryImpl implements TaskRepository {
    private final JdbcTemplate jdbcTemplate;
    private final TaskRowMapper rowMapper = new TaskRowMapper();

    JdbcTaskRepositoryImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public Task create(String title) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(
                "INSERT INTO tasks (title, done) VALUES (?, ?)",
                Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, title);
            ps.setBoolean(2, false);
            return ps;
        }, keyHolder);
        return new Task(keyHolder.getKey().intValue(), title, false);
    }

    @Override
    public Task findById(int id) {
        List<Task> results = jdbcTemplate.query(
            "SELECT id, title, done FROM tasks WHERE id = ?", rowMapper, id);
        return results.isEmpty() ? null : results.get(0);
    }

    @Override
    public List<Task> findAll() {
        return jdbcTemplate.query("SELECT id, title, done FROM tasks ORDER BY id", rowMapper);
    }

    @Override
    public void update(Task task) {
        jdbcTemplate.update("UPDATE tasks SET title = ?, done = ? WHERE id = ?",
            task.getTitle(), task.isDone(), task.getId());
    }

    @Override
    public void deleteById(int id) {
        jdbcTemplate.update("DELETE FROM tasks WHERE id = ?", id);
    }
}

Sample Run

Click Run to see what this code prints.

Extend This Project

Add a findByDone(boolean done) method using query() with a WHERE done = ? clause to filter the task list.
Replace the manual RowMapper with BeanPropertyRowMapper.newInstance(Task.class), and compare the column-to-property matching convention it relies on against the explicit version written here.
Switch the embedded H2 database for a real PostgreSQL or MySQL DataSource, changing only the AppConfig bean — confirming JdbcTaskRepository never needs to change.
Add an integration test using the real embedded H2 database (no mocking) alongside the mocked unit test from Step 7, and discuss what each one actually verifies that the other cannot.
Wrap create() and update() in a @Transactional-style manual PlatformTransactionManager to see how Spring JDBC handles multi-statement atomicity without an ORM.

Summary

You built a complete CRUD repository directly on top of JdbcTemplate, writing every SQL statement by hand and mapping every result row yourself with a RowMapper — the layer an ORM like Spring Data JPA exists to automate, now fully visible. query(), queryForObject()-style single-row handling, and update() covered every read and write this project needed, an embedded H2 DataSource gave the whole app a real (if temporary) database with zero external setup, and mocking JdbcTemplate with Mockito showed how to unit test a repository's own logic in isolation from the database underneath it.