Core Java · Chapter 41 of 48
JDBC basics
By the end of this chapter you can connect to a database from Java, create a table, insert, update and query rows with PreparedStatement, and turn the rows into records. 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
- 6 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
Anjali changed the price of cutting chai to ₹30 last week. Twelve counters still charge ₹25, because the menu is a Map written into the code, and a new price means a new build at every station. Meera wants the menu in a database: change it once, and every counter sees it.
Files from chapter 37 could hold the menu, but every counter would read and rewrite the whole file, two counters writing at once would overwrite each other, and finding one item means reading everything. A database does all of that for you; Java needs a way to talk to it.
What you build: 6 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
Database.java: connect to an in-memory H2 database
In Database.java, make open(name) return a connection from DriverManager to the in-memory H2 database with that name, whose URL is jdbc:h2:mem: followed by the name and ;DB_CLOSE_DELAY=-1, so the database outlives the connection and a second connection with the same name sees the same tables.
For example, open("tapri") connects to jdbc:h2:mem:tapri;DB_CLOSE_DELAY=-1; a table created through it is still there for the next open("tapri").
- Task 2
MenuRepository.java: create the menu_item table
In MenuRepository.java, make createTable() create the table menu_item exactly as in its comment: id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, price INT NOT NULL. Use the connection the repository was given and do not close it.
For example, afterwards two inserted items get the ids 1 and 2; a second Masala chai, a row with no price and a row with no name are all refused with an SQLException.
- Task 3
MenuRepository.java: insert an item and return its new id
In MenuRepository.java, make add(name, price) insert one row with that name and price into menu_item, using a PreparedStatement with ? placeholders, and return the id the database generated for it. A name with an apostrophe is stored as it is.
For example, on an empty table add("Masala chai", 40) returns 1, add("Cutting chai", 25) returns 2 and add("Nani's special", 60) returns 3, leaving the rows 1 Masala chai 40, 2 Cutting chai 25 and 3 Nani's special 60.
- Task 4
MenuRepository.java: read every row into a list of MenuItem records
In MenuRepository.java, make findAll() return every row of menu_item as a MenuItem record (id, name, price), cheapest first, and by name A to Z when two items cost the same. An empty table gives an empty list.
For example, Masala chai 40 (id 1), Cutting chai 25 (id 2), Bun maska 50 (id 3) and Elaichi chai 40 (id 4) give [MenuItem[id=2, name=Cutting chai, price=25], MenuItem[id=4, name=Elaichi chai, price=40], MenuItem[id=1, name=Masala chai, price=40], MenuItem[id=3, name=Bun maska, price=50]].
- Task 5
MenuRepository.java: find one item by name, or an empty Optional
In MenuRepository.java, make findByName(name) return the item whose name is exactly that name, wrapped in an Optional, or an empty Optional when there is none. The name must be a value in a placeholder, never part of the SQL text.
For example, with Masala chai 40 and Bun maska 50, findByName("Bun maska") returns Optional[MenuItem[id=2, name=Bun maska, price=50]]; findByName("Kulfi") and findByName("' OR '1'='1") return an empty Optional.
- Task 6
MenuRepository.java: change a price and report the rows changed
In MenuRepository.java, make updatePrice(name, price) set the price of the item with that name, leave every other item as it was, and return how many rows changed: 1, or 0 when there is no such item (no exception).
For example, with Masala chai 40 and Cutting chai 25, updatePrice("Cutting chai", 30) returns 1 and leaves Masala chai at 40; updatePrice("Kulfi", 60) returns 0 and changes nothing.
What you will learn
- JDBC and a driver: one API, java.sql, for every database, and a URL that picks the database.
- PreparedStatement: SQL with ? placeholders, and values set apart from the SQL text.
- ResultSet: a cursor over the rows a query returns.
- Closing: connections, statements and result sets in try-with-resources.
More in Beyond the basics
Build "JDBC basics" 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.