Commiting and Rolling Back transaction

In this JDBC tutorial you will learn how to commit or roll back the transaction

In this JDBC tutorial you will learn how to commit or roll back the transaction

Rolling Back Transaction

When you do any operation such as , SELECT, INSERT, DELETE, or UPDATE in database then the transaction is committed after the execute update. This is default mode of transaction. To commit more than one transaction at a time you need to set connection object auto-commit(false) (conn.setAutoCommit(false);) and then execute the query and finally call conn.commit();. If any error occurs you can roll back your transaction by calling connection.rollBack();

An example given below which illustrates the commit() rollBack(),

At first create table named student in MySql database and inset values into it as.

CREATE TABLE student (
RollNo int(9) PRIMARY KEY NOT NULL,
Name tinytext NOT NULL,
Course varchar(25) NOT NULL,
Address text
);

Then Write the Following java code.

JDBCTransactionCommitAndRollBack.java

package roseindia.net;

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

public class JDBCTransactionCommitAndRollBack {
	public static void main(String[] args) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String driverName = "com.mysql.jdbc.Driver";
		String connectionUrl = "jdbc:mysql://localhost:3306/";
		String databaseName = "student";
		String userName = "root";
		String password = "root";
		try {
			// Loading Driver
			Class.forName(driverName);
			// Creating Connection
			conn = DriverManager.getConnection(connectionUrl + databaseName,
					userName, password);
			
			// Turning auto-commit Off
			conn.setAutoCommit(false);
			System.out.println("Databese Connection Done........");
		} catch (ClassNotFoundException e) {
			System.out.println(e.toString());
			System.out.println("Error In Connection");
			System.exit(0);
		}
		try {
			// Creating Statement
			stmt = conn.createStatement();
			// Creating Query String
			String updateQuery2 = "INSERT INTO student VALUES(8,'Ram','BCA','Patna')";
			String selectQuery = "SELECT * FROM student";
			try{
				stmt.executeUpdate(updateQuery2);
				rs = stmt.executeQuery(selectQuery);
				conn.commit();
				System.out.println("Transaction is committed");
			}catch(Exception e){
				System.err.print(e);
				System.out.println(e.toString());
				System.out.println("Transaction is rolling Back");
				conn.rollback();
			}
			
			// turning auto-commit on
			while (rs.next()) {
				System.out.println("Roll No.- " + rs.getInt("RollNo")
						+ ", Name- " + rs.getString("Name") + ", Course- "
						+ rs.getString("Course") + ", Address- "
						+ rs.getString("Address"));
			}
		} catch (Exception e) {
			System.out.println(e.toString());
			System.exit(0);
		} finally {
			// Closing Connection
			conn.close();
			stmt.close();
			rs.close();
			System.out.println("Connection Closed..........");
		}
	}
}

When you run this application it will display message as shown below:

Databese Connection Done........
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '8' for key 1
Transaction is rolling Back
java.lang.NullPointerException
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '8' for key 1

Download this example code

Ads