JAVA / CAPSTONE PROJECTS
Project: a library catalogue backed by a real database
Store a library catalogue in a real SQL database from Java: schema with constraints, parameterised queries, generated keys and transactional borrowing.
What you will learn
- Design a schema whose UNIQUE, NOT NULL and CHECK constraints enforce catalogue rules
- Bind every value to a ? placeholder with PreparedStatement instead of building SQL
- Make borrow atomic with setAutoCommit(false), commit() and rollback() in the catch
- Read a ResultSet cursor into records inside its try block, then return the records
Understanding Project: a library catalogue backed by a real database
When the catalogue lives in a file, your Java code is the only thing standing between a typo and a corrupt catalogue: every rule about ISBNs, copy counts and missing titles is an if-statement somewhere. When it lives in a database, you declare those rules once in the schema. A column written as isbn VARCHAR(13) NOT NULL UNIQUE means no code path can ever create two records for the same book, not even a second copy of your program running at the same time, because the check happens inside the store rather than in one caller.
JDBC hands you three short-lived objects that each wrap something the database is holding open for you: a Connection is a session with a transaction attached, a PreparedStatement is a statement the database has already parsed plus one slot per parameter, and a ResultSet is a cursor positioned over rows still being streamed. That is why all three go in try-with-resources, and why a ResultSet is not a List: you copy rows into records inside the block and return those. The ? in a prepared statement is a slot in the parsed statement, not a hole in a string, so a title containing an apostrophe or the text DROP TABLE needs no escaping and can never change the shape of the query.
Autocommit is on by default, so each statement is its own transaction that commits the moment it succeeds. That is fine for adding one book and wrong for borrowing one, which is an UPDATE that decrements copies plus an INSERT that records the loan. Calling setAutoCommit(false) turns the pair into a single unit that commit() makes permanent and rollback() erases, which is what lets you stop asking "is a copy free?" in Java and instead let the database refuse a negative copy count and undo the half-done work.
// One jar on the classpath is enough: java -cp h2-2.2.224.jar Catalogue.java (JDK 17+)
import java.sql.*;
public class Catalogue {
public static void main(String[] args) throws SQLException {
try (Connection db = DriverManager.getConnection("jdbc:h2:mem:catalogue")) {
createSchema(db);
try (PreparedStatement add = db.prepareStatement(
"INSERT INTO book (isbn, title, copies) VALUES (?, ?, ?)")) {
insert(add, "9780134685991", "Effective Java", 3);
insert(add, "9781617294945", "Modern Java in Action", 2);
insert(add, "9780596009205", "Head First Design Patterns", 1);
}
try (PreparedStatement find = db.prepareStatement(
"SELECT id, title, copies FROM book WHERE title LIKE ? ORDER BY title")) {
find.setString(1, "%Java%");
try (ResultSet rs = find.executeQuery()) {
while (rs.next()) {
System.out.printf("#%d %-24s %d copies%n",
rs.getInt("id"), rs.getString("title"), rs.getInt("copies"));
}
}
}
try (PreparedStatement add = db.prepareStatement(
"INSERT INTO book (isbn, title, copies) VALUES (?, ?, ?)")) {
insert(add, "9780134685991", "Effective Java, 3rd ed.", 1);
} catch (SQLIntegrityConstraintViolationException e) {
System.out.println("duplicate isbn rejected, SQLState class "
+ e.getSQLState().substring(0, 2));
}
try (Statement s = db.createStatement();
ResultSet rs = s.executeQuery("SELECT COUNT(*) FROM book")) {
rs.next();
System.out.println("rows stored: " + rs.getInt(1));
}
}
}
static void createSchema(Connection db) throws SQLException {
try (Statement s = db.createStatement()) {
s.execute("""
CREATE TABLE book (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
isbn VARCHAR(13) NOT NULL UNIQUE,
title VARCHAR(200) NOT NULL,
copies INT NOT NULL CHECK (copies >= 0)
)""");
}
}
static void insert(PreparedStatement add, String isbn, String title, int copies)
throws SQLException {
add.setString(1, isbn);
add.setString(2, title);
add.setInt(3, copies);
add.executeUpdate();
}
}With a real database the schema owns the catalogue's rules and the transaction owns its consistency, leaving Java as a thin client that binds parameters and maps rows.
Worked examples
Borrowing as one transaction
Two statements succeed or fail together, and the database itself refuses to lend a copy that is not there.
import java.sql.*;
public class Borrowing {
public static void main(String[] args) throws SQLException {
try (Connection db = DriverManager.getConnection("jdbc:h2:mem:borrowing")) {
try (Statement s = db.createStatement()) {
s.execute("CREATE TABLE book (id INT PRIMARY KEY, "
+ "copies INT NOT NULL CHECK (copies >= 0))");
s.execute("CREATE TABLE loan (book_id INT NOT NULL REFERENCES book(id), "
+ "borrower VARCHAR(50) NOT NULL)");
s.execute("INSERT INTO book VALUES (1, 1)");
}
System.out.println("first borrow : " + borrow(db, 1, "ada"));
System.out.println("second borrow: " + borrow(db, 1, "grace"));
System.out.println("copies left : " + one(db, "SELECT copies FROM book WHERE id = 1"));
System.out.println("loan rows : " + one(db, "SELECT COUNT(*) FROM loan"));
}
}
static boolean borrow(Connection db, int bookId, String borrower) throws SQLException {
db.setAutoCommit(false);
try (PreparedStatement take = db.prepareStatement(
"UPDATE book SET copies = copies - 1 WHERE id = ?");
PreparedStatement log = db.prepareStatement(
"INSERT INTO loan (book_id, borrower) VALUES (?, ?)")) {
take.setInt(1, bookId);
take.executeUpdate();
log.setInt(1, bookId);
log.setString(2, borrower);
log.executeUpdate();
db.commit();
return true;
} catch (SQLException e) {
db.rollback();
return false;
} finally {
db.setAutoCommit(true);
}
}
static int one(Connection db, String sql) throws SQLException {
try (Statement s = db.createStatement(); ResultSet rs = s.executeQuery(sql)) {
rs.next();
return rs.getInt(1);
}
}
}Example explained
Line 1setAutoCommit(false) makes the UPDATE and the INSERT one unit, so nothing is durable until commit() runs.
Line 2CHECK (copies >= 0) rejects the second decrement inside the database, which is why borrow needs no prior SELECT to test availability.
Line 3rollback() in the catch undoes the decrement that already executed, so no loan row survives for a book that was unavailable.
Line 4The finally block puts autocommit back so later single-statement calls on this shared Connection behave as expected.
Why the placeholder is not string building
The same title breaks a concatenated INSERT and stores cleanly through a parameter.
import java.sql.*;
public class Quoting {
public static void main(String[] args) throws SQLException {
String title = "The Programmer's Brain";
try (Connection db = DriverManager.getConnection("jdbc:h2:mem:quoting")) {
try (Statement s = db.createStatement()) {
s.execute("CREATE TABLE book (title VARCHAR(100) NOT NULL)");
}
try (Statement s = db.createStatement()) {
s.executeUpdate("INSERT INTO book VALUES ('" + title + "')");
System.out.println("concatenated: inserted");
} catch (SQLException e) {
System.out.println("concatenated: rejected, SQLState class "
+ e.getSQLState().substring(0, 2));
}
try (PreparedStatement ps = db.prepareStatement("INSERT INTO book VALUES (?)")) {
ps.setString(1, title);
System.out.println("placeholder: rows inserted = " + ps.executeUpdate());
}
try (Statement s = db.createStatement();
ResultSet rs = s.executeQuery("SELECT title FROM book")) {
while (rs.next()) {
System.out.println("stored: " + rs.getString(1));
}
}
}
}
}Example explained
Line 1The concatenated text reads INSERT INTO book VALUES ('The Programmer's Brain'), so the parser ends the literal at Programmer and cannot make sense of the rest.
Line 2SQLState class 42 means a syntax problem, while class 23 (seen in the main example) means the SQL was fine but a constraint said no.
Line 3setString(1, title) ships the characters as a value, so apostrophes, semicolons and any injected SQL text stay data.
Line 4executeUpdate returns the affected row count, which is the only honest way to confirm an insert or update actually changed something.
Getting the id the database chose
Retrieves generated keys after an insert and maps result rows into records.
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class Ids {
record Book(int id, String title, int published) {}
public static void main(String[] args) throws SQLException {
try (Connection db = DriverManager.getConnection("jdbc:h2:mem:ids")) {
try (Statement s = db.createStatement()) {
s.execute("""
CREATE TABLE book (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
title VARCHAR(200) NOT NULL,
published INT NOT NULL
)""");
}
System.out.println("stored with id " + add(db, "Java Concurrency in Practice", 2006));
System.out.println("stored with id " + add(db, "Modern Java in Action", 2018));
for (Book b : newest(db)) {
System.out.println(b.published() + " " + b.title() + " id=" + b.id());
}
}
}
static int add(Connection db, String title, int published) throws SQLException {
try (PreparedStatement ps = db.prepareStatement(
"INSERT INTO book (title, published) VALUES (?, ?)",
Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, title);
ps.setInt(2, published);
ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
keys.next();
return keys.getInt(1);
}
}
}
static List<Book> newest(Connection db) throws SQLException {
List<Book> books = new ArrayList<>();
try (PreparedStatement ps = db.prepareStatement(
"SELECT id, title, published FROM book ORDER BY published DESC");
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
books.add(new Book(rs.getInt("id"), rs.getString("title"), rs.getInt("published")));
}
}
return books;
}
}Example explained
Line 1Statement.RETURN_GENERATED_KEYS asks the driver to bring back the identity value the database assigned, which you need before you can insert loan rows that reference the book.
Line 2getGeneratedKeys() returns a one-row cursor, so keys.next() must be called before keys.getInt(1) reads it.
Line 3newest() converts rows to Book records inside the try block, so the returned list stays valid after the statement and cursor are closed.
Line 4ORDER BY published DESC sorts in the database rather than in Java, so the ordering can use an index and the JVM never holds rows it does not need.
Important notes
DriverManager finds the driver through the service loader, so no Class.forName is needed; the message "No suitable driver found for jdbc:h2:mem:catalogue" means the jar is missing from the classpath, not that the SQL is wrong.
A jdbc:h2:mem: database exists only while a connection to it is open, so closing your last Connection discards the catalogue; that is convenient for tests and useless as storage.
Common mistakes
Concatenating a title or ISBN into the SQL string: a real title such as The Programmer's Brain becomes a syntax error, and a hostile value becomes an injected statement.
Leaving autocommit on inside borrow: the decrement commits on its own, so when the loan INSERT fails the catalogue permanently shows one fewer copy with nobody holding it.
Returning the ResultSet from a search method: try-with-resources closes the statement as the method returns, so the caller's first rs.next() throws "The object is already closed".
Reading copies with a SELECT, deciding in Java that a copy is free, then updating: two callers can both pass the check, and only a constraint or a conditional UPDATE stops the count going negative.
Try it yourself
Change, predict, then run
Add a returnBook(Connection, int bookId, String borrower) method that, in one transaction, deletes that borrower's loan row and increments copies only when the DELETE affected exactly one row. Print false and roll back when it did not.
Open the Java workspaceCheck your understanding
Your borrow() method runs UPDATE book SET copies = copies - 1 and then an INSERT into loan, which fails because borrower is NULL. You never touched autocommit. What state is the catalogue in?
- The copy count is already decremented and stays decremented, and there is no loan row
- Both statements are undone, because a failed statement rolls back everything on the connection
- Both statements are applied, because the driver retries the INSERT with a default borrower
- Neither statement is applied, because JDBC holds statements until you call commit()
Show answer
With autocommit on, each executeUpdate is its own transaction that commits as soon as it succeeds, so the decrement was already permanent before the INSERT was even sent. The last option is tempting because it describes exactly what happens after setAutoCommit(false), where the two statements form one unit that commit() finishes and rollback() discards, but that only applies when you have explicitly turned autocommit off.