Querying and mapping rows to objects

Written Explanation

Prepare, bind, execute

String sql = "SELECT id, title, isbn, copies FROM books WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setInt(1, 2);
    try (ResultSet rs = ps.executeQuery()) {
        // read rows
    }
}

Three ideas:

1. The SQL text contains ? placeholders for values. Never type the value into the string.
2. setInt(1, 2) fills the first placeholder with 2. Positions start at 1, not 0; the Java documentation says the first parameter is 1, the second is 2.
3. Choose the setter that matches the SQL type: setInt for INTEGER, setString for text. The driver sends the value separately from the SQL text. (Why that matters is lesson 4's whole story.)

A placeholder stands for a value only. You cannot use ? for a table or column name, and you must not put quotes around it: title = '?' contains a literal question mark inside text, not a placeholder.

executeQuery and executeUpdate

Using the wrong one fails. In our run, executeQuery() on an UPDATE threw No results were returned by the query. and executeUpdate() on a SELECT threw A result was returned when none was expected. One more surprise: with auto-commit on (the default, see lesson 5), the UPDATE had already been executed by the time the exception was thrown. A wrong method choice can still change data.

Reading the ResultSet

A ResultSet is a cursor that starts before the first row. Each call to next() moves to the next row and returns true, or returns false when the rows run out. That gives the standard loop:

while (rs.next()) {
    int id = rs.getInt("id");
    String title = rs.getString("title");
}

Read columns by name (getString("title")) so reordering the SELECT does not break your code. Names are case-insensitive. If you select an expression without a name, such as COUNT(*), read it by index, which also starts at 1: rs.getInt(1).

Reading before the first next(), or reading a column that is not in the query, throws SQLException. We saw ResultSet not positioned properly, perhaps you need to call next. and The column name nope was not found in this ResultSet.

Mapping a row to an object

Put the mapping in one small method so every query reuses it:

private static Book map(ResultSet rs) throws SQLException {
    return new Book(rs.getInt("id"), rs.getString("title"),
            rs.getString("isbn"), rs.getInt("copies"));
}

With Book as a record:

public record Book(int id, String title, String isbn, int copies) { }

Then a method that returns many rows builds a list, and one that returns at most one row returns an Optional.

Order and column choice

A SELECT without ORDER BY returns rows in no promised order, so add ORDER BY id whenever the order matters, as findAll does. Also name the columns you need instead of SELECT *: the query then keeps working if someone adds a column to the table, and your map method documents exactly what it reads. For a table with millions of rows, findAll would be a bad idea; a LIMIT ? parameter (Level C) keeps results bounded.

Zero or one row: Optional

findById has two honest outcomes: a book, or no book. Returning null hides the second outcome and invites a NullPointerException. Optional<Book> makes the caller deal with it. Code that finds nothing returns Optional.empty().

SQL NULL

A column that allows NULL can come back empty. getString returns null for a NULL. But getInt returns 0 for NULL, which looks exactly like a real zero. Two safe ways: call rs.wasNull() immediately after the getter, or read with getObject("col", Integer.class), which gives null. Example 3 shows both.