GymTrack Modules 9 to 11

Why a database now?

Through Module 8, GymTrack reads workout_log.json from the classpath on every run. That file is fixed at build time — the app can read a log but it can't record one. The moment a coach wants to log today's session and see it next week, you need persistence that survives restarts and lets many writers add rows safely.

This part adds a relational store with the standard Java database API, JDBC. It mirrors the back-end persistence work any real product reaches eventually: design tables, hide SQL behind a data-access layer, and wrap multi-row writes in transactions.

Concept map · what carries overorientation

Earlier GymTrack idea	Module 9–11 analog
`WorkoutDataProvider` interface (hides the source)	`ExerciseDAO` / `WorkoutSessionDAO` (hide the SQL)
`ObjectMapper.readValue(...)` → POJO	`ResultSet` → POJO mapping
Factory wires impl + provider	`DatabaseConnection` singleton wires the driver + URL
Failover keeps the app working	Transactions keep the data consistent
JSON file as the data source	MySQL tables as the data source

The architecture habit is identical to what you already practiced: program against a small interface, let one class own the messy details, and keep the rest of the app unaware of where data lives.

How JDBC actually works

JDBC (Java Database Connectivity) is a standard Java API that does three things: connect to a SQL database, send SQL, and process results. It's also a spec telling each vendor how to write a driver, so your code stays the same whether it runs on MySQL or Postgres — you just swap the driver jar.

Architecture: App → JDBC API → DriverManager → Driver → DB. The DriverManager reads your connection string (the JDBC URL) and routes you to the matching vendor driver, which translates generic JDBC calls into native MySQL calls.

Common misconception DriverManager is not the driver. DriverManager ships in the standard library (java.sql); the driver is the vendor jar on your classpath (here mysql-connector-j).

The six connection steps

Every JDBC program is a variation of this recipe. In GymTrack, the singleton owns steps 1–3 and 6; your DAO methods are steps 4–5.

Load the driver Class.forName("com.mysql.cj.jdbc.Driver"). [+] Since JDBC 4.0 the jar auto-registers, so this is optional — but doing it makes the step explicit.

Build the URL jdbc:mysql://localhost:3306/gymtrack — protocol, host, port, database.

Establish the connection DriverManager.getConnection(url, user, pwd) → a Connection.

Execute a PreparedStatement: executeQuery() for SELECT, executeUpdate() for INSERT/UPDATE/DELETE.

Process the ResultSet row by row with rs.next() and getXXX(...).

Close — release resources, or you leak connections until the pool runs dry.

Why it matters Steps 1–3 set up access; step 6 cleans up. Forgetting step 6 is a classic production bug: the database is healthy, but every connection slot is held by a leaked session, so new requests fail. GymTrack's DAOs use try-with-resources on every statement and result set so closing happens automatically.

Design the schemadata model

Three tables capture the domain you already modeled in JSON. An exercise is a named movement; a session is one training day for a user; a set entry is one logged set (the sets × reps × weight you computed as volume in Module 1) belonging to a session and an exercise.

exercises

idINT · PK
nameVARCHAR · UNIQUE
muscle_groupVARCHAR

sessions

idINT · PK
user_nameVARCHAR
session_dateDATE
created_atTIMESTAMP

set_entries

idINT · PK
session_id→ sessions.id
exercise_id→ exercises.id
set_noINT
repsINT
weight_kgDECIMAL

● primary key● foreign key

This is a one-to-many chain: a session has many set entries; an exercise has many set entries. The same relational shape behind almost every app — orders & line items, polls & choices, sessions & sets.

resources/init-schema.sql

Create tables in dependency order — a table can only reference one that already exists, so exercises and sessions come before set_entries.

app/src/main/resources/init-schema.sql

-- drop in reverse-dependency order so re-runs are clean
DROP TABLE IF EXISTS set_entries;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS exercises;

CREATE TABLE exercises (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(120) NOT NULL UNIQUE,
    muscle_group  VARCHAR(60) NOT NULL
);

