Home Tutorial Java Jdbc JDBC Transaction Example

 
 

JDBC Transaction Example
Posted on: October 14, 2010 at 12:00 AM
Advertisement
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
 );

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

Advertisement

Related Tags for JDBC Transaction Example:


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 14, 2010

Recommend the tutorial

Advertisements Advertisements
 

 

 

DMCA.com