JDBC & Databases

CRUD Operations

15 min Lesson 6 of 13

CRUD Operations

CRUD — Create, Read, Update, Delete — is the backbone of nearly every database-driven application. In this lesson we take these four operations from first principles through production-grade patterns, examining the trade-offs at every step. You already know how to open a Connection and create a Statement; here we connect those pieces into real, reliable data-access code.

The Setup: a Sample Table

All examples in this lesson work against a simple products table:

CREATE TABLE products (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(100) NOT NULL,
    price    DECIMAL(10, 2) NOT NULL,
    stock    INT NOT NULL DEFAULT 0
);

We will use a helper method that returns a connection from a pool or driver manager. For now assume getConnection() is available.

Create — Inserting Rows

Always use PreparedStatement for inserts that include user-supplied data. The key point here is how to retrieve the auto-generated primary key after the insert:

public long insertProduct(Connection conn, String name, double price, int stock)
        throws SQLException {

    String sql = "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)";

    // RETURN_GENERATED_KEYS tells the driver to capture the new PK
    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();   // returns number of rows inserted
        if (affected == 0) {
            throw new SQLException("Insert failed, no rows affected.");
        }

        try (ResultSet keys = ps.getGeneratedKeys()) {
            if (keys.next()) {
                return keys.getLong(1);      // column index 1 = the generated PK
            } else {
                throw new SQLException("Insert failed, no generated key returned.");
            }
        }
    }
}

Why check affected == 0? executeUpdate() returns the row count. If a trigger or a database-level constraint silently suppressed the insert, the count would be 0 and your code would try to read a key that does not exist. Checking early makes failures loud and obvious.

Calling the method and using the returned id:

try (Connection conn = getConnection()) {
    long newId = insertProduct(conn, "Wireless Mouse", 29.99, 150);
    System.out.println("Inserted product with id = " + newId);
}

Read — Querying Rows

Reading data involves executing a SELECT and iterating the ResultSet. Two common patterns are: fetch a single row by primary key, or fetch a collection filtered by criteria.

Fetch one row by id:

public Optional<Product> findById(Connection conn, long id) throws SQLException {
    String sql = "SELECT id, name, price, stock FROM products WHERE id = ?";

    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setLong(1, id);

        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                return Optional.of(mapRow(rs));
            }
            return Optional.empty();
        }
    }
}

private Product mapRow(ResultSet rs) throws SQLException {
    return new Product(
        rs.getLong("id"),
        rs.getString("name"),
        rs.getBigDecimal("price"),   // prefer BigDecimal for money
        rs.getInt("stock")
    );
}

Use column names, not column indexes, when reading a ResultSet. Index-based access (rs.getString(2)) breaks silently if the SELECT list is reordered. Name-based access (rs.getString("name")) is self-documenting and resilient to query changes.

Fetch a list with a filter:

public List<Product> findByMinStock(Connection conn, int minStock)
        throws SQLException {

    String sql = "SELECT id, name, price, stock FROM products WHERE stock >= ?";

    List<Product> results = new ArrayList<>();

    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setInt(1, minStock);

        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                results.add(mapRow(rs));
            }
        }
    }
    return results;
}

Note the >= comparison: inside a code-block the angle brackets must be entity-escaped so the browser renders them correctly, which is why the source says >=. This is a display concern, not a JDBC concern — the SQL string in your Java source code uses a plain >=.

Update — Modifying Existing Rows

Updates follow the same prepared-statement pattern. Always return the affected row count so the caller can verify the row actually existed:

public int updatePrice(Connection conn, long id, double newPrice)
        throws SQLException {

    String sql = "UPDATE products SET price = ? WHERE id = ?";

    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setDouble(1, newPrice);
        ps.setLong(2, id);
        return ps.executeUpdate();   // 1 if found, 0 if no row with that id
    }
}

Usage pattern that distinguishes "not found" from other errors:

int rows = updatePrice(conn, productId, 24.99);
if (rows == 0) {
    throw new NoSuchElementException("Product " + productId + " not found.");
}

Never build a WHERE clause by concatenating user input. Even a simple update like "UPDATE products SET price = " + price + " WHERE id = " + id is vulnerable to SQL injection. Always bind values through prepared-statement parameters.

Delete — Removing Rows

Deletes are structurally identical to updates: a PreparedStatement with a WHERE clause, returning the affected row count:

public int deleteProduct(Connection conn, long id) throws SQLException {
    String sql = "DELETE FROM products WHERE id = ?";

    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setLong(1, id);
        return ps.executeUpdate();
    }
}

Putting It All Together — A Mini CRUD Demo

Here is a short, self-contained demo that exercises every operation in sequence so you can see how they compose:

try (Connection conn = getConnection()) {

    // CREATE
    long id = insertProduct(conn, "USB-C Hub", 49.99, 75);
    System.out.println("Created: id=" + id);

    // READ
    findById(conn, id).ifPresentOrElse(
        p -> System.out.println("Read: " + p),
        () -> System.out.println("Not found.")
    );

    // UPDATE
    int updated = updatePrice(conn, id, 44.99);
    System.out.println("Updated rows: " + updated);

    // DELETE
    int deleted = deleteProduct(conn, id);
    System.out.println("Deleted rows: " + deleted);
}

Key Trade-offs and Best Practices

Close resources in reverse order. Always close ResultSet before PreparedStatement before Connection. Using nested try-with-resources guarantees this automatically.
Money columns: use BigDecimal, not double. rs.getBigDecimal("price") preserves exact decimal values; getDouble introduces floating-point rounding errors that accumulate over time.
Check affected row counts. A count of 0 on an update or delete means the WHERE clause matched nothing — that is usually a bug, not a success.
Single-responsibility methods. Each method does one thing (find, insert, update, delete). This keeps logic testable and makes it easy to swap the SQL later without touching callers.
Let exceptions propagate. Catching SQLException at the data-access layer and swallowing it hides bugs. Either propagate it or wrap it in an unchecked domain exception (DataAccessException) so the caller can decide how to handle it.

Summary

Every CRUD operation follows the same three-step rhythm: prepare the SQL with placeholders, bind the parameters, execute and check the result. For inserts, capture the generated key with RETURN_GENERATED_KEYS. For reads, prefer column names over indexes and map rows to domain objects in a dedicated helper. For updates and deletes, inspect the affected row count to distinguish "row not found" from a successful zero-change operation. In the next lesson we will group multiple CRUD operations into atomic database transactions.