CREATE TABLE sessions (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    user_name     VARCHAR(120) NOT NULL,
    session_date  DATE NOT NULL,
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE set_entries (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    session_id   INT NOT NULL,
    exercise_id  INT NOT NULL,
    set_no       INT NOT NULL,
    reps         INT NOT NULL,
    weight_kg    DECIMAL(6,2) NOT NULL,
    FOREIGN KEY (session_id)  REFERENCES sessions(id),
    FOREIGN KEY (exercise_id) REFERENCES exercises(id)
);

Read me AUTO_INCREMENT is what makes MySQL hand back a generated id on insert — the DAOs lean on this constantly. UNIQUE on exercises.name means you can't accidentally store "Bench Press" twice; the database enforces it, not fragile Java checks.

Connection + externalized configsteps 1–3

One class folds the first three connection steps into a reusable singleton. The app shares one Connection (opening connections is expensive), and credentials live in a properties file — never hardcoded.

resources/db.properties

app/src/main/resources/db.properties

# key=value — swap this file to point at dev / test / prod, no recompile
db.url=jdbc:mysql://localhost:3306/gymtrack
db.user=gymtrack
db.password=gymtrack
db.driver=com.mysql.cj.jdbc.Driver

util/DatabaseConnection.java

app/src/main/java/org/gymtrack/util/DatabaseConnection.java

package org.gymtrack.util;

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

public class DatabaseConnection {

    private final String url, user, password;
    private Connection connection;
    private static DatabaseConnection instance;

    private DatabaseConnection(String url, String user,
                               String password, String driver) {
        this.url = url; this.user = user; this.password = password;
        try {
            Class.forName(driver);          // STEP 1: load the driver
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Driver not on classpath", e);
        }
    }

    public static synchronized DatabaseConnection getInstance(
            String url, String user, String password, String driver) {
        if (instance == null) {
            instance = new DatabaseConnection(url, user, password, driver);
        }
        return instance;
    }

    public Connection getConnection() throws SQLException {
        if (connection == null || connection.isClosed()) {
            // STEPS 2-3: URL already built; open the connection
            connection = DriverManager.getConnection(url, user, password);
        }
        return connection;
    }
}

Consequence for your DAOs The connection is shared and long-lived, so a DAO must never close the Connection — that would break the next caller. You do close PreparedStatement and ResultSet, and try-with-resources does that for you. This is the same "one owner for the messy resource" rule you used for the thread pool's shutdown() in Module 8.

The three access patternssteps 4–5

Every DAO method is one of three shapes, mapping directly to CRUD:

Operation	SQL	Method	Returns
Read	SELECT	`executeQuery()`	`ResultSet`
Create	INSERT	`executeUpdate()`	`int` rows
Update	UPDATE	`executeUpdate()`	`int` rows
Delete	DELETE	`executeUpdate()`	`int` rows

Every statement is a PreparedStatement — precompiled SQL with ? placeholders you fill with typed setters. The key reason is SQL-injection safety: parameters travel to the database separately from the SQL text, so a malicious value like ' OR '1'='1 bound via setString is treated as literal text, never as SQL. [+] Think of it as a fill-in-the-blank form — the blanks can't smuggle in instructions.

Common pitfall · everything is 1-based Placeholder positions (ps.setInt(1, ...)) and result columns (rs.getInt(1)) start at 1, not 0. getInt(0) throws Column Index out of range. The cursor also starts before the first row, so you must call rs.next() once before reading.

Pattern A · INSERT + read the generated id

pattern · create

String sql = "INSERT INTO t (col) VALUES (?)";
try (PreparedStatement ps =
         conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    ps.setString(1, value);            // bind ?#1 (1-based)
    ps.executeUpdate();                // INSERT → rows affected
    try (ResultSet keys = ps.getGeneratedKeys()) {
        keys.next();                   // move onto the single key row
        int id = keys.getInt(1);       // the auto id MySQL made
    }
}

Pattern B · SELECT and map rows

pattern · read

try (PreparedStatement ps = conn.prepareStatement(
         "SELECT * FROM t WHERE id = ?")) {
    ps.setInt(1, id);
    try (ResultSet rs = ps.executeQuery()) {   // SELECT → ResultSet
        while (rs.next()) {              // next() advances; false at end
            String c = rs.getString("col");  // read by column NAME
        }
    }
}

Pattern C · UPDATE / DELETE

pattern · update

try (PreparedStatement ps = conn.prepareStatement(
         "UPDATE t SET col = ? WHERE id = ?")) {
    ps.setString(1, newVal);
    ps.setInt(2, id);
    int rows = ps.executeUpdate();   // rows matched by WHERE (0 is valid)
}

Why try-with-resources everywhere A try (Resource r = ...) block calls r.close() the instant it ends, even on exception — step 6 done automatically for statements and result sets. We deliberately leave the shared Connection out of these blocks so it stays open for the next caller.

M10

ExerciseDAO — the lookup layer

The DAO (Data Access Object) is the database equivalent of the WorkoutDataProvider you built in Module 3: a small class that hides how data is stored so the rest of the app just calls methods. ExerciseDAO handles the exercises table — create one and look one up by name (you'll need its id when logging sets).

Models stay immutable, the same discipline as OverloadResult: read raw columns out of the ResultSet and build a finished object through its constructor.

model/Exercise.java

app/src/main/java/org/gymtrack/model/Exercise.java

package org.gymtrack.model;

public class Exercise {
    private final int id;
    private final String name;
    private final String muscleGroup;

    public Exercise(int id, String name, String muscleGroup) {
        this.id = id; this.name = name; this.muscleGroup = muscleGroup;
    }
    public int getId() { return id; }
    public String getName() { return name; }
    public String getMuscleGroup() { return muscleGroup; }
}

dao/ExerciseDAO.java

app/src/main/java/org/gymtrack/dao/ExerciseDAO.java

package org.gymtrack.dao;

import org.gymtrack.model.Exercise;
import org.gymtrack.util.DatabaseConnection;
import java.sql.*;

public class ExerciseDAO {

    private final DatabaseConnection db;
    public ExerciseDAO(DatabaseConnection db) { this.db = db; }

    /** Pattern A — insert an exercise, return it with its generated id. */
    public Exercise create(String name, String muscleGroup) throws SQLException {
        String sql = "INSERT INTO exercises (name, muscle_group) VALUES (?, ?)";
        Connection conn = db.getConnection();
        try (PreparedStatement ps =
                 conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, name);
            ps.setString(2, muscleGroup);
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                keys.next();
                return new Exercise(keys.getInt(1), name, muscleGroup);
            }
        }
    }

    /** Pattern B — find by name; returns null if there's no match. */
    public Exercise findByName(String name) throws SQLException {
        String sql = "SELECT * FROM exercises WHERE name = ?";
        Connection conn = db.getConnection();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return new Exercise(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("muscle_group"));
                }
                return null;            // no exercise with that name
            }
        }
    }

    /** Insert if missing, otherwise return the existing row. Handy when seeding. */
    public Exercise findOrCreate(String name, String muscleGroup)
            throws SQLException {
        Exercise existing = findByName(name);
        return existing != null ? existing : create(name, muscleGroup);
    }
}

