For Spring Boot applications that only work with one database, you can refer to the article Configure database in Spring Boot project using JPA Starter dependency to know how to configure the database for the application. In reality, sometimes we will work with applications that need to connect to 2 or more different databases. So in these cases, how can we connect to all these databases to work with them? I will guide you on how to do this in this tutorial!
First, I will create a new Spring Boot project with Spring Data JPA Starter, MySQL Driver, PostgreSQL Driver, and Lombok as an example:
MySQL Driver and PostgreSQL Driver will help my example application connect to the MySQL database and PostgreSQL database as an example!
As an example and for simplicity, I will define a table in MySQL database containing student information with 2 columns as follows:
|
1 2 3 4 5 |
CREATE TABLE `student` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; |
Entity of table “student” is as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
package com.huongdanjava.springboot.mysql.entity; import jakarta.persistence.Column; import jakarta.persistence.Entity; import jakarta.persistence.GeneratedValue; import jakarta.persistence.Id; import jakarta.persistence.Table; import java.io.Serializable; import lombok.Getter; import lombok.Setter; @Table(name = "student") @Entity @Getter @Setter public class Student implements Serializable { private static final long serialVersionUID = 1L; @Id @GeneratedValue private Long id; @Column private String name; } |
The Repository class for this Student entity is as follows:
|
1 2 3 4 5 6 7 8 |
package com.huongdanjava.springboot.mysql.repository; import com.huongdanjava.springboot.mysql.entity.Student; import org.springframework.data.jpa.repository.JpaRepository; public interface StudentRepository extends JpaRepository<Student, Long> { } |
Similarly, I also defined a table in the PostgreSQL database containing class information with 2 columns as follows:
|
1 2 3 4 |
CREATE TABLE clazz ( id serial PRIMARY KEY, name varchar(50) NOT NULL ) |
Entity of table “clazz” will be as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
package com.huongdanjava.springboot.postgresql.entity; import jakarta.persistence.Column; import jakarta.persistence.Entity; import jakarta.persistence.GeneratedValue; import jakarta.persistence.Id; import jakarta.persistence.Table; import java.io.Serializable; import lombok.Getter; import lombok.Setter; @Table(name = "clazz") @Entity @Getter @Setter public class Clazz implements Serializable { private static final long serialVersionUID = 1L; @Id @GeneratedValue private Long id; @Column private String name; } |
The repository class for the Clazz entity is as follows:
|
1 2 3 4 5 6 7 8 |
package com.huongdanjava.springboot.postgresql.repository; import com.huongdanjava.springboot.postgresql.entity.Clazz; import org.springframework.data.jpa.repository.JpaRepository; public interface ClazzRepository extends JpaRepository<Clazz, Long> { } |
Now, I will configure the information of these two databases.
I will create a new PostgreSQLDatabaseConfiguration class to configure the PostgreSQL database with the @EnableJpaRepositories annotation as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
package com.huongdanjava.springboot.postgresql; import org.springframework.context.annotation.Configuration; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; @Configuration @EnableJpaRepositories( basePackages = {"com.huongdanjava.springboot.postgresql.repository"}, entityManagerFactoryRef = "postgresqlEntityManagerFactory", transactionManagerRef = "postgresqlTransactionManager" ) public class PostgreSQLDatabaseConfiguration { } |
The basePackages attribute will have the value of the package containing all repositories related to the PostgreSQL database, mine here is “com.huongdanjava.springboot.postgresql.repository”, guys! The values of the two attributes entityManagerFactoryRef and transactionManagerRef will point to the two beans that I will define for the PostgreSQL database as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
@Bean @Primary @ConfigurationProperties(prefix = "postgresql.datasource") public DataSource postgresqlDataSource() { return DataSourceBuilder.create().build(); } @Bean public LocalContainerEntityManagerFactoryBean postgresqlEntityManagerFactory( EntityManagerFactoryBuilder builder) { return builder .dataSource(postgresqlDataSource()) .packages("com.huongdanjava.springboot.postgresql.entity") .build(); } @Bean public PlatformTransactionManager postgresqlTransactionManager( EntityManagerFactory postgresqlEntityManagerFactory) { return new JpaTransactionManager(postgresqlEntityManagerFactory); } |
Due to some reasons related to auto-configuration, the EntityManagerFactoryBuilder class in the JpaBaseConfiguration class needs to determine the bean of the Datasource class to initialize itself in the Spring container. You need to specify one of the 2 Datasource beans for their 2 databases as primary by using Spring’s @Primary annotation, as I did above.
Information related to the PostgreSQL database I will configure in the application.properties file as follows:
|
1 2 3 4 |
postgresql.datasource.jdbc-url=jdbc:postgresql://localhost:5432/example postgresql.datasource.driver-class-name=org.postgresql.Driver postgresql.datasource.username=postgres postgresql.datasource.password=123456 |
Please note that we need to declare postgresql.datasource.jdbc-url instead of postgresql.datasource.url because by default Spring Boot will initialize HirakiDataSource. This HirakiDataSource class uses the HikariConfig class to store database configuration information. This HikariConfig class uses the jdbcUrl attribute to store information about the connection string!
Similar to PostgreSQL, for MySQL database, I will also create a new MySQLDatabaseConfiguration class to configure it as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
package com.huongdanjava.springboot.mysql; import jakarta.persistence.EntityManagerFactory; import javax.sql.DataSource; 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.data.jpa.repository.config.EnableJpaRepositories; import org.springframework.orm.jpa.JpaTransactionManager; import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; import org.springframework.transaction.PlatformTransactionManager; @Configuration @EnableJpaRepositories( basePackages = {"com.huongdanjava.springboot.mysql.repository"}, entityManagerFactoryRef = "mysqlEntityManagerFactory", transactionManagerRef = "mysqlTransactionManager" ) public class MySQLDatabaseConfiguration { @Bean @ConfigurationProperties(prefix = "mysql.datasource") public DataSource mysqlDataSource() { return DataSourceBuilder.create().build(); } @Bean public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory( EntityManagerFactoryBuilder builder) { return builder .dataSource(mysqlDataSource()) .packages("com.huongdanjava.springboot.mysql.entity") .build(); } @Bean public PlatformTransactionManager mysqlTransactionManager( EntityManagerFactory mysqlEntityManagerFactory) { return new JpaTransactionManager(mysqlEntityManagerFactory); } } |
Information related to MySQL database, I will also configure in the application.properties file as follows:
|
1 2 3 4 |
mysql.datasource.jdbc-url=jdbc:mysql://localhost:3306/example mysql.datasource.driver-class-name=com.mysql.cj.jdbc.Driver mysql.datasource.username=root mysql.datasource.password=123456 |
Now, I will implement the CommandLineRunner interface for the SpringBootMultipleDatasourcesApplication class to retrieve student and class information from the above two databases as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
package com.huongdanjava.springboot; import com.huongdanjava.springboot.mysql.repository.StudentRepository; import com.huongdanjava.springboot.postgresql.repository.ClazzRepository; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.CommandLineRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication public class SpringBootMultipleDatasourcesApplication implements CommandLineRunner { @Autowired StudentRepository studentRepository; @Autowired ClazzRepository clazzRepository; public static void main(String[] args) { SpringApplication.run(SpringBootMultipleDatasourcesApplication.class, args); } @Override public void run(String... args) throws Exception { System.out.println("Getting all students from MySQL database ..."); studentRepository.findAll().forEach(s -> System.out.println(s.getName())); System.out.println("Getting all classes from PostgreSQL database ..."); clazzRepository.findAll().forEach(c -> System.out.println(c.getName())); } } |
If my MySQL and PostgreSQL databases have the following information about classes and students:
MySQL:
PostgreSQL:
then when you run the example, you will see the following results:
So we have successfully configured the Spring Boot application to connect and work with multiple databases at the same time!
