The previous lesson built SQL strings by gluing user input into the query text and showed how that opens the door to SQL injection. PreparedStatement is the fix. It separates the SQL template from the values that fill it in, which makes injection impossible and lets the database cache the query plan across calls. This lesson covers placeholders, setter methods, reusing a prepared statement for many calls, batch inserts, and retrieving the auto-generated keys you get back from an INSERT.

Two reasons, in order of importance: safety and speed.

The safety reason is that PreparedStatement separates code from data. The SQL template is sent to the database once, the values are sent separately, and the database treats them as values no matter what characters they contain. A user who types '; DROP TABLE products; -- into a search box ends up searching for a product literally named that, instead of running a second statement. The database never parses the user's input as SQL.

The speed reason is that databases cache query plans by template. When you reuse the same PreparedStatement for 1000 inserts, the database parses and plans the query once and then executes it 1000 times with different values. With Statement and string concatenation, every row gets its own unique SQL text and the planner does the same work over and over.

Here is the vulnerable version side by side with the fixed version. Both look up a product by name in our e-commerce database.

The WHERE clause turned into WHERE name = 'Laptop' OR '1'='1', which matches every row. Nothing about that input looked dangerous in isolation; the danger came from the string concatenation. Now the parameterized version:

The query searches for a product literally named Laptop' OR '1'='1, finds nothing, and returns zero rows. The exact same input that wrecked the first program is harmless to the second. The fix is one method call: prepareStatement instead of createStatement, and setString instead of string concatenation.

The diagram shows the structural difference. With Statement, the SQL string the database receives is whatever your code built, including any malicious fragments the user supplied. With PreparedStatement, the SQL template and the values travel as separate pieces, and the database fills the placeholders in a way that cannot change the query's structure.

Every Statement.execute call forces the database to re-parse and re-plan a fresh SQL string. A reused PreparedStatement parses and plans once and then executes the cached plan repeatedly. For workloads that run the same shape of query thousands of times, this is a meaningful gain on top of the safety benefit.

A PreparedStatement uses ? characters in the SQL template to mark spots where values go. Each ? is a parameter, and parameters are numbered starting at 1, not 0. You assign values with setter methods that take the index and the value.

The first ? is parameter 1, bound to the price ceiling. The second ? is parameter 2, bound to the minimum stock. The database substitutes those values when it runs the prepared plan. The order in the SQL is the order of the indexes; the names of the columns play no part.

One gotcha: a ? only stands in for a value, never for a column name, table name, or SQL keyword. You cannot write SELECT ? FROM products and bind "name" to choose the column at runtime. The same goes for ORDER BY ? and WHERE ? > 10. If you need a dynamic column or table, build that part of the SQL yourself from a fixed allowlist, and use ? only for the actual values.

You also cannot put ? inside a string literal. The SQL WHERE name = '?' searches for the literal character ?, not for a parameter. The placeholder lives outside quotes because the database adds the quoting when it binds the value.

What's wrong with this code?

The ? inside the quotes is a literal question mark, not a placeholder. prepareStatement sees zero parameters, and setString(1, ...) throws SQLException: Parameter index out of range.

Fix:

The placeholder lives outside quotes. The JDBC driver wraps the value in proper quoting when it binds it.

Each Java type has its own setter on PreparedStatement. Pick the one that matches the column's SQL type. If you use the wrong setter, the driver will often convert for you, but some conversions either fail or change the value silently, so it's better to match deliberately.

For money values, use setBigDecimal against a DECIMAL column. Using setDouble works but introduces floating-point rounding that you do not want in a price or a total. For timestamps with date and time together, prefer setTimestamp over setDate; the latter drops the time-of-day part.

Here is a customer insert that exercises a handful of these setters at once.

executeUpdate returns the row count for INSERT, UPDATE, and DELETE. executeQuery is for SELECT and returns a ResultSet. Calling the wrong one throws an exception: executeUpdate on a SELECT complains that the statement returned rows, and executeQuery on an INSERT complains that it did not.

