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.
Method | Description |
void setAutoCommit(boolean status) | It is true bydefault means each transaction is committed bydefault. |
void commit() | commits the transaction. |
void rollback() | cancels the 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.