Spring Boot services · Chapter 15 of 41

Plain SQL with JdbcClient

By the end of this chapter you can read and write a database table with SQL you write yourself, get the rows back as records, and keep user input 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: Data
  • 6 tasks
  • Spring Boot
  • In the all labs plan

The first 2 chapters of Spring Boot services are free. This one comes with the all labs plan.

Why you need this

Tapri Cloud has kept the menu in a Java list so far. Every restart wipes Ravi's price changes, and two servers would disagree about what cutting chai costs. "Put it in a database," Meera says. "Today."

In the Java lab you wrote JDBC by hand: open a connection, prepare a statement, set each value, walk the result set, copy each column into an object, close three resources, and catch a checked SQLException around all of it. For every query. Kabir wants the SQL to stay yours, and the rest to go.

What you build: 6 tasks

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

  1. Task 1

    data.sql: seed the four menu items

    Make data.sql insert the menu every counter starts with, in this order: Masala chai 40, Cutting chai 25, Bun maska 50 and Parle-G 10. Right after startup, menu_item holds exactly these four rows, in id order.

    Keep the Masala chai insert that is already there, and leave the id out: the identity column numbers the rows.

  2. Task 2

    MenuRepository.java: list the whole menu

    Write MenuRepository.findAll() so it returns every row of menu_item as a MenuItem (id, name and price), in id order.

    An empty table gives an empty list.

  3. Task 3

    MenuRepository.java: find one item by name

    Write MenuRepository.findByName(name) so it returns the item whose name is exactly name, as an Optional MenuItem (findByName("Bun maska") gives Bun maska with its id and price 50).

    A name that is not on the menu (Samosa) gives Optional.empty(), not an exception. Bind the name as a named parameter.

  4. Task 4

    MenuRepository.java: insert an item and return its new id

    Write MenuRepository.add(name, price) so it inserts a new row into menu_item and returns the id the database generated for it.

    The id returned is the one now stored for that name, and an item added later gets a bigger id. The price is stored as given (Poha, 35).

  5. Task 5

    MenuRepository.java: change a price and count the rows

    Write MenuRepository.updatePrice(name, price) so it sets the price of the item with that name and returns how many rows changed: 1 for Cutting chai (now 30), and 0 for Samosa, which is not on the menu (not an exception).

    Every other item keeps its price.

  6. Task 6 · interview question

    Interview: search the menu safely, without SQL injection

    Write MenuRepository.search(text) so it returns the items whose name contains text, ignoring case, in id order: search("CHAI") gives Masala chai, then Cutting chai.

    Bind the text as a parameter, never glue it into the SQL: search("ravi's") finds Ravi's special with no SQL error, and search("' or 1=1 --") finds nothing. Be ready to explain SQL injection and how bound parameters stop it.

What you will learn

  • schema.sql and data.sql: scripts Spring Boot runs at startup to create and fill the tables.
  • JdbcClient: one fluent call that runs your SQL, binds the values and maps each row to a record.
  • Generated keys: reading the id the database just gave a new row.
  • Bound parameters: why input must never be glued into SQL (the interview question).

More in Data

Build "Plain SQL with JdbcClient" yourself

Tapri Cloud: the counters’ Java grows into a Spring Boot service with a database. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Spring Boot services are free. This one comes with the all labs plan.