Spring Boot : Understand Connection and Transaction – Surasint : examples on internet that really work

If you want to know how transaction and connection work in Spring Boot by “trying” with examples, this page is for you.

This example is related to Spring Boot with all examples. However, code in here is not in that link.

Full Code with Spring Boot 1.5.8.RELEASE:

8_spring-boot-test-db-transaction.zip

What you need for run this:

Maven 3
Java 8
Mysql 5.x

What the code will do:

The code will show you multiple examples of how to use connection and transaction in Spring.
This page will explain those examples case by case.

Things you should know before start:

First, you should read this Spring Boot with Database, Transaction, JDBI example before. It will explain you about @Transactional and code structure in basic.

Second, there is a class named UniqueObjectIdHolder.java. This class looks like this:

package com.surasint.example.util;

import java.util.IdentityHashMap;
import java.util.Map;

public class UniqueObjectIdHolder {

    private static Map<Object, Integer> map = new IdentityHashMap<>();
    private static int count =0;

    public static int getId(Object object){
        if (!map.containsKey(object)){
            count++;
            map.put(object,count);
        }
        return map.get(object);
    }
}

package com.surasint.example.util;

import java.util.IdentityHashMap;

import java.util.Map;

public class UniqueObjectIdHolder {

private static Map<Object, Integer> map = new IdentityHashMap<>();

private static int count =0;

public static int getId(Object object){

if (!map.containsKey(object)){

count++;

map.put(object,count);

}

return map.get(object);

}

This class will just return an unique ID (just a number) of each object. It will be used to check which DB connection is using at a moment.

For example, UserDao.insert() has this code:

    public List<UserBean> list(){
        Connection conn =  DataSourceUtils.getConnection(dataSource);
        System.out.println("userDao:list:datasource:"+ UniqueObjectIdHolder.getId(dataSource));
        System.out.println("userDao:list:connection:"+ UniqueObjectIdHolder.getId(conn));
        Handle handle = DBI.open(conn);
        UserSQLs userQLs = handle.attach(UserSQLs.class);
        return userQLs.list();
    }

public List<UserBean> list(){

Connection conn = DataSourceUtils.getConnection(dataSource);

System.out.println("userDao:list:datasource:"+ UniqueObjectIdHolder.getId(dataSource));

System.out.println("userDao:list:connection:"+ UniqueObjectIdHolder.getId(conn));

Handle handle = DBI.open(conn);

UserSQLs userQLs = handle.attach(UserSQLs.class);

return userQLs.list();

}

Line 3 and 4 show how to use UniqueObjectIdHolder. They are just printing unique IDs of “dataSource ” and “conn” object.

Prepare to run:

create a database named “testdb” and run dbscript.sql to the database in MySql:

CREATE TABLE users (
  id               INT PRIMARY KEY AUTO_INCREMENT,
  username             VARCHAR(100) NOT NULL
);

CREATE TABLE users (

id INT PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(100) NOT NULL

);

change application.properties to your MySql username and password:

#Database
spring.datasource.url=jdbc:mysql://localhost/testdb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

#Database

spring.datasource.url=jdbc:mysql://localhost/testdb

spring.datasource.username=root

spring.datasource.password=root

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

run this in console:

mvn clean package

1	mvn clean package

Then run this to start:

mvn clean spring-boot:run

1	mvn clean spring-boot:run

You should see something like this:

 .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.5.8.RELEASE)

.....
.....
2017-12-12 22:03:04.243  INFO 260 --- [           main] s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.context.embedded.Annotation
ConfigEmbeddedWebApplicationContext@7595f8d7: startup date [Tue Dec 12 22:03:01 CET 2017]; root of context hierarchy

. ____ _ __ _ _

/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \

( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \

\\/ ___)| |_)| | | | | || (_| | ) ) ) )

' |____| .__|_| |_|_| |_\__, | / / / /

=========|_|==============|___/=/_/_/_/

:: Spring Boot :: (v1.5.8.RELEASE)

.....

2017-12-12 22:03:04.243 INFO 260 --- [ main] s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.context.embedded.Annotation

ConfigEmbeddedWebApplicationContext@7595f8d7: startup date [Tue Dec 12 22:03:01 CET 2017]; root of context hierarchy

Open a browswer and try to go to this url:

http://localhost:8080/api/user/list

1	http://localhost:8080/api/user/list

You should see this:

If you see this, that means it is working fine.

TIP: before trying with each case, you should clean all rows in database by call this:

http://localhost:8080/api/user/deleteAll

1	http://localhost:8080/api/user/deleteAll

And check the row by calling “list” again:

http://localhost:8080/api/user/list

1	http://localhost:8080/api/user/list

You will see nothing.

Case 1: Normal Insert

Let start with very basic one. You want to insert a row in database.

Try to call this url:

http://localhost:8080/api/user/insertOK

1	http://localhost:8080/api/user/insertOK

You will see a number, like this:

The code has nothing complex. It just calls TestRestController. insertOK() -> UserService.InsertOK() -> UserDao.insert(). The code looks like:

TestRestController:

    @GetMapping("/api/user/insertOK")
    public Integer insertOK() {
        return userService.insertOK();
    }

@GetMapping("/api/user/insertOK")

public Integer insertOK() {

return userService.insertOK();

}

UserService:

    @Transactional
    public Integer insertOK(){
        UserBean test = new UserBean();
        test.setUsername("username"+new Date().getTime());
        return userDao.insert(test);
    }

@Transactional

public Integer insertOK(){

UserBean test = new UserBean();

test.setUsername("username"+new Date().getTime());

return userDao.insert(test);

}

UserDao:

    public Integer insert(UserBean item){
        Connection conn =  DataSourceUtils.getConnection(dataSource);
        System.out.println("userDao:insert:datasource:"+ UniqueObjectIdHolder.getId(dataSource));
        System.out.println("userDao:insertLconnection:"+ UniqueObjectIdHolder.getId(conn));
        Handle handle = DBI.open(conn);
        UserSQLs userSQLs = handle.attach(UserSQLs.class);
        return userSQLs.insert(item);
    }

public Integer insert(UserBean item){

Connection conn = DataSourceUtils.getConnection(dataSource);

System.out.println("userDao:insert:datasource:"+ UniqueObjectIdHolder.getId(dataSource));

System.out.println("userDao:insertLconnection:"+ UniqueObjectIdHolder.getId(conn));

Handle handle = DBI.open(conn);

UserSQLs userSQLs = handle.attach(UserSQLs.class);

return userSQLs.insert(item);

}

You will see these lines in the log or console:

userDao:insert:datasource:1
userDao:insertLconnection:5

1 2	userDao:insert:datasource:1 userDao:insertLconnection:5

Try to call the url again, then you will see other two new lines:

userDao:insert:datasource:1
userDao:insertLconnection:5
userDao:insert:datasource:1
userDao:insertLconnection:6

userDao:insert:datasource:1

userDao:insertLconnection:5

userDao:insert:datasource:1

userDao:insertLconnection:6

Note that “You get the same datasource but different connections” . In other words, Spring Boot gives you different connection in each request.

You can try to list the row by calling this url:

http://localhost:8080/api/user/list

1	http://localhost:8080/api/user/list

You will see this:

Also note that, in the log, you will get different connection (see line 5 and 6):

userDao:insert:datasource:1
userDao:insertLconnection:5
userDao:insert:datasource:1
userDao:insertLconnection:6
userDao:list:datasource:1
userDao:list:connection:7

userDao:insert:datasource:1

userDao:insertLconnection:5

userDao:insert:datasource:1

userDao:insertLconnection:6

userDao:list:datasource:1

userDao:list:connection:7

The most important is that the connection is closed by Spring Boot, you do not have to do anything. You can check by checking processes that connect to database (I suggest HeidiSQL, I have been using this nice lightweight MySql Client for 10 years. Please donate to the developer if you please).

Case 2: Exception and Rollback

Let say, you want to make sure that if there is something wrong, any insert/delete/update should be rollbacked.

We will call the code in UserService which looks like this:

    @Transactional
    public void insertAndFail(){
        UserBean test = new UserBean();
        test.setUsername("username"+new Date().getTime());
        userDao.insert(test);

        throw new RuntimeException("test fail");
    }

@Transactional

public void insertAndFail(){

UserBean test = new UserBean();

test.setUsername("username"+new Date().getTime());

userDao.insert(test);

throw new RuntimeException("test fail");

}

This method will just insert a row in database but then it will throw a RuntimeException.

Try to run insertAndFail() by calling:

http://localhost:8080/api/user/insertNotOK

1	http://localhost:8080/api/user/insertNotOK

Then you will see this:

Try to check if the row is in database:

http://localhost:8080/api/user/list

1	http://localhost:8080/api/user/list

You will see nothing.

So, Spring Boot takes care rollback for you. And again the connection is closed by Spring Boot, you do not have to do anything.

