JAVA-250 · Lesson 12 of 12 · 31 min

Capstone: a persistent inventory service

▶ Video coming soon A video version of this lesson is on the way.

The capstone week ran from Monday, January 3, to Thursday, January 6, 2028, while the shop was quiet after the holidays, and the review was on Friday, January 7, in the parts room, with Ingrid and Kofi Amankwah, the shop's parts and stock lead, whose request for a better stock tool had started StockRoom in the first place. The task was the one the course had been heading towards since September: move StockRoom's data out of its file and into a database, as StockStore, without losing a single part, a single movement or a single feature Kofi relied on. Hamza's StockRoom design from the winter stayed in place: the same part codes, the same reorder rule in whole packs, Tamsin Rhee's repair rack drawing parts with the REPAIR reason. What changed was where the data lived and how every change reached it. This lesson walks through the requirements, the design, the code and the review, and ends with the decision the team recorded about JDBC and JPA.

Requirements

Ingrid wrote the requirements as a numbered list on the Monday morning, so that each could be checked at the review. Every one comes from a lesson in the course, and several from a real problem, such as the eleven changes lost on August 24 when StockRoom's computer restarted before it saved.

StockStore requirements, Monday, January 3, 2028.
No.RequirementFrom
R1The schema is created and changed only by Flyway migrations in db/migrationLesson 11
R2All SQL lives in DAO classes behind interfaces and uses prepared statementsLessons 3 and 6
R3Receiving, selling and repair use are atomic: the stock change and its movement row commit together, and stock never goes below zeroLesson 4
R4The reorder report lists every part below its reorder level, with the quantity to order in whole packsStockRoom
R5A one-time import loads StockRoom's file in one transaction and reports every bad lineLessons 3 and 4
R6A reconciliation check proves that each part's stock equals its opening count plus its movementsLesson 4
R7Connections come from a pool sized for SQLiteLesson 5
R8Services are tested with in-memory DAOs; every SQL statement has an integration test against a temporary SQLite fileLesson 6

The design

StockStore has three layers, the shape of lesson 6. The screens, the counter screen and the parts-room screen that StockRoom already had, call a StockService. The service holds the business rules and runs each operation in a transaction through the Tx helper. Below it are two DAO interfaces, PartDao and MovementDao, each with a JDBC implementation for production and an in-memory one for tests. The JDBC implementations borrow connections from a HikariCP pool over one SQLite file, stockroom.db, and Flyway brings that file's schema up to date before anything else runs.

A layered diagram. Top: Counter screen and Parts-room screen. Next: StockService with receive, sell, useForRepair, reorderReport and reconcile. Next: two interfaces, PartDao and MovementDao, each with a JDBC implementation and an in-memory implementation for tests. Next: Tx helper and HikariCP pool. Bottom: stockroom.db, with Flyway applying V1 to V4 at startup.
StockStore's layers: screens, service, DAOs, pool and the SQLite file, with Flyway at startup.

The migrations are four files. V1 creates the supplier and part tables of lesson 1, V2 adds stock_movement, V3 adds the index on stock_movement(code) that the reconciliation query needs, and V4 renames reorder_at to reorder_level, Ingrid's change from October, done in one step because StockStore is the only program that uses the file and it stops for upgrades. The listing below is V2, which runs unchanged in SQLite and, apart from the AUTOINCREMENT keyword, in PostgreSQL.

V2__add_stock_movement.sql (table name suffixed for this demonstration)
CREATE TABLE movement_v2 (
    id     INTEGER PRIMARY KEY AUTOINCREMENT,
    code   TEXT NOT NULL REFERENCES part (code),
    change INTEGER NOT NULL CHECK (change <> 0),
    reason TEXT NOT NULL CHECK (reason IN ('RECEIVED', 'SOLD', 'REPAIR', 'COUNT', 'IMPORT')),
    at     TEXT NOT NULL
);

What this code does

Every change to stock is a row with a part code, a signed change, a reason from a fixed list and a time. The CHECK constraints make the database refuse a zero change or an unknown reason, so a bug in the Java code cannot write either. The table is named movement_v2 here only because the sample database already has a stock_movement table.

Result: the columns V2 creates.
name
id
code
change
reason
at

The service and its rules