Line of the lesson In create, keys.next() moves the cursor onto the single row of generated keys; getInt(1) reads the first (1-based) column — the new id. In findByName, if (rs.next()) guards a single-row read and we return null when nothing matched. Both are the exact ResultSet cursor rules.

Try it Add findById(int id) using Pattern B, then write a JUnit test that creates "Bench Press", looks it up by id, and asserts the name and muscle group round-trip correctly. This is your first read-your-own-write test.

M10 · part 1 done

Immutable Exercise model mapped from a ResultSet
ExerciseDAO.create (Pattern A) returns the generated id
findByName / findOrCreate (Pattern B) with null-on-miss

WorkoutSessionDAO — the transactional write

Logging a session is the most instructive method in this part, and the reason you need transactions. Saving "Tuesday's workout" means inserting one session row plus every set entry under it. Those writes must all succeed or all fail — a session with half its sets is corrupt data, the database equivalent of money disappearing mid-transfer.

A transaction groups multiple operations into a single all-or-nothing unit. That "all-or-nothing" property is atomicity, the A in ACID. By default a JDBC connection is in auto-commit mode — every statement commits on its own — so to group statements you must turn auto-commit off and control commit/rollback yourself.

model/SetEntry.java

app/src/main/java/org/gymtrack/model/SetEntry.java

