Spring Boot: Configure multiple databases

Today we are going to see how to configure a Spring Boot project to use several data sources. There is a lot of information on the internet about this topic, but in most cases, a complete example is not indicated. Here, we will do that and also indicate different configuration options.

For the example we have used the following libraries:

• spring-boot-starter-parent: 2.5.7
• spring-boot-starter-web
• spring-boot-starter-data-jpa
• mysql-connector-java

And to simulate the databases we have used a docker-compose, with three MySQL instances with the same schema and tables but deployed in different ports and with different data.

We will begin by configuring the dataSources. For it, we will have to indicate the properties of the connection in the default configuration file, application.properties. These properties will be unified with the same suffix for each one of the databases that we create.

## Datasource properties
app.datasource.pre.url=jdbc:mysql://localhost:3306/library?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false
app.datasource.pre.username=reader
app.datasource.pre.password=password
app.datasource.pre.driverClassName=com.mysql.cj.jdbc.Driver
app.datasource.pre.schema=library

#hikari configuration
app.datasource.pre.poolConf.maximum-pool-size=30

The next step is to configure this data source programmatically. The configuration must be done in a class with the @Configuration annotation so that the bean is taken into account during the execution. And one of the data sources must be configured as primary through the @Primary annotation. The same must be done with the entity or transaction managers, which we will configure later.

The configuration of the dataSource can be done in different ways. In the following example, we can see the most complete option. On the one hand, we will configure the properties of the dataSource and on the other hand, we will configure those of the connection pool. All this will be done with the help of the @ConfigurationProperties annotation that will allow us to indicate a suffix of the properties we want to use and we have declared before.

The default connection pool is HikariCP. And the set of properties that it allows to configure can be seen in the official documentation, here.

We can make an even more basic configuration, without indicating the configuration of the pool and creating the data source in a single method. But in this case the url property should be renamed to jdbcUrl, which is the property known to HikariCP. In the previous case, the preDataSourceProperties bean is in charge of this conversion.

We can also make the same configuration as the previous one at the Java code level but changing the source of the property file. With the @PropertySource annotation, we can specify another source of the configuration file. This would help us to implement the twelve-factor app by specifying files that are in the environment and not in the classpath.

Now we will configure the entityManager necessary for the operation of our DAOs/Repository objects. And the transactionManager that will allow us to manage transactions automatically. This can be done in three simple steps:

• Create the entityManager bean from a Datasource.
• Create the transactionManager bean from the EntityManager.
• Include the @EnableJpaRepositories annotation at the class level.

When creating the entityManager we must at least configure the associated dataSource. Apart from this, we can give more or less detail to the configuration of the same one. Optionally, we can indicate to which entities the object to be created should be applied and which properties will have the JPA implementation we use, in this case, Hibernate.

The configuration of the package of entities to be taken into account by the entityManager object can be omitted if we indicate in each of the classes to which schema it is associated through the @Table annotation. And the specific properties of Hibernate can be collected with an Environment object, from any of the property files that we have configured.

For the other dataSource, if we want to make a more basic configuration, we could do it in the following way.

The transactionManager configuration will be simpler. The following statement will suffice:

And finally, we have the annotation at class level @EnableJpaRepositories. With this annotation, we can indicate a set of DAOs objects to be configured with the entityManager and transactionManager we want. Performing the configuration automatically.

With this, we would have finished the configuration and we could obtain the information of different BD without problems. On the one hand, we would create the interfaces Repository for each one of the entities of each one of the dataSource.

And on the other hand, we would use them in our controller:

If you want to see all the code you have it, as always, here.