Hamza wrote the service first, test-first, against in-memory DAOs, exactly as lesson 6 recommended. The example below is a cut-down but complete version of the core: an in-memory store with a transaction that either commits all its changes or none, and the three operations that change stock. The checks at the end are the acceptance tests for R3, R4 and R6 that Hamza ran on the Monday evening, before any SQL existed.

Java Example · Main.java
import java.util.*;

public class Main {
    record Part(String code, int quantity, int reorderLevel, int packSize) {}
    record Movement(String code, int change, String reason) {}

    static class Store {                                         // in-memory PartDao and MovementDao together
        Map<String, Part> parts = new TreeMap<>();
        List<Movement> movements = new ArrayList<>();
        Map<String, Integer> opening = new TreeMap<>();

        // run work atomically: on failure, restore the state from before
        boolean inTransaction(Runnable work) {
            Map<String, Part> savedParts = new TreeMap<>(parts);
            int savedMoves = movements.size();
            try { work.run(); return true; }
            catch (RuntimeException e) {
                parts = savedParts;
                movements.subList(savedMoves, movements.size()).clear();
                System.out.println("  rolled back: " + e.getMessage());
                return false;
            }
        }
        void change(String code, int delta, String reason) {
            Part p = parts.get(code);
            if (p == null) throw new IllegalArgumentException("unknown part " + code);
            movements.add(new Movement(code, delta, reason));              // written first, to show the rollback
            if (p.quantity() + delta < 0) throw new IllegalStateException("not enough " + code);
            parts.put(code, new Part(code, p.quantity() + delta, p.reorderLevel(), p.packSize()));
        }
    }

    static class StockService {
        final Store store;
        StockService(Store store) { this.store = store; }
        boolean receive(String code, int n) { return store.inTransaction(() -> store.change(code, n, "RECEIVED")); }
        boolean sell(String code, int n) { return store.inTransaction(() -> store.change(code, -n, "SOLD")); }
        boolean useForRepair(String code, int n) { return store.inTransaction(() -> store.change(code, -n, "REPAIR")); }

        Map<String, Integer> reorderReport() {
            Map<String, Integer> order = new TreeMap<>();
            for (Part p : store.parts.values()) {
                if (p.quantity() >= p.reorderLevel()) continue;
                int wanted = p.reorderLevel() * 2 - p.quantity();
                order.put(p.code(), (wanted + p.packSize() - 1) / p.packSize() * p.packSize());
            }
            return order;
        }

        List<String> reconcile() {
            List<String> problems = new ArrayList<>();
            for (Part p : store.parts.values()) {
                int expected = store.opening.get(p.code()) + store.movements.stream()
                        .filter(m -> m.code().equals(p.code())).mapToInt(Movement::change).sum();
                if (expected != p.quantity()) problems.add(p.code() + ": stock " + p.quantity() + ", movements say " + expected);
            }
            return problems;
        }
    }

    static void check(String req, boolean ok) { System.out.println((ok ? "PASS " : "FAIL ") + req); }

    public static void main(String[] args) {
        Store store = new Store();
        for (Part p : List.of(new Part("BELL", 3, 4, 6), new Part("CHAIN-11", 1, 3, 1),
                              new Part("PAD-ROAD", 3, 5, 4), new Part("GRIPS", 9, 4, 2))) {
            store.parts.put(p.code(), p);
            store.opening.put(p.code(), p.quantity());
        }
        StockService service = new StockService(store);

        service.receive("BELL", 6);
        service.useForRepair("PAD-ROAD", 1);
        boolean oversell = service.sell("CHAIN-11", 2);
        service.sell("GRIPS", 2);

        check("R3 an oversell is refused", !oversell && store.parts.get("CHAIN-11").quantity() == 1);
        check("R3 a refused sale leaves no movement", store.movements.stream().noneMatch(m -> m.code().equals("CHAIN-11")));
        check("R4 reorder report " + service.reorderReport(), service.reorderReport().equals(Map.of("CHAIN-11", 5, "PAD-ROAD", 8)));
        check("R6 stock matches movements", service.reconcile().isEmpty());
        System.out.println("Movements: " + store.movements);
    }
}
Output
  rolled back: not enough CHAIN-11
PASS R3 an oversell is refused
PASS R3 a refused sale leaves no movement
PASS R4 reorder report {CHAIN-11=5, PAD-ROAD=8}
PASS R6 stock matches movements
Movements: [Movement[code=BELL, change=6, reason=RECEIVED], Movement[code=PAD-ROAD, change=-1, reason=REPAIR], Movement[code=GRIPS, change=-2, reason=SOLD]]

What this code does

The oversell of chains writes its movement and then fails, and the rollback removes both the movement and any stock change, which is the all-or-nothing rule of R3. After six bells arrive, bells are no longer low, so the report lists only chains, five to bring one up to six, and road pads, two packs of four. Reconciliation adds each part's movements to its opening count and finds no difference.

The production service runs the same operations through JDBC. The sale is the conditional update of lesson 4 and the movement insert, both on one connection inside the Tx helper of lesson 6. If the update changes no rows, because the stock is too low or the code is unknown, the service throws, the helper rolls back and no movement is written.

Java Example · StockService.java (needs a driver)
public final class StockService {
    private final Tx tx;
    private final PartDao parts;
    private final MovementDao movements;
    private final Clock clock;

    public StockService(Tx tx, PartDao parts, MovementDao movements, Clock clock) {
        this.tx = tx; this.parts = parts; this.movements = movements; this.clock = clock;
    }

    public void sell(String code, int count) {
        if (count <= 0) throw new IllegalArgumentException("count must be positive");
        tx.inTransaction(c -> {
            if (!parts.adjust(c, code, -count))                // UPDATE ... WHERE code = ? AND quantity + ? >= 0
                throw new InsufficientStockException(code, count);
            movements.record(c, code, -count, Reason.SOLD, Instant.now(clock));
            return null;
        });
    }

    public List<ReorderLine> reorderReport() {
        return parts.reorderLines();                          // one query, shown below
    }
}

What this code does

The Clock is passed in so that tests can fix the time, a habit from the testing course. Both DAO calls take the transaction's connection, so the update and the insert commit together or not at all. The service contains business rules and no SQL; the SQL is in the DAOs.

The reorder report, R4, is one query. It does the pack arithmetic in SQL with integer division, so the database returns the finished report; the Java side only maps rows to records. Run here against the sample data, before any of the week's changes, it gives the same answers as the reorder rule in lesson 6, except for chains, of which the sample data has one rather than none.

SELECT code, quantity, reorder_at,
       ((reorder_at * 2 - quantity) + pack_size - 1) / pack_size * pack_size AS to_order
FROM part
WHERE quantity < reorder_at
ORDER BY code;

What this code does

For each low part the query computes how many are needed to reach twice the reorder level and rounds up to whole packs: bells need 5 and come in sixes, chains need 5 and come singly, and road pads need 7 and come in fours. The column is still reorder_at here because the sample database is at version 3; after V4 the query says reorder_level.

Result: the reorder report on the sample data.
codequantityreorder_atto_order
BELL346
CHAIN-11135
PAD-ROAD358

Importing StockRoom's data

R5 was the part Kofi cared about most, because the import was the moment the shop would switch over. StockRoom's file had one line per part, with the fields separated by commas. The import reads every line, validates it, and inserts the good ones as a batch inside one transaction with an IMPORT movement for each, so that the reconciliation of R6 starts from a known point. If any line is bad, it reports all the bad lines, with their numbers, and imports nothing; Kofi fixes the file and runs it again. A half-finished import, with some parts in the database and some not, was the one outcome everybody agreed to rule out.

Java Example · Main.java
import java.util.*;

public class Main {
    record Part(String code, String name, String shelf, int quantity, int reorderLevel, int packSize, int priceCents) {}
    record ImportResult(List<Part> parts, List<String> errors) {}

    static ImportResult parse(List<String> lines) {
        List<Part> parts = new ArrayList<>();
        List<String> errors = new ArrayList<>();
        Set<String> seen = new HashSet<>();
        for (int i = 0; i < lines.size(); i++) {
            int lineNo = i + 1;
            String[] f = lines.get(i).split(",", -1);
            if (f.length != 7) { errors.add("line " + lineNo + ": expected 7 fields, found " + f.length); continue; }
            try {
                Part p = new Part(f[0].trim(), f[1].trim(), f[2].trim(), Integer.parseInt(f[3].trim()),
                        Integer.parseInt(f[4].trim()), Integer.parseInt(f[5].trim()), Integer.parseInt(f[6].trim()));
                if (p.quantity() < 0) errors.add("line " + lineNo + ": negative quantity for " + p.code());
                else if (p.packSize() < 1) errors.add("line " + lineNo + ": pack size must be at least 1 for " + p.code());
                else if (!seen.add(p.code())) errors.add("line " + lineNo + ": duplicate code " + p.code());
                else parts.add(p);
            } catch (NumberFormatException e) {
                errors.add("line " + lineNo + ": not a number (" + e.getMessage().replace("For input string: ", "") + ")");
            }
        }
        return new ImportResult(parts, errors);
    }

    static List<List<Part>> batches(List<Part> parts, int size) {
        List<List<Part>> out = new ArrayList<>();
        for (int i = 0; i < parts.size(); i += size) out.add(parts.subList(i, Math.min(i + size, parts.size())));
        return out;
    }

    public static void main(String[] args) {
        List<String> file = List.of(
            "TUBE-700x25,Inner tube 700x25c,A-02,14,10,10,899",
            "BELL,Brass bell,C-03,3,4,6,650",
            "PAD-ROAD,Road brake pads (pair),B-07,three,5,4,1499",
            "GRIPS,Lock-on grips,C-03,9,4,2",
            "BELL,Brass bell,C-03,3,4,6,650",
            "CHAIN-11,11-speed chain,B-14,1,3,1,3999");
        ImportResult r = parse(file);
        if (!r.errors().isEmpty()) {
            System.out.println("Import refused, nothing written:");
            r.errors().forEach(e -> System.out.println("  " + e));
        }

        // after Kofi fixes the file: 612 good lines, inserted in batches of 250 inside one transaction
        List<Part> all = new ArrayList<>();
        for (int i = 1; i <= 612; i++) all.add(new Part("P-" + i, "part " + i, "A-01", 1, 1, 1, 100));
        List<List<Part>> b = batches(all, 250);
        System.out.println("Fixed file: " + all.size() + " parts in " + b.size() + " batches of " +
                b.stream().map(List::size).toList() + ", then one commit");
    }
}
Output
Import refused, nothing written:
  line 3: not a number ("three")
  line 4: expected 7 fields, found 6
  line 5: duplicate code BELL
Fixed file: 612 parts in 3 batches of [250, 250, 112], then one commit

What this code does

Three of the six lines are wrong in different ways, a word where a number belongs, a missing field and a repeated code, and all three are reported with their line numbers, so Kofi can fix the file in one pass. Because errors stop the whole import, nothing is written until the file is clean. The fixed file's 612 parts go to the database as three JDBC batches of at most 250 rows each, all inside a single transaction.

In the JDBC version, the parsed parts go through a PreparedStatement with addBatch and executeBatch, the pattern of lesson 3, and the movements through a second batch, all on one connection with auto-commit off. On the parts-room computer the import of 612 parts took well under a second; SQLite is fast when it commits once, and painfully slow when it commits after every row, because each commit waits for the disk.

Reconciliation

R6 is the requirement that lets Kofi trust the database. After the import, every part's quantity equals its IMPORT movement. After that, every change goes through the service and leaves a movement. So at any moment, the sum of a part's movements must equal its quantity, and a query can check it for all 612 parts at once. If it ever finds a difference, something changed the stock without the service, such as a hand-edited row, and the report names the part.

INSERT INTO stock_movement (code, change, reason, at)
    SELECT code, quantity, 'IMPORT', '2028-01-06 09:00'
    FROM part WHERE code IN ('BELL', 'CHAIN-11', 'GRIPS');
UPDATE part SET quantity = quantity - 2 WHERE code = 'GRIPS';
INSERT INTO stock_movement (code, change, reason, at) VALUES ('GRIPS', -2, 'SOLD', '2028-01-06 10:15');
UPDATE part SET quantity = 5 WHERE code = 'BELL';   -- a hand edit, with no movement

What this code does

Three parts are imported, grips are sold through the normal path with a movement, and then someone edits the bells by hand. The check sums each part's movements since the import and compares them with its quantity: chains and grips agree, and bells show a difference of 2, the hand edit. The FILTER clause, supported by SQLite and PostgreSQL, ignores the sample data's movements from before the import.

Result: the reconciliation check.
codequantitymovementsdifference
BELL532
CHAIN-11110
GRIPS770

The reconciliation query runs every night from a scheduled task on the parts-room computer, and its result appears at the top of the parts-room screen the next morning. On the first night it found nothing. Kofi said he would believe it after a month.

Wiring it together

The main method is short, because each piece does one job. It reads the configuration of lesson 2, builds the pool, runs Flyway, creates the DAOs and the service, and starts the screens. If Flyway fails, for example because a migration's checksum does not match, StockStore stops before it can touch the data.

Java Example · StockStore.java (needs HikariCP, Flyway and the SQLite driver)
public static void main(String[] args) {
    DbConfig cfg = DbConfig.fromEnvironment();                 // STOCKSTORE_DB_URL, never a password in code
    HikariConfig hc = new HikariConfig();
    hc.setJdbcUrl(cfg.url());                                  // jdbc:sqlite:/srv/secondgear/stockroom.db
    hc.setMaximumPoolSize(2);                                  // SQLite allows one writer at a time
    hc.setConnectionInitSql("PRAGMA foreign_keys = ON");       // SQLite checks foreign keys only when asked
    hc.setPoolName("stockstore");

    try (HikariDataSource ds = new HikariDataSource(hc)) {
        Schema.migrate(ds);                                    // Flyway: V1 to V4
        Tx tx = new Tx(ds);
        StockService service = new StockService(tx, new JdbcPartDao(ds), new JdbcMovementDao(ds), Clock.systemDefaultZone());
        Screens.start(service);                                // the counter and parts-room screens
    }
}

What this code does

A small pool suits SQLite, which serializes writers; more connections would only wait on each other. SQLite enforces foreign keys only when each connection turns them on, so the pool runs that pragma for every new connection. The try-with-resources block closes the pool, and with it every connection, when the screens exit.

Representative startup log
$ java -jar stockstore.jar
[main] INFO com.zaxxer.hikari.HikariDataSource - stockstore - Starting...
[main] INFO com.zaxxer.hikari.HikariDataSource - stockstore - Start completed.
[main] INFO org.flywaydb.core.FlywayExecutor - Database: jdbc:sqlite:/srv/secondgear/stockroom.db (SQLite 3.51)
[main] INFO org.flywaydb.core.internal.command.DbValidate - Successfully validated 4 migrations
[main] INFO org.flywaydb.core.internal.command.DbMigrate - Current version of schema "main": 4
[main] INFO org.flywaydb.core.internal.command.DbMigrate - Schema "main" is up to date. No migration necessary.
StockStore ready: 612 parts, 3 below reorder level

What this code does

This is representative output, abbreviated, from a start on the Thursday after all four migrations had run once; the exact logger names and wording depend on the library versions. Flyway validates the four applied migrations, finds nothing pending and lets the program continue. The last line is StockStore's own.

Testing

R8 split the tests in two, the pattern from lesson 6. The service tests, like the example above, ran against in-memory DAOs and took a fraction of a second, so Hamza ran them after every change. The integration tests ran each DAO against a fresh temporary SQLite file: each test created the file, ran Flyway's migrations on it, inserted the rows it needed, called one DAO method and checked the result, so every SQL statement in StockStore was executed at least once against a real SQLite with the real schema. Running the migrations in every integration test also tested the migrations themselves, which caught a typo in V3 on the Tuesday.

Java Example · Main.java
import java.util.*;
import java.util.function.*;

public class Main {
    record Check(String requirement, String name, BooleanSupplier test) {}

    public static void main(String[] args) {
        Map<String, Integer> stock = new TreeMap<>(Map.of("BELL", 3, "CHAIN-11", 1));
        List<String> applied = List.of("1", "2", "3", "4");
        List<String> files = List.of("V1__create_parts.sql", "V2__add_stock_movement.sql",
                "V3__index_movement_code.sql", "V4__rename_reorder_at.sql");

        List<Check> checks = List.of(
            new Check("R1", "every migration file is applied", () -> files.size() == applied.size()),
            new Check("R1", "migration names follow V<n>__<description>.sql",
                    () -> files.stream().allMatch(f -> f.matches("V\\d+(\\.\\d+)*__\\w+\\.sql"))),
            new Check("R3", "stock is never negative", () -> stock.values().stream().allMatch(q -> q >= 0)),
            new Check("R7", "pool size suits one SQLite writer", () -> { int maxPool = 2; return maxPool >= 1 && maxPool <= 4; }),
            new Check("R5", "import refuses a file with errors", () -> {
                List<String> errors = List.of("line 3: not a number (\"three\")");
                int written = errors.isEmpty() ? 6 : 0;
                return written == 0;
            }));

        int passed = 0;
        for (Check c : checks) {
            boolean ok = c.test().getAsBoolean();
            if (ok) passed++;
            System.out.printf("%-4s %-4s %s%n", ok ? "PASS" : "FAIL", c.requirement(), c.name());
        }
        System.out.println(passed + " of " + checks.size() + " checks passed");
    }
}
Output
PASS R1   every migration file is applied
PASS R1   migration names follow V<n>__<description>.sql
PASS R3   stock is never negative
PASS R7   pool size suits one SQLite writer
PASS R5   import refuses a file with errors
5 of 5 checks passed

What this code does

Each check is a requirement number, a name and a BooleanSupplier, so the list reads like the requirements table and new checks are one line each. The printf format pads the columns so the report lines up. Running the list at the review let Ingrid tick requirements off against a printout instead of taking them on trust.

The review

On Friday, January 7, Hamza ran the acceptance checks, imported a fresh copy of Kofi's file into an empty stockroom.db, and then let Kofi try to break it. Kofi sold more chains than there were and got a refusal with nothing written. He pulled the parts-room computer's power cable halfway through a delivery of tubes and, after the restart, found the delivery either fully there or not at all, never half; it had been not at all, and the counter screen said so. He edited a bell count with a database browser and watched the next morning's reconciliation name it. Ingrid read the DAOs and asked one question: why the reorder report did its arithmetic in SQL rather than in Java. Hamza's answer was that the query returned the finished report in one round trip and the same rule was covered by a test in each place; she accepted it, and asked for a comment in the code saying so.

Decision record: JDBC for StockStore, JPA for ShopBook

The last item of the review was the question that lessons 7 to 10 had raised: should StockStore use JPA? The team wrote its answer as a short decision record, a page that states a decision, the options considered and the reasons, and keeps it with the code so that whoever wonders why in two years can find out.

Decision record, January 7, 2028.
StockStoreShopBook
Shape of the workCounts and movements; reports and conditional updatesA graph of bikes, bookings and customers, loaded, changed and saved
SizeTwo tables that matter, about a dozen queriesMany tables and relationships, growing
DecisionPlain JDBC behind DAOsMove to JPA with Hibernate 7, starting with Bike and Booking
Main reasonEvery operation is one or two SQL statements that are clearer as SQLMost of the old BookingDao was mapping and change-tracking code that JPA provides
Risks notedMore boilerplate if the model growsN+1 queries and lazy loading; mitigated by LAZY everywhere, fetch plans per screen and query counting in tests
Review againWhen StockStore moves to PostgreSQL for the web serviceAfter the first month of the Columbus trial

Both decisions keep the same rules: Flyway owns every schema, all persistence code sits behind interfaces, and every change that matters runs in a transaction. That is the real outcome of the course. JDBC and JPA are two ways of talking to a database, and a team that understands what both of them do underneath, connections, statements, transactions, the persistence context and the SQL that comes out, can choose between them for each program and change its mind later without a rewrite.

Try it yourself

Sell or refuse

Complete sell so that it refuses (returns false and changes nothing) when the part is unknown or there is not enough stock; otherwise it reduces the stock and adds a movement code:-n:SOLD.

The program prints true false false, then {BELL=1, CHAIN-11=1} and [BELL:-2:SOLD].

import java.util.*;

public class Main {
    static Map<String, Integer> stock = new TreeMap<>(Map.of("BELL", 3, "CHAIN-11", 1));
    static List<String> movements = new ArrayList<>();

    static boolean sell(String code, int n) {
        stock.merge(code, -n, Integer::sum);
        return true;
    }

    public static void main(String[] args) {
        System.out.println(sell("BELL", 2) + " " + sell("CHAIN-11", 2) + " " + sell("HORN", 1));
        System.out.println(stock);
        System.out.println(movements);
    }
}

Try it yourself

Reconcile stock with movements

Write differences that returns, for each part whose stock differs from the sum of its movements, an entry code=difference (stock minus movements), in code order.

The program prints {BELL=2}.

import java.util.*;

public class Main {
    record Movement(String code, int change) {}

    static Map<String, Integer> differences(Map<String, Integer> stock, List<Movement> movements) {
        return Map.of();
    }

    public static void main(String[] args) {
        Map<String, Integer> stock = new TreeMap<>(Map.of("BELL", 5, "CHAIN-11", 1, "GRIPS", 7));
        List<Movement> moves = List.of(new Movement("BELL", 3), new Movement("CHAIN-11", 1),
                new Movement("GRIPS", 9), new Movement("GRIPS", -2));
        System.out.println(differences(stock, moves));
    }
}

Common mistakes

  • MistakeImporting part of a file and leaving the rest for later.

    InsteadValidate everything first and import in one transaction, or import nothing.

  • MistakeCommitting after every row of a large import.

    InsteadUse batches inside one transaction and commit once.

  • MistakeChanging stock without writing a movement.

    InsteadRoute every change through the service, which writes both in one transaction.

  • MistakeForgetting that SQLite checks foreign keys only when asked.

    InsteadRun PRAGMA foreign_keys = ON on every connection, for example as the pool's init SQL.

  • MistakeChoosing JDBC or JPA once for every program.

    InsteadDecide per program, record the reasons and review them when the program changes.

Summary and key terms

  • StockStore moved StockRoom's data into one SQLite file behind a layered design: screens, a StockService with the business rules, PartDao and MovementDao interfaces with JDBC and in-memory implementations, a Tx helper, a small HikariCP pool and Flyway migrations run at startup.
  • Each requirement came from a lesson.
  • Stock changes are conditional updates committed together with a movement row, so stock never goes negative and every change is recorded.
  • The reorder report is one query with pack arithmetic in SQL.
  • The import validates the whole file, reports every bad line and inserts in batches inside one transaction.
  • A reconciliation query compares each part's stock with the sum of its movements and finds changes made outside the service.
  • Service tests use in-memory DAOs; integration tests run the migrations and each SQL statement against a temporary SQLite file.
  • The decision record kept plain JDBC for StockStore and chose JPA with Hibernate 7 for ShopBook, with the same rules for schema, interfaces and transactions in both.
StockStore
The database-backed successor to StockRoom, built in January 2028.
Acceptance check
A test that shows a stated requirement is met.
Movement log
The table of every stock change with its reason and time.
Reconciliation
Checking that each part's stock equals its opening count plus its movements.
One-time import
Loading existing data into a new system, validated and committed as one unit.
Batch insert
Sending many INSERTs to the database in one round trip with addBatch and executeBatch.
Init SQL
A statement the pool runs on every new connection, such as a SQLite pragma.
Integration test
A test that runs code against a real database with the real schema.
Decision record
A short document stating a decision, the options considered and the reasons.
Expand and contract
The stepwise schema change a shared database would need, avoided here because StockStore stops for upgrades.

Practice problems

Work each problem on paper first, then reveal the answer to check.

  1. Which requirement stops stock from going negative, and how?

    Show answer

    R3, with a conditional UPDATE that changes no rows if the result would be negative, followed by a rollback.

  2. Why does the import refuse a file with any bad line?

    Show answer

    So the database never holds a half-finished import.

  3. What did the reconciliation find on Friday morning?

    Show answer

    The bell count that Kofi had edited by hand.

  4. Why does StockStore's pool have only two connections?

    Show answer

    SQLite allows one writer at a time, so more connections would only wait.

  5. Why did ShopBook choose JPA when StockStore did not?

    Show answer

    ShopBook works with a graph of objects it loads, changes and saves; StockStore's work is clearer as SQL.

Lesson quiz · 5 questions

Score 60% or more to complete this lesson.

Take the quiz