Home Tutorial Java Jdbc Turning transaction off and on

 
 

Share on Google+Share on Google+
Turning transaction off and on
Posted on: October 23, 2010 at 12:00 AM
Advertisement
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

Advertisement

Related Tags for Turning transaction off and on:


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: October 23, 2010

Recommend the tutorial

Advertisements Advertisements
 

 

 

DMCA.com