Core Java · Chapter 42 of 48

JDBC transactions and SQL injection

By the end of this chapter you can make several SQL statements succeed or fail together, send many updates as one batch, and keep text typed by a person out of your SQL. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Beyond the basics
  • 5 tasks
  • Java
  • In the all labs plan

The first 2 chapters of Core Java are free. This one comes with the all labs plan.

Why you need this

Ravi has a printout from NDLS: five masala chai sold from a counter that had three. The sale row was saved, the stock update was not. Then Anjali forwards a bug report: looking up Nani's ladoo on the menu crashes the counter. Both bugs are in StockDao, the class every counter uses to sell, move and restock.

With the JDBC from chapter 41, every executeUpdate is saved the moment it runs: when the second of two statements fails, the first one stays. And a query built by joining strings runs whatever the user typed as SQL.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1 · interview question

    Interview: StockDao.java, close the SQL injection hole in the price lookup

    In StockDao.java, rewrite priceOf(item) so the item name is sent as a value, through a PreparedStatement with a ? placeholder, and is never glued into the SQL text. It returns an Optional holding the price, or an empty Optional when no menu item has exactly that name. A name containing SQL must find nothing, and a name with an apostrophe must not throw.

    For example, priceOf("Masala chai") returns Optional[40]; priceOf("Filter coffee"), priceOf("nothing' or '1'='1") and priceOf("Nani's ladoo") all return an empty Optional.

  2. Task 2

    StockDao.java: make a sale and its stock update one transaction

    In StockDao.java, make sell(station, item, qty) run its two statements, the sale row and the stock update, in one transaction: switch auto-commit off, commit when both worked, and when anything throws, roll back and throw the same exception on to the caller. Afterwards, always put auto-commit back to what it was before (on, in the checks). Another connection must see both changes or neither.

    For example, NDLS has 3 masala chai: sell("NDLS", "Masala chai", 5) throws an SQLException (the database refuses stock below 0), and afterwards NDLS has no sale row and still 3 masala chai. Then sell("NDLS", "Masala chai", 2) commits one sale with amount 80 and leaves 1.

  3. Task 3

    StockDao.java: move stock between two stations, both sides or neither

    In StockDao.java, make transfer(from, to, item, qty) take qty of the item off the from station's stock and add qty to the to station's stock, in one transaction. Too little stock at from: the database refuses with an SQLException. A station with no stock row for the item: throw an IllegalArgumentException (an update that matches no row does not throw on its own). On either failure, roll back so nothing changed and throw the exception on. Put auto-commit back to what it was before.

    For example, transfer("SBC", "NDLS", "Bun maska", 2) leaves SBC with 3 and NDLS with 6. Then transfer("SBC", "CSMT", "Bun maska", 2) throws an IllegalArgumentException (CSMT has no bun maska row) and SBC still has 3; transfer("NDLS", "SBC", "Masala chai", 4) throws an SQLException (NDLS has only 3) and NDLS keeps 3, SBC 10.

  4. Task 4

    StockDao.java: restock a station in one batch, all or nothing

    In StockDao.java, make restock(station, delivery) add each quantity in the delivery map (item to quantity) to that station's stock. Send the updates as one batch, inside one transaction. If any quantity is zero or less, throw an IllegalArgumentException and change nothing. If the station has no stock row for any item in the delivery, throw an IllegalArgumentException and roll back, so no item from that delivery is kept. Put auto-commit back to what it was before.

    For example, restock("SBC", Map.of("Masala chai", 5, "Bun maska", 10)) leaves SBC with 15 masala chai and 15 bun maska. A delivery of 4 masala chai, 6 cutting chai and 10 bun maska to CSMT throws (CSMT has no bun maska row), and CSMT keeps 6 masala chai and 12 cutting chai. restock("SBC", Map.of("Cutting chai", -3)) throws, and SBC keeps 20.

  5. Task 5

    StockDao.java: read a station's sales back as records

    In StockDao.java, make salesAt(station) return every sale row of that station as Sale records (station, item, qty, amount), oldest first, which is the order of the sale table's id. A station with no sales gives an empty list.

    For example, with sales SBC 2 Masala chai 80, NDLS 1 Bun maska 50 and SBC 3 Cutting chai 75 added in that order, salesAt("SBC") returns [Sale[station=SBC, item=Masala chai, qty=2, amount=80], Sale[station=SBC, item=Cutting chai, qty=3, amount=75]], and salesAt("CSMT") returns an empty list.

What you will learn

  • Transactions: switching auto-commit off, then commit or rollback, so that two statements happen together or not at all.
  • Batches: addBatch and executeBatch, many updates in one trip, with one count per update.
  • SQL injection: why gluing text into SQL is a hole, and why a ? placeholder closes it.

More in Beyond the basics

Build "JDBC transactions and SQL injection" yourself

Tapri runs chai counters at railway stations, and every counter runs a Java program you write. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Core Java are free. This one comes with the all labs plan.