How do you handle transactions in JDBC?

Transactions in JDBC help to ensure data consistency and integrity. A transaction is a sequence of database operations which should be executed as a whole or not at all. JDBC API provides support for performing transaction-related operations through the Connection object.

Here’s how you can handle transactions in JDBC:

1. Set AutoCommit to false: By default, JDBC has the autocommit feature enabled, which automatically commits each SQL statement execution as a single transaction. To disable auto-commit, set the "autocommit" property of the Connection object to false as follows:

    Connection conn = DriverManager.getConnection(url, username, password);
    conn.setAutoCommit(false);

2. Execute SQL Statements: You can now execute a series of SQL statements that make up your transaction. Perform all database operations, such as inserting, updating, and deleting data within this transaction.

    PreparedStatement stmt = conn.prepareStatement("INSERT INTO employees (id, name, salary) VALUES (?, ?, ?)");
    stmt.setInt(1, 123);
    stmt.setString(2, "John Smith");
    stmt.setDouble(3, 50000);
    stmt.executeUpdate();
    stmt.close();

3. Commit or Rollback Transactions: Once you have executed your SQL statements, you can commit or rollback your transaction based on the outcome of the JDBC operation.

    try {
        // execute SQL statements
        conn.commit();
    } catch (SQLException e) {
        // rollback the transaction in case of exception
        conn.rollback();
    }

The commit() method checks if all SQL statements have been executed successfully within the transaction. If successful, it commits the transaction and makes it permanent. Otherwise, the rollback() method is called to undo all changes made during the transaction, thus ensuring data integrity.

In conclusion, using transactions in JDBC provides a way to execute a series of database operations as a single unit, ensuring data consistency and integrity, even in the face of error or application failures.