Spring Boot : Connect to Multiple Databases

Soo Kim
3 min readApr 25, 2024

--

During my project, I wanted to connect to both my local database and another database, and while I did find a plethora of information online, I had still struggled to make the code work because none of them worked perfectly.

In the end, it was a pretty simple configuration that to be honest, I still need to properly understand why. But for anyone else struggling, here’s how I did it. (Full Code)

Project Structure

I created two entities: student and teacher, each of which will connect to a different database — demo and demo_teacher respectively.

This is my application.yml.

server:
port: 9202

spring:
'profiles.active': dev
config:
import: secrets.yml

jpa:
hibernate:
ddl-auto: none
show-sql: true
properties:
hibernate:
'[format_sql]': true
# CamelCase to Underscores naming is not done by default with multiple db
'[physical_naming_strategy]': org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy

Although ‘jpa.properties.hibernate.physical_naming_strategy’ property is not necessary with just one database, I found that when I connected multiple databases, entity/table names (e.g. firstName) did not get converted to underscore naming (first_name), which is why I added the property (instead of using Column(name = “first_name”) everywhere).

Here’s my secret.yml.

# make sure this file is in gitignore!
spring:
'config.activate.on-profile': dev

# make sure to create metadata
datasource:
student:
driver-class-name: org.mariadb.jdbc.Driver
# if you have multiple datasources --> instead of url --> jdbc-url
jdbc-url: jdbc:{your_jdbc_url}/demo
username: root
password: your_password
teacher:
driver-class-name: org.mariadb.jdbc.Driver
jdbc-url: jdbc:{your_jdbc_url}/demo_teacher
username: root
password: your_password

Initially, your datasource properties would look like this:

spring:
datasource:
driver-class-name: dcn
url: url
username: u
password: p

Whichever way you decide to format your database properties, make sure that you have the correct property names, including ‘jdbc-url’ (not url).

Data Source Config

Each of my data sources (the two databases) will have separate configuration files.

StudentDataSourceConfig.java
This will be the primary — or default — datasource. Note that each bean has a Primary annotation.

package com.mooky.multipledatabases.global.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

import jakarta.persistence.EntityManagerFactory;

@EnableJpaRepositories(
basePackages = {"com.mooky.multipledatabases.domain.student"},
entityManagerFactoryRef = "studentEntityManagerFactory",
transactionManagerRef = "studentTransactionManager"
)
@Configuration
public class StudentDataSourceConfig {

@Primary
@Bean
@ConfigurationProperties(prefix = "spring.datasource.student")
DataSource studentDataSource() {
return DataSourceBuilder.create().build();
}

@Primary
@Bean
LocalContainerEntityManagerFactoryBean studentEntityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier("studentDataSource") DataSource dataSource) {
return builder
.dataSource(dataSource)
.packages("com.mooky.multipledatabases.domain")
.persistenceUnit("student")
.build();
}

@Bean
PlatformTransactionManager studentTransactionManager(
@Qualifier("studentEntityManagerFactory") EntityManagerFactory localEntityManagerFactory) {
return new JpaTransactionManager(localEntityManagerFactory);
}

}

‘@EnableJpaRepositories’
(1) declare which packages to attach this data source
(2) link EntityManagerFactory and TransactionManager

studentDataSource()’
(3) create the datasource and and bind it to the correct property

studentEntityManagerFactory()
(4) create JPA EntityManager, which manages and searches for entities
- There are two types of EntityManager: container-managed and application-managed. This method create the a container-managed entity manager.
- attach datasource, packages to scan, and not necessary but as advised by the docs, give separate persistence unit names

studentTransactionManager()’
(5) create a PlatformTransactionManager, which allows you to do transactional work (such as creating, updating, and deleting) on the entities

TeacherDataSourceConfig.java

This is basically the same as the student data source config but with different datasource and names.

@Configuration
@EnableJpaRepositories(
basePackages = { "com.mooky.multipledatabases.domain.teacher" },
entityManagerFactoryRef = "teacherEntityManagerFactory",
transactionManagerRef = "teacherTransactionManager"
)
public class TeacherDataSourceConfig {

@Bean
@ConfigurationProperties(prefix = "spring.datasource.teacher")
DataSource teacherDataSource() {
return DataSourceBuilder.create().build();
}

@Bean
LocalContainerEntityManagerFactoryBean teacherEntityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier("teacherDataSource") DataSource dataSource) {
return builder
.dataSource(dataSource)
.packages("com.mooky.multipledatabases.domain")
.persistenceUnit("teacher")
.build();
}

@Bean
PlatformTransactionManager teacherTransactionManager(
@Qualifier("teacherEntityManagerFactory") EntityManagerFactory teacherEntityManagerFactory) {
return new JpaTransactionManager(teacherEntityManagerFactory);
}
}

Happy coding!

--

--

Soo Kim
Soo Kim

Written by Soo Kim

Flutter & Node.js Full-Stack Developer

No responses yet