For null values, you cannot pass null to a setter that takes a primitive (setInt, setDouble, etc.) because there is no such thing as a null int. Use setNull with the SQL type constant from java.sql.Types:

That inserts a SQL NULL into the column. For reference types like String, you can pass null directly to setString and the driver translates it to SQL NULL, but using setNull everywhere is more explicit and avoids surprises with edge-case drivers.

A prepared statement is a long-lived object. You can set parameters, execute, set new parameters, execute again, and keep going. The query plan is parsed once and reused across every execution.

This pattern shows up any time you have a loop that runs the same query for many inputs: insert all rows from a list, update stock for every product in a cart, look up details for every order id. Build the statement once outside the loop and reuse it inside.

One prepareStatement call, four executions. Each iteration sets the three parameters and calls executeUpdate. The driver does not need to re-parse the SQL between executions; it just rebinds and runs.

The setters are sticky between executions. If you forget to set parameter 2 on the second iteration, it keeps the value from the first iteration. That sounds convenient until you have a partial update that silently uses leftover state, so make a habit of setting every parameter on every iteration. If you want to clear all parameters back to "unbound," call ps.clearParameters(), but it is simpler to just set them all.

Each executeUpdate is still a round trip to the database. Four executions equal four round trips even though only one parse happened. To collapse those round trips into one, you batch.

What's wrong with this code?

The prepareStatement call is inside the loop, so the JDBC driver may send a parse request to the database for every iteration. The whole point of PreparedStatement is parse once, execute many. Pull the prepareStatement call outside the loop.

Fix:

One parse, four executions. The same statement object is reused across the loop.

Reusing one prepared statement avoids re-parsing, but each executeUpdate is still a round trip across the network to the database. For 1000 inserts that means 1000 round trips. Batching collapses those round trips into a small number of large ones.

You queue up parameter sets with addBatch() and then send the whole queue with executeBatch(). The database executes every queued statement in order and returns an array of row counts, one per statement.

The loop builds up 1000 parameter sets in memory on the driver side, then executeBatch ships them to the database in one large request (or a handful of large requests, depending on driver configuration). The database executes them as one logical unit and returns a row-count array of length 1000.

1000 individual executeUpdate calls cost 1000 round trips, often hundreds of milliseconds to several seconds depending on network latency. The same 1000 inserts via addBatch plus one executeBatch typically finish in tens of milliseconds. The ratio gets worse the further your app sits from the database. Batch every bulk insert, update, or delete.

For very large batches, do not let the in-memory queue grow without bound. Flush every few thousand rows so that a 10-million-row import does not balloon driver memory. The pattern is to call executeBatch() inside the loop on every Nth iteration and again at the end for the leftovers.

Ten round trips instead of 5000, with a steady memory ceiling. The final executeBatch after the loop catches any rows that did not fill a full chunk; here the math works out exactly, but in real code the input size rarely divides evenly.

The diagram shows where the savings come from. The work the database does per row is roughly the same in both paths; what changes is how many trips the driver makes across the network. On a one-millisecond local link the gap is hard to feel. On a cross-region link of 30 ms, the same 1000 inserts go from 30 seconds to under a second.

There is one wrinkle: if any statement in a batch fails, the driver throws BatchUpdateException, and what happens to the rows that came before the failure depends on whether you wrapped the batch in a transaction. Without an explicit transaction, some rows may have committed and some may not. With a transaction, you can roll the whole batch back. For bulk loads that need all-or-nothing behavior, run the batch inside a transaction.

When you insert a row with an auto-incremented primary key, you usually need the generated id to link the row to other tables. A new order needs its id to attach line items; a new customer needs its id to record an address. JDBC lets you ask for those generated keys back as part of the insert.

