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