Application Integration & Schema Evolution

Prepared Statements and Parameter Binding

A prepared statement separates fixed SQL structure from typed data values. Binding protects the value boundary and can reuse protocol or planning work, but it neither validates dynamic SQL structure nor guarantees one optimal cached plan.

Placeholders represent values, not grammar.
Binding preserves the code/data boundary.
Type choice is part of correctness.
Preparation and plan caching differ.
Lifetime follows a session unless documented otherwise.
Dynamic identifiers require an allowlist.

JDBC value binding on the commerce schema

String sql = """
    SELECT order_id, status, placed_at
    FROM orders
    WHERE customer_id = ? AND placed_at >= ?
    ORDER BY placed_at DESC, order_id DESC
    """;
try (PreparedStatement ps = connection.prepareStatement(sql)) {
  ps.setLong(1, customerId);
  ps.setTimestamp(2, Timestamp.from(fromInstant), UTC_CALENDAR);
  try (ResultSet rows = ps.executeQuery()) {
    while (rows.next()) consume(rows);
  }
}

Allowlisted dynamic order

String orderBy = switch (requestedSort) {
  case "newest" -> "placed_at DESC, order_id DESC";
  case "oldest" -> "placed_at ASC, order_id ASC";
  default -> throw new IllegalArgumentException("unsupported sort");
};
String sql = "SELECT order_id, status, placed_at FROM orders "
           + "WHERE customer_id = ? ORDER BY " + orderBy;
// Only the constant fragment is concatenated; customer_id remains bound.

Three distinct mechanisms
Mechanism	Primary purpose	Does not guarantee
Value binding	preserve type and code/data boundary	safe identifiers or optimal plan
Server preparation	name parsed statement in one session	cross-session lifetime
Plan caching	reuse some planning artifacts	same plan or best plan for every value