But BEWARE it will take care only “unchecked exception” such as RuntimeException. What happen if it is checked exception? It will not rollback, see the detail in the next case.

Anyways, you may ask what “checked exception” and “unchecked exception”. Here quick explanation:

checked exception is the exception that you declare in method signature, for example:

public String myMethod() throws Exception {

1	public String myMethod() throws Exception {

unchecked exception is the exception that you do not (do not have to) declare in method signature, for example RuntimeException or its subclass.

Case 3 : Exception but NOT rollback

If you have checked exception like this;

    @Transactional
    public void insertAndFailButNotRollback() throws Exception {
        UserBean test = new UserBean();
        test.setUsername("username"+new Date().getTime());
        userDao.insert(test);

        throw new Exception("test fail");
    }

@Transactional

public void insertAndFailButNotRollback() throws Exception {

UserBean test = new UserBean();

test.setUsername("username"+new Date().getTime());

userDao.insert(test);

throw new Exception("test fail");

}

The inserted row in line 5 will not be rollbacked.

You can try by calling:

http://localhost:8080/api/user/insertNotOKNotRollback

1	http://localhost:8080/api/user/insertNotOKNotRollback

You will see this:

But the transaction is not rollbacked. You can check query in the database or by just calling this url:

http://localhost:8080/api/user/list

1	http://localhost:8080/api/user/list

You may ask “Even it does not rollback, will the Spring boot close the connection for me?” Yes it will. You can check by checking processes that connect to database. They will not increase.

Then , how to make Spring Boot rollback the checked exception?

You can just change @Transactional to @Transactional(rollbackFor = Exception.class). Like this:

    @Transactional(rollbackFor = Exception.class)
    public void insertAndFailAndRollback() throws Exception {
        UserBean test = new UserBean();
        test.setUsername("username"+new Date().getTime());
        userDao.insert(test);

        throw new Exception("test fail");
    }

@Transactional(rollbackFor = Exception.class)

public void insertAndFailAndRollback() throws Exception {

UserBean test = new UserBean();

test.setUsername("username"+new Date().getTime());

userDao.insert(test);

throw new Exception("test fail");

}

Try to call this url:

http://localhost:8080/api/user/insertNotOKAndRollback

1	http://localhost:8080/api/user/insertNotOKAndRollback

You will see the error page and transaction also rollback.

Case 4 : Is it the same transaction ?

We know from the Case 1 that you will get different connection from the different request, but you may doubt that:

Will I get the same connection across method call?
Will I get the same connection across different DAO classes?

To answer the first question, in UserService, I have this method:

    @Transactional
    public List<UserBean> listTwoTimes(){
        userDao.list();
        return userDao.list();
    }

@Transactional

public List<UserBean> listTwoTimes(){

userDao.list();

return userDao.list();

}

It will just call the same DAO object twice. The purpose of this is just to see if different method call still give use the same connection.

You can try by calling this url:

http://localhost:8080/api/user/listTwoTimes

1	http://localhost:8080/api/user/listTwoTimes

You will see from the log that you will get the same ID of connection:

userDao:list:datasource:1
userDao:list:connection:11
userDao:list:datasource:1
userDao:list:connection:11

userDao:list:datasource:1

userDao:list:connection:11

userDao:list:datasource:1

userDao:list:connection:11

To answer the second question, in UserService, I have this method:

    @Transactional
    public List<UserBean> listTwoDaos(){
        userDao.list();
        return userDao2.list();
    }

@Transactional

public List<UserBean> listTwoDaos(){

userDao.list();

return userDao2.list();

}

It will call the TWO DAO objects. The purpose of this is to see if different object call still give use the same connection.

You can call the method by calling this url:

http://localhost:8080/api/user/listTwoDaos

1	http://localhost:8080/api/user/listTwoDaos

You will see from the log that you will get the same ID of connection:

userDao:list:datasource:1
userDao:list:connection:12
userDao:list:datasource:1
userDao:list:connection:12

userDao:list:datasource:1

userDao:list:connection:12

userDao:list:datasource:1

userDao:list:connection:12

What you just know:

You should get some basic idea about how connection and transaction behave in Spring Boot.

You should see how powerful Spring Boot helps us about connection and transaction. “Great power comes with great responsibility” so before using it, you must understand it.

These cases should cover 90% (I guess) of basic needs of web applications with database.

Believe it or not, many developers do not know such this basic things and create a production-like applications. Database transaction is too important to just let some framework handles without understanding.