Beginner

Data Access in Java SE 21 Fundamentals

Data access is fundamental to almost all Java applications. Even if you use JPA or Hibernate, ultimately everything relies on JDBC. This course covers the fundamentals of data access in t...

Technologies: Java 21 (LTS), JDBC 4.3, Maven, MySQL 8, Docker Level: Beginner to intermediate

Table of Contents

  1. Course Overview
  2. Project configuration and business case
  1. Connecting and querying the database
  1. Statements, PreparedStatement and ResultSets
  1. Batch inserts and updates
  1. Deleting records
  1. Architecture and the Template Method Pattern

1. Course Overview

Data access is fundamental to almost all Java applications. Even if you use JPA or Hibernate, ultimately everything relies on JDBC. This course covers the fundamentals of data access in the most recent Long-Term Support (LTS) version of Java SE, Java 21.

Major topics covered:

  • Complete CRUD operations on a database: Create, Read, Update, Delete
  • Using try-with-resources to properly handle closing connections and other resources
  • Using Optionals to handle null results
  • The Template Method Pattern to clean up the architecture and reduce code duplication

Prerequisites:

  • Basic knowledge of Java
  • Knowledge of Maven is useful but not required

2. Project configuration and business case

2.1 Introduction and versions used

Data access in Java has been stable since before Java 21, but several independently released features will be collected in this course. Version matching is one of the most common causes of problems in the help forums.

TechnologyVersion used in the course
Java (LTS)Java 21
JDBC4.3
MavenLatest stable version
IDEIntelliJ IDEA (any compatible IDE works)
DatabaseMySQL 8.0
JDBC drivermysql-connector-java 8.0.28

Important: Versions must match. Most support questions come from version incompatibility.

2.2 What you will build

The demonstration application is focused on the business case of a library. The application will allow you to retrieve, create, update and delete books (Book), categories and other associated entities. This is a simple use case, but sufficient to illustrate all database operations.

Source code is available via the exercise files or a GitHub repository.

2.3 Business focus — the verbose JDBC problem

Here is the fundamental problem with naive JDBC code. For a simple SELECT, the developer ends up with a very verbose block where the business logic is buried in infrastructure code:

// Exemple de JDBC verbeux — à NE PAS reproduire tel quel
Connection con = null;
PreparedStatement stmt = null;
ResultSet rset = null;
try {
    con = DriverManager.getConnection(url, username, password);
    stmt = con.prepareStatement("SELECT ID, TITLE FROM BOOK WHERE ID = ?");
    stmt.setLong(1, id);
    rset = stmt.executeQuery();
    if (rset.next()) {
        Book book = new Book();
        book.setId(rset.getLong("ID"));
        book.setTitle(rset.getString("TITLE"));
        return book;
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    if (rset != null) try { rset.close(); } catch (SQLException e) {}
    if (stmt != null) try { stmt.close(); } catch (SQLException e) {}
    if (con  != null) try { con.close();  } catch (SQLException e) {}
}

The actual business code — what the business really wants — comes down to this:

SELECT ID, TITLE FROM BOOK WHERE ID = ?

And extracting the returned object. Everything else is boilerplate. This course shows how to gradually improve this situation.

2.4 Creating the project with Maven Archetype

The following Maven command generates the standard project structure. It must be run in the parent directory where you want the project to be created.

mvn archetype:generate \
  -DarchetypeGroupId=org.apache.maven.archetypes \
  -DarchetypeArtifactId=maven-archetype-quickstart \
  -DarchetypeVersion=1.4 \
  -DgroupId=com.pluralsight \
  -DartifactId=library-app

The archetype automatically generates:

  • A basic pom.xml file
  • The src/main/java and src/test/java directory structure
  • A startup App.java class
  • A test AppTest.java

2.5 Project structure

library-app/
├── pom.xml
└── src/
    ├── main/
    │   └── java/
    │       └── com/
    │           └── pluralsight/
    │               └── App.java
    ├── test/
    │   └── java/
    │       └── com/
    │           └── pluralsight/
    │               └── AppTest.java
    └── site/
        └── site.xml

2.6 Database configuration with Docker

To create the database, this course uses Docker. All the companies consulted by the trainer in the last three years install their databases via Docker. If you don’t have it yet, download Docker Desktop for your operating system. It is also possible to install MySQL natively, but Docker is simpler.

Essential Docker commands:

# Démarrer le conteneur (depuis le répertoire contenant docker-compose.yml)
docker compose up -d

# Vérifier que le conteneur tourne
docker ps

# Arrêter le conteneur
docker compose down

2.7 Docker-compose.yml file

This YAML file must be created at the root of the project. It defines the MySQL container with all the necessary parameters.

version: '3.8'

networks:
  default:

services:
  db:
    image: mysql:8.0
    container_name: library
    ports:
      - 3306:3306
    volumes:
      - "./.data/db:/var/lib/mysql"
    environment:
      MYSQL_ROOT_PASSWORD: pass
      MYSQL_DATABASE: library_db

Key points:

  • image: mysql:8.0 — official MySQL version 8 image
  • container_name: library — container name
  • ports: 3306:3306 — exposure of standard MySQL port
  • volumes: Data is persisted in ./.data/db to survive reboots
  • MYSQL_ROOT_PASSWORD: pass — root password (for local development only)
  • MYSQL_DATABASE: library_db — the database automatically created on startup

Security Note: These credentials are for local development only. Never use such a simple password in production.

2.8 Module 2 Summary

  • Checking versions (Java 21 LTS, JDBC 4.3, Maven)
  • Running the Maven archetype to create the library-app project
  • Discovering the Java Maven standard structure
  • Configuring and starting MySQL database via Docker Compose
  • Verify that the container is operational before moving to the next module

3. Connecting and querying the database

3.1 The JDBC driver

Regardless of the connection method — JNDI lookup, connection pool, or direct connection — the first step is always to obtain the JDBC driver. It is the library that interprets all commands sent to the specific database engine (here MySQL).

The drivers are versioned and must match the database version:

  • For MySQL 8 → mysql-connector-java:8.0.28
  • For MySQL 5.7 → use the driver corresponding to version 5.x

Since JDBC 4.0, it is no longer necessary to use Class.forName(...) to manually load the driver. The DriverManager automatically loads it from the classpath.

3.2 Added MySQL dependency in pom.xml

The initial pom.xml generated by the archetype must be updated:

  • Change source/target compiler versions from 1.7 to 17
  • Remove obsolete JUnit dependency (version 3.8.1)
  • Add dependency for MySQL driver
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.pluralsight</groupId>
  <artifactId>library-app</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>library-app</name>
  <description>A simple library-app.</description>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>17</maven.compiler.source>
    <maven.compiler.target>17</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.28</version>
    </dependency>
  </dependencies>

  <build>
    <pluginManagement>
      <plugins>
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.7.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-jar-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

After adding, IntelliJ will offer to load Maven changes (“Load Maven Changes” button). Once loaded, the mysql-connector-java jar will be visible in the External Libraries of the project.

3.3 DriverManager

The DriverManager class is used to obtain a database connection. It takes a JDBC URL, a username and a password. It automatically searches for the appropriate driver in the classpath.

JDBC URL format for MySQL:

jdbc:mysql://<host>:<port>/<database>

Example: jdbc:mysql://localhost:3306/library_db

3.4 AbstractDao — centralize the connection

Rather than copy/paste the connection logic into each DAO, we create an AbstractDao class with a getConnection() method. All DAOs will extend this class to achieve a connection uniformly.

This centralizes connection information. If we later switch to a JNDI or a connection pool, we only need to modify one place.

package com.pluralsight.repository;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class AbstractDao {

    protected Connection getConnection() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/library_db";
        String username = "root";
        String password = "pass";

        return DriverManager.getConnection(url, username, password);
    }
}

Important points:

  • Method is protected — accessible only by subclasses
  • It declares throws SQLException — subclasses handle this exception or propagate it
  • The hard-coding of credentials here is intentional for simplicity; in production, we would use a properties file, JNDI, or environment variables

3.5 The Dao interface

The Dao interface defines the contract that all Data Access Object classes must respect. It is generic (<T>) to be reusable with any type of entity.

package com.pluralsight.repository;

import java.util.List;

public interface Dao<T> {

    List<T> findAll();
}

This interface is inspired by the way JPA, Hibernate, TopLink and EclipseLink define their data access contracts.

3.6 Model Book

package com.pluralsight.model;

public class Book {

    private long id;
    private String title;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

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

3.7 BookDao — first implementation with Statement

BookDao extends AbstractDao and implements the Dao<Book> interface. It uses a simple Statement and a ResultSet to query the BOOK table.

package com.pluralsight.repository;

import com.pluralsight.model.Book;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class BookDao extends AbstractDao implements Dao<Book> {

    @Override
    public List<Book> findAll() {
        List<Book> books = Collections.emptyList();
        String sql = "SELECT * FROM BOOK";

        try (
                Connection con = getConnection();
                Statement stmt = con.createStatement();
                ResultSet rset = stmt.executeQuery(sql);
        ) {
            books = new ArrayList<>();

            while (rset.next()) {
                Book book = new Book();
                book.setId(rset.getLong("id"));
                book.setTitle(rset.getString("title"));
                books.add(book);
            }

        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }
        return books;
    }
}

Key points:

  • try-with-resources: Connection, Statement and ResultSet are all declared in the try(...) clause. They will be automatically closed at the end of the block, even if an exception is thrown.
  • Reading by column name: rset.getLong("id") and rset.getString("title") — more robust than reading by index.
  • Collections.emptyList(): safe initialization of the list before the try.

3.8 Main application — App.java

package com.pluralsight;

import com.pluralsight.model.Book;
import com.pluralsight.repository.BookDao;
import com.pluralsight.repository.Dao;

import java.util.List;

public class App {
    public static void main(String[] args) {
        Dao<Book> bookDao = new BookDao();

        List<Book> books = bookDao.findAll();

        for (Book book : books) {
            System.out.println("Id: " + book.getId());
            System.out.println("Title: " + book.getTitle());
        }
    }
}

3.9 Creating the table in MySQL Workbench

To create the table via MySQL Workbench:

