Navigation

Java jdbc 6 min read

PreparedStatement

PreparedStatement is the go-to JDBC interface for executing SQL that contains parameters. It pre-compiles the query once, lets you plug in values safely at runtime, and reuses the compiled plan — giving you security against SQL injection and better performance for repeated queries.

Why PreparedStatement?

The plain Statement interface sends raw SQL strings to the database every time. That works fine for one-off DDL commands, but it has two serious problems for everyday application code:

SQL injection — if you concatenate user input into the string, an attacker can manipulate the query.
Performance — the database parses and plans the same SQL on every call.

PreparedStatement solves both: you write the SQL once with ? placeholders, the database compiles an execution plan, and then you bind typed values before each execution. The plan is reused; the values stay separate from the SQL structure.

Creating a PreparedStatement

You get a PreparedStatement from a live Connection by calling prepareStatement(sql):

import java.sql.*;

public class PrepareExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/school";
        String sql = "SELECT id, name FROM students WHERE grade = ?";

        try (Connection conn = DriverManager.getConnection(url, "root", "secret");
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, "A");           // bind first ? to "A"
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println(rs.getInt("id") + " -> " + rs.getString("name"));
                }
            }
        }
    }
}

Output:

1 -> Alice
3 -> Carol

Parameter indices are 1-based (not 0-based like arrays). The first ? is index 1, the second is 2, and so on.

Setting Parameters

PreparedStatement provides a typed setter for every Java and SQL type you’ll encounter.

Setter	Java type	SQL type
`setInt(n, value)`	`int`	`INTEGER`
`setLong(n, value)`	`long`	`BIGINT`
`setDouble(n, value)`	`double`	`DOUBLE`
`setString(n, value)`	`String`	`VARCHAR`, `CHAR`
`setBoolean(n, value)`	`boolean`	`BOOLEAN`
`setDate(n, value)`	`java.sql.Date`	`DATE`
`setTimestamp(n, value)`	`java.sql.Timestamp`	`TIMESTAMP`
`setBigDecimal(n, value)`	`BigDecimal`	`DECIMAL`, `NUMERIC`
`setBytes(n, value)`	`byte[]`	`BLOB`, `BINARY`
`setNull(n, sqlType)`	—	sets `NULL`

Tip: For monetary values, always use setBigDecimal rather than setDouble. Floating-point arithmetic loses precision that you cannot afford in financial calculations.

INSERT, UPDATE, DELETE

For write operations, call executeUpdate() instead of executeQuery():

import java.sql.*;

public class InsertExample {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/school";
        String sql = "INSERT INTO students (name, grade) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(url, "root", "secret");
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, "Diana");
            ps.setString(2, "B");
            int rows = ps.executeUpdate();
            System.out.println("Rows inserted: " + rows);
        }
    }
}

Output:

Rows inserted: 1

To update or delete, write the corresponding SQL with ? placeholders and follow the same pattern.

String updateSql = "UPDATE students SET grade = ? WHERE name = ?";
PreparedStatement ps = conn.prepareStatement(updateSql);
ps.setString(1, "A");
ps.setString(2, "Diana");
int updated = ps.executeUpdate();

Retrieving Auto-Generated Keys

When you insert into a table with an auto-increment primary key, pass Statement.RETURN_GENERATED_KEYS to prepareStatement:

String sql = "INSERT INTO students (name, grade) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    ps.setString(1, "Eve");
    ps.setString(2, "A");
    ps.executeUpdate();

    try (ResultSet keys = ps.getGeneratedKeys()) {
        if (keys.next()) {
            System.out.println("New ID: " + keys.getLong(1));
        }
    }
}

Output:

New ID: 5

Batch Inserts with PreparedStatement

For bulk operations, batching dramatically reduces the number of network round-trips. Call addBatch() after setting parameters, then executeBatch() at the end:

import java.sql.*;

public class BatchInsertExample {
    public static void main(String[] args) throws Exception {
        String url  = "jdbc:mysql://localhost:3306/school";
        String sql  = "INSERT INTO students (name, grade) VALUES (?, ?)";

        String[][] data = {
            {"Frank", "B"},
            {"Grace", "A"},
            {"Hank",  "C"}
        };

        try (Connection conn = DriverManager.getConnection(url, "root", "secret");
             PreparedStatement ps = conn.prepareStatement(sql)) {

            conn.setAutoCommit(false);

            for (String[] row : data) {
                ps.setString(1, row[0]);
                ps.setString(2, row[1]);
                ps.addBatch();
            }

            int[] results = ps.executeBatch();
            conn.commit();
            System.out.println("Batch complete, statements: " + results.length);
        }
    }
}

Output:

Batch complete, statements: 3

Warning: Always wrap batches in a transaction (setAutoCommit(false) + commit()/rollback()). If one row fails mid-batch, a BatchUpdateException is thrown — call conn.rollback() to undo the partial writes. See Transaction Management for the full pattern.

Reusing a PreparedStatement

One of the biggest performance wins is running the same PreparedStatement in a loop with different parameters. The SQL is parsed only once:

String sql = "UPDATE products SET price = ? WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    Map<Integer, Double> priceUpdates = Map.of(1, 9.99, 2, 14.99, 3, 4.49);

    conn.setAutoCommit(false);
    for (Map.Entry<Integer, Double> entry : priceUpdates.entrySet()) {
        ps.setDouble(1, entry.getValue());
        ps.setInt(2, entry.getKey());
        ps.executeUpdate();
    }
    conn.commit();
}

You can also call ps.clearParameters() between executions to reset all bound values explicitly, though simply overwriting them with new set* calls is sufficient.

SQL Injection Prevention

This is the most critical reason to use PreparedStatement. Consider the difference:

// DANGEROUS — never concatenate user input
String name = request.getParameter("name"); // attacker sends: ' OR '1'='1
String sql  = "SELECT * FROM users WHERE name = '" + name + "'";
// actual SQL: SELECT * FROM users WHERE name = '' OR '1'='1'  — returns ALL rows

// SAFE — use PreparedStatement
String safeSql = "SELECT * FROM users WHERE name = ?";
PreparedStatement ps = conn.prepareStatement(safeSql);
ps.setString(1, name); // driver escapes the value automatically

The driver sends the value as data, not as SQL syntax. No matter what the user types, it cannot alter the query structure.

Note: PreparedStatement parameterises values, not identifiers (table names, column names). You cannot use ? for a dynamic table name — those still require careful allow-list validation in your Java code.

Under the Hood

When you call conn.prepareStatement(sql), the driver sends a prepare message to the database server. The server:

Parses the SQL and validates it against the schema.
Builds an optimised execution plan.
Stores the plan, returning a server-side statement handle to the driver.

On each subsequent executeQuery() / executeUpdate(), the driver sends only the handle and the bound parameter values — not the full SQL text. The server skips parsing and planning entirely and executes the cached plan directly.

This matters most when:

The same query runs hundreds of times per second (typical in web apps).
The query has a complex join or subquery that is expensive to plan.
You are doing bulk inserts in a tight loop.

On the Java side, the driver also performs type-safe encoding — converting your int, String, Timestamp, etc. into the correct wire-protocol bytes, handling character escaping and null representation automatically.

Note: Most connection pools (HikariCP, c3p0, DBCP) support statement caching — the pool transparently reuses prepared statement handles across connections, multiplying the performance benefit.