JDBC transaction management example

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.

Download this example.