Prepared statements

A PreparedStatement sends the SQL and the values separately: the statement goes first with placeholders, the database parses it into a plan, and the values arrive afterwards as values. That one property is worth two different things — a security guarantee and a performance one — and they are worth separating, because people learn the first and miss the second.

The security half has its own lesson, Injection, in the security course: why parameters work where escaping does not, and what placeholders cannot cover. This lesson is the JDBC mechanism.

#The shape

try (PreparedStatement ps = c.prepareStatement(
        "select id, name from item where price < ? and name like ?")) {
    ps.setInt(1, 500);
    ps.setString(2, "b%");
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) { ... }
    }
}

Three things that trip people on the first day:

• Parameter indexes start at 1, not 0. Everything else in Java starts at 0; this does not, because it follows SQL.
• A ? stands for one value. where id in (?) cannot take a list. You build in (?, ?, ?) for the size you have, or use setArray where the driver supports it.
• The % belongs in the value, not the SQL. like ? with "b%" — not like '?%', which is a literal question mark.

#Setting NULL

ps.setString(2, null);              // works for most drivers
ps.setNull(2, Types.VARCHAR);       // explicit, and portable

The typed form exists because some drivers cannot infer the SQL type of a null and will reject the statement. setNull is the habit worth having, and it is also self-documenting at the call site.

#The plan cache, which is the half people miss

Parsing SQL and choosing a plan is real work — for a complex query it can cost more than running it. Because a prepared statement's text never changes, the database can keep the plan and reuse it for every set of values.

Concatenated SQL cannot be cached, because every execution is a different string:

select * from item where price < 500     -- a new statement
select * from item where price < 750     -- and another
select * from item where price < 900     -- and another

A thousand values means a thousand parses and a thousand cache entries, evicting everything else. The same query as a parameterised statement is one entry, parsed once.

#Reuse the statement, not just the string

The performance argument only pays if you prepare once and execute many times:

// prepared once, outside the loop
try (PreparedStatement ps = c.prepareStatement("insert into event values (?, ?)")) {
    for (Event e : events) {
        ps.setInt(1, e.id);
        ps.setString(2, e.payload);
        ps.executeUpdate();
    }
}

Preparing inside the loop asks the driver to prepare a thousand times. Many drivers and pools cache prepared statements per connection anyway — HikariCP's cachePrepStmts for MySQL, PostgreSQL's own server-side cache after a few executions — but writing the loop this way is free and does not depend on a setting being on.

And note what this loop still does badly: one network round trip per row. That is the batching lesson, next.

#Getting the generated key back

An insert into a table with a generated id usually needs that id:

try (PreparedStatement ps = c.prepareStatement(
        "insert into orders (customer_id, total) values (?, ?)",
        Statement.RETURN_GENERATED_KEYS)) {
    ps.setLong(1, customerId);
    ps.setInt(2, total);
    ps.executeUpdate();
    try (ResultSet keys = ps.getGeneratedKeys()) {
        if (keys.next()) orderId = keys.getLong(1);
    }
}

The flag has to be passed at prepareStatement time — asking afterwards is too late, because the driver had to tell the database to return them. On PostgreSQL the more explicit alternative is insert ... returning id, executed as an ordinary query.

#executeQuery, executeUpdate, execute

• executeQuery returns a ResultSet. For a select.
• executeUpdate returns the number of rows affected. For insert, update, delete — and that number is worth checking. An update that affects 0 rows usually means the where matched nothing, which is a silent no-op you would rather hear about.
• execute returns a boolean and is for when you do not know which you have. Rare in application code.

#Where this shows up above JDBC

JdbcTemplate and JdbcClient use prepared statements for every parameterised call — you get the mechanism without writing it. JPA does too: setParameter on a JPQL query becomes a bind variable, which is why the JPA course insists on named parameters rather than string building, and why a native @Query with concatenation gives up both halves of this lesson at once.