Skip to content
Tutorials Center
My learning

Topics

Lesson 113 of 119 ยท Projects

Project: Banking Application with JDBC

Build a banking app in Java with JDBC and H2 - a double-entry ledger, deposits and transfers in transactions, row locks, idempotent requests, statements and JUnit tests.

Money is the toughest test for database code. A library can live with a report that is a second out of date, but a bank can't lose one cent, can't book the same payment twice, and can't let two cash machines spend the same balance at the same moment. Every rule you learned about transactions suddenly has a price tag. In this project you will build a small banking application on JDBC and H2 that gets those rules right, and you will prove it with tests that hammer it from eight threads at once.

The app manages customers and their checking and savings accounts. Tellers deposit and withdraw cash, customers transfer money, and everyone can ask for a monthly statement. Under the hood sits a double-entry ledger, the same bookkeeping idea real banks have used for centuries. You will build it in four stages, and each stage is a complete program that repeats the code it needs from the earlier stages, because every run starts with an empty database.

This project builds on Transactions with JDBC, The DAO and Repository Patterns and Project: Library Database with JDBC. The library project hid the transaction's connection in a ThreadLocal. This one deliberately takes the other road and passes the connection explicitly, so you can compare both styles. If you built the in-memory bank account system in the Java course, you'll see how much changes once the data lives in a database that many threads share.

What we are building

Write the requirements down first:

  • Customers own accounts. An account is CHECKING or SAVINGS, has a number like ACC-1001, a balance, an optional overdraft limit, and a status: OPEN, FROZEN or CLOSED.
  • Tellers can deposit and withdraw cash; customers can transfer between any two open accounts.
  • A balance may never drop below minus the overdraft limit. Amounts are positive with at most two decimal places.
  • Every money movement is atomic: both sides are booked, or nothing is.
  • Each request carries a reference. If the same request arrives twice (a network retry, a double click), the money moves only once.
  • Concurrent requests must never create or destroy money, and must never deadlock the app.
  • A statement shows one account's month: opening balance, every movement with the running balance, and the closing balance.
  • An audit proves at any moment that the books balance.

The layers are the same as in the library project, with one new rule about connections:

Layer Classes Knows about
Schema SQL text blocks Tables, constraints, the ledger rules
JDBC plumbing Jdbc, Database Statements, connections, commit, rollback, retry
DAOs AccountDao, LedgerDao SQL and row mapping; every method takes a Connection
Service BankService Money rules, locking order, idempotency, time
User interface BankConsole Commands from the Input box and formatted output

Stage 1: a double-entry ledger

The obvious design is an account table with a balance column that deposits and withdrawals update. It works until someone asks "why is this balance 379.50?" and nobody can answer. Banks therefore keep a ledger: an append-only list of every movement, from which any balance can be recomputed.

This project uses double-entry bookkeeping. Every money movement is a txn row (with a unique reference, a kind and a memo) plus two or more entry rows, one per affected account, and the entries of a transaction always sum to zero. A transfer of 120.50 is one entry of -120.50 on the source and one of +120.50 on the target. Cash needs an account too, so the bank has its own system account CASH-0001: a deposit moves money from cash to the customer, and a withdrawal does the opposite. The bank's cash account goes negative as customers deposit money, which is exactly right: it shows how much the bank owes its customers.

Two consequences make this design powerful:

  • Money can't appear or vanish. If every transaction sums to zero, all balances together always sum to zero. Any other total proves a bug.
  • Every balance is explainable. It equals the sum of the account's entries, and each entry stores balance_after, the running balance, so a statement needs no arithmetic at all.

Notice that this project stores the balance in account, while the library project deliberately computed free copies instead of storing them. Here there are good reasons to store it: reading a balance must be fast even after millions of entries, the CHECK constraint needs a column to check, and the account row is the natural thing to lock (stage 2). The price is that the stored balance and the ledger could drift apart, so the ledger stays the source of truth and an audit query compares the two. That is precisely the trade-off real banks make, and the audit is what auditors run at the end of each day.

The schema protects the rules the database can check by itself:

  • DECIMAL(15, 2) for every amount: exact decimal arithmetic, never floating point. Java maps it to BigDecimal.
  • balance_within_limit keeps customer balances above minus the overdraft limit; the system account is exempt.
  • owner_unless_system says that customer accounts have an owner and the system account doesn't, written as one comparison of two Boolean expressions.
  • txn_reference_unique makes each reference usable once, which stage 2 turns into idempotency.
  • A sequence hands out account numbers, so two tellers opening accounts at once never get the same number.
