JDBC Transaction Rollback Example (original) (raw)

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().

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

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.