  1. Open MySQL Workbench and create a connection (host: localhost, port: 3306, user: root, default schema: library_db)
  2. Test the connection (Test Connection) then click OK
  3. In the left panel, right click on Tables → Create Table
  4. Table name: BOOK
  5. Columns:
  • id: INT, Primary Key, Not NULL, Auto Increment
  • title: VARCHAR(255)
  1. Click Apply — MySQL Workbench displays the generated SQL — click Apply again

The equivalent SQL generated:

CREATE TABLE `library_db`.`BOOK` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NULL,
  PRIMARY KEY (`id`)
);

3.10 Module 3 Summary

  • Added JDBC MySQL driver to pom.xml
  • Creating AbstractDao with centralized getConnection() method
  • Creation of the Dao<T> interface with findAll()
  • Implementing BookDao with Statement and ResultSet in a try-with-resources
  • Creating table BOOK in MySQL Workbench
  • Running the application and checking the returned data

4. Statements, PreparedStatements and ResultSets

4.1 The ResultSet in detail

The ResultSet interface (provided by the JDBC driver) contains the result of the operation executed against the database. The ResultSet cursor starts before the first line; we call rset.next() to move forward.

Best practice — read by column name rather than index:

// ✅ Par nom de colonne — préféré, moins sujet aux erreurs
book.setId(rset.getLong("ID"));
book.setTitle(rset.getString("TITLE"));

// ⚠️ Par index — fragile si l'ordre des colonnes change
book.setId(rset.getLong(1));
book.setTitle(rset.getString(2));

4.2 PreparedStatement — why use it

The PreparedStatement is the improved version of the Statement:

CriterionStatementPreparedStatement
Protection against SQL injection❌ No✅ Yes
Pre-compilation❌ No✅ Yes (more efficient)
Settings with automatic escape❌ No✅ Yes
Recommended UseQueries without parametersAlways if possible

Security: A generic Statement allows SQL injection attacks. The PreparedStatement automatically escapes parameters and compiles them before execution.

4.3 Optionals — handle null values

Optional<T> are a Java feature (introduced in Java 8) allowing you to properly handle objects that could be null. In the JDBC context, they are particularly useful for queries that may not return any results.

// Sans Optional — risque de NullPointerException
Book book = bookDao.findById(99); // peut retourner null
System.out.println(book.getTitle()); // NullPointerException si book est null !

// Avec Optional — gestion explicite du cas null
Optional<Book> optBook = bookDao.findById(99);
if (optBook.isPresent()) {
    System.out.println(optBook.get().getTitle());
}

// Ou avec ifPresent()
optBook.ifPresent(b -> System.out.println(b.getTitle()));

4.4 Dao interface updated with findById

package com.pluralsight.repository;

import java.util.List;
import java.util.Optional;

public interface Dao<T> {

    Optional<T> findById(long id);

    List<T> findAll();
}

4.5 BookDao with PreparedStatement and Optional

package com.pluralsight.repository;

import com.pluralsight.model.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

public class BookDao extends AbstractDao implements Dao<Book> {

    @Override
    public Optional<Book> findById(long id) {
        Optional<Book> book = Optional.empty();
        String sql = "SELECT ID, TITLE FROM BOOK WHERE ID = ?";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(sql);
        ) {
            prepStmt.setLong(1, id);  // Liaison du paramètre — protège contre SQL injection

            try (ResultSet rset = prepStmt.executeQuery()) {
                Book resBook = new Book();

                if (rset.next()) {
                    resBook.setId(rset.getLong("ID"));
                    resBook.setTitle(rset.getString("TITLE"));
                }
                book = Optional.of(resBook);
            }
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return book;
    }

    @Override
    public List<Book> findAll() {
        List<Book> books = Collections.emptyList();
        String sql = "SELECT * FROM BOOK";

        try (
                Connection con = getConnection();
                Statement stmt = con.createStatement();
                ResultSet rset = stmt.executeQuery(sql);
        ) {
            books = new ArrayList<>();

            while (rset.next()) {
                Book book = new Book();
                book.setId(rset.getLong("id"));
                book.setTitle(rset.getString("title"));
                books.add(book);
            }

        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }
        return books;
    }
}

Key points of findById:

  • Optional.empty() — safe default value
  • con.prepareStatement(sql) — SQL is compiled with ? as placeholder
  • prepStmt.setLong(1, id) — binding the parameter to index 1 (indexes start at 1 in JDBC)
  • ResultSet nested inside a second try-with-resources to ensure it closes
  • if (rset.next()) — checks if a record was found (no while loop for unique ID search)

