Use Multiple Databases with Spring Data JPA in Spring Boot

In Spring Boot, it is possible to configure and use multiple databases with Spring Data JPA. Here are the steps to follow:

1. Define the database configurations in your application.properties or application.yml file. For example:

spring.datasource.url=jdbc:mysql://localhost:3306/database_name1
spring.datasource.username=username1
spring.datasource.password=password1

spring.second-datasource.url=jdbc:mysql://localhost:3306/database_name2
spring.second-datasource.username=username2
spring.second-datasource.password=password2

spring.third-datasource.url=jdbc:mysql://localhost:3306/database_name3
spring.third-datasource.username=username3
spring.third-datasource.password=password3   

The second and third DataSources should be configured in a manner similar to that of the first DataSource, but with a different property namespace.

2. Create three separate DataSource beans for each database configuration:

package com.example.config;

import javax.sql.DataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

@Configuration
public class DataSourceConfg {

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

	@Bean(name = "db2DataSource")
	@ConfigurationProperties(prefix = "spring.second-datasource")
	public DataSource db2DataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean(name = "db3DataSource")
	@ConfigurationProperties(prefix = "spring.third-datasource")
	public DataSource db3DataSource() {
		return DataSourceBuilder.create().build();
	}

}

3. Create EntityManagerFactory beans for each DataSource using the LocalContainerEntityManagerFactoryBean class:

package com.example.config;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
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;

@Configuration
@EnableJpaRepositories(basePackages = "com.example.config", 
	entityManagerFactoryRef = "db1EntityManagerFactory", 
	transactionManagerRef = "db1TransactionManager")
public class Db1Config {

	@Primary
	@Bean(name = "db1EntityManagerFactory")
	public LocalContainerEntityManagerFactoryBean db1EntityManagerFactory(EntityManagerFactoryBuilder builder,
			@Qualifier("db1DataSource") DataSource dataSource) {
		return builder.dataSource(dataSource).packages("com.example.config").build();
	}

	@Primary
	@Bean(name = "db1TransactionManager")
	public PlatformTransactionManager db1TransactionManager(
			@Qualifier("db1EntityManagerFactory") EntityManagerFactory entityManagerFactory) {
		return new JpaTransactionManager(entityManagerFactory);
	}

}

package com.example.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
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;

@Configuration
@EnableJpaRepositories(basePackages = "com.example.config", 
	entityManagerFactoryRef = "db2EntityManagerFactory", 
	transactionManagerRef = "db2TransactionManager")
public class Db2Config {
	@Bean(name = "db2EntityManagerFactory")
	public LocalContainerEntityManagerFactoryBean db2EntityManagerFactory(EntityManagerFactoryBuilder builder,
			@Qualifier("db2DataSource") DataSource dataSource) {
		return builder.dataSource(dataSource).packages("com.example.config").build();
	}

	@Bean(name = "db2TransactionManager")
	public PlatformTransactionManager db2TransactionManager(
			@Qualifier("db2EntityManagerFactory") EntityManagerFactory entityManagerFactory) {
		return new JpaTransactionManager(entityManagerFactory);
	}

}

package com.example.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
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;

@Configuration
@EnableJpaRepositories(basePackages = "com.example.config", 
	entityManagerFactoryRef = "db2EntityManagerFactory", 
	transactionManagerRef = "db2TransactionManager")
public class Db3Config {
	@Bean(name = "db2EntityManagerFactory")
	public LocalContainerEntityManagerFactoryBean db2EntityManagerFactory(EntityManagerFactoryBuilder builder,
			@Qualifier("db2DataSource") DataSource dataSource) {
		return builder.dataSource(dataSource).packages("com.example.config").build();
	}

	@Bean(name = "db2TransactionManager")
	public PlatformTransactionManager db2TransactionManager(
			@Qualifier("db2EntityManagerFactory") EntityManagerFactory entityManagerFactory) {
		return new JpaTransactionManager(entityManagerFactory);
	}

}

4. Annotate your entities with the appropriate @Entity annotations. For example:

package com.example.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "user")
public class User {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;

	// other fields here
}

package com.example.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "customer")
public class Customer {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	
	//other fields here
}

package com.example.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "student")
public class Student {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	
	//other fields here
}

5. Create separate repositories for each database. For example:

package com.example.repositories;

import com.example.entity.User;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

}

package com.example.repositories;

import com.example.entity.Student;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {

}

package com.example.repositories;

import com.example.entity.Customer;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long> {

}

6. You can now inject the appropriate repository for each database into your services:

package com.example.service;

import com.example.repositories.CustomerRepository;
import com.example.repositories.StudentRepository;
import com.example.repositories.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserService {

	@Autowired
	private UserRepository userRepository;
	
	@Autowired
	private StudentRepository studentRepository;
	
	@Autowired
	private CustomerRepository customerRepository;
	
}