Turning transaction off and on

In this JDBC tutorial you will learn how to tun on and off the transaction

In this JDBC tutorial you will learn how to tun on and off the transaction

Turning Committing Transaction Off and On

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();

An example given below which illustrate this,

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.

JDBCTransactionExmple.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 JDBCTransactionExmple {
	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 updateQuery1 = "INSERT INTO student VALUES(1,'Vinay','MCA','Motihari')";
			String updateQuery2 = "INSERT INTO student VALUES(2,'Ram','BCA','Patna')";
			String selectQuery = "SELECT * FROM student";
			stmt.executeUpdate(updateQuery1);
			stmt.executeUpdate(updateQuery2);
			rs = stmt.executeQuery(selectQuery);
			
			// turning auto-commit on
			conn.commit();
			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:

1:Java:MCA:Motihari
2:Ravi:BCA:Patna
3:Mansukh:M.Sc:Katihar
4:Raman:B.Tech:Betiah
5:Kanhaiya:M.Tech:Delhi
6:Tinkoo:MBA:Alligarh
7:Pawan:BBA:Darbhanga

Download this example code

Ads