Assignment 11 — The bank in a database

BankLab, part 3 of 4: the accounts move into a database, so they outlive the server. Every method one transaction, every value through a prepared statement, and the server changed by one line.

Due Nov 19, 2026

BankLab, part 3. A10’s server forgets every account when it stops. This week the bank keeps them in a database instead: a file beside the project, read and written through JDBC, so a server started again finds every account where it left it. A12 is all of it together, against the BankLab rubric. This week’s work is most of the rubric’s first row, the row on concurrency for the database, and the second row’s claim that the bank was replaced without rewriting the server.

This one has its own repo. Accept it the same way you accepted A10, clone it, open the folder in IntelliJ and let it finish loading; this time that also fetches the H2 database, which runs inside your own program, with no server to install. Run DatabaseBankTest from the arrow beside its name: the red tests are the methods you are about to write, and a task is done when its tests are green, yours included.

What is given

A10’s server, session and client, finished, and A9’s bank beside them. If you would rather build on your own A10 Session and BankServer, put yours in place of these two and commit that on its own, before anything else. Either way costs nothing.

DatabaseBank is the one file you write: a Bank like A9’s MemoryBank, the same seven methods and the same refusals, kept in a database. Everything else in the project is finished, and task 6 changes one line of BankServer. DatabaseBank’s constructor takes a JDBC URL, which says which database:

  • jdbc:h2:./bank is the file bank.mv.db beside the project. The server uses it.
  • jdbc:h2:mem:test1 is one in memory, gone when the program ends. The tests use a new one each, so no test sees another’s accounts.

StorageException, a BankException of its own, is for when the database fails or refuses: an SQLException you cannot turn into one of A9’s refusals becomes a StorageException with the same message, and the server sends it on as an ERROR line like any other.

The two tables. The constructor makes them, if the database does not have them yet:

CREATE TABLE IF NOT EXISTS customer (
    name     VARCHAR(40) PRIMARY KEY,
    password VARCHAR(40) NOT NULL,
    balance  BIGINT NOT NULL
)

CREATE TABLE IF NOT EXISTS transaction_record (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    source      VARCHAR(40) REFERENCES customer (name),
    destination VARCHAR(40) REFERENCES customer (name),
    cents       BIGINT NOT NULL
)

One row of customer per account, its balance in cents. One row of transaction_record per deposit, withdrawal or transfer: its source is the account the money left and its destination the account it reached, NULL for a deposit’s source and a withdrawal’s destination. The database numbers the rows itself, 1, 2, 3, …, which is every entry’s id, and a transfer is one row, so one id, as A9 asked.

Three rules for every method

  • Every value goes in through a PreparedStatement, never into the SQL by +: prepareStatement("SELECT balance FROM customer WHERE name = ?"), then setString(1, name). A name is whatever a client sent, and x' OR '1'='1 pasted into the text of a query changes what the query says; given as a value, it is only a name nobody has.
  • A method that changes two things changes both or neither: one transaction. db.setAutoCommit(false) before the first change, db.commit() after the last, and db.rollback() when anything is refused or fails in between.
  • Each method opens a connection of its own, in a try-with-resources: try (Connection db = DriverManager.getConnection(url)) { ... }. Many client threads call the bank at once, and a connection is used by one thread at a time; closing it closes its statements too.

Each task ends with a test of your own, at the bottom of DatabaseBankTest, with a database of its own from freshUrl(), as the given tests have. It is done when it is green, and it goes red when you change the value it expects (then put it back).

