Using Transaction With JDBC in Spring

by

Overview

In the first post of this series, I briefly discussed the problem with multiple database operations and the need for using transaction management. In the last post, I also showed you how to set up PostgreSQL database using docker-compose.

In this post, I’m going to show you the problems and solutions with code.

The data model

As mentioned in the first post, I’m going to use the bank transfer scenarios to demonstrate transactions.

Let’s first create the table in PostgreSQL:

CREATE TABLE IF NOT EXISTS  bank_user (
    id serial8,
    balance bigint,
    name varchar(50)
)

Next, let’s insert Alice and Bob into the table. They all have an initial balance of 100:

insert into bank_user (name, balance) values ('Alice', 1000);
insert into bank_user (name, balance) values ('Bob', 1000);

Now, I have both Alice and Bob in the table and they all have 1000 credits:

Alice and Bob both have 1000 credits
Alice and Bob both have 1000 credits

Let’s create a test to make sure the data in the database is what we expected:

package com.datmt.springdata.springdatajpatransactional;

import com.datmt.springdata.springdatajpatransactional.config.AppConfig;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestInstance;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import static org.junit.jupiter.api.Assertions.*;
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
public class CreditTransferWithoutTransaction {

    private final ApplicationContext context = new AnnotationConfigApplicationContext(AppConfig.class);
    private final DataSource datasource = (DataSource) context.getBean("default_datasource");

    @BeforeAll
    void setup() throws SQLException {
        Connection connection = datasource.getConnection();
        connection.createStatement().executeUpdate("UPDATE bank_user set balance = 1000");
        connection.close();
    }

    @Test
    void test_query_existing_initial_data() throws SQLException {
        Connection connection = datasource.getConnection();

        //get alice details
        var aliceStatement = connection.createStatement().executeQuery("SElECT * FROM bank_user WHERE name = 'Alice'");
        var bobStatement = connection.createStatement().executeQuery("SElECT * FROM bank_user WHERE name = 'Bob'");

        if (aliceStatement.next() && bobStatement.next()) {
            var aliceAmount = aliceStatement.getLong("balance");
            var bobAmount = bobStatement.getLong("balance");

            assertEquals(1000L, aliceAmount);
            assertEquals(1000L, bobAmount);
        } else {
            fail("Bad data in the database");
        }

    }

}

As you can see, at the beginning of all tests, I reset the amount of both Alice and Bob to 1000. The next test ensures that I’m able to pull the data and verify both Alice and Bob have 1000 credit each.

Let’s perform a money transfer from Alice to Bob. Here are the steps:

  1. Withdraw credits from Alice (100)
  2. Deposit credits to Bob’s account(100)

Suppose after a successful withdrawal, an exception in code occurs.

Here is the code:

    @Test
    void test_transfer_money_with_exception(){
        try (Connection connection = datasource.getConnection()) {
            //withdraw credits from Alice's account
            connection.createStatement().executeUpdate("UPDATE bank_user SET balance = 900 WHERE name = 'Alice'");

            //Simulate an exception
            if (true) {
                throw new RuntimeException("Opps! System crashes...");
            }
            connection.createStatement().executeUpdate("UPDATE bank_user SET balance = 1100 WHERE name = 'Bob'");
        } catch (SQLException ex) {
            //
        } catch (RuntimeException ex) {
            //
            System.out.println("Got a runtime exception");
        }

    }

In this code, I first withdraw credits from Alice’s account then intentionally throw an exception to simulate a system error. The update on Bob’s account, thus, never happens.

Checking the database and sure enough, you can see data is not in a consistent state. Before the transaction, Alice and Bob had a total of 2000 credits, now they only have 1900.

Data inconsistent when not using transaction
Data inconsistent when not using transaction

Someone has to fix that!

Low-level transaction management with JDBC

You can mitigate the issue above using JDBC only.

To create a transaction, you first need to set autocommit to false. What is autocommit then? If enabled, the changes persisted in the database after every statement, which is not what you want.

