PreparedStatement and Parameters

Bind values to ? placeholders with the right setters, handle NULL, read generated keys and batch inserts without a round trip per row.

Syntax

PreparedStatement ps = conn.prepareStatement("UPDATE users SET name = ? WHERE id = ?");
ps.setString(1, name);         // first ? is 1, not 0
ps.setLong(2, id);
int rows = ps.executeUpdate(); // rows affected

A PreparedStatement is SQL text with ? placeholders, kept apart from the values that fill them. The values are bound with typed setters: the PostgreSQL driver sends them separately from the SQL, and drivers that build the final statement on the client, such as MySQL Connector/J by default, escape them correctly for that server. Either way a value can never change the statement. That is what makes it the defence against SQL injection, and for any query that takes input it is the only statement type you need.

Binding values

Placeholders are numbered from 1, in the order they appear in the text. Use the setter that matches the column: setLong, setString, setBoolean, and setBigDecimal for money. For dates and times, setObject(i, localDate) and setObject(i, offsetDateTime) pass java.time values straight through, so the old java.sql.Date and Timestamp classes are not needed.

Primitive setters cannot express SQL NULL. For a nullable value call setNull(i, Types.VARCHAR), or setObject(i, value, Types.VARCHAR), which binds NULL when the value is null.

Running it

executeQuery() runs a SELECT and returns a ResultSet.
executeUpdate() runs an INSERT, UPDATE or DELETE and returns the number of rows affected. Check it: an update that changed 0 rows usually means the id does not exist, which is a 404 in an API, not a success.

Generated keys

To get the id the database assigned, prepare the statement with the key column names, conn.prepareStatement(sql, new String[] {"id"}), and read getGeneratedKeys() after the update. In PostgreSQL you can also write INSERT ... RETURNING id and call executeQuery().

Batches

Inserting 1,000 rows with 1,000 executeUpdate calls costs 1,000 network round trips. Bind each row, call addBatch(), then call executeBatch() once. Run the batch inside a transaction so a failure does not leave half the rows behind. The PostgreSQL driver's reWriteBatchedInserts=true and MySQL's rewriteBatchedStatements=true URL options go further and turn the batch into multi-row inserts.

Mistakes people make

Quoting a placeholder. '?' is a string literal containing a question mark, not a parameter.
Expecting a placeholder to stand for a table name, a column name or ASC/DESC. Placeholders hold values only.
Binding a whole list to one ? in IN (?). Generate one placeholder per element instead, and handle the empty list before building the SQL, because IN () is a syntax error.
Reusing one statement across threads. Like its connection, a statement belongs to one unit of work.

Example

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.time.LocalDate;
import javax.sql.DataSource;

final class OrderWriter {
    private final DataSource dataSource;

    OrderWriter(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    long create(long customerId, BigDecimal total, LocalDate deliveryDate, String note) throws SQLException {
        String sql = """
            INSERT INTO orders (customer_id, total, delivery_date, note)
            VALUES (?, ?, ?, ?)
            """;
        // Ask for the generated "id" column back.
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql, new String[] {"id"})) {
            ps.setLong(1, customerId);
            ps.setBigDecimal(2, total);
            ps.setObject(3, deliveryDate);           // LocalDate maps to SQL DATE
            ps.setObject(4, note, Types.VARCHAR);     // binds NULL when note is null
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                keys.next();
                return keys.getLong(1);
            }
        }
    }
}

When to use it

Insert a new order from a POST body and return its database-generated id in the Location header.
Import a CSV upload of thousands of rows with one batched statement instead of one round trip per row.
Fetch the products for a cart in one query with an IN list built from the cart's product ids.

More examples

Batch insert inside a transaction

executeBatch returns an int array of update counts; with the batch-rewriting URL options the driver may report Statement.SUCCESS_NO_INFO (-2) instead of exact counts. For very large imports, execute the batch every few thousand rows to bound memory.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

record OrderLine(long productId, int quantity) {}

final class OrderLines {
    static void insertAll(Connection conn, long orderId, List<OrderLine> lines) throws SQLException {
        String sql = "INSERT INTO order_lines (order_id, product_id, quantity) VALUES (?, ?, ?)";
        boolean previous = conn.getAutoCommit();
        conn.setAutoCommit(false);
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (OrderLine line : lines) {
                ps.setLong(1, orderId);
                ps.setLong(2, line.productId());
                ps.setInt(3, line.quantity());
                ps.addBatch();                        // queued, not sent yet
            }
            ps.executeBatch();                        // one round trip (or a few) for all rows
            conn.commit();
        } catch (SQLException | RuntimeException e) {
            conn.rollback();                          // before the finally re-enables auto-commit
            throw e;
        } finally {
            conn.setAutoCommit(previous);
        }
    }
}

One placeholder per IN-list element

Only the count of question marks comes from the input, never the values, so the SQL text stays safe. Cap the list size in the API (for example 100 ids), since databases limit the number of bind parameters in one statement.

import java.util.Collections;
import java.util.List;

String placeholders(int count) {
    if (count == 0) {
        throw new IllegalArgumentException("IN list must not be empty");
    }
    return String.join(", ", Collections.nCopies(count, "?"));
}

void main() {
    List<Long> ids = List.of(7L, 12L, 31L);
    String sql = "SELECT id, name FROM products WHERE id IN (" + placeholders(ids.size()) + ")";
    IO.println(sql);
    // Then bind each value in order: ps.setLong(i + 1, ids.get(i))
    for (int i = 0; i < ids.size(); i++) {
        IO.println("parameter " + (i + 1) + " = " + ids.get(i));
    }
    try {
        placeholders(0);
    } catch (IllegalArgumentException e) {
        IO.println("rejected: " + e.getMessage());
    }
}

// Output:
// SELECT id, name FROM products WHERE id IN (?, ?, ?)
// parameter 1 = 7
// parameter 2 = 12
// parameter 3 = 31
// rejected: IN list must not be empty

Using the update count

Ignoring the count is how a PUT to a missing id ends up returning 200. The row count is free information from the database, so turn it into the right status code.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;

final class ProductCommands {
    private final DataSource dataSource;

    ProductCommands(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /** Returns false when no product has this id, so the handler can answer 404. */
    boolean rename(long id, String name) throws SQLException {
        String sql = "UPDATE products SET name = ?, updated_at = now() WHERE id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setLong(2, id);
            return ps.executeUpdate() == 1;
        }
    }
}

Discussion

Be the first to comment on this lesson.

Binding values

Running it

Generated keys

Batches

Mistakes people make

Example

When to use it

More examples

Batch insert inside a transaction

One placeholder per IN-list element

Using the update count

Related

Discussion