The tasks

  1. The tables. The constructor opens a connection and runs the two CREATE TABLE IF NOT EXISTS statements, with a plain Statement: db.createStatement().executeUpdate(...). Nothing in them comes from a client, so there is nothing to prepare.

    Your test: two DatabaseBanks on the same URL, one after the other; the second one makes no error and finds an account the first one registered.

  2. register and login. register checks the name and password as A9 did, then inserts a row with a balance of 0. A name that is taken is refused by the database itself: the primary key will not take a second row with the same name, and the SQLException it throws has getSQLState() equal to "23505". Turn that one into a NameTakenException, and any other into a StorageException. Letting the key refuse is also what makes two threads registering one name at once safe, with no lock of yours.

    login selects the password: no row is a NoSuchAccountException, and the wrong one a WrongPasswordException.

    Your test: a name with a quote in it, o'neil, registers, logs in and holds a deposit; and balance("x' OR '1'='1") is refused as an account that does not exist.

  3. balance, deposit and withdraw. A deposit is two changes, the balance and a row of transaction_record, so it is a transaction. A withdrawal takes the money only where the balance covers it, in one statement:

    UPDATE customer SET balance = balance - ? WHERE name = ? AND balance >= ?

    executeUpdate returns the number of rows it changed: 1 when the money was there, 0 when it was not, or when there is no such account; a SELECT then says which. The check and the change are one step, so no other thread can take the money in between: the rule against a negative balance lives in this one statement.

    Your test: a withdrawal of one cent more than the balance is refused, and the balance and the history are as they were.

  4. transfer. One transaction: take from one account, add to the other, and record one row. Check in A9’s order, the amount, the two names, the same account twice, and then the money.

    The database locks a row while a transaction changes it, until the commit. So two transfers at once in opposite directions can each lock the row it changes first and wait for the other’s: a deadlock, as in A9. H2 notices, and rolls one of the two transfers back with an SQLException saying Deadlock detected; tried here with two threads, about one transfer in three failed that way. The cure is A9’s: change the two rows in name order, whichever account’s name comes first alphabetically, first.

    Your test: two threads, one transferring 1 cent from alice to bob 5,000 times, the other 1 cent back 5,000 times, both starting with 1,000.00. No transfer throws, and the two balances add up to 2,000.00. Mark it @Timeout(30).

  5. history. One SELECT of the account’s rows of transaction_record, ORDER BY id, each turned into an Entry: a row with no source is a deposit, one with no destination a withdrawal, and a transfer is TRANSFER_TO when the account is its source and TRANSFER_FROM when it is its destination.

    Your test: after a deposit, a withdrawal, a transfer out and a transfer in, the history holds those four kinds, oldest first.

  6. The server, on the database. In BankServer, change the one line that makes the bank:

    Bank bank = new DatabaseBank("jdbc:h2:./bank;DB_CLOSE_DELAY=-1");

    DB_CLOSE_DELAY=-1 keeps the database open between connections while the server runs. Nothing else in the server or in Session changes: they only ever used Bank’s seven methods.

    Done when a client registers, deposits and says bye; you stop the server with the red square and start it again; and a new client logs in and finds the same balance. bank.mv.db is the bank now: delete it to start over from nothing.

  7. Many clients at once. Every client’s thread uses the one DatabaseBank, each method on a connection of its own, and the database’s row locks do what A9’s synchronized blocks did.

    Your test: four threads each deposit 1 cent 1,000 times into one account; after the joins, the balance is 4,000 cents and the history holds 4,000 entries.

  8. Say what works. Fill in What works, and what does not in your repository’s README.md, as before: for each task, the names of your tests that check it, and anything you know does not work.

    Done when the section names at least one test of yours for each of tasks 1 to 7.

Optional — the auditor, in SQL

A9’s optional auditor added up every balance at one moment. With the accounts in a database, one statement does it, and a second one can check the books. Add to DatabaseBank, and to Session as a command AUDIT:

  • The total, SELECT SUM(balance) FROM customer: one statement, so the database answers it at one moment, with no lock of yours.
  • What the records say each balance should be. Every account’s balance is its deposits, minus its withdrawals, minus its transfers out, plus its transfers in. Build it in this order:
    • First the money that reached each account: SELECT destination, SUM(cents) FROM transaction_record GROUP BY destination, which skips the rows whose destination is NULL.
    • Then the money that left each: the same with source.
    • Then both at once, beside the balances: a LEFT JOIN of customer with each of the two, so an account with no records still appears, and COALESCE(x, 0) for its missing sums.
  • The worked example. alice deposits 10.00, withdraws 2.00, sends bob 3.00 and gets 1.00 back: in 10.00 and 1.00, out 2.00 and 3.00, so 11.00 minus 5.00, 6.00, which is her balance.
  • AUDIT answers AUDIT and the total, then a line for each account whose balance and records disagree, then OK. On a sound bank it is two lines.

Handing in

Commit and push. The last commit before the deadline is what gets marked.

Every push is graded, one check at a time, and the result lands on your feedback pull request, so push early and read what failed. The checks run A9’s checks against your DatabaseBank, each on a new database in memory, eight threads at once among them; then a second bank on the same database, a name with a quote in it, and your BankServer started, stopped and started again on bank.mv.db; then DatabaseBankTest, which needs your six tests and at least one that starts threads. A green check is a floor, not the mark: the mark is read off your code too, as the rubric’s rows describe.

A student presents this one in class on Monday, November 23.