Java PreparedStatement
A Java PreparedStatement (java.sql.PreparedStatement) is a SQL command with ? placeholders that you fill with values before running it. The database receives the SQL and the values separately, so user input can never change the meaning of the query.
This makes PreparedStatement the standard way to run SQL that contains variables. It protects against SQL injection, handles quoting and data types for you, and runs faster when the same SQL is executed many times. In this lesson you will create prepared statements, set every common type of parameter, query and update data, read generated keys, work with NULL, LIKE and IN, and build a safe product search with optional filters.
How a Java PreparedStatement Works
You write the SQL once with ? where values belong. Then you call a setXxx(index, value) method for each placeholder and run the statement.
PreparedStatement statement = connection.prepareStatement("SQL with ? placeholders");
statement.setString(1, firstValue); // placeholders are numbered from 1
statement.setInt(2, secondValue);
statement.executeUpdate(); // or executeQuery() for SELECT
| Step | Method | What happens |
|---|---|---|
| 1. Prepare | connection.prepareStatement(sql) | The SQL is sent to the driver (and often the database) and parsed once |
| 2. Bind | setInt(), setString(), ... | Each ? gets a typed value, counted from 1, not 0 |
| 3. Execute | executeQuery() / executeUpdate() / execute() | The database runs the SQL with the bound values |
| 4. Reuse | Bind new values, execute again | No new parsing is needed |
Your First PreparedStatement
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class FirstPreparedStatement {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(30), marks INT)");
String sql = "INSERT INTO students (id, name, marks) VALUES (?, ?, ?)";
try (PreparedStatement insert = connection.prepareStatement(sql)) {
insert.setInt(1, 1); // first ? -> id
insert.setString(2, "Asha"); // second ? -> name
insert.setInt(3, 88); // third ? -> marks
int rowsInserted = insert.executeUpdate();
System.out.println("Rows inserted: " + rowsInserted);
}
try (ResultSet rows = setup.executeQuery("SELECT id, name, marks FROM students")) {
while (rows.next()) {
System.out.println(rows.getInt("id") + " | " + rows.getString("name") + " | " + rows.getInt("marks"));
}
}
}
}
}
Output:
Rows inserted: 1
1 | Asha | 88
How it works:
- The
INSERTcontains three placeholders. Notice there are no quotes around?, even for the text value. The driver adds whatever quoting the database needs. setInt(1, 1)fills the first placeholder,setString(2, "Asha")the second andsetInt(3, 88)the third.executeUpdate()returns the number of rows changed, exactly like it does on a plain Java JDBC Statement.
Note: All examples use the H2 in-memory database, so they run with only the H2 driver on the classpath. To use MySQL or PostgreSQL, change only the connection URL, as shown in Java Database Connection.
PreparedStatement Setter Methods
Every placeholder needs a value of the right type. The setter you choose tells the driver which SQL type to send.
| Java value | Setter | Typical SQL column |
|---|---|---|
String | setString(i, value) | VARCHAR, CHAR, TEXT |
int / long | setInt(i, value) / setLong(i, value) | INT / BIGINT |
double | setDouble(i, value) | DOUBLE, FLOAT |
BigDecimal | setBigDecimal(i, value) | DECIMAL, NUMERIC (money) |
boolean | setBoolean(i, value) | BOOLEAN, BIT |
LocalDate / LocalDateTime | setObject(i, value) | DATE / TIMESTAMP |
| no value | setNull(i, Types.XXX) | any nullable column |
| any supported object | setObject(i, value) | the driver picks the SQL type |
Example: Binding Different Data Types
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.time.LocalDate;
public class SetterTypes {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:company", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("""
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(30),
salary DECIMAL(10, 2),
active BOOLEAN,
joined DATE,
manager_id INT
)""");
String sql = "INSERT INTO employees VALUES (?, ?, ?, ?, ?, ?)";
try (PreparedStatement insert = connection.prepareStatement(sql)) {
insert.setInt(1, 101);
insert.setString(2, "Kavin");
insert.setBigDecimal(3, new BigDecimal("45000.50"));
insert.setBoolean(4, true);
insert.setObject(5, LocalDate.of(2024, 6, 3));
insert.setNull(6, Types.INTEGER); // no manager yet
insert.executeUpdate();
}
try (PreparedStatement select = connection.prepareStatement("SELECT * FROM employees WHERE id = ?")) {
select.setInt(1, 101);
try (ResultSet row = select.executeQuery()) {
row.next();
System.out.println("Name: " + row.getString("name"));
System.out.println("Salary: " + row.getBigDecimal("salary"));
System.out.println("Active: " + row.getBoolean("active"));
System.out.println("Joined: " + row.getObject("joined", LocalDate.class));
System.out.println("Manager: " + row.getObject("manager_id", Integer.class));
}
}
}
}
}
Output:
Name: Kavin
Salary: 45000.50
Active: true
Joined: 2024-06-03
Manager: null
How it works:
setBigDecimal()stores the salary exactly. Never usedoublefor money, because binary floating point cannot represent values such as0.1precisely.setObject()with aLocalDatemaps to an SQLDATE. Modern drivers (JDBC 4.2 and later) supportLocalDate,LocalTimeandLocalDateTimethis way, so you do not need the oldjava.sql.Dateclass.setNull(6, Types.INTEGER)stores SQLNULL. The second argument tells the driver the column's type.- The
SELECTis also a prepared statement, because theidis a value.
Running a SELECT Query with Parameters
For queries, bind the parameters and call executeQuery(). It returns a ResultSet you read row by row.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class QueryWithParameters {
static void printTopStudents(PreparedStatement query, int minimumMarks) throws SQLException {
query.setInt(1, minimumMarks);
System.out.println("Students with marks >= " + minimumMarks + ":");
try (ResultSet rows = query.executeQuery()) {
while (rows.next()) {
System.out.println(" " + rows.getString("name") + " (" + rows.getInt("marks") + ")");
}
}
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(30), marks INT)");
setup.executeUpdate("INSERT INTO students VALUES (1, 'Asha', 88), (2, 'Ravi', 72), (3, 'Meena', 92), (4, 'Kavin', 81)");
String sql = "SELECT name, marks FROM students WHERE marks >= ? ORDER BY marks DESC";
try (PreparedStatement query = connection.prepareStatement(sql)) {
printTopStudents(query, 80);
printTopStudents(query, 90);
}
}
}
}
Output:
Students with marks >= 80:
Meena (92)
Asha (88)
Kavin (81)
Students with marks >= 90:
Meena (92)
How it works:
- The same
PreparedStatementruns twice with different values. The SQL is parsed only once. - Calling
setInt(1, ...)again simply replaces the previous value of that placeholder. - Each
executeQuery()returns a newResultSet, closed by its own try-with-resources block.
Reusing a PreparedStatement in a Loop
Inserting many rows is where prepared statements shine. Prepare once, then bind and execute inside the loop.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ReuseInLoop {
public static void main(String[] args) throws SQLException {
String[] names = {"Asha", "Ravi", "Meena", "Kavin"};
int[] marks = {88, 72, 92, 81};
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(30), marks INT)");
try (PreparedStatement insert = connection.prepareStatement("INSERT INTO students VALUES (?, ?, ?)")) {
for (int i = 0; i < names.length; i++) {
insert.clearParameters(); // optional: start each row with no values
insert.setInt(1, i + 1);
insert.setString(2, names[i]);
insert.setInt(3, marks[i]);
insert.executeUpdate();
}
}
try (ResultSet totals = setup.executeQuery("SELECT COUNT(*), SUM(marks) FROM students")) {
totals.next();
int saved = totals.getInt(1);
int sumOfMarks = totals.getInt(2);
System.out.println("Students saved: " + saved);
System.out.printf("Average marks: %.2f%n", (double) sumOfMarks / saved);
}
}
}
}
Output:
Students saved: 4
Average marks: 83.25
How it works:
clearParameters()removes all bound values. It is optional here because every placeholder is set again, but it guarantees that a forgotten setter causes an error instead of silently reusing the previous row's value.- The average is
(88 + 72 + 92 + 81) / 4 = 83.25. - Each
executeUpdate()is still one trip to the database. To send all rows in one trip, use batching, covered in Java JDBC Batch Update.
Getting Generated Keys from a PreparedStatement
When the database creates the primary key, ask for it when you prepare the statement by passing Statement.RETURN_GENERATED_KEYS.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class PreparedGeneratedKeys {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE courses (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, title VARCHAR(40))");
String sql = "INSERT INTO courses (title) VALUES (?)";
try (PreparedStatement insert = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
for (String title : new String[] {"Java Basics", "SQL Fundamentals", "Spring Boot"}) {
insert.setString(1, title);
insert.executeUpdate();
try (ResultSet keys = insert.getGeneratedKeys()) {
if (keys.next()) {
System.out.println("Saved '" + title + "' as course #" + keys.getInt(1));
}
}
}
}
}
}
}
Output:
Saved 'Java Basics' as course #1
Saved 'SQL Fundamentals' as course #2
Saved 'Spring Boot' as course #3
How it works:
- The flag goes to
prepareStatement(), not toexecuteUpdate(), because a prepared statement's SQL is fixed when it is created. - After each insert,
getGeneratedKeys()returns a one-rowResultSetwith the new id. if (keys.next())is safer than callingnext()blindly, because a driver may return no key if nothing was generated.
Note: Instead of
RETURN_GENERATED_KEYS, you can pass an array of column names, such asprepareStatement(sql, new String[] {"id"}). This is useful in PostgreSQL and Oracle, where the default may return every column. Column-name case rules differ between databases.
Handling NULL Values in a PreparedStatement
In SQL, NULL means "unknown", and column = NULL is never true. Binding NULL to = ? therefore matches nothing. Use IS NULL instead.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
public class NullParameters {
static int countTasks(Connection connection, String sql, String assignee) throws SQLException {
try (PreparedStatement query = connection.prepareStatement(sql)) {
if (sql.contains("?")) {
if (assignee == null) {
query.setNull(1, Types.VARCHAR);
} else {
query.setString(1, assignee);
}
}
try (ResultSet rows = query.executeQuery()) {
rows.next();
return rows.getInt(1);
}
}
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:tasks", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE tasks (id INT PRIMARY KEY, title VARCHAR(40), assignee VARCHAR(20))");
setup.executeUpdate("INSERT INTO tasks VALUES (1, 'Write tests', 'Asha'), (2, 'Fix login', NULL), (3, 'Deploy', NULL)");
System.out.println("assignee = 'Asha' : "
+ countTasks(connection, "SELECT COUNT(*) FROM tasks WHERE assignee = ?", "Asha"));
System.out.println("assignee = NULL : "
+ countTasks(connection, "SELECT COUNT(*) FROM tasks WHERE assignee = ?", null));
System.out.println("assignee IS NULL : "
+ countTasks(connection, "SELECT COUNT(*) FROM tasks WHERE assignee IS NULL", null));
}
}
}
Output:
assignee = 'Asha' : 1
assignee = NULL : 0
assignee IS NULL : 2
How it works:
setNull()correctly sends SQLNULL, butassignee = NULLevaluates to "unknown" for every row, so the count is0.IS NULLis the SQL test for missing values and finds the two unassigned tasks.- When a filter value may be
null, choose the SQL in Java:IS NULLfornull,= ?otherwise.
Using LIKE and IN with Placeholders
A placeholder always stands for one complete value. For LIKE, put the wildcards into the value. For IN, generate one ? per item.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.List;
public class LikeAndIn {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE courses (id INT PRIMARY KEY, title VARCHAR(40))");
setup.executeUpdate("""
INSERT INTO courses VALUES
(1, 'Java Basics'), (2, 'Advanced Java'), (3, 'SQL Fundamentals'),
(4, 'JavaScript Essentials'), (5, 'Spring Boot')""");
// LIKE: the wildcards are part of the value, not the SQL
String searchTerm = "java";
try (PreparedStatement like = connection.prepareStatement(
"SELECT title FROM courses WHERE LOWER(title) LIKE ? ORDER BY title")) {
like.setString(1, "%" + searchTerm.toLowerCase() + "%");
System.out.println("Titles containing '" + searchTerm + "':");
try (ResultSet rows = like.executeQuery()) {
while (rows.next()) {
System.out.println(" " + rows.getString("title"));
}
}
}
// IN: one placeholder per value
List<Integer> selectedIds = List.of(1, 3, 5);
String placeholders = String.join(", ", Collections.nCopies(selectedIds.size(), "?"));
String sql = "SELECT id, title FROM courses WHERE id IN (" + placeholders + ") ORDER BY id";
System.out.println("SQL: " + sql);
try (PreparedStatement in = connection.prepareStatement(sql)) {
for (int i = 0; i < selectedIds.size(); i++) {
in.setInt(i + 1, selectedIds.get(i));
}
try (ResultSet rows = in.executeQuery()) {
while (rows.next()) {
System.out.println(" #" + rows.getInt("id") + " " + rows.getString("title"));
}
}
}
}
}
}
Output:
Titles containing 'java':
Advanced Java
Java Basics
JavaScript Essentials
SQL: SELECT id, title FROM courses WHERE id IN (?, ?, ?) ORDER BY id
#1 Java Basics
#3 SQL Fundamentals
#5 Spring Boot
How it works:
- Writing
LIKE '%?%'would not work, because a?inside quotes is just a question-mark character. The%signs belong in the bound value. LOWER(title)andtoLowerCase()make the search case-insensitive.Collections.nCopies(3, "?")produces three?strings, andString.jointurns them into?, ?, ?. Only placeholders are added to the SQL text; the ids are still bound withsetInt().
Note: Placeholders work only for values. You cannot bind a table name, a column name or a keyword such as
ASC. If a user chooses the sort column, check it against a fixed list of allowed names in Java before adding it to the SQL.
Why PreparedStatement Prevents SQL Injection
With a plain Statement, values are glued into the SQL text, so a value containing a quote can rewrite the query. A PreparedStatement sends values separately from the SQL, so they are always treated as data. The previous lesson shows a full login-bypass attack in Java JDBC Statement. Here is the short version:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class InjectionSafe {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:shop", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(40))");
setup.executeUpdate("INSERT INTO customers VALUES (1, 'Asha'), (2, 'O''Brien')");
String[] inputs = {"O'Brien", "x' OR '1'='1"};
try (PreparedStatement find = connection.prepareStatement("SELECT COUNT(*) FROM customers WHERE name = ?")) {
for (String input : inputs) {
find.setString(1, input);
try (ResultSet rows = find.executeQuery()) {
rows.next();
System.out.println("Input [" + input + "] matched " + rows.getInt(1) + " row(s)");
}
}
}
}
}
}
Output:
Input [O'Brien] matched 1 row(s)
Input [x' OR '1'='1] matched 0 row(s)
How it works:
- The name
O'Briencontains a quote. With string gluing it would break the SQL; as a bound value it simply matches the customer. - The attack string is searched for literally, as a name, and matches nothing. The
OR '1'='1'never becomes SQL.
Real-World Example: Product Search with Optional Filters
Search forms usually have optional fields. This example builds the WHERE clause from only the filters the user filled in, while every value still goes through a placeholder.
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class ProductSearch {
static List<String> search(Connection connection, String category, BigDecimal maxPrice,
String keyword, boolean onlyInStock) throws SQLException {
StringBuilder sql = new StringBuilder("SELECT name, category, price FROM products WHERE 1 = 1");
List<Object> params = new ArrayList<>();
if (category != null) {
sql.append(" AND category = ?");
params.add(category);
}
if (maxPrice != null) {
sql.append(" AND price <= ?");
params.add(maxPrice);
}
if (keyword != null && !keyword.isBlank()) {
sql.append(" AND LOWER(name) LIKE ?");
params.add("%" + keyword.toLowerCase() + "%");
}
if (onlyInStock) {
sql.append(" AND in_stock = TRUE"); // fixed text, no user value
}
sql.append(" ORDER BY price");
System.out.println("SQL: " + sql);
List<String> results = new ArrayList<>();
try (PreparedStatement statement = connection.prepareStatement(sql.toString())) {
for (int i = 0; i < params.size(); i++) {
statement.setObject(i + 1, params.get(i)); // placeholders are numbered from 1
}
try (ResultSet rows = statement.executeQuery()) {
while (rows.next()) {
results.add(rows.getString("name") + " (" + rows.getString("category") + ") - "
+ rows.getBigDecimal("price"));
}
}
}
return results;
}
static void show(List<String> results) {
if (results.isEmpty()) {
System.out.println(" No products found");
}
results.forEach(line -> System.out.println(" " + line));
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:shop", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("""
CREATE TABLE products (
id INT PRIMARY KEY, name VARCHAR(40), category VARCHAR(20),
price DECIMAL(10, 2), in_stock BOOLEAN)""");
setup.executeUpdate("""
INSERT INTO products VALUES
(1, 'Wireless Mouse', 'Electronics', 799.00, TRUE),
(2, 'Mechanical Keyboard', 'Electronics', 3499.00, TRUE),
(3, 'USB-C Cable', 'Electronics', 299.00, FALSE),
(4, 'Java Programming Book', 'Books', 650.00, TRUE),
(5, 'Desk Lamp', 'Home', 1199.00, TRUE),
(6, 'Notebook Set', 'Books', 180.00, TRUE)""");
show(search(connection, "Electronics", null, null, true));
show(search(connection, null, new BigDecimal("700"), null, false));
show(search(connection, null, null, "book", false));
show(search(connection, null, null, "'; DROP TABLE products; --", false));
try (ResultSet count = setup.executeQuery("SELECT COUNT(*) FROM products")) {
count.next();
System.out.println("Products still in table: " + count.getInt(1));
}
}
}
}
Output:
SQL: SELECT name, category, price FROM products WHERE 1 = 1 AND category = ? AND in_stock = TRUE ORDER BY price
Wireless Mouse (Electronics) - 799.00
Mechanical Keyboard (Electronics) - 3499.00
SQL: SELECT name, category, price FROM products WHERE 1 = 1 AND price <= ? ORDER BY price
Notebook Set (Books) - 180.00
USB-C Cable (Electronics) - 299.00
Java Programming Book (Books) - 650.00
SQL: SELECT name, category, price FROM products WHERE 1 = 1 AND LOWER(name) LIKE ? ORDER BY price
Notebook Set (Books) - 180.00
Java Programming Book (Books) - 650.00
SQL: SELECT name, category, price FROM products WHERE 1 = 1 AND LOWER(name) LIKE ? ORDER BY price
No products found
Products still in table: 6
How it works:
WHERE 1 = 1as a base. It is always true, so every optional filter can start withANDwithout checking whether it is the first condition.- SQL text and values grow together. Each time a filter adds a
?to the SQL, its value is added to theparamslist in the same order. The two lists always stay in step. setObject()binds any type. The list holds aString, aBigDecimaland theLIKEpattern.setObject()lets the driver map each Java type to the matching SQL type.- Fixed conditions need no placeholder.
in_stock = TRUEcomes from the program, not the user, so it is safe to write directly into the SQL. - The injection attempt is harmless. The last search passes
'; DROP TABLE products; --as a keyword. It becomes part of aLIKEpattern, matches nothing, and the table still has all six products. BigDecimalkeeps money exact. Prices print as799.00with two decimal places, exactly as stored in theDECIMAL(10, 2)column.
To read query results in more depth, including NULL handling, column metadata and scrollable cursors, continue with Java ResultSet.