JDBC Transaction example

JDBC Transaction let you control how and when a transaction should commit into database.


//transaction block start

//SQL insert statement
//SQL update statement 
//SQL delete statement

//transaction block end

In simple, JDBC transaction make sure SQL statements within a transaction block are all executed successful, if either one of the SQL statement within transaction block is failed, abort and rollback everything within the transaction block.

See below two examples to understand how JDBC transaction works.

1. Without JDBC Transaction

By default, data will be committed into database when executeUpdate() is called.


String insertTableSQL = "INSERT INTO DBUSER"
			+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
			+ "(?,?,?,?)";

String updateTableSQL = "UPDATE DBUSER SET USERNAME =? "
			+ "WHERE USER_ID = ?";

preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
preparedStatementInsert.setInt(1, 999);
preparedStatementInsert.setString(2, "mkyong101");
preparedStatementInsert.setString(3, "system");
preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
preparedStatementInsert.executeUpdate(); //data COMMITTED into database.

preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
preparedStatementUpdate.setString(1, "A very very long string caused DATABASE ERROR"); 
preparedStatementUpdate.setInt(2, 999);

preparedStatementUpdate.executeUpdate(); //Error, value too big,  ignore this update statement, 
                                                //but user_id=999 is inserted
                                      

When this code is executed, the USER_ID = ‘999’ is inserted but the username is not update.

2. With JDBC Transaction

To put this in a transaction, you can use

  1. dbConnection.setAutoCommit(false); to start a transaction block.
  2. dbConnection.commit(); to end a transaction block.

See code snippets :


dbConnection.setAutoCommit(false); //transaction block start

String insertTableSQL = "INSERT INTO DBUSER"
			+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
			+ "(?,?,?,?)";

String updateTableSQL = "UPDATE DBUSER SET USERNAME =? "
			+ "WHERE USER_ID = ?";

preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
preparedStatementInsert.setInt(1, 999);
preparedStatementInsert.setString(2, "mkyong101");
preparedStatementInsert.setString(3, "system");
preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
preparedStatementInsert.executeUpdate(); //data IS NOT commit yet

preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
preparedStatementUpdate.setString(1, "A very very long string caused DATABASE ERROR"); 
preparedStatementUpdate.setInt(2, 999);
preparedStatementUpdate.executeUpdate(); //Error, rollback, including the first insert statement.

dbConnection.commit(); //transaction block end

When this code is executed, update statement is hits error, and make both insert and update statements rollback together.

Full JDBC Transaction example

See a complete JDBC transaction example.


package com.mkyong.jdbc;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCTransactionExample {

	private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";

	public static void main(String[] argv) throws SQLException {

		Connection dbConnection = null;
		PreparedStatement preparedStatementInsert = null;
		PreparedStatement preparedStatementUpdate = null;

		String insertTableSQL = "INSERT INTO DBUSER"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
				+ "(?,?,?,?)";

		String updateTableSQL = "UPDATE DBUSER SET USERNAME =? "
				+ "WHERE USER_ID = ?";

		try {
			dbConnection = getDBConnection();

			dbConnection.setAutoCommit(false);

			preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
			preparedStatementInsert.setInt(1, 999);
			preparedStatementInsert.setString(2, "mkyong101");
			preparedStatementInsert.setString(3, "system");
			preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
			preparedStatementInsert.executeUpdate();

			preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
			// preparedStatementUpdate.setString(1,
			// "A very very long string caused db error");
			preparedStatementUpdate.setString(1, "new string");
			preparedStatementUpdate.setInt(2, 999);
			preparedStatementUpdate.executeUpdate();

			dbConnection.commit();

			System.out.println("Done!");

		} catch (SQLException e) {

			System.out.println(e.getMessage());
			dbConnection.rollback();

		} finally {

			if (preparedStatementInsert != null) {
				preparedStatementInsert.close();
			}

			if (preparedStatementUpdate != null) {
				preparedStatementUpdate.close();
			}

			if (dbConnection != null) {
				dbConnection.close();
			}

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
					DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

	private static java.sql.Timestamp getCurrentTimeStamp() {

		java.util.Date today = new java.util.Date();
		return new java.sql.Timestamp(today.getTime());

	}

}

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
Invi
Guest
Invi

but how to do connection rollback in try-with-resources block (without nesting another try-block)?

Edison
Guest
Edison

Do we have to add “dbConnection.setAutoCommit(true);” to finally block?

Yisus
Guest
Yisus

You save me, thanks

captain
Guest
captain

try {
DataBase.insert…
DataBase.update…
DataBase.connection.commit();
System.out.println(“?????????????????”);
} catch (Exception e) {
DataBase.connection.rollback();
System.out.println(“????????????????”);
}
Transaction ???????????catch?MySQLSyntaxErrorException?

Shi
Guest
Shi

Very helpful, love your website.

Andrey
Guest
Andrey

Wonderful clear example.
Really appreciate your work.

Rajeev
Guest
Rajeev

What if ” dbConnection.rollback() ” throws an Exception? .

Ryan
Guest
Ryan

Addition to this, engine must be InnoDB, MyISAM does not support transactions

Victor
Guest
Victor

As a user said… Our lives just became easier. Thanks man

raja
Guest
raja

Hi lokesh
The query will run because the session has been started and even if u stop the JVM the session will run until unless it is in same module

raja
Guest
raja

Hi,
I have a doubt, after updating the values in service level it is not getting committed into the database what may be the reason??

lokesh
Guest
lokesh

Hi,
i have a small doubt in jdbc. suppose i started the query execution which takes more than 5 min from jdbc.after initiating the executequery command.If i stop the jvm.what will happen to that query.

1.Will query run in db?
if yes..how?
2.Will the query execution stops?
if yes ..how?
consider oracle db here…

please reply asap

Sandeep
Guest
Sandeep

Hi Lokesh, you got the anser, if yes please share it

Evan
Guest
Evan

I think Hibernate also use this to do “session.beginTransaction()” and “session.getTransaction().commit()”.

Lisandro Duran
Guest
Lisandro Duran

You’re great man.
Thank you!

Rajesh
Guest
Rajesh

awesome examples, thanks a lot which is helping me a lot

Mustafa Asif
Guest
Mustafa Asif

Simple post even if you want to revise your fundamentals. Thanks.

Binh Thanh Nguyen
Guest
Binh Thanh Nguyen

Thanks, nice post

Marcos
Guest
Marcos

Having a second thought about the right usage finalizers (finally block) and specifically related to the closing of the database connection. I’ve wondered and researched that furthermore to the explicit call of the termination method “dbConnection.close()” within the finally block (which may be used as safety-net measure), it would be more efficient to add an extra “dbConnection.close()” at the end of the “try” block. Could someone tell me if it is worthwhile to do?

karunakaran
Guest
karunakaran

Very good example

Toto
Guest
Toto

I’m very glad that I found your blog ! My life is much easier. Thank you so much :)

pea
Guest
pea

My life is easier since I found your blogs.
Very nice blogs.

arunkumar
Guest
arunkumar

how do i execute sql trigger using jdbc connection ..

Dhinakaran Pragasam
Guest
Dhinakaran Pragasam

Refer below link for execute sql trigger and procedure using jdbc http://techdive.in/java/mysql-createtrigger-using-jdbc-connection

roger
Guest
roger

You set them in autoCommit(false); but never change it back :|