Transaction:
A transaction is a sequence of operation which works as an atomic unit. A transaction only completes if all the operations completed successfully. A transaction has the Atomicity, Consistency, Isolation and Durability properties (ACID).
JDBC transaction management:
By default JDBC connection is in auto-commit mode and we can’t control it i.e. whenever a SQL statement is completed it will commit automatically. If we want to control the JDBC transaction then set setAutoCommit() method to false.
Transaction management methods of Connection interface:
1. setAutoCommit(boolean status): It is used to set the auto-commit mode true or false.
Syntax: public void setAutoCommit(boolean autoCommit) throws SQLException
2. commit(): It is used to commit all the changes made in the current transaction.
Syntax: public void commit() throws SQLException
3. rollback(): It is used to undo all the changes made in the current transaction.
Syntax: public void commit() throws SQLException
Example:
JDBCTest.java
import java.sql.Connection; import java.sql.Statement; import com.w3schools.util.JDBCUtil; /** * This class is used show the transaction management. * @author w3schools */ public class JDBCTest { public static void main(String args[]){ Connection conn = null; Statement statement = null; String query1 = "insert into EMPLOYEE " + "(EMPLOYEE_ID, NAME, SALARY) " + "values (11, 'Harish Kansal', 50000)"; String query2 = "insert into EMPLOYEE " + "(EMPLOYEE_ID, NAME, SALARY) " + "values (12, 'Vivek Solenki', 80000)"; try{ //get connection conn = JDBCUtil.getConnection(); //set auto commit to false conn.setAutoCommit(false); //create statement statement = conn.createStatement(); //execute query statement.executeUpdate(query1); //date will not commit directly statement.executeUpdate(query2); //data will rollback here if any exception occurs //commit will commit here conn.commit(); //close connection statement.close(); conn.close(); System.out.println("Records inserted successfully."); }catch(Exception e){ e.printStackTrace(); } } } |
JDBCUtil.java
import java.sql.Connection; import java.sql.DriverManager; /** * This is a utility class for JDBC connection. * @author jawithease */ public class JDBCUtil { //JDBC and database properties. private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver"; private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:XE"; private static final String DB_USERNAME = "system"; private static final String DB_PASSWORD = "oracle"; public static Connection getConnection(){ Connection conn = null; try{ //Register the JDBC driver Class.forName(DB_DRIVER); //Open the connection conn = DriverManager. getConnection(DB_URL, DB_USERNAME, DB_PASSWORD); if(conn != null){ System.out.println("Successfully connected."); }else{ System.out.println("Failed to connect."); } }catch(Exception e){ e.printStackTrace(); } return conn; } } |
Output:
Successfully connected.
Records inserted successfully. |