Java JDBC generated keys: validate one insert and one returned identity

Operational contract

Generated-key support and which columns are returned vary by driver and SQL operation. This example asks for a key for a single inserted manifest, rejects zero or multiple returned key rows, and preserves the connection's transaction boundary for the caller. It does not commit. A key seen before commit can still disappear on rollback. For multi-row inserts, define an explicit mapping between input rows and generated identities rather than assuming a convenient order.

Failure case

A review process inserts manifest-47 and needs the new row ID for a child audit record. It requests the key while using the same transaction, then inserts the child row and commits both together. If the insert reports no row or the driver returns no key, the operation fails and the owner rolls back. The application never invents an ID from the last value seen in another request.

Java code

import java.sql.*;

public class ManifestIdentityWriter {
    public static long insert(Connection connection, String manifestName) throws SQLException {
        try (PreparedStatement statement = connection.prepareStatement(
                "INSERT INTO manifests (name) VALUES (?)", Statement.RETURN_GENERATED_KEYS)) {
            statement.setString(1, manifestName);
            if (statement.executeUpdate() != 1) throw new SQLException("Expected one inserted row");
            try (ResultSet keys = statement.getGeneratedKeys()) {
                if (!keys.next()) throw new SQLException("Generated key absent");
                long identity = keys.getLong(1);
                if (keys.wasNull() || keys.next()) throw new SQLException("Invalid generated key result");
                return identity;
            }
        }
    }
}

Performance and ownership cost

The client performs one insert and one key-result read. Database indexing and transaction work dominate the actual cost. Keeping the child write in the same transaction prevents a published ID from describing a parent row that was later rolled back.

Common Mistakes

• Do not assume generated keys are available for every driver and statement.
• Do not treat a pre-commit key as a committed record.
• Do not assume multi-row key order without a database-specific contract.

Connected lessons

• Java JDBC transactions: atomic updates and rollback
• Java JDBC PreparedStatement: values stay outside SQL grammar
• Java database project: one receipt and its aggregate in one transaction
• Java JDBC batches: inspect update counts and own rollback
• Java ResultSet: cursor position and SQL NULL are separate states
• Java JDBC fetch size: a hint, not a memory guarantee
• Java file, JDBC, and subprocess boundaries quiz
• Advanced Java