4.6 App.java with findById

package com.pluralsight;

import com.pluralsight.model.Book;
import com.pluralsight.repository.BookDao;
import com.pluralsight.repository.Dao;

import java.util.List;
import java.util.Optional;

public class App {
    public static void main(String[] args) {
        Dao<Book> bookDao = new BookDao();

        List<Book> books = bookDao.findAll();

        for (Book book : books) {
            System.out.println("Id: " + book.getId());
            System.out.println("Title: " + book.getTitle());
        }

        Optional<Book> optBook = bookDao.findById(1);
        if (optBook.isPresent()) {
            Book book = optBook.get();
            System.out.println("Id: " + book.getId());
            System.out.println("Title: " + book.getTitle());
        }
    }
}

4.7 Summary of Module 4

  • The ResultSet is an interface whose implementation is provided by the JDBC driver
  • Reading columns by name is more robust than by index
  • PreparedStatement is preferred over generic Statement — it protects against SQL injections, is compiled ahead of time, and escapes parameters
  • Optional<T> allow elegantly handling the case where no record is found

5. Batch inserts and updates

5.1 Insert records — generated keys

Data insertion uses the same mechanisms as an update. An important real-world aspect: returning the automatically generated ID from the database after an INSERT.

To retrieve the generated keys, we pass Statement.RETURN_GENERATED_KEYS when preparing the statement:

PreparedStatement prepStmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

After executing executeUpdate(), an additional ResultSet containing the generated keys is available via prepStmt.getGeneratedKeys().

5.2 Update records

Update is almost the same as insert — only the SQL changes. We use executeUpdate() in both cases.

UPDATE BOOK SET TITLE = ? WHERE ID = ?

The method can also return the number of rows affected, which is useful for validating that the operation completed as expected.

5.3 Batch updates

batch update is the most efficient technique for updating a large number of records. The most expensive part of database operations is establishing the connection and preparing the statement. By grouping several operations in a single batch, this cost is minimized.

Steps of a batch update:

  1. Create the connection and prepare the statement
  2. For each record: configure the parameters then call prepStmt.addBatch()
  3. Call prepStmt.executeBatch() to send all records at once
  4. executeBatch() returns an int[] array with the number of rows affected for each operation

Before implementing the batch update in Java, a new rating column (of type INT) was added to the BOOK table via MySQL Workbench:

ALTER TABLE `library_db`.`BOOK`
ADD COLUMN `rating` INT NULL;

5.4 Complete Dao interface (create + update + batch)

package com.pluralsight.repository;

import java.util.List;
import java.util.Optional;

public interface Dao<T> {

    Optional<T> findById(long id);

    List<T> findAll();

    T create(T t);

    T update(T t);

    int[] update(List<T> t);
}

5.5 Book model with rating field

package com.pluralsight.model;

public class Book {

    private long id;
    private String title;
    private int rating;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

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

    public int getRating() {
        return rating;
    }

    public void setRating(int rating) {
        this.rating = rating;
    }
}

5.6 BookDao complete — create, update, batch update

package com.pluralsight.repository;

import com.pluralsight.model.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

public class BookDao extends AbstractDao implements Dao<Book> {

    @Override
    public Optional<Book> findById(long id) {
        Optional<Book> book = Optional.empty();
        String sql = "SELECT ID, TITLE FROM BOOK WHERE ID = ?";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(sql);
        ) {
            prepStmt.setLong(1, id);

            try (ResultSet rset = prepStmt.executeQuery()) {
                Book resBook = new Book();

                if (rset.next()) {
                    resBook.setId(rset.getLong("ID"));
                    resBook.setTitle(rset.getString("TITLE"));
                }
                book = Optional.of(resBook);
            }
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return book;
    }

    @Override
    public List<Book> findAll() {
        List<Book> books = Collections.emptyList();
        String sql = "SELECT * FROM BOOK";

        try (
                Connection con = getConnection();
                Statement stmt = con.createStatement();
                ResultSet rset = stmt.executeQuery(sql);
        ) {
            books = new ArrayList<>();

            while (rset.next()) {
                Book book = new Book();
                book.setId(rset.getLong("id"));
                book.setTitle(rset.getString("title"));
                books.add(book);
            }

        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }
        return books;
    }

    @Override
    public Book create(Book book) {
        String sql = "INSERT INTO BOOK (TITLE) VALUES (?)";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(
                        sql, Statement.RETURN_GENERATED_KEYS);  // Demande le retour des clés générées
        ) {
            prepStmt.setString(1, book.getTitle());
            prepStmt.executeUpdate();

            try (ResultSet genKeys = prepStmt.getGeneratedKeys()) {
                if (genKeys.next()) {
                    book.setId(genKeys.getLong(1));  // L'ID généré est assigné à l'objet retourné
                }
            }
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return book;
    }

