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.