Spring Boot with multiple databases – Example

If you want to see how to make Spring Boot working with multiple database and how to control transactions, this page is for you.

This example is a part of  Spring Boot with all examples

Full Code with Spring Boot 1.5.8.RELEASE:

10_spring-boot-two-database2.zip

What you need for run this:

  • Maven 3
  • Java 8
  • Mysql 5.x

What the code will do:

  • Connect to two databases.
  • Give example how to control db transactions across databases.

Warning:

You should know how to connect a single database, and control db transaction with Spring Boot , see Spring Boot with Database, Connection, Transaction, JDBI – Example.

This page will tell the differences when you want to connection multiple databases (from a single database).

I recommend you to read this page Spring Boot : Try Connection and Transaction . It will give you more detail about how connection and transaction behave in Spring Boot with runnable examples.

Explain:

This is the file structure:

From application.properties :

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

database2.datasource.url=jdbc:mysql://localhost/testdb2
database2.datasource.username=root
database2.datasource.password=root
database2.datasource.driver-class-name=com.mysql.jdbc.Driver

Note that there are two prefix here “database1” and “database2”. Each of them connect to different database.

From DataSourceConfiguration.java :

package com.surasint.example.db;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfiguration {

    @Bean(name = "datasource1")
    @ConfigurationProperties("database1.datasource")
    @Primary
    public DataSource dataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "datasource2")
    @ConfigurationProperties("database2.datasource")
    public DataSource dataSource2(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name="tm1")
    @Autowired
    @Primary
    DataSourceTransactionManager tm1(@Qualifier ("datasource1") DataSource datasource) {
        DataSourceTransactionManager txm  = new DataSourceTransactionManager(datasource);
        return txm;
    }

    @Bean(name="tm2")
    @Autowired
    DataSourceTransactionManager tm2(@Qualifier ("datasource2") DataSource datasource) {
        DataSourceTransactionManager txm  = new DataSourceTransactionManager(datasource);
        return txm;
    }
}

Important points:

  • Line 17 and  24 @Bean(name=”datasourceX”) : To define that each datasource from each method can be referred with @Qualifier(“datasourceX”) in DAO classes
  • Line 18 and 25 @ConfigurationProperties(“databaseX.datasource”) : To define that each method will get properties (in application.properties) with different prefix to create Datasource
  • Line 19 @Primary : To define that this datasource is the default datasource.
  • Line 30 and 38 @Bean(name=”tmX”) : To define that each DataSourceTransactionManager from each method can be referred as @Transactional(“tm2”) in UserService2 class.
  • Line 32 @Primary: To define that this DataSourceTransactionManager is the default. So we can use @Transactional instead of @Transactional(“tm1”)
  • Line 33 and 40 @Qualifier(“datasourceX”) : To define which datasource is linked each DataSourceTransactionManager .

Sum up the relations:

  • “database1.datasource” prefix  -> “datasource1” -> “tm1”
  • “datasource1” will be used with DataSourceTransactionManager  “tm1”
  • “datasource1” will be used in UserDao1 as @Qualifier(“datasource1”)
  • DataSourceTransactionManager  “tm1”  will be used in UserService1 as @Transactional
  • DataSourceTransactionManager  “tm2”  will be used in UserService2 as @Transactional(“tm2”)
  • So, @Transactional is the annotation to control db transaction of the first database and @Transactional(“tm2”) is the annotaton to control db transaction of the second database

In UserDao.java, you will see this :

    @Qualifier("datasource1")
    @Autowired
    private DataSource dataSource;
  • LIne 1 @Qualifier(“datasource1”) : To define which datasource is linked to.

Similar as UserDao2.java , it is “datasource2” :

    @Qualifier("datasource2")
    @Autowired
    private DataSource dataSource;

In UserService1.java :

package com.surasint.example.service;

import com.surasint.example.db.UserBean;
import com.surasint.example.db.UserDao1;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

@Component
public class UserService1 {

    @Autowired
    private UserDao1 userDao;

    @Autowired
    private UserService2 userService2;

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

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

    @Transactional
    public String insertBothOK(){
        UserBean test = new UserBean();
        test.setUsername("username"+new Date().getTime());
        String result ="";
        result = "" + userDao.insert(test);
        result = result + " and "+ userService2.insertOK();

        return result;
    }

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