package org.gymtrack.model;

public class SetEntry {
    private final int exerciseId, setNo, reps;
    private final double weightKg;

    public SetEntry(int exerciseId, int setNo, int reps, double weightKg) {
        this.exerciseId = exerciseId; this.setNo = setNo;
        this.reps = reps; this.weightKg = weightKg;
    }
    public int getExerciseId() { return exerciseId; }
    public int getSetNo() { return setNo; }
    public int getReps() { return reps; }
    public double getWeightKg() { return weightKg; }
}

dao/WorkoutSessionDAO.java

app/src/main/java/org/gymtrack/dao/WorkoutSessionDAO.java

package org.gymtrack.dao;

import org.gymtrack.model.SetEntry;
import org.gymtrack.util.DatabaseConnection;
import java.sql.*;
import java.util.List;

public class WorkoutSessionDAO {

    private final DatabaseConnection db;
    public WorkoutSessionDAO(DatabaseConnection db) { this.db = db; }

    /**
     * Log a whole session atomically: one session row + all its set entries.
     * Either everything saves, or nothing does.
     */
    public int logSession(String userName, String date,
                          List<SetEntry> sets) throws SQLException {

        String sessionSql =
            "INSERT INTO sessions (user_name, session_date) VALUES (?, ?)";
        String setSql =
            "INSERT INTO set_entries " +
            "(session_id, exercise_id, set_no, reps, weight_kg) " +
            "VALUES (?, ?, ?, ?, ?)";

        Connection conn = db.getConnection();
        conn.setAutoCommit(false);                  // BEGIN transaction

        try (PreparedStatement sessionPs =
                 conn.prepareStatement(sessionSql, Statement.RETURN_GENERATED_KEYS);
             PreparedStatement setPs = conn.prepareStatement(setSql)) {

            // 1. insert the session, grab its id
            sessionPs.setString(1, userName);
            sessionPs.setString(2, date);
            sessionPs.executeUpdate();

            int sessionId;
            try (ResultSet keys = sessionPs.getGeneratedKeys()) {
                keys.next();
                sessionId = keys.getInt(1);
            }

            // 2. insert every set, all tied to this session
            for (SetEntry s : sets) {
                setPs.setInt(1, sessionId);
                setPs.setInt(2, s.getExerciseId());
                setPs.setInt(3, s.getSetNo());
                setPs.setInt(4, s.getReps());
                setPs.setDouble(5, s.getWeightKg());
                setPs.executeUpdate();
            }

            conn.commit();                          // all good → persist together
            return sessionId;

        } catch (SQLException e) {
            conn.rollback();                        // any failure → undo everything
            throw e;
        } finally {
            conn.setAutoCommit(true);                // restore default for next caller
        }
    }
}

Why a transaction here A session with only half its sets is the in-between state that makes data untrustworthy. Wrapping the inserts in one transaction guarantees the session and all its sets appear together or not at all — the database-layer version of Module 6's failover keeping the app consistent, applied now to the data. ExerciseDAO.create is a single statement, so it's atomic on its own and needs no explicit transaction.

Common pitfall · the beginner bug 1. Forgetting setAutoCommit(false) — auto-commit is ON by default, so each insert commits itself and rollback() can't reverse the earlier ones. 2. Not restoring setAutoCommit(true) in finally — the shared connection stays in transaction mode and poisons later calls. 3. Using executeQuery on an INSERT — it expects a ResultSet and throws; writes always use executeUpdate.

