Java JDBC PreparedStatement: values stay outside SQL grammar

Java 8+. The JDBC programs need the H2 driver on the runtime classpath.

Bind values, not identifiers

The receipt lookup uses a fixed WHERE clause and a bound receipt ID. A quote inside an ID remains data. Concatenating that ID into the SQL string would force the input to participate in parsing.

A placeholder cannot stand in for a table name, column name, or arbitrary ORDER BY expression. If users choose a sorting field, map an allowed choice to a fixed SQL fragment owned by the application. Binding values does not validate unrestricted SQL fragments.

This program creates a disposable in-memory H2 database. Add com.h2database:h2:2.3.232 to the runtime dependencies. The program uses only JDBC types at compilation but still needs a database driver when DriverManager opens the URL.

Keep result ownership inside the query

The method returns a String rather than an open ResultSet. Nested try-with-resources closes the result and statement before returning. The caller owns the connection and decides its lifetime.

A missing row returns null here. The boundary is explicit; another API might use Optional. Multiple equal IDs are prevented by the primary key. Without that constraint, taking the first row would hide ambiguous data.

A JDBC driver and database may differ in preparation, caching, conversion, and timeout behavior. The portable claim is parameter binding; it is not a promise that every engine parses exactly once.

Working program

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ReceiptLookup {
    static String status(Connection connection, String receiptId) throws SQLException {
        try (PreparedStatement query = connection.prepareStatement(
                "SELECT status FROM receipts WHERE receipt_id = ?")) {
            query.setString(1, receiptId);
            try (ResultSet rows = query.executeQuery()) {
                return rows.next() ? rows.getString(1) : null;
            }
        }
    }
    public static void main(String[] args) throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:receipts")) {
            try (PreparedStatement schema = connection.prepareStatement(
                    "CREATE TABLE receipts(receipt_id VARCHAR PRIMARY KEY, status VARCHAR)")) {
                schema.executeUpdate();
            }
            try (PreparedStatement insert = connection.prepareStatement(
                    "INSERT INTO receipts VALUES (?, ?)")) {
                insert.setString(1, "rcpt-'54");
                insert.setString(2, "paid");
                insert.executeUpdate();
            }
            System.out.println(status(connection, "rcpt-'54"));
            System.out.println(status(connection, "absent"));
        }
    }
}

Output

paid
null

Cost and design choices

Lookup cost depends on the query plan, indexes, result size, and engine. A bound parameter is not an indexing strategy. This primary key supports one-row lookup, but a portable JDBC lesson cannot assign a universal O(1) database cost.

Holding connections during unrelated work consumes a pool budget. Close query resources promptly and keep request deadlines, statement timeouts, and connection ownership explicit.

Common Mistakes

• Do not concatenate untrusted values into SQL.
• Do not expect a placeholder to bind a table name.
• Do not return a ResultSet after closing its statement.

Connect the contracts

Parameter binding does not define commit and rollback for a group of writes.

Apply this contract in Spring

Spring JdbcTemplate: bound values and visible database constraints. These lessons keep framework assembly separate from the Java contract.

Continue with checked tenant security

Continue with Spring JdbcTemplate tenant predicates: put ownership in the SQL query.