Here is the code that uses JDBC transaction management :

    @Test
    void test_transfer_money_with_transaction_management() {
        try (Connection connection = datasource.getConnection()) {
            connection.setAutoCommit(false);
            //withdraw credits from Alice's account
            connection.createStatement().executeUpdate("UPDATE bank_user SET balance = 900 WHERE name = 'Alice'");

            //Simulate an exception
            if (true) {
                throw new RuntimeException("Opps! System crashes...");
            }
            connection.createStatement().executeUpdate("UPDATE bank_user SET balance = 1100 WHERE name = 'Bob'");
            connection.commit();
        } catch (SQLException ex) {
            //
        } catch (RuntimeException ex) {
            //
            System.out.println("Got a runtime exception");
        }

        try (

                Connection connection = datasource.getConnection();
        ) {
            //get alice details
            var aliceStatement = connection.createStatement().executeQuery("SElECT * FROM bank_user WHERE name = 'Alice'");
            var bobStatement = connection.createStatement().executeQuery("SElECT * FROM bank_user WHERE name = 'Bob'");

            if (aliceStatement.next() && bobStatement.next()) {
                var aliceAmount = aliceStatement.getLong("balance");
                var bobAmount = bobStatement.getLong("balance");

                assertEquals(1000L, aliceAmount, "Alice still has 1000 credits");
                assertEquals(1000L, bobAmount, "Bob still has 1000 credits");
            } else {
                fail("Bad data in the database");
            }

        } catch (SQLException ex) {
            //
        }


    }

Sure enough, the tests passed and both Alice and Bob still have 1000 credits in their account:

Alice and Bob still have 1000 in their account despite system failure
Alice and Bob still have 1000 in their account despite system failure

Use PlatformTransactionManager to manage transactions

Spring Framework provides an interface named PlatformTransactionManager to help developers with managing transactions. This is the common interface for many kinds of transactions (including JPA, JMS).

There are three methods in this interface you can use to manage transactions:

  • commit
  • rollback
  • getTransaction

Here are some notable implementations:

DataSourceTransactionManagerSuitable for managing transactions of a single data source
JtaTransactionManagerThis transaction manager is appropriate for handling distributed transactions, i.e. transactions that span multiple resources, and for controlling transactions on application server resources (e.g. JDBC DataSources available in JNDI) in general
HibernateTransactionManagerThis transaction manager is appropriate for applications that use a single Hibernate SessionFactory for transactional data access

Since I’m using a single data source, I will configure a DataSourceTransactionManager bean in the AppConfig class:

    @Bean(name = "datasource_tx_manager")
    public DataSourceTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }

Now let’s use this transaction manager to make Alice and Bob less unhappy (since the money is still not transferred).

    private final ApplicationContext context = new AnnotationConfigApplicationContext(AppConfig.class);
    private final DataSource datasource = (DataSource) context.getBean("default_datasource");
    private final PlatformTransactionManager txManager = (DataSourceTransactionManager) context.getBean("datasource_tx_manager");
    

   @Test
    void test_transfer_money_with_platform_transaction_management_using_jdbc_template() {

        try {
            var definition = new DefaultTransactionDefinition();
            var status = txManager.getTransaction(definition);
            var jdbcTemplate = new JdbcTemplate(datasource);
            //withdraw credits from Alice's account
            jdbcTemplate.update("UPDATE bank_user SET balance = 900 WHERE name = 'Alice'");
            //Simulate an exception
            if (true) {
                throw new RuntimeException("Opps! System crashes...");
            }
            jdbcTemplate.update("UPDATE bank_user SET balance = 1100 WHERE name = 'Bob'");

            txManager.commit(status);
        } catch (RuntimeException e) {
            //
        }

      //extract to method to validate the amount of ALice and Bob in the database
        validateAliceAndBobAmount(1000L, 1000L);

    }

Here I use JdbcTempalte instead of creating connections and statements as in previous examples. With transaction manager, the code is less verbose.

The data in the database is still consistent. When the exception occurred, the transaction manager rollbacks the previous operations.

Conclusion

In this post, I showed you how to manage transitions using JDBC and also the transaction manager interface of Spring to achieve data consistency. These methods work however, they are verbose. In the next post, I’m going to show you how you can use Spring AOP to achieve the same result with cleaner code.

The code for this tutorial is available on Github here.

Leave a Comment