Java JDBC setNull: bind a nullable column with its SQL type

Operational contract

The method updates a nullable VARCHAR courier note by shipment ID. It uses setNull with Types.VARCHAR when the note is absent and setString otherwise. It rejects a note longer than 47 characters before contacting the database, then requires exactly one updated row. The database schema may impose a different length or character rule, so this application cap must be aligned with migration policy. SQL NULL is not the same as an empty string. The connection and transaction remain owned by the caller.

Failure case

A carrier clears the note for shipment 82. Passing null to setString may be handled by a driver, but this code states the SQL type explicitly. A zero-row update signals an unknown shipment rather than reporting success, and a two-row update signals a broken uniqueness assumption.

Java code

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;

public class CourierNoteUpdate {
    public static void apply(Connection connection, long shipmentId, String note)
            throws SQLException {
        if (note != null && note.length() > 47)
            throw new IllegalArgumentException("Courier note exceeds 47 characters");
        try (PreparedStatement statement = connection.prepareStatement(
                "UPDATE shipments SET courier_note = ? WHERE shipment_id = ?")) {
            if (note == null) statement.setNull(1, Types.VARCHAR);
            else statement.setString(1, note);
            statement.setLong(2, shipmentId);
            if (statement.executeUpdate() != 1)
                throw new SQLException("Expected one shipment update");
        }
    }
}

Performance and ownership cost

Parameter binding is O(L) or driver-dependent for a note of length L; this code holds at most 47 Java UTF-16 code units. Database update cost depends on indexing, locks, and transaction work. The method allocates no result collection.

Common Mistakes

• Do not equate SQL NULL with an empty string.
• Do not ignore a zero-row or multi-row update when one row is required.
• Do not assume a Java string-length cap exactly matches database byte or character semantics.

Connected lessons

• Java JDBC PreparedStatement: values stay outside SQL grammar
• Java ResultSet: cursor position and SQL NULL are separate states
• Java JDBC generated keys: validate one insert and one returned identity
• Java JDBC ResultSet binary streams: read before advancing the cursor
• Java JDBC binary parameters: cap bytes before binding a BLOB
• Java JDBC boundary contracts quiz
• Advanced Java