    @Override
    public Book update(Book book) {
        String sql = "UPDATE BOOK SET TITLE = ? WHERE ID = ?";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(sql);
        ) {
            prepStmt.setString(1, book.getTitle());  // Paramètre 1 : nouveau titre
            prepStmt.setLong(2, book.getId());        // Paramètre 2 : ID du livre à modifier
            prepStmt.executeUpdate();
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return book;
    }

    @Override
    public int[] update(List<Book> books) {
        int[] records = {};
        String sql = "UPDATE BOOK SET TITLE = ?, RATING = ? WHERE ID = ?";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(sql);
        ) {
            for (Book book : books) {
                prepStmt.setString(1, book.getTitle());
                prepStmt.setInt(2, book.getRating());
                prepStmt.setLong(3, book.getId());

                prepStmt.addBatch();  // Ajoute chaque opération au batch
            }

            records = prepStmt.executeBatch();  // Exécute toutes les opérations en une seule fois
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return records;
    }
}

5.7 App.java — demo inserts and updates

package com.pluralsight;

import com.pluralsight.model.Book;
import com.pluralsight.repository.BookDao;
import com.pluralsight.repository.Dao;

import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

public class App {
    public static void main(String[] args) {
        Dao<Book> bookDao = new BookDao();

        // Lister tous les livres
        List<Book> books = bookDao.findAll();
        for (Book book : books) {
            System.out.println("Id: " + book.getId());
            System.out.println("Title: " + book.getTitle());
        }

        // Trouver et mettre à jour un livre par ID
        Optional<Book> optBook = bookDao.findById(1);
        if (optBook.isPresent()) {
            Book book = optBook.get();

            System.out.println("Id: " + book.getId());
            System.out.println("Title: " + book.getTitle());

            book.setTitle("Effective Java: Second Edition");
            bookDao.update(book);
        }

        // Créer un nouveau livre (commenté dans la démo finale)
        // Book newBook = new Book();
        // newBook.setTitle("The River Why");
        // newBook = bookDao.create(newBook);
        // System.out.println("Id: " + newBook.getId());
        // System.out.println("Title: " + newBook.getTitle());

        // Batch update — mettre à jour le rating de tous les livres avec Java Streams
        books = bookDao.findAll();

        List<Book> updatedEntries = books.stream()
                .peek(b -> b.setRating(5))   // peek() modifie chaque livre sans changer le stream
                .collect(Collectors.toList());

        bookDao.update(updatedEntries);
    }
}

Using Stream.peek() for batch update:

  • peek() allows you to modify each element of the stream without creating a new stream
  • This is an elegant way to configure the rating on all objects before sending them in batch to the database

5.8 Module 5 Summary

  • Insert uses PreparedStatement with Statement.RETURN_GENERATED_KEYS to retrieve generated ID
  • Unit update is almost identical to insert (only the SQL differs)
  • The batch update groups together several operations to minimize the cost of connections and preparation of statements
  • Using Java Streams with peek() is a modern approach to preparing data before a batch

6. Deleting records

6.1 Delete a record

Deleting a record is basically the same as updating — we use executeUpdate(). The main difference is that the SQL is a DELETE and you can choose to return the number of rows affected.

Returning the number of rows affected is useful for:

  • Validate that the deletion has taken place
  • Detect unexpected deletions (e.g.: several lines deleted when only one was expected)
  • Potentially trigger a rollback if the result is unexpected
DELETE FROM BOOK WHERE ID = ?

6.2 Batch deletes

Just like updates, deletions can be grouped in batches. This use case occurs in particular when:

  • Cleaning reporting data after processing
  • Merge accounts and remove duplicates
  • Archiving and purging temporary data

The code is identical to the batch update, with a DELETE FROM BOOK WHERE ID = ? in place of the UPDATE.

6.3 Final Dao interface with delete

package com.pluralsight.repository;

import java.util.List;
import java.util.Optional;

public interface Dao<T> {

    Optional<T> findById(long id);

    List<T> findAll();

    T create(T t);

    T update(T t);

    int[] update(List<T> t);

    int delete(T t);  // Retourne le nombre de lignes supprimées
}

6.4 BookDao with delete method

package com.pluralsight.repository;

import com.pluralsight.model.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

public class BookDao extends AbstractDao implements Dao<Book> {

    @Override
    public Optional<Book> findById(long id) {
        Optional<Book> book = Optional.empty();
        String sql = "SELECT ID, TITLE FROM BOOK WHERE ID = ?";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(sql);
        ) {
            prepStmt.setLong(1, id);

            try (ResultSet rset = prepStmt.executeQuery()) {
                Book resBook = new Book();

                if (rset.next()) {
                    resBook.setId(rset.getLong("ID"));
                    resBook.setTitle(rset.getString("TITLE"));
                }
                book = Optional.of(resBook);
            }
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return book;
    }