Try it Force a rollback to prove it works: pass a set whose exerciseId doesn't exist. The foreign key makes MySQL reject that insert, the catch rolls back, and a follow-up SELECT COUNT(*) FROM sessions shows the session row never landed either. Then comment out setAutoCommit(false) and watch the orphan session survive — the exact bug to avoid.

M10 · part 2 done

Immutable SetEntry model
logSession writes one session + N sets in a single transaction
setAutoCommit(false) → work → commit() / rollback() → restore

M11

The progress summary — JOIN + GROUP BY

This is the payoff read, and it's the database doing the work you previously did in Java loops. For a given user, show each exercise with its top weight and total sets logged — the persistent version of the volume summaries from Module 1, now computed across all sessions ever recorded.

Let the database aggregate. One JOIN + GROUP BY beats pulling thousands of rows into Java and tallying them by hand — less data over the wire, less memory, and the DB is built for exactly this.

model/ProgressRow.java

app/src/main/java/org/gymtrack/model/ProgressRow.java

package org.gymtrack.model;

public class ProgressRow {
    private final String exercise;
    private final int totalSets;
    private final double topWeightKg;

    public ProgressRow(String exercise, int totalSets, double topWeightKg) {
        this.exercise = exercise; this.totalSets = totalSets;
        this.topWeightKg = topWeightKg;
    }
    public String getExercise() { return exercise; }
    public int getTotalSets() { return totalSets; }
    public double getTopWeightKg() { return topWeightKg; }
}

dao/WorkoutSessionDAO.java (add this read method)

WorkoutSessionDAO.java · getProgress

public List<ProgressRow> getProgress(String userName)
        throws SQLException {

    String sql =
        "SELECT e.name AS exercise, " +
        "       COUNT(se.id) AS total_sets, " +
        "       MAX(se.weight_kg) AS top_weight " +
        "FROM exercises e " +
        "JOIN set_entries se ON se.exercise_id = e.id " +
        "JOIN sessions s    ON s.id = se.session_id " +
        "WHERE s.user_name = ? " +
        "GROUP BY e.id, e.name " +
        "ORDER BY top_weight DESC";

    List<ProgressRow> rows = new ArrayList<>();
    Connection conn = db.getConnection();
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, userName);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                rows.add(new ProgressRow(
                    rs.getString("exercise"),
                    rs.getInt("total_sets"),
                    rs.getDouble("top_weight")));
            }
        }
    }
    return rows;
}

Reading the query

JOIN set_entries → sessions chains each set up to the user who logged it.
WHERE s.user_name = ? scopes to one user — bound as a parameter, so it's injection-safe.
COUNT(se.id) tallies sets per exercise; MAX(se.weight_kg) finds the heaviest.
GROUP BY e.id collapses the joined rows into one line per exercise; ORDER BY ranks them — the SQL version of the Comparators from Module 2.

The big idea The result columns (exercise, total_sets, top_weight) are read by name in step 5 and mapped straight into immutable ProgressRow objects — the same ResultSet → POJO move as every read, just over aggregated data instead of raw rows.

Try it Add a since date filter (AND s.session_date >= ?) so coaches can see progress over the last 90 days only. You'll bind a second parameter at index 2 — a reminder that placeholder order is 1-based and matches the ? order in the SQL.

Wire it into App

The entry point walks the six steps from the top: load config with Properties, build the connection singleton (which loads the driver and opens the connection), run the schema, then call the DAOs. No hardcoded URL or credentials — the externalized-config habit in action.

app/src/main/java/org/gymtrack/App.java (persistence path)

Properties props = new Properties();
try (InputStream in = App.class.getClassLoader()
        .getResourceAsStream("db.properties")) {
    props.load(in);
}

DatabaseConnection db = DatabaseConnection.getInstance(
    props.getProperty("db.url"),
    props.getProperty("db.user"),
    props.getProperty("db.password"),
    props.getProperty("db.driver"));

