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 :

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

From DataSourceConfiguration.java :

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 :

  • LIne 1 @Qualifier(“datasource1”) : To define which datasource is linked to.

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

In UserService1.java :

Important points:

  • Line 16 – 17 :

It is using UserDao1.

  • Line 18 – 19 :

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 :

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 :

Important points:

  • Line 15 – 16 :

It is using UserDao2

  • Line 18 , 23 , 30 :

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

In TestRestController.java :

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:

Change application.properties to your MySql username and password:

run this in console:

Then run this to start:

You should see something like this:

Open a browswer and try to go to this url:

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

You can list it by calling this:

Then you will see this :

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

Now try to insert to both databases:

You can list to show the result again.

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

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