    @Override
    public List<Book> findAll() {
        List<Book> books = Collections.emptyList();
        String sql = "SELECT * FROM BOOK";

        try (
                Connection con = getConnection();
                Statement stmt = con.createStatement();
                ResultSet rset = stmt.executeQuery(sql);
        ) {
            books = new ArrayList<>();

            while (rset.next()) {
                Book book = new Book();
                book.setId(rset.getLong("id"));
                book.setTitle(rset.getString("title"));
                books.add(book);
            }

        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }
        return books;
    }

    @Override
    public Book create(Book book) {
        String sql = "INSERT INTO BOOK (TITLE) VALUES (?)";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(
                        sql, Statement.RETURN_GENERATED_KEYS);
        ) {
            prepStmt.setString(1, book.getTitle());
            prepStmt.executeUpdate();

            try (ResultSet genKeys = prepStmt.getGeneratedKeys()) {
                if (genKeys.next()) {
                    book.setId(genKeys.getLong(1));
                }
            }
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return book;
    }

    @Override
    public Book update(Book book) {
        String sql = "UPDATE BOOK SET TITLE = ? WHERE ID = ?";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(sql);
        ) {
            prepStmt.setString(1, book.getTitle());
            prepStmt.setLong(2, book.getId());
            prepStmt.executeUpdate();
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return book;
    }

    @Override
    public int[] update(List<Book> books) {
        int[] records = {};
        String sql = "UPDATE BOOK SET TITLE = ?, RATING = ? WHERE ID = ?";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(sql);
        ) {
            for (Book book : books) {
                prepStmt.setString(1, book.getTitle());
                prepStmt.setInt(2, book.getRating());
                prepStmt.setLong(3, book.getId());
                prepStmt.addBatch();
            }

            records = prepStmt.executeBatch();
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return records;
    }

    @Override
    public int delete(Book book) {
        int rowsAffected = 0;
        String sql = "DELETE FROM BOOK WHERE ID = ?";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(sql);
        ) {
            prepStmt.setLong(1, book.getId());
            rowsAffected = prepStmt.executeUpdate();  // Retourne le nombre de lignes supprimées
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return rowsAffected;
    }
}

6.5 App.java — delete demonstration

package com.pluralsight;

import com.pluralsight.model.Book;
import com.pluralsight.repository.BookDao;
import com.pluralsight.repository.Dao;

import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

public class App {
    public static void main(String[] args) {
        Dao<Book> bookDao = new BookDao();

        List<Book> books = bookDao.findAll();
        for (Book book : books) {
            System.out.println("Id: " + book.getId());
            System.out.println("Title: " + book.getTitle());
        }

        Optional<Book> optBook = bookDao.findById(1);
        if (optBook.isPresent()) {
            Book book = optBook.get();

            System.out.println("Id: " + book.getId());
            System.out.println("Title: " + book.getTitle());

            book.setTitle("Effective Java: Second Edition");
            bookDao.update(book);
        }

        // Créer un livre puis le supprimer immédiatement
        Book newBook = new Book();
        newBook.setTitle("The River Why");
        newBook = bookDao.create(newBook);

        System.out.println("Id: " + newBook.getId());
        System.out.println("Title: " + newBook.getTitle());

        int numDel = bookDao.delete(newBook);
        System.out.println("Number of records deleted: " + numDel);

        // Batch update commenté pour cette démo
        // books = bookDao.findAll();
        // List<Book> updatedEntries = books.stream()
        //         .peek(b -> b.setRating(5))
        //         .collect(Collectors.toList());
        // bookDao.update(updatedEntries);
    }
}

6.6 Summary of module 6

  • The DELETE uses executeUpdate() — exactly like an UPDATE
  • Returning the number of rows affected (int rowsAffected) validates the operation
  • Deletions can also be grouped in batch (addBatch() + executeBatch())
  • Dao<T> interface is now complete with all CRUD operations

7. Architecture and the Template Method Pattern

7.1 The problem — JDBC code duplication

By observing the code written throughout the course, several architectural problems emerge:

1. Code duplication (copy and paste) For each new JDBC method, the same try-with-resources block is copied: getConnection(), prepareStatement(), ResultSet handling, catch(SQLException). This is the first red flag that something needs to be refactored.

2. The business focus is drowned out Business doesn’t care if you get a connection or prepare a statement. What interests him is the logic of data recovery.

3. Exception handling clutters the code Try-with-resources is a good solution for automatically closing resources, but it visibly burdens each method.

4. Testability is low The current implementation mixes JDBC infrastructure and business logic, making unit tests very difficult to write.

7.2 The Template Method Pattern — the solution

The Template Method Pattern (or a pattern close to the Strategy Pattern) consists of encapsulating the entire redundant JDBC infrastructure in an abstract class, delegating only the variable part (the data mapping) to an abstract method that the subclasses implement.

Benefits obtained:

Initial problemSolution provided
Code copied and pastedWrapped only once in JdbcQueryTemplate
Blurred business focusThe abstract method mapItem() contains only business logic
Cumbersome ExceptionsManaged only once in the template class
Low testabilityBetter adherence to SOLID principles

This pattern is directly inspired by what Spring JDBC does internally with JdbcTemplate.