The trick is two parts. First, pass Statement.RETURN_GENERATED_KEYS to prepareStatement. Second, after executeUpdate, call getGeneratedKeys() to get a small result set with the keys.

The order insert returned the generated id as 1. We then used that id as a foreign key when inserting the two line items. Without RETURN_GENERATED_KEYS, getGeneratedKeys() returns an empty result set even on a table with an auto-increment column, because the driver does not automatically ask the database for the new keys; you have to opt in.

If you batch multiple inserts and request generated keys, the result set returned by getGeneratedKeys() contains one row per inserted row, in the order the rows were added. Walk through it with next() just like any other result set.

The sequence shows the round trip. The flag tells the driver to ask the database for the new keys as part of the insert response, so no second query is needed. Without the flag, you would have to run a separate SELECT MAX(id) or call a database-specific function, both of which are slower and race-prone.

The performance side of PreparedStatement goes beyond skipping a parse. Most database engines maintain a server-side cache of plans keyed by the SQL template. When the same template comes in repeatedly, the database hits the cache instead of redoing the analysis.

For a complex query with joins, subqueries, and an ORDER BY over indexed columns, the planner's work can be a meaningful share of the per-execution cost. A web app that runs the same product-search query thousands of times a minute, with different ? parameters, lets the database cache the plan once and amortize it across every call.

The driver-side picture has two pieces. The JDBC driver caches the parsed SQL template so it does not have to re-tokenize on the client. The database caches the plan keyed by that template. Reusing the same PreparedStatement object also reuses the server-side handle, so even the bind-and-execute message is smaller than rebuilding the full statement.

A trivial query like SELECT id FROM products WHERE name = ? may only save a few microseconds of parse per call. A complex multi-join report query can save milliseconds per call. Either way, the savings compound over a workload that runs the same shape of query at high frequency.

The catch is that the cache is keyed by exact text. WHERE id = ? and WHERE id = ? are the same template. WHERE id IN (?, ?, ?) and WHERE id IN (?, ?, ?, ?) are different templates, because the number of placeholders differs. If your application builds IN clauses with a variable number of values, you end up with a different plan per arity, and the cache fills up with similar plans. The common fix is to round the number of placeholders up to a small set of fixed sizes (5, 10, 50) and pad the values with duplicates, or to use a temporary table for very long lists.

A handful of PreparedStatement mistakes show up over and over.

Forgetting to set a parameter. If you have three ? placeholders and only call setString(1, ...) and setString(2, ...), executeUpdate throws SQLException complaining about an unset parameter. The setters do not have defaults; an unbound parameter is an error.

The fix is mechanical: set every parameter in the template, every execution.

Off-by-one on the index. Parameter indexes start at 1, not 0. setString(0, ...) throws SQLException: Invalid value 0 for parameter index. Coming from arrays and lists where indexes start at 0, this is easy to slip on.

Wrong setter for the column type. Using setString to set an INT column sometimes works because the driver parses the string, but it bypasses type checking and can break on locale-specific number formats. Pick the setter that matches the column type. If you're not sure, look at the schema or use setObject, which lets the driver choose, accepting a small loss of clarity in exchange.

Placeholders inside string literals. '?' inside the SQL is a literal question mark, not a parameter. The driver sees zero parameters and setString(1, ...) fails. Keep ? outside any quotes; the driver handles the quoting itself when it binds the value.

Placeholders for identifiers. You cannot bind a column name, table name, or SQL keyword. The driver only substitutes values. If you need a dynamic column name, build that part of the query string from a fixed allowlist of safe identifiers, then use ? only for the actual values.

The allowlist keeps the dynamic part safe because the user's input is compared against a fixed set instead of being passed through to the SQL parser.

Mixing types. Setting a LocalDate with setObject(i, localDate) works on modern drivers because of JDBC 4.2's java.time support, but setDate(i, localDate) does not compile because setDate expects java.sql.Date. The conversion goes through Date.valueOf(localDate).