JDBC Transaction Example

In this tutorial you will learn how run multiple SQL statements in JDBC using JDBC transaction

In this tutorial you will learn how run multiple SQL statements in JDBC using JDBC transaction

JDBC Transaction Example

JDBC Transaction

JDBC transaction is used to execute a group of SQL statement so that they can execute together successfully. When you create a connection using JDBC, by default it is in auto-commit mode and each SQL statement is treated as transaction and it is committed automatically when an SLQ statement is executed. When you want that one or more SQL statement can execute together, then you have to set conn.setAutoCommit(false).

When you set a auto-commit false then SQL statement will be executed only when you call commit() method explicitly. All the previous call will committed together or there is a problem then all statement will roll back.

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

Here is the video tutorial which shows you how to run the code example with instruction.

How to manage transaction in JDBC?

JDBC Transaction Video Tutorial

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);
	// Setting auto commit false
	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);
	// Calling commit() method
	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:

Databese Connection Done........
Roll No.- 1, Name- Vinay, Course- MCA, Address- Motihari
Roll No.- 2, Name- Ram, Course- BCA, Address- Patna
Connection Closed..........

Download this example code

Download the code discussed in the Video Tutorials in Eclipse project format.

Ads