7.3 JdbcQueryTemplate — generic abstract class

package com.pluralsight.repository;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public abstract class JdbcQueryTemplate<T> extends AbstractDao {

    public JdbcQueryTemplate() {
        // Constructeur sans arguments — contrôle le contrat de création
    }

    /**
     * Exécute la requête SQL et retourne une liste d'objets T
     * en déléguant le mapping de chaque ligne à mapItem().
     */
    public List<T> queryForList(String sql) {
        List<T> items = new ArrayList<>();

        try (
                Connection con = getConnection();
                Statement stmt = con.createStatement();
                ResultSet rset = stmt.executeQuery(sql);
        ) {
            while (rset.next()) {
                items.add(mapItem(rset));   // Délègue le mapping à la sous-classe
            }
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }
        return items;
    }

    /**
     * Méthode abstraite que les sous-classes DOIVENT implémenter.
     * Elle définit comment transformer une ligne du ResultSet en objet T.
     */
    public abstract T mapItem(ResultSet rset) throws SQLException;
}

Architecture:

  • JdbcQueryTemplate<T> is abstract and generic
  • It extends AbstractDao to access getConnection()
  • queryForList() contains all JDBC boilerplate
  • mapItem() is the only variable part — it must be implemented by anonymous classes or subclasses
  • All try-with-resources, exception handling and iteration of the ResultSet are encapsulated here

7.4 BookDao refactored with JdbcQueryTemplate

package com.pluralsight.repository;

import com.pluralsight.model.Book;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

public class BookDao extends AbstractDao implements Dao<Book> {

    @Override
    public Optional<Book> findById(long id) {
        Optional<Book> book = Optional.empty();
        String sql = "SELECT ID, TITLE FROM BOOK WHERE ID = ?";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(sql);
        ) {
            prepStmt.setLong(1, id);

            try (ResultSet rset = prepStmt.executeQuery()) {
                Book resBook = new Book();

                if (rset.next()) {
                    resBook.setId(rset.getLong("ID"));
                    resBook.setTitle(rset.getString("TITLE"));
                }
                book = Optional.of(resBook);
            }
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return book;
    }

    /**
     * findAll() refactorisé avec JdbcQueryTemplate.
     * Tout le boilerplate JDBC disparaît — il ne reste que la logique métier dans mapItem().
     */
    @Override
    public List<Book> findAll() {
        List<Book> books = Collections.emptyList();

        // Création d'une instance anonyme de JdbcQueryTemplate<Book>
        // On n'implémente QUE la méthode mapItem() — le reste est dans la classe template
        JdbcQueryTemplate<Book> template = new JdbcQueryTemplate<Book>() {
            @Override
            public Book mapItem(ResultSet rset) throws SQLException {
                Book book = new Book();
                book.setId(rset.getLong("ID"));
                book.setTitle(rset.getString("TITLE"));
                book.setRating(rset.getInt("RATING"));
                return book;
            }
        };

        books = template.queryForList("SELECT ID, TITLE, RATING FROM BOOK");

        return books;
    }

    @Override
    public Book create(Book book) {
        String sql = "INSERT INTO BOOK (TITLE) VALUES (?)";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(
                        sql, Statement.RETURN_GENERATED_KEYS);
        ) {
            prepStmt.setString(1, book.getTitle());
            prepStmt.executeUpdate();

            try (ResultSet genKeys = prepStmt.getGeneratedKeys()) {
                if (genKeys.next()) {
                    book.setId(genKeys.getLong(1));
                }
            }
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return book;
    }

    @Override
    public Book update(Book book) {
        String sql = "UPDATE BOOK SET TITLE = ? WHERE ID = ?";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(sql);
        ) {
            prepStmt.setString(1, book.getTitle());
            prepStmt.setLong(2, book.getId());
            prepStmt.executeUpdate();
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return book;
    }

    @Override
    public int[] update(List<Book> books) {
        int[] records = {};
        String sql = "UPDATE BOOK SET TITLE = ?, RATING = ? WHERE ID = ?";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(sql);
        ) {
            for (Book book : books) {
                prepStmt.setString(1, book.getTitle());
                prepStmt.setInt(2, book.getRating());
                prepStmt.setLong(3, book.getId());
                prepStmt.addBatch();
            }

            records = prepStmt.executeBatch();
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return records;
    }

    @Override
    public int delete(Book book) {
        int rowsAffected = 0;
        String sql = "DELETE FROM BOOK WHERE ID = ?";

        try (
                Connection con = getConnection();
                PreparedStatement prepStmt = con.prepareStatement(sql);
        ) {
            prepStmt.setLong(1, book.getId());
            rowsAffected = prepStmt.executeUpdate();
        } catch (SQLException sqe) {
            sqe.printStackTrace();
        }

        return rowsAffected;
    }
}

Before/after comparison for findAll():

