CRUD with PreparedStatement
Raw Statement objects exist in JDBC, but you should never use them for queries that include user-supplied data. PreparedStatement is the professional default for every parameterised query: it pre-compiles the SQL once, accepts typed bind parameters, and eliminates SQL injection by design. This lesson walks through all four CRUD operations — Create, Read, Update, Delete — using PreparedStatement correctly and idiomatically inside a DAO class.
The Domain Model
All four operations work on a single table. Here is the SQL and the matching Java record that the DAO layer maps:
-- schema
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(150) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
package com.example.model;
public record Product(int id, String name, double price, int stock) {}
Using a record (Java 16+) gives you an immutable value object with generated equals, hashCode, and toString for free. The DAO does not store mutable state — it just maps rows to records and records back to rows.
CREATE — Inserting a Row
Use Statement.RETURN_GENERATED_KEYS so that the database-assigned id is available after the insert. Without this flag, you have no reliable way to retrieve the new primary key across all databases.
public Product create(Connection conn, String name, double price, int stock)
throws SQLException {
String sql = "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)";
try (PreparedStatement ps =
conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, name);
ps.setDouble(2, price);
ps.setInt(3, stock);
int affected = ps.executeUpdate();
if (affected == 0) {
throw new SQLException("Insert failed — no rows affected.");
}
try (ResultSet keys = ps.getGeneratedKeys()) {
if (keys.next()) {
return new Product(keys.getInt(1), name, price, stock);
}
throw new SQLException("Insert failed — no generated key returned.");
}
}
}
Parameter indices are 1-based, not 0-based. The first ? placeholder is set with index 1, the second with 2, and so on. This is a constant source of off-by-one bugs when adding or rearranging columns — always count the ? marks in your SQL to double-check.
READ — Querying Rows
Reading a single row by primary key and reading all rows follow the same pattern. The only difference is the SQL and whether the result set returns one row or many.
// Find by primary key — returns Optional to signal possible absence
public Optional<Product> findById(Connection conn, int id) throws SQLException {
String sql = "SELECT id, name, price, stock FROM products WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return Optional.of(mapRow(rs));
}
return Optional.empty();
}
}
}
// Find all rows
public List<Product> findAll(Connection conn) throws SQLException {
String sql = "SELECT id, name, price, stock FROM products ORDER BY id";
List<Product> list = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
list.add(mapRow(rs));
}
}
return list;
}
// Private helper — maps the current row to a Product record
private Product mapRow(ResultSet rs) throws SQLException {
return new Product(
rs.getInt("id"),
rs.getString("name"),
rs.getDouble("price"),
rs.getInt("stock")
);
}
Always use column names, not column indices, in ResultSet calls. rs.getString("name") survives a column reorder in the SELECT list or the table DDL. rs.getString(2) silently returns the wrong value the moment the column order changes.
UPDATE — Modifying an Existing Row
executeUpdate() returns the number of rows affected. Checking that count lets you distinguish "the product was updated" from "no product with that id exists" — a detail callers often care about.
public boolean update(Connection conn, Product product) throws SQLException {
String sql = "UPDATE products SET name = ?, price = ?, stock = ? WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, product.name());
ps.setDouble(2, product.price());
ps.setInt(3, product.stock());
ps.setInt(4, product.id()); // WHERE clause goes last
return ps.executeUpdate() == 1; // true ⟹ exactly one row updated
}
}
Forgetting the WHERE clause on an UPDATE deletes all your data. UPDATE products SET price = ? with no WHERE sets that price on every row in the table. Always verify the WHERE clause exists and that its parameter is bound before running DML against production data. A staging environment and a database backup strategy are non-negotiable safeguards.
DELETE — Removing a Row
Deletes follow exactly the same shape as updates. Return the affected-row count so the caller can tell whether anything was actually removed.
public boolean delete(Connection conn, int id) throws SQLException {
String sql = "DELETE FROM products WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
return ps.executeUpdate() == 1;
}
}
Typed Setters and Null Handling
PreparedStatement provides a typed setter for every SQL type: setString, setInt, setLong, setDouble, setBigDecimal, setBoolean, setTimestamp, and more. When a column is nullable, do not pass Java null to a typed setter — use setNull with the matching SQL type constant instead:
// Correct null handling for a nullable column
if (product.description() != null) {
ps.setString(5, product.description());
} else {
ps.setNull(5, Types.VARCHAR);
}
Passing null to setString works on most drivers, but relying on it is driver-specific behaviour. setNull is explicit and portable.
Putting It All Together — A Minimal DAO
A complete DAO for the products table collects all four operations under one class. The Connection is always received from the caller (injected), never created inside the DAO — this is what keeps the DAO testable and keeps transaction control in the service layer, which is covered in Lesson 6.
package com.example.dao;
import com.example.model.Product;
import java.sql.*;
import java.util.*;
public class ProductDao {
public Product create(Connection conn, String name, double price, int stock)
throws SQLException { /* ... as above ... */ }
public Optional<Product> findById(Connection conn, int id)
throws SQLException { /* ... as above ... */ }
public List<Product> findAll(Connection conn)
throws SQLException { /* ... as above ... */ }
public boolean update(Connection conn, Product product)
throws SQLException { /* ... as above ... */ }
public boolean delete(Connection conn, int id)
throws SQLException { /* ... as above ... */ }
private Product mapRow(ResultSet rs) throws SQLException { /* ... as above ... */ }
}
Why pass Connection into every method rather than storing it as a field? A stored connection is not thread-safe and ties the DAO to a single unit of work. Accepting the connection as a parameter means the service layer decides when to open, commit, and close it — enabling multiple DAO calls to share one transaction without the DAO knowing anything about transaction management.
Summary
Every CRUD operation follows a tight, repeatable pattern: prepare the SQL with ? placeholders, bind parameters with typed setters (1-based), execute with executeUpdate() or executeQuery(), and close everything in a try-with-resources block. Return meaningful values — generated keys for inserts, Optional for nullable lookups, and boolean or row counts for mutations. Keep the connection out of the DAO and you get testability and transaction flexibility for free.