Many times it is a practical requirement to have multiple databases for a single application. These databases could be at different locations on the cloud and different entities in your application may be dealing with these databases.
Multi-tenancy is a great requirement when multiple data sources are needed. Many tenants may insist on having their own databases. Here we present how we can configure the spring application to interact with multiple databases.
The first step is to look at our
application.properties file. We have a list of properties defined for the default dataSource which we will need to replicate for our second database. Let's assume we are going to use two data sources, the first one is called the
user and the second one is called
other.
As we can see above, we have replicated all the data source properties and given it a new prefix,
other. Now, these two databases could have completely independent settings. They could point to totally different databases. Each of these properties could be configured to completely different settings. Here we have just changed the name of the
database,
username,
password.
To make this work. we will have to split the repositories and the entity objects for each of the data sources. Here we create the following hierarchy of packages for each of the data sources.
src/main/java
- in.springframework.blog.tutorials
- user
- domain
- repository
- other
- domain
- repository
As we can see for each of the data sources, we have a domain package that would contain the entities and a repository package that would contain the repository class. This is needed so that each of the entity managers only searches for its own classes.
Now we need to define a configuration of each of the data sources that we have defined. The first data source will contain
the user table and will also be the primary data source.
As we can see in the class above, it uses all the properties prefixed with
spring.datasource and scans directories related to the user data source. Now let's look at the configuration for
other data source.
The things to note in the
other data source is that the
@Primary annotation doesn't exist because we can have only one set of primary beans of a type. Also, the directories to be searched are for the
other data source's
domain and
repository objects.
At this time we also move the old
User and
UserRepository classes to their respective subdirectories. We also create
Other and
OtherRepository classes in their respective subdirectories. We also change
CrudRepository to
JpaRepository in each of the repository classes.
Now, our application is set up to use two different data sources and we can verify that by running the application. Since we have set
ddl-auto property to
update, it should create a new schedule when the application is run.
$ mysql -u tutorial -ptutorial123 tutorial
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 115
Server version: 8.0.12 Homebrew
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
+--------------------+
| Tables_in_tutorial |
+--------------------+
| hibernate_sequence |
| user |
+--------------------+
2 rows in set (0.00 sec)
mysql> desc user;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id | bigint(20) | NO | PRI | NULL | |
| email | varchar(255) | YES | UNI | NULL | |
| fullname | varchar(255) | YES | | NULL | |
| password | varchar(255) | YES | | NULL | |
| username | varchar(255) | YES | UNI | NULL | |
| auth_token | varchar(255) | YES | UNI | NULL | |
| expiry | datetime | YES | | NULL | |
| mask | bigint(20) | NO | | NULL | |
| authToken | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
$ mysql -u other -pother123 other
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 116
Server version: 8.0.12 Homebrew
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
+--------------------+
| Tables_in_other |
+--------------------+
| hibernate_sequence |
| other |
+--------------------+
2 rows in set (0.00 sec)
mysql> desc other
-> ;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | bigint(20) | NO | PRI | NULL | |
| otherData | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
The source code for this tutorial is available at git repository as
v1.2.