Before (without template)After (with JdbcQueryTemplate)
Connection, Statement, ResultSet, catchOnly mapItem() to implement
~20 boilerplate lines~8 lines focused on the profession
Infrastructure logic mixed with businessClear separation of responsibilities

7.5 App.java final

package com.pluralsight;

import com.pluralsight.model.Book;
import com.pluralsight.repository.BookDao;
import com.pluralsight.repository.Dao;

import java.util.List;

public class App {
    public static void main(String[] args) {
        Dao<Book> bookDao = new BookDao();

        // findAll() utilise maintenant JdbcQueryTemplate en interne
        List<Book> books = bookDao.findAll();

        for (Book book : books) {
            System.out.println("Id: " + book.getId());
            System.out.println("Title: " + book.getTitle());
            System.out.println("Rating: " + book.getRating());
        }
    }
}

7.6 Summary of module 7 and conclusion

The last module addressed the architectural issues inherent in naively written JDBC code:

  1. Duplication — try-with-resources copied and pasted into each method
  2. Fuzzy business focus — infrastructure logic obscures business logic
  3. Excessive verbosity — exception handling clutters the code
  4. Low testability — mixing responsibilities complicates unit testing

The solution — the Template Method Pattern implemented via JdbcQueryTemplate<T> — encapsulates the JDBC boilerplate only once. The abstract method mapItem() becomes the only point of variation, focusing only on what interests the business.

This pattern is directly inspired by what Spring JDBC does with its JdbcTemplate, and the way Hibernate and JPA encapsulate JDBC internally.

8. Summary — Evolution of the project architecture

Final package structure

src/
└── main/
    └── java/
        └── com/
            └── pluralsight/
                ├── App.java
                ├── model/
                │   └── Book.java
                └── repository/
                    ├── AbstractDao.java
                    ├── Dao.java               (interface générique)
                    ├── BookDao.java
                    └── JdbcQueryTemplate.java (module 7)

Evolution of the Dao interface

ModuleAvailable methods
3findAll()
4findAll(), findById(long id)
5findAll(), findById(), create(T), update(T), update(List<T>)
6findAll(), findById(), create(T), update(T), update(List<T>), delete(T)

Class hierarchy

AbstractDao
    └── BookDao implements Dao<Book>

JdbcQueryTemplate<T> extends AbstractDao (abstract)
    └── Classes anonymes dans BookDao.findAll()

Docker Commands Reference

# Démarrer la base de données
docker compose up -d

# Vérifier l'état
docker ps

# Voir les logs du conteneur
docker logs library

# Arrêter et supprimer les conteneurs (les données persistent dans .data/)
docker compose down

Reference SQL — BOOK table structure

CREATE TABLE `library_db`.`BOOK` (
  `id`     INT         NOT NULL AUTO_INCREMENT,
  `title`  VARCHAR(255) NULL,
  `rating` INT          NULL,
  PRIMARY KEY (`id`)
);

9. Key Takeaways

  • JDBC is the foundation of all data access in Java, even with JPA or Hibernate
  • Since JDBC 4.0, Class.forName() is no longer necessary — the driver is loaded automatically
  • Always prefer PreparedStatement to generic Statement — SQL injection protection and better performance
  • try-with-resources ensures automatic closing of Connection, Statement and ResultSet
  • Optionals avoid NullPointerException on query results
  • batch update (addBatch() + executeBatch()) is the most efficient solution for bulk operations — establishing the connection and preparing the statement is the most expensive operation
  • Access to ResultSet columns by name is more robust than by index
  • The Template Method Pattern applied to JDBC separates the infrastructure from the business code — it is the direct inspiration of JdbcTemplate in Spring JDBC

Search Terms

data · access · java · se · fundamentals · backend · architecture · full-stack · web · app.java · batch · bookdao · dao · interface · delete · book · jdbc · method · records · update · updates · business · class · configuration

More Java, Backend Architecture & Full-Stack courses

View all 30

Full-stack Java Development with Spring Boot 3 and React

A job-portal app — Spring Boot 3 backend, JWT/OAuth2 auth, a React frontend and Docker CI/CD on AWS.

Intermediate

Full-stack Java Development with Play Framework and React

Non-blocking REST APIs and WebSockets with Play and Akka Streams, a React+Redux UI and AWS deployment.

Advanced

Microservices Foundations

What microservices are, why and when to use them, communication patterns and how to adopt them.

Beginner

Asynchronous Programming in Java

By the end of this course, you will be fully equipped to understand the structure of the CompletableFuture API and navigate its many methods.

Intermediate

Building a REST API with Python 3

In this course you will learn how to build a complete Python project from scratch. You will use the excellent and very popular FastAPI framework to build a REST API that serves data from...

Intermediate

Building an Application Using Java SE

This course teaches how to build a true end-to-end Java application, using industry standard tools, libraries, and practices. The objective is not to learn a particular framework, but to...

Beginner

Interested in this course?

Contact us to book it or get a custom training plan for your team.

Get in Touch Back to Java, Backend Architecture & Full-Stack