Java Stage 1: a double-entry ledger schema

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The three audit checks pass on the sample data, and the four bad writes fail with SQLState 23513 (check constraint) or 23505 (unique key). Each message names the constraint, which is why every constraint in the schema has a name.

The sample data was written by hand, so its balances and balance_after values had to be computed by hand too. From stage 2 on, only BankService writes money movements, and the audit is the safety net that would catch a mistake in it.

Locks and deadlocks: an experiment

Before writing the service, it's worth seeing the problem that shapes its design. A transfer reads two balances, checks the rules, and writes two balances. If two transfers touch the same account at the same time and both read the old balance, one update overwrites the other, a lost update, and money appears or disappears. You fix that with SELECT ... FOR UPDATE, which locks each account row until the transaction ends, as the library project did for books.

Locking two rows creates a new danger. Suppose Priya sends money to Lukas while Lukas sends money to Priya, and each transfer locks its source account first. Transfer A locks Priya's account, transfer B locks Lukas's, and now each waits for the row the other holds. That is a deadlock: neither can ever continue. The database notices the cycle and kills one transaction with SQLState 40001.

The experiment runs exactly that race, and then the same two transfers again, but this time both lock the account with the smaller number first.

Java Experiment: two transfers in opposite directions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

When each transfer locks its own source first, one of them is rolled back with Deadlock detected. When both follow one global order, the second transfer simply waits for the first to finish, and both commit. This is the same rule you know from Java locks in Deadlocks, Livelocks and Starvation: acquire locks in one fixed order and cycles can't form.

Ordered locking prevents the deadlocks your own code could cause, but a database can still pick a victim for reasons outside your control (another program, an index page lock). Production code therefore also retries a transaction that failed with SQLState 40001, usually two or three times. The Database helper in stage 2 does both.

Stage 2: DAOs and a transactional service

Stage 2 adds the persistence layer and the money rules.

JDBC plumbing. Jdbc has the static helpers query, queryOne, update and insert, which all take a Connection as the first argument. Database.inTransaction opens a connection, turns autocommit off, runs the work, and commits or rolls back. If the database reports a deadlock (40001), it runs the whole unit of work again, up to three attempts, and counts the retries so the demo can show them.

Explicit connections. Every DAO method takes the transaction's Connection as its first parameter. Compare this with the library project's ThreadLocal: the method signatures are noisier, but there is no hidden state. You can see in the code which calls belong to one transaction, and a DAO call made on another thread can't silently run outside it, because it has no connection to use. Spring's JdbcTemplate takes the ThreadLocal road; many teams that write plain JDBC prefer the explicit one.

The money rules live in BankService.move, which deposits, withdrawals and transfers all call:

  1. Lock both accounts in a fixed order. The account numbers go into a TreeSet, which iterates in sorted order, and each is locked with AccountDao.lock (SELECT ... FOR UPDATE).
  2. Check the reference. If a transaction with this reference already exists, return its id and move nothing. The check runs after the locks on purpose: a second request with the same reference touches the same accounts, so it waits for the first to commit and then finds its row.
  3. Check the rules on the locked, current rows: both accounts open, enough money available (balance + overdraftLimit).
  4. Write the txn row and both entries, and update both stored balances.

A request that breaks a rule throws BankException, whose message a teller can show. Anything else (a lost connection, a constraint violation that slipped past the rules) becomes a DataAccessException, which the user interface treats as a bug, not as a refusal.

Amounts are validated before the transaction starts: they must be positive, and amount.stripTrailingZeros().scale() > 2 rejects fractions of a cent such as 0.001. Never use double for money; BigInteger and BigDecimal shows why.

The demo books a few movements, runs into every rule, replays a request, and then lets eight "tellers" (threads) book 200 random transfers between the four customer accounts at the same time.

Java Stage 2: DAOs and a transactional BankService

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Match each line to a rule:

  • Priya's withdrawal of 400.00 takes her checking account to -20.50, inside her 100.00 overdraft. The next 100.00 is refused because only 79.50 is available.
  • Sofia can't send money she doesn't have, and an unknown account or an amount like 0.001 is refused before anything is written.
  • The same request again returns transaction 7 a second time, and Sofia's balance shows 500.00, not 1000.00. A client that lost the response to its first request can safely retry with the same reference.
  • After 200 concurrent transfers, the customers hold exactly as much money as before (2650.00), the audit finds no problems, and there were no deadlock retries, thanks to the sorted locking. How many of the 200 transfers were booked or refused differs from run to run, which is why the program only prints their sum.

