Hey there! Let's get your Google Cloud Spanner DML operations sorted out. The error you're seeing happens because Cloud Spanner has specific requirements for executing write operations—either via explicit DML statements (with the right transaction setup) or using Mutations (the builder approach you started with). Here's how to make both work:

Using DML Statements (INSERT/UPDATE/DELETE) in JDBC #

The key issue here is that Cloud Spanner's JDBC connection defaults to read-only mode, which blocks DML operations. You need to explicitly enable write transactions and ensure you're connected to a writable instance (not a read replica).

Here's a working code example:

// Replace with your Spanner JDBC URL
String jdbcUrl = "jdbc:cloudspanner://localhost:9010/projects/your-project/instances/your-instance/databases/your-db;usePlainText=true";

try (Connection connection = DriverManager.getConnection(jdbcUrl)) {
    // Disable read-only mode to allow writes
    connection.setReadOnly(false);
    // Turn off auto-commit to manage transactions manually
    connection.setAutoCommit(false);

    try (PreparedStatement insertStmt = connection.prepareStatement(
            "INSERT INTO your_table (id, column1, column2) VALUES (?, ?, ?)")) {
        insertStmt.setLong(1, 101);
        insertStmt.setString(2, "Sample Value");
        insertStmt.setTimestamp(3, Timestamp.from(Instant.now()));
        
        int rowsInserted = insertStmt.executeUpdate();
        System.out.printf("Inserted %d row(s)%n", rowsInserted);

        // Add UPDATE statement example
        try (PreparedStatement updateStmt = connection.prepareStatement(
                "UPDATE your_table SET column1 = ? WHERE id = ?")) {
            updateStmt.setString(1, "Updated Value");
            updateStmt.setLong(2, 101);
            int rowsUpdated = updateStmt.executeUpdate();
            System.out.printf("Updated %d row(s)%n", rowsUpdated);
        }

        // Commit the transaction to apply all changes
        connection.commit();
    } catch (SQLException e) {
        // Rollback on failure to maintain data consistency
        connection.rollback();
        System.err.println("Transaction failed: " + e.getMessage());
        throw e;
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Important checks:

• Ensure you're connecting to the primary instance of your Spanner database (read replicas don't support writes).
• Verify your JDBC driver version is up-to-date—older versions might have limitations on DML support.

Using Mutations (The Builder Approach) #

Mutations are Cloud Spanner's native way to handle writes, especially for bulk operations. They're often more efficient than DML for multiple writes. You were on the right track with Mutation.newInsertBuilder—here's how to complete that flow using the Spanner Java client:

import com.google.cloud.spanner.*;
import java.util.ArrayList;
import java.util.List;
import java.time.Instant;

public class SpannerMutationsExample {
    public static void main(String[] args) {
        String projectId = "your-project-id";
        String instanceId = "your-instance-id";
        String databaseId = "your-database-id";

        // Initialize Spanner client (try-with-resources auto-closes it)
        try (Spanner spanner = SpannerOptions.newBuilder().setProjectId(projectId).build().getService()) {
            DatabaseClient dbClient = spanner.getDatabaseClient(
                    DatabaseId.of(projectId, instanceId, databaseId));

            // Create a list of mutations (atomic: all succeed or none do)
            List<Mutation> mutations = new ArrayList<>();

            // Insert mutation
            mutations.add(Mutation.newInsertBuilder("your_table")
                    .set("id").to(102)
                    .set("column1").to("Mutation Insert")
                    .set("column2").to(Timestamp.from(Instant.now()))
                    .build());

            // Update mutation
            mutations.add(Mutation.newUpdateBuilder("your_table")
                    .set("column1").to("Mutation Update")
                    .where("id").equals(102)
                    .build());

            // Delete mutation
            mutations.add(Mutation.newDeleteBuilder("your_table")
                    .where("id").equals(102)
                    .build());

            // Execute all mutations in one atomic operation
            dbClient.write(mutations);
            System.out.println("Mutations executed successfully");
        }
    }
}

Mutations are inherently atomic—either all of them apply, or none do. This makes them ideal for maintaining data consistency when making multiple related changes.

Key Differences Between DML and Mutations #

• DML: Uses familiar SQL syntax, supports conditional logic in statements, works well for single or small batches of writes. Requires a writable transaction setup.
• Mutations: More efficient for bulk writes, atomic by default, are the preferred method for high-throughput write operations in Spanner.

内容的提问来源于stack exchange，提问作者spannerUser