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!