Transactions & Batch Processing in JDBC

Modern database applications require data consistency, reliability, and high performance. JDBC provides powerful features like transaction management and batch processing to handle these requirements efficiently.

? These features ensure that database operations are safe, consistent, and optimized for performance, especially in real-world applications.

---

What is Transaction in JDBC?

A transaction is a group of SQL operations executed as a single unit.

In simple words: Either all operations succeed, or none of them are applied.

? This means if any step fails, all previous changes are rolled back to maintain consistency.

Example (JDBC Transaction Code)

import java.sql.*;

public class TransactionExample {
    public static void main(String[] args) throws Exception {

        Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/testdb", "root", "password");

        // Step 1: Disable auto-commit
        con.setAutoCommit(false);

        try {
            // Step 2: First operation
            PreparedStatement ps1 = con.prepareStatement(
                "UPDATE accounts SET balance = balance - 1000 WHERE id = 1"
            );
            ps1.executeUpdate();

            // Step 3: Second operation
            PreparedStatement ps2 = con.prepareStatement(
                "UPDATE accounts SET balance = balance + 1000 WHERE id = 2"
            );
            ps2.executeUpdate();

            // Step 4: Commit transaction
            con.commit();
            System.out.println("Transaction Successful!");

        } catch (Exception e) {

            // Step 5: Rollback if error occurs
            con.rollback();
            System.out.println("Transaction Failed! Rolled back.");

        }

        con.close();
    }
}

How it Works

• setAutoCommit(false) → Starts transaction mode
• Multiple SQL operations are executed
• commit() → Saves all changes permanently
• rollback() → Cancels all operations if error occurs

Why Transactions are Important

Without transactions:

• Data can become inconsistent
• Partial updates may occur (half work done)
• System reliability decreases
• Errors can corrupt important data

---

Transaction Management in JDBC

JDBC provides methods to control transactions manually, allowing you to decide when changes should be saved or cancelled.

1. commit()

commit() is used to save changes permanently in the database.

All SQL operations executed after disabling auto-commit are stored temporarily and only saved when commit() is called.

Example

Connection con = DriverManager.getConnection(url, user, pass);

// Disable auto-commit mode
con.setAutoCommit(false);

Statement stmt = con.createStatement();

// First operation
stmt.executeUpdate(
    "UPDATE accounts SET balance = balance - 100 WHERE id = 1"
);

// Second operation
stmt.executeUpdate(
    "UPDATE accounts SET balance = balance + 100 WHERE id = 2"
);

// Save all changes
con.commit();

Explanation

• setAutoCommit(false) → Starts manual transaction mode
• Multiple SQL operations are executed as one unit
• commit() → Saves all changes permanently to the database

? Without calling commit(), changes will not be saved, and may be lost if the connection is closed.

---

2. rollback()

rollback() is used to undo changes if something goes wrong during a transaction.

It cancels all operations performed after setAutoCommit(false) and restores the database to its previous state.

Example

try {
    con.setAutoCommit(false);

    stmt.executeUpdate(
        "UPDATE accounts SET balance = balance - 100 WHERE id = 1"
    );

    stmt.executeUpdate(
        "UPDATE accounts SET balance = balance + 100 WHERE id = 2"
    );

    con.commit();

} catch (Exception e) {

    // Undo all changes if error occurs
    con.rollback();
}

Explanation

• setAutoCommit(false) → Starts transaction mode
• SQL operations are executed as a single unit
• If everything is successful → commit() saves changes
• If any error occurs → rollback() cancels all operations

? rollback() ensures data safety and consistency, especially in critical operations like banking and transactions.

---

3. Savepoint

A Savepoint allows you to create a checkpoint within a transaction.

It helps you rollback only a specific part of the transaction, instead of undoing everything.

Example

con.setAutoCommit(false);

stmt.executeUpdate("INSERT INTO users VALUES (1, 'Amit')");

// Create savepoint
Savepoint sp = con.setSavepoint();

stmt.executeUpdate("INSERT INTO users VALUES (2, 'Rahul')");

// Rollback only second insert
con.rollback(sp);

// Commit remaining changes
con.commit();

Explanation

• First insert is executed
• setSavepoint() → Creates a checkpoint
• Second insert is executed
• rollback(sp) → Undoes only the operations after savepoint
• commit() → Saves remaining changes

? Only changes after the savepoint are undone, earlier changes remain safe.

---

4. Auto-Commit Behavior

By default, JDBC runs in auto-commit mode.

This means every SQL statement is automatically saved in the database.

Default Behavior

• Each SQL statement is committed automatically
• No need to call commit() manually
• No transaction control

Disable Auto-Commit

con.setAutoCommit(false);

Explanation

• Disabling auto-commit allows manual control of transactions
• You can now use commit() and rollback()
• Required for handling multiple operations as a single unit

? Auto-commit must be disabled when you want to implement proper transaction management in JDBC.

---

ACID Properties (Conceptual)

Transactions follow ACID properties to ensure reliability, consistency, and safety of database operations.

In simple words: ACID rules make sure that data is always correct and safe, even if errors occur.

Atomicity

• All operations succeed or none
• No partial execution

? If any step fails, the entire transaction is rolled back

Example:

Money deducted but not added → Not allowed → Entire transaction cancelled

Consistency

• Database remains in a valid state

? Before and after the transaction, the database must follow all rules (constraints, relations)

Example:

Account balance should never go negative (if rule exists)

Isolation

• Transactions do not interfere with each other

? Multiple transactions can run simultaneously without affecting each other

Example:

User A and User B transactions run at the same time without conflict

Durability

• Once committed, data is permanently saved

? Even if system crashes, committed data will not be lost

Example:

After commit(), data is stored safely in the database

---

Batch Processing in JDBC

Batch processing allows executing multiple SQL queries together instead of one by one.

? This significantly improves performance, especially when dealing with large amounts of data.

Why Use Batch Processing?

Without Batch

• Each query is sent separately to the database
• More network calls between application and database
• Slower execution

With Batch

• Multiple queries are sent together in one request
• Reduced network overhead
• Faster execution and better performance

Example: Batch Processing

Connection con = DriverManager.getConnection(url, user, pass);

Statement stmt = con.createStatement();

stmt.addBatch("INSERT INTO users VALUES (1, 'Amit')");
stmt.addBatch("INSERT INTO users VALUES (2, 'Rahul')");
stmt.addBatch("INSERT INTO users VALUES (3, 'Neha')");

int[] result = stmt.executeBatch();

Explanation

• addBatch() → Adds multiple SQL queries to the batch
• executeBatch() → Executes all queries at once
• int[] result → Returns update count for each query

---

Batch with PreparedStatement (Best Practice)

PreparedStatement ps = con.prepareStatement(
    "INSERT INTO users (id, name) VALUES (?, ?)"
);

ps.setInt(1, 1);
ps.setString(2, "Amit");
ps.addBatch();

ps.setInt(1, 2);
ps.setString(2, "Rahul");
ps.addBatch();

int[] result = ps.executeBatch();

Explanation

• prepareStatement() → Precompiles the SQL query
• setInt() / setString() → Sets parameter values
• addBatch() → Adds each set of values to the batch
• executeBatch() → Executes all queries together
• int[] result → Returns number of rows affected for each query

---

Performance Benefits

Batch processing improves performance by:

• Reducing network round trips between application and database
• Minimizing the number of database calls
• Improving overall execution speed
• Efficiently handling large volumes of data

? Instead of executing queries one by one, batch processing executes them in a single go, which saves time and resources.

? Ideal for bulk operations (1000+ records or more)

---

Real-World Use Cases

Batch processing is widely used in real-world applications such as:

• Banking transactions (processing multiple transfers together)
• E-commerce order processing (bulk order updates)
• Bulk data import/export (CSV, Excel uploads)
• Payroll systems (salary processing for employees)
• Logging and analytics systems (storing large-scale data)

---

Common Mistakes to Avoid

• Forgetting to call commit() after disabling auto-commit (changes won’t be saved)
• Not handling rollback() in case of failure (can lead to inconsistent data)
• Using batch processing for very small operations (unnecessary overhead)
• Ignoring proper exception handling (may cause silent failures)
• Not clearing batch using clearBatch() when reusing statements

---

Conclusion

Transactions and batch processing are essential for building robust, reliable, and high-performance applications.

• Transactions ensure data safety, consistency, and integrity
• Batch processing improves performance and scalability