Java JDBC read-only mode: use the hint without treating it as authorization

Last updated: 5 Oct 20265 min read

tutorial

Connection.setReadOnly is a hint to the driver and database for read-oriented work. It is not an access-control mechanism and must be set outside an active transaction.

Operational contract

The method requires an auto-commit connection at entry, saves its previous read-only flag, enables the hint, performs a bounded one-row lookup, then restores the flag when its scope closes. The caller still owns the connection. A pooled connection must not leak a read-only setting into the next borrower. If both lookup and reset fail, try-with-resources preserves the lookup error and suppresses the reset error; production pool code should discard a connection it cannot reset. Database credentials, SQL privileges, and service authorization determine whether writes are allowed.

Failure case

A reporting service reads depot 47 through a pooled connection. The hint may help a driver choose a route or plan. When the method returns, the next borrower must not unexpectedly inherit the hint. A malicious caller does not gain or lose write permission merely because this flag changed.

Java code

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

public class ReadOnlyDepotLookup {
    private static final class ReadOnlyScope implements AutoCloseable {
        private final Connection connection;
        private final boolean previous;

        private ReadOnlyScope(Connection connection) throws SQLException {
            if (!connection.getAutoCommit())
                throw new IllegalStateException("Active transaction policy");
            this.connection = connection;
            this.previous = connection.isReadOnly();
            connection.setReadOnly(true);
        }

        @Override public void close() throws SQLException {
            connection.setReadOnly(previous);
        }
    }

    public static String name(Connection connection, long depotId) throws SQLException {
        try (ReadOnlyScope scope = new ReadOnlyScope(connection);
             PreparedStatement statement = connection.prepareStatement(
                "SELECT depot_name FROM depots WHERE depot_id = ?")) {
            statement.setLong(1, depotId);
            try (ResultSet rows = statement.executeQuery()) {
                if (!rows.next()) return null;
                String depotName = rows.getString(1);
                if (rows.next()) throw new SQLException("Duplicate depot ID");
                return depotName;
            }
        }
    }
}

Performance and ownership cost

The flag changes may involve driver or database calls; the lookup's indexed and lock behavior dominates work. The Java method holds O(1) data. Scope closure restores ordinary paths, but a broken connection after a network failure must be handled by the connection owner or pool.

Common Mistakes

Do not treat setReadOnly as a security permission.
Do not toggle it inside a transaction.
Do not return a connection with uncertain reset state to a pool.

Connected lessons

java

transaction and capability boundaries

jdbc-readonly-hint-not-authorization