Batch Processing

import java.sql.*;

public class BatchMechanism {
    public static void batchWithPreparedStatement(Connection conn, java.util.List<String> names)
            throws SQLException {
        String sql = "INSERT INTO tags (name) VALUES (?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (String name : names) {
                ps.setString(1, name);
                ps.addBatch();   // queues the currently-bound value; SQL text stays fixed
            }

            int[] results = ps.executeBatch();

            for (int i = 0; i < results.length; i++) {
                if (results[i] == Statement.EXECUTE_FAILED) {
                    System.err.println("Statement " + i + " failed");
                } else if (results[i] == Statement.SUCCESS_NO_INFO) {
                    System.out.println("Statement " + i + " succeeded (row count unknown)");
                } else {
                    System.out.println("Statement " + i + " affected " + results[i] + " row(s)");
                }
            }

            ps.clearBatch();  // defensive — avoids re-queuing if this ps is reused
        }
    }

    public static void batchWithPlainStatement(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.addBatch("UPDATE inventory SET status = 'reviewed' WHERE category = 'electronics'");
            stmt.addBatch("UPDATE inventory SET status = 'reviewed' WHERE category = 'books'");
            stmt.addBatch("DELETE FROM stale_flags WHERE created_at < NOW() - INTERVAL '30 days'");
            // Plain Statement batching is fine for distinct, static statements —
            // but note each entry here is a full SQL string, unlike PreparedStatement's
            // single-SQL-text-plus-many-parameter-sets model.
            int[] results = stmt.executeBatch();
        }
    }
}

import java.sql.*;
import java.util.List;

public class BulkImportWithBatchAndTransaction {
    private static final int FLUSH_EVERY = 500;

    public static void importRecords(Connection conn, List<String[]> rows) throws SQLException {
        String sql = "INSERT INTO imported_records (external_id, payload) VALUES (?, ?)";
        boolean originalAutoCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            int queued = 0;

            for (String[] row : rows) {
                ps.setString(1, row[0]);
                ps.setString(2, row[1]);
                ps.addBatch();
                queued++;

                if (queued % FLUSH_EVERY == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                    // NOTE: no commit() here — the whole import is one transaction;
                    // flushing batches periodically only manages client-side memory
                }
            }
            ps.executeBatch();  // flush any remainder under the last FLUSH_EVERY boundary

            conn.commit();  // entire import becomes permanent atomically

        } catch (SQLException e) {
            conn.rollback();  // undo the entire import, including already-flushed batches
            throw e;
        } finally {
            conn.setAutoCommit(originalAutoCommit);
        }
    }
}

// ── Enabling driver-specific batch rewriting via the JDBC URL ──────────────

// MySQL Connector/J: rewrites a batch of INSERTs into one multi-row INSERT
String mysqlUrl = "jdbc:mysql://localhost:3306/storedb?rewriteBatchedStatements=true";

// PostgreSQL pgjdbc: similar optimization, opt-in via its own property name
String pgUrl = "jdbc:postgresql://localhost:5432/storedb?reWriteBatchedInserts=true";

// ── Without rewriting, illustratively, this might be N round trips: ───────
//   INSERT INTO t (a, b) VALUES (1, 'x');
//   INSERT INTO t (a, b) VALUES (2, 'y');
//   INSERT INTO t (a, b) VALUES (3, 'z');
// ── With rewriting enabled, the driver may send something closer to: ──────
//   INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y'), (3, 'z');
// ── as a single statement — but this is a driver implementation detail,
// not something the JDBC specification guarantees, and behavior should be
// verified against the specific driver version in use rather than assumed.

import java.sql.*;

public class BatchRewriteAwareInsert {
    public static void insertMany(Connection conn, java.util.List<Object[]> rows) throws SQLException {
        // Application code is unchanged regardless of whether the driver rewrites
        // the batch under the hood — the rewriting is transparent to this method.
        String sql = "INSERT INTO t (a, b) VALUES (?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (Object[] row : rows) {
                ps.setObject(1, row[0]);
                ps.setObject(2, row[1]);
                ps.addBatch();
            }
            ps.executeBatch();
        }
    }
}