A real payment API should also check that a replayed reference matches the original request (same accounts, same amount) and reject a mismatch with an error, typically HTTP 409 or 422. Otherwise a client bug that reuses a reference for a different payment is silently swallowed. The txn table would then store the amount and accounts of each request so the comparison is possible.

Stage 3: statements and a console

Tellers need a user interface and customers need statements. Stage 3 adds both, plus a clock you can move forward.

Statements come straight from the ledger. LedgerDao.balanceBefore finds the opening balance: the balance_after of the last entry booked before the first day of the month, or zero. LedgerDao.lines returns the month's entries, joined with their transactions for the reference and memo. The closing balance is the last line's balance_after. The console also adds the amounts to the opening balance and would flag a statement that doesn't add up, so every statement is its own small audit.

Months are half-open ranges: created_at >= first day AND created_at < first day of next month. That form is correct for timestamps at any precision, while BETWEEN '2026-03-01' AND '2026-03-31' would miss everything after midnight on the 31st.

Time comes from a SettableClock, a Clock subclass whose day command moves it forward. The service only ever calls LocalDateTime.now(clock), so the demo can book movements on different days in one run. The clock refuses to go backward, because the statement code relies on entries being booked in time order.

The console reads one command per line from the Input box, prints it after a > prompt, and catches BankException to print a friendly "Sorry". It generates a fresh reference (T-0001, T-0002, ...) for every request. A refused request uses up its reference too, so gaps in the numbers are normal, just like gaps in database sequences. Edit the input and run again to try your own session.

Java Stage 3: statements and a console for tellers

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Priya's March statement opens at 379.50 (the balance at the end of February), lists her withdrawal, the top-up from her savings and the concert tickets with their running balances, and closes at 69.25. Sofia's statement for April, the current month after day 2026-04-01, opens at 30.25, which is March's closing balance. Two months of statements chain together without any extra bookkeeping, because the ledger stores balance_after on every entry.

The accounts list at the end shows the new savings account ACC-1005, numbered by the sequence. Its 30.00 came from Sofia's checking account in a transfer, so it has a ledger history from its very first cent.

In a real bank, statements are usually generated once at month end and stored, because they are legal documents that must not change. Our ledger is append-only (nothing ever updates or deletes an entry), so regenerating a past month always gives the same result. Corrections are made the way accountants make them: with a new, reversing transaction, never by editing an old one.

Stage 4: tests that try to break the money rules

The demos show that the rules can work. Tests prove that they keep working after every change. BankServiceTest runs with JUnit 6 inside the program, just as in JUnit Basics, and follows the setup from the library project: each test gets a fresh in-memory database (bank-test-1, bank-test-2, ...) and creates exactly the data it needs. The console isn't needed for testing the rules, so it's left out to keep the program shorter.

Three ideas make these tests stronger than a list of happy paths:

  • An audit after every test. An @AfterEach method runs the auditor's checks and fails the test if the books don't balance. Every test, even one about statements, also verifies the ledger invariants for free.
  • A crash in the middle of a transfer. aCrashBetweenTheTwoLegsRollsBackTheFirstLeg builds a second BankService with a LedgerDao subclass that throws an SQLException when it is asked to write the credit entry. By then the debit entry and the new source balance are already written. The test proves that the rollback undoes them. A fake that fails on purpose is the only way to test a failure that "never happens"; see Test Doubles and Mockito.
  • A real race. concurrentTransfersNeitherCreateNorLoseMoney lets eight threads book 320 random transfers between four accounts, then checks that the total is unchanged, no balance is negative, and no deadlock retry was needed.

The parameterized test tries three invalid amounts with one method; Parameterized Tests covers @ValueSource and friends. The listener in main prints one line per test, so you can see the three parameterized runs.

Java Stage 4: JUnit tests for the money rules

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

