sql

JDBC Transaction Rollback Example

In this example will talk about how to perform rollback in JDBC transactions.

When we are making changes in the database through a java.sql.Connection, it’s necessary prevent it form going to an inconsistent state, in case of an exception for example. So how do we do that? There are some key steps.

  • Manage manually the changes with transactions, disabling the auto commit
  • Explicitly call commit and rollback
  • Rollback a transaction in a catch clause
  • Explicitly close the connection.

 
It’s so important that you remember that if you call connection.commit() the changes cannot be reverted with connection.rollback().

1. Example

Let’s take a look of the example with this code snippet.

DBConnection.java:

package com.javacodegeeks.jdbc.transactions;

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

/**
 * @author Andres.Cespedes
 *
 */
public class DBConnection {

	private static String DB_URL = "jdbc:mysql://localhost:3307/test";
	private static String DB_USER = "admin";
	private static String DB_PASSWORD = "admin";

	public static Connection getConnection() throws SQLException {
		Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
		System.err.println("The connection is successfully obtained");
		return connection;
	}
}

Rollback.java:

package com.javacodegeeks.jdbc.transactions;

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

/**
 * @author Andres.Cespedes
 *
 */
public class Rollback {

	private static String INSERT = "INSERT INTO test.department (idDepartment, name) VALUES (?, ?)";

	public static void insertRow(Connection conn, int idRow, String contentRow)
			throws SQLException {
		PreparedStatement pstmt = null;
		pstmt = conn.prepareStatement(INSERT);
		pstmt.setInt(1, idRow);
		pstmt.setString(2, contentRow);
		pstmt.execute();
		pstmt.close();
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Connection connection = null;
		try {
			// 1st Step, Make a connection
			connection = DBConnection.getConnection();
		} catch (SQLException e) {
			System.err.println("There was an error getting the connection");
		}
		try {
			// 2nd Step, Disable the auto commit
			connection.setAutoCommit(false);
			System.err.println("The autocommit was disabled!");
		} catch (SQLException e) {
			System.err.println("There was an error disabling autocommit");
		}
		// Starts JDBC Transaction
		try {
			// 3rd Step, Execute the statements
			insertRow(connection, 1, "Malaga");
			insertRow(connection, 2, "Barcelona");
			// 4th Step, Complete a transaction, committing the changes.
			connection.commit();
			System.err.println("The transaction was successfully executed");
		} catch (SQLException e) {
			try {
				// 5th and Final Step, We must rollback the transaction if a
				// SQLException occurs
				connection.rollback();
				System.err.println(e.getMessage());
				System.err.println("Transaction rollback");
			} catch (SQLException e1) {
				System.err.println(e1.getMessage());
				System.err.println("There was an error making a rollback");
			}
		}
	}

}

2. The Output

In this example, we define a column with a VARCHAR(6). This means that we can store up to six characters to it. In the first insert, we put "Malaga", whose length is within the limit. This will be stored in the database, but remember that the change is not still permanent. In the second insert we put in "Barcelona", whose length exceeds the limit. So it generates a SQLException thus connection.rollback(); is called.

This is the output.

The connection is successfully obtained
The autocommit was disabled!
Data truncation: Data too long for column 'name' at row 1
The transaction rollback

Now if you go to your database you will see that "Malaga" is not inserted to the table, because the transaction rolled back. So all the changes that have not been committed will be reverted.

3. Download Source Code

Download
You can download the full source code of this example here: JDBCRollbackExample.zip

Andres Cespedes

Andres is a Java Software Craftsman from Medellin Colombia, who strongly develops on DevOps practices, RESTful Web Services, Continuous integration and delivery. Andres is working to improve software process and modernizing software culture on Colombia.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button