        //this code will never be reached
        throw new RuntimeException("Test error while trying to insert both database");
    }
}

Important points:

  • Line 15 – 16 :
    @Autowired
    private UserDao userDao;

It is using UserDao1.

  • Line 17 – 18 :
    @Autowired
    private UserService2 userService2;

It is also using UserService2, which is used in the insertBotNotOK() method. I will explain later why we should use UserService2 instead of UserDao2.

  • Line 21 , 26 , 33 , 44 : @Transactional , Now that we can use @Transactional instead of @Transactional(“tm1”). That is because we declare @Primary in DataSourceConfiguration.java, so Spring know which DataSourceTransactionManager is for @Transactional
  • Line 44 – 52 :
    @Transactional()
    public void insertBothNotOK(){
        UserBean test = new UserBean();
        test.setUsername("username"+new Date().getTime());
        userDao.insert(test);
        userService2.insertNotOK();
 
        //this code will never be reached
        throw new RuntimeException("Test error while trying to insert both database");
    }

In this method , we want to test rollback. userService2.insertNotOK() will always throw an exception. Note that we insert to the second database via UserService2, not UserDao2. The reason is that we have to use @Transactional(“tm2”) in UserService2 to control db transaction for the second database.

If we use UserDao2 here, it will rollback only the first database, but not the second database because there is not transaction control is declared.

In UserService2.java :

package com.surasint.example.service;

import com.surasint.example.db.UserBean;
import com.surasint.example.db.UserDao2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

@Component
public class UserService2 {

    @Autowired
    private UserDao2 userDao2;

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

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

    @Transactional("tm2")
    public void insertNotOK(){
        UserBean test = new UserBean();
        test.setUsername("username"+new Date().getTime());
        userDao2.insert(test);
        throw new RuntimeException("Test error while trying to insert both database");
    }
}

Important points:

  • Line 15 – 16 :
    @Autowired
    private UserDao2 userDao2;

It is using UserDao2

  • Line 18 , 23 , 30 :
    @Transactional("tm2")

It is using DataSourceTransactionManager “tm2” which is declared in DataSourceConfiguration.java.

In TestRestController.java :

package com.surasint.example.web.api;

import com.surasint.example.db.UserBean;
import com.surasint.example.service.UserService1;
import com.surasint.example.service.UserService2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class TestRestController {

    private static int i;

    @Autowired
    UserService1 userService1;

    @Autowired
    UserService2 userService2;

    @GetMapping("/api/user1/list")
    public List<UserBean> list() {
        return userService1.list();
    }

    @GetMapping("/api/user1/insertOK")
    public Integer insertOK() {
        return userService1.insertOK();
    }


    @GetMapping("/api/user2/list")
    public List<UserBean> list2() {
        return userService2.list();
    }

    @GetMapping("/api/user2/insertOK")
    public Integer insertOK2() {
        return userService2.insertOK();
    }

    @GetMapping("/api/user/insertBothOK")
    public String insertBothOK() {
        return userService1.insertBothOK();
    }

    @GetMapping("/api/user/insertBothNotOK")
    public String insertBothNotOK() {
        userService1.insertBothNotOK();
        return "never happen";
    }

}

There is nothing special, each mapping just call method in UserService1 or UserService2.

Try:

create two databases named “testdb” and “testdb2” and run dbscript.sql to those databases in MySql:

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

Change application.properties to your MySql username and password:

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

database2.datasource.url=jdbc:mysql://localhost/testdb2
database2.datasource.username=root
database2.datasource.password=root
database2.datasource.driver-class-name=com.mysql.jdbc.Driver

run this in console:

mvn clean package

Then run this to start:

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

Open a browswer and try to go to this url:

http://localhost:8080/api/user1/insertOK

It will insert a row in the database1. Then You will see something like this :

You can list it by calling this:

http://localhost:8080/api/user1/list

Then you will see this :

You can insert to and list from the database2 by calling this :

http://localhost:8080/api/user2/insertOK
http://localhost:8080/api/user2/list

Now try to insert to both databases:

http://localhost:8080/api/user/insertBothOK

You can list to show the result again.

Now the most interesting part is rollback. You can test by calling this url:

http://localhost:8080/api/user/insertBothNotOK

If you try to list , you will not see any new row in both databases.