All eleven tests pass. Now break the code on purpose and watch the tests catch it:

  • Remove FOR UPDATE from AccountDao.lock. The concurrency test fails: transfers now overwrite each other's balances, and the four accounts end up holding thousands instead of 2000.00, a different wrong number on every run. The audit fails too, because the stored balances no longer match the ledger.
  • Replace new TreeSet<>(List.of(from, to)) with List.of(from, to), so each transfer locks its source first. Money stays correct, because the retry saves every deadlock victim, but the test reports dozens of deadlock retries. In production that would mean slow requests and, under heavier load, failures after the last attempt.
  • Remove c.rollback() from Database.inTransaction. Surprisingly, every test still passes, because H2 rolls back an open transaction when its connection is closed. The JDBC specification leaves that case implementation-defined: some drivers (Oracle's, traditionally) commit instead, and a connection pool doesn't really close the connection at all. A green test on one database is not proof for another, so keep the explicit rollback and run integration tests against the database you deploy on (see Integration Testing).

Design decisions in review

Decision Choice here Why
Bookkeeping Double-entry ledger; entries of a transaction sum to zero Money can't appear or vanish, and every balance is explainable
Balances Stored and derivable from the ledger, checked by an audit Fast reads and a lockable row, with the ledger as the truth
Money type DECIMAL(15, 2) and BigDecimal, at most 2 decimal places Exact arithmetic, no fractions of a cent
Connections Passed explicitly to every DAO method Transaction boundaries are visible in the code
Concurrency SELECT ... FOR UPDATE on both accounts, in sorted order No lost updates, no deadlocks
Deadlock victims Retry on SQLState 40001, at most 3 attempts Survives deadlocks the code didn't cause
Duplicate requests Unique reference, checked after locking A retried request moves money once
History Append-only entries with balance_after Statements need no arithmetic; corrections are new transactions
Errors BankException vs DataAccessException Refusals are shown; failures are bugs
Time Injected Clock Statements and tests on any date

When you later move this app to Spring, @Transactional replaces inTransaction, a repository method with @Lock(PESSIMISTIC_WRITE) replaces AccountDao.lock, and the ledger design doesn't change at all. Entity Lifecycle and Transactions covers JPA's locking options, including optimistic locking, the alternative to row locks when conflicts are rare.

Ideas for extending the project

  • Interest. Add a credit-interest <yyyy-mm> command that pays 0.5% a year on savings balances, computed daily from the ledger. Round with RoundingMode.HALF_EVEN and book all payments in one transaction from a new system account INTEREST-0001.
  • Fees. Charge 1.00 for each withdrawal beyond the fifth in a month, booked as its own transaction with a FEE kind.
  • Closing an account. Allow close <account> only when the balance is zero, and refuse all movements on closed accounts.
  • Holds. Card payments first reserve money and settle later. Add a hold table and make available subtract open holds.
  • Multiple currencies. Add a currency column, refuse transfers between currencies, and add an exchange transaction that books four entries through two system accounts, one per currency.
  • Request checks. Store the amount and accounts of each request with its reference, and refuse a replay that doesn't match.
  • A REST API. Put the service behind HttpServer as in Project: A REST API from Scratch, with the reference sent in an Idempotency-Key header.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In the double-entry ledger, what must be true of the entries that belong to one transaction?

    Every movement takes money from somewhere and puts it somewhere else, so the entries of a transaction always sum to zero. That's why all balances together also sum to zero.
  2. 2. Two transfers run at the same time, ACC-1003 to ACC-1001 and ACC-1001 to ACC-1003. Each locks its source account first. What can happen?

    The two transfers lock the accounts in opposite orders, which forms a cycle. Locking in one global order, such as sorted account numbers, prevents it.
  3. 3. A client sends a transfer with reference PAY-42, the response is lost, and it sends the identical request again. What does BankService do?

    move() checks for an existing transaction with the same reference after locking the accounts and returns it. The UNIQUE constraint on txn.reference is the safety net behind that check.
  4. 4. In aCrashBetweenTheTwoLegsRollsBackTheFirstLeg, the debit entry and the source balance were already written when the exception was thrown. Why is the source balance still 100.00 afterwards?

    All writes of a transfer happen on one connection with autocommit off. The rollback undoes every write since the transaction began, which is exactly what makes the transfer atomic.
  5. 5. Why does a statement's opening balance come from the balance_after of the last entry before the month, instead of from the account table?

    The stored balance is the current balance. Historical balances are only in the append-only ledger, and balance_after records the running balance after each entry.

Summary

  • A double-entry ledger books every movement as a transaction whose entries sum to zero. A system account represents cash, so deposits and withdrawals are transfers too.
  • Store balances for speed and locking, but keep the ledger as the source of truth and run an audit that compares them, checks that transactions sum to zero, and checks that all balances total zero.
  • Use DECIMAL and BigDecimal for money, and reject amounts with more than two decimal places.
  • Lock every account a transfer touches with SELECT ... FOR UPDATE, always in the same order, to prevent lost updates and deadlocks; retry transactions that fail with SQLState 40001.
  • Make requests idempotent with a unique reference that is checked after the locks are taken.
  • Pass the transaction's Connection explicitly to DAO methods when you want transaction boundaries visible in the code.
  • Test money code with an audit after every test, a fake that fails between two writes, and a real multi-threaded race.

Next: Project: Login and Registration with Servlets, JSP and JDBC builds a classic web application with registration, password hashing, sessions and an authentication filter.