DatabaseSetup.runScript(db, "init-schema.sql");   // create tables

ExerciseDAO exercises = new ExerciseDAO(db);
WorkoutSessionDAO sessions = new WorkoutSessionDAO(db);

// seed exercises (re-uses the JSON log from Module 1 as the source)
Exercise bench = exercises.findOrCreate("Bench Press", "Chest");
Exercise squat = exercises.findOrCreate("Squat", "Legs");

// log a session transactionally
int sessionId = sessions.logSession("ada", "2026-06-26", List.of(
    new SetEntry(bench.getId(), 1, 10, 50),
    new SetEntry(bench.getId(), 2, 10, 52.5),
    new SetEntry(squat.getId(), 1, 5,  90)));

// read the aggregated progress back
for (ProgressRow r : sessions.getProgress("ada")) {
    System.out.printf("%-14s  %d sets  top %.1f kg%n",
        r.getExercise(), r.getTotalSets(), r.getTopWeightKg());
}

DatabaseSetup helper DatabaseSetup.runScript reads init-schema.sql from the classpath and runs each ;-separated statement through a Statement — the same resource-loading move as getResourceAsStream from Module 1, just feeding SQL instead of JSON.

Run & verify

Add the connector dependency, create the database, then run.

app/build.gradle (add)

dependencies {
    // ... Jackson, Spring Web from earlier modules ...
    implementation 'mysql:mysql-connector-j:8.4.0'   // JDBC driver
    testImplementation libs.junit.jupiter
}

Create the database & user In sudo mysql: CREATE DATABASE gymtrack; then create the gymtrack user and grant it privileges.

Run the app ./gradlew run — builds the schema, logs a session transactionally, prints the progress summary.

Verify in SQL SELECT * FROM set_entries; — confirm all three sets landed under one session_id.

When it fails, read in this order Connection refused → MySQL isn't running, or creds in db.properties are wrong. Table doesn't exist → the schema didn't load (check the resource name/path and statement order). Cannot add or update a child row → a foreign key points at a missing exercise_id/session_id — seed the parent first.

DONE

What you built (M9–M11)

GymTrack now has a memory. It went from re-reading a static JSON file to a database-backed app: a normalized MySQL schema (M9), a clean DAO layer that hides SQL behind methods and maps rows to immutable models (M10), a transactional write that records a whole session atomically, and an aggregated progress read computed by the database itself (M11). Same architecture instincts as the rest of the project — program to a thin interface, give one class the messy details — applied to persistence.

★ The JDBC concepts you now own

Six steps — singleton owns 1–3 and 6; DAO methods are execute + process.
execute split — executeQuery() (SELECT → ResultSet) vs executeUpdate() (INSERT/UPDATE → row count).
ResultSet — cursor-based, loop on next(), read by name, 1-based indexes.
PreparedStatement — ? placeholders + typed setters → injection-safe.
Transaction — logSession is the all-or-nothing unit (atomicity / ACID): setAutoCommit(false) → commit / rollback; auto-commit is ON by default.
Aggregation — JOIN + GROUP BY + COUNT/MAX does the counting in SQL, not Java.
Config — connection details externalized with java.util.Properties.

Full file tree after Module 11

gymtrack/
└─ app/src/main/
   ├─ java/org/gymtrack/
   │  ├─ App.java         (M11 persistence path)
   │  ├─ dao/
   │  │  ├─ ExerciseDAO.java     (M10)
   │  │  └─ WorkoutSessionDAO.java (M10 write, M11 read)
   │  ├─ model/ Exercise, SetEntry, ProgressRow (M10–M11)
   │  └─ util/  DatabaseConnection, DatabaseSetup (M9)
   └─ resources/
      ├─ init-schema.sql (M9)
      └─ db.properties   (M9)

Next, if you want to keep going: add an UPDATE/DELETE path so a coach can fix a mistyped set; introduce a connection pool (javax.sql.DataSource) instead of the single shared connection; or expose getProgress through a small REST endpoint so a frontend can chart it.