A ledger where the balance is always right
Accounts, deposits, withdrawals and transfers over raw JDBC — no ORM, no Spring — so that the transaction, the prepared statement and the pool are things you wrote rather than things that happened.
The business problem
Build the smallest correct bank: open an account, deposit, withdraw, transfer between two accounts, and print a statement. Every movement of money is a row in a ledger table, and the balance of an account is always the sum of its ledger rows.
The bar is the transfer. It debits one account and credits another, and it must do both or neither — when the second write fails, when the process dies between them, and when two transfers hit the same account at the same moment.
What you will have at the end
- A schema with the constraints that make bad data impossible, not merely unlikely
- PreparedStatement for every query, and a demonstration of why
- A transaction boundary you opened and closed yourself, with the isolation level chosen on purpose
- A connection pool, sized, and a leak you found and fixed
Milestones
Each one ends in something you can observe. Without that a milestone is a heading, and you have no way to know you finished.
The schema, as a migration
accounts (id, owner, opened_at) and ledger (id, account_id, amount in minor units, kind, created_at). A CHECK that amount is not zero, a foreign key, and an index on (account_id, created_at) for the statement. Apply it with Flyway, not by hand.
done whenInserting a ledger row for an account that does not exist fails at the database, and the statement query's plan uses the index.
Deposits and withdrawals
One PreparedStatement per operation. A withdrawal that would take the balance below zero is refused — and the check must be in the same transaction as the insert, or two withdrawals can both pass it.
done whenTwo threads withdrawing from an account with 100 in it, 100 each, end with exactly one success and a balance of 0, proven by a test.
The transfer
Two ledger rows in one transaction: setAutoCommit(false), both inserts, commit; rollback in the catch. Then break it on purpose — throw between the inserts — and check the balances.
done whenA transfer that fails after the debit leaves both balances unchanged, and a test proves it by throwing between the two writes.
The pool and the leak
HikariCP with a maximum of five connections. Write one operation that forgets to close its connection in a try-with-resources, run it six times, and watch what happens. Then fix it.
done whenThe sixth call blocks or times out before the fix, and Hikari's leak detection names the line after you enable it.
The statement
A running balance per row, oldest first, paged. Compute the running balance in SQL with a window function, and compare the plan against computing it in Java.
done whenThe statement for an account with 100,000 rows returns its first page in milliseconds, and the balance on the last row equals the account's balance.
Data
Amounts are integers in minor units (paise), never a floating-point type. The ledger is append-only: a correction is a new row, not an edit — and that decision is what makes the statement trustworthy.