Java program to execute a database transaction.

10.A] Write a Java program to execute a database transaction.

Answer:

• A transaction is a set of actions to be carried out as a single, atomic action. Either all of the actions are carried out, or none of them are.
• Advantage is fast performance It makes the performance fast because database is hit at the time of commit.

In JDBC, Connection interface provides methods to manage transaction.

Java program to execute a database transaction

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcTransactionExample {
    public static void main(String[] args) {
        // Assume a valid Connection object
        Connection conn = DriverManager.getConnection(url, user, password); 

        try {
            // Disable auto-commit
            conn.setAutoCommit(false);

            // Create a Statement object
            Statement stmt = conn.createStatement();

            // Execute a valid SQL statement
            String sql1 = "INSERT INTO Employees VALUES (106, 20, 'Rita', 'Tez')";
            stmt.executeUpdate(sql1);

            // Execute a malformed SQL statement that will cause an error
            String sql2 = "INSERTED IN Employees VALUES (107, 22, 'Sita', 'Singh')";
            stmt.executeUpdate(sql2);

            // If there is no error, commit the transaction
            conn.commit();
            System.out.println("Transaction committed successfully.");

        } catch (SQLException se) {
            // Rollback in case of an error
            try {
                if (conn != null) {
                    conn.rollback();
                    System.out.println("Transaction rolled back.");
                }
            } catch (SQLException rollbackEx) {
                System.out.println("Error during rollback.");
                rollbackEx.printStackTrace();
            }
            se.printStackTrace();
        }
    }
}

Key Points:

• Disable Auto-Commit: conn.setAutoCommit(false);
• Execute Statements: First valid, then intentionally malformed.
• Commit or Rollback:
• Commit: conn.commit(); if all statements succeed.
• Rollback: conn.rollback(); if an error occurs.
• Error Handling: Print stack traces for debugging.

Note: This code snippet assumes you have already initialized the conn variable with a valid database connection. Ensure to replace conn with your actual connection object.