Earlier, we have explored various approaches for Joining Unrelated Entities and Mapping the Result to POJO with Spring Data JPA and Hibernate. In this article, we are gonna configure multiple databases, entity managers, transaction managers, and Hikari connection pool in a Spring Boot Application.
Project Structure
This is how our project structure will look like. The model and repository packages are database-specific. So that we can specify the respective package in the database-specific configuration classes for scanning entities and repositories. The entities and repositories of multiple databases should not be put together in the same package.
Dependencies
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.0</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.javachinna</groupId>
<artifactId>multiple-datasources</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>multiple-datasources</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
Configure Data Sources and Connection Pool
Let’s assume that we have 2 different databases namely PRIME and PRODUCT. PRIME is the primary database that is used for user management and PRODUCT is used for product management. Now let’s configure both the databases in our Spring Boot application. Also, we are gonna configure Hikari CP as that is the default connection pool used by Spring Boot 2.x
Configure Application Properties
application.properties
### Prime Database Details
app.datasource.prime.url=jdbc:mysql://localhost:3306/prime?createDatabaseIfNotExist=true
app.datasource.prime.username=root
app.datasource.prime.password=secret
app.datasource.prime.driver-class-name=com.mysql.cj.jdbc.Driver
### Prime Database Connection Pool Details
app.datasource.prime.hikari.idle-timeout=10000
app.datasource.prime.hikari.maximum-pool-size=10
app.datasource.prime.hikari.minimum-idle=5
app.datasource.prime.hikari.pool-name=PrimeHikariPool
### Product Database Details
app.datasource.product.url=jdbc:mysql://localhost:3306/product?createDatabaseIfNotExist=true
app.datasource.product.username=root
app.datasource.product.password=secret
app.datasource.product.driver-class-name=com.mysql.cj.jdbc.Driver
### Product Database Connection Pool Details
app.datasource.product.hikari.idle-timeout=10000
app.datasource.product.hikari.maximum-pool-size=10
app.datasource.product.hikari.minimum-idle=5
app.datasource.product.hikari.pool-name=ProductHikariPool
# Hibernate props
hibernate.hbm2ddl.auto=create
hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
Configure Primary Database
PrimeDataSourceConfiguration.java
@Primary annotation indicates that a bean should be given preference when multiple candidates are qualified to autowire a single-valued dependency. If exactly one ‘primary’ bean exists among the candidates, it will be the autowired value. In simple words, it is used to mark a bean as a default one. This is helpful when there are multiple beans of the same type defined and we wanna inject a bean without specifying its name.
package com.javachinna.config;
import java.util.HashMap;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import com.zaxxer.hikari.HikariDataSource;
@Configuration
@EnableJpaRepositories(basePackages = "com.javachinna.repo.prime", entityManagerFactoryRef = "primeEntityManager", transactionManagerRef = "primeTransactionManager")
public class PrimeDataSourceConfiguration {
@Autowired
Environment env;
@Bean
@Primary
@ConfigurationProperties(prefix = "app.datasource.prime")
public DataSourceProperties primeDataSourceProperties() {
return new DataSourceProperties();
}
@Bean
@Primary
public DataSource primeDataSource() {
return primeDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
}
@Bean
@Primary
public LocalContainerEntityManagerFactoryBean primeEntityManager() {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(primeDataSource());
em.setPackagesToScan("com.javachinna.model.prime");
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
HashMap<String, Object> properties = new HashMap<>();
properties.put("hibernate.hbm2ddl.auto", env.getProperty("hibernate.hbm2ddl.auto"));
properties.put("hibernate.dialect", env.getProperty("hibernate.dialect"));
em.setJpaPropertyMap(properties);
return em;
}
@Bean
@Primary
public PlatformTransactionManager primeTransactionManager() {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(primeEntityManager().getObject());
return transactionManager;
}
}
Notice that we have specified com.javachinna.repo.prime and com.javachinna.model.prime as the package names for scanning JPA repositories and entities of the PRIME database respectively.
Configure Secondary Database
ProductDataSourceConfiguration.java
package com.javachinna.config;
import java.util.HashMap;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import com.zaxxer.hikari.HikariDataSource;
@Configuration
@EnableJpaRepositories(basePackages = "com.javachinna.repo.product", entityManagerFactoryRef = "productEntityManager", transactionManagerRef = "productTransactionManager")
public class ProductDataSourceConfiguration {
@Autowired
Environment env;
@Bean
@ConfigurationProperties(prefix = "app.datasource.product")
public DataSourceProperties productDataSourceProperties() {
return new DataSourceProperties();
}
@Bean
public DataSource productDataSource() {
return productDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
}
@Bean
public LocalContainerEntityManagerFactoryBean productEntityManager() {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(productDataSource());
em.setPackagesToScan("com.javachinna.model.product");
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
HashMap<String, Object> properties = new HashMap<>();
properties.put("hibernate.hbm2ddl.auto", env.getProperty("hibernate.hbm2ddl.auto"));
properties.put("hibernate.dialect", env.getProperty("hibernate.dialect"));
em.setJpaPropertyMap(properties);
return em;
}
@Bean
public PlatformTransactionManager productTransactionManager() {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(productEntityManager().getObject());
return transactionManager;
}
}
Notice that we have specified com.javachinna.repo.product and com.javachinna.model.product as the package names for scanning JPA repositories and entities of the PRODUCT database respectively.
Create JPA Entities and POJO’s
UserInfo.java
This POJO class is used for maping the user info from the native query resultset in prime database.
package com.javachinna.model.prime;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserInfo {
private String id;
private String name;
}
Role.java
This entity maps to the role table in the prime database
package com.javachinna.model.prime;
import java.io.Serializable;
import java.util.Objects;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
/**
* The persistent class for the role database table.
*
*/
@Entity
@Getter
@Setter
@NoArgsConstructor
public class Role implements Serializable {
private static final long serialVersionUID = 1L;
public static final String USER = "USER";
public static final String ADMIN = "ADMIN";
public static final String ROLE_USER = "ROLE_USER";
public static final String ROLE_ADMIN = "ROLE_ADMIN";
@Id
@Column(name = "ROLE_ID")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int roleId;
private String name;
// bi-directional many-to-many association to User
@ManyToMany(mappedBy = "roles")
private Set<User> users;
public Role(String name) {
this.name = name;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((name == null) ? 0 : name.hashCode());
return result;
}
@Override
public boolean equals(final Object obj) {
if (this == obj) {
return true;
}
if (obj == null) {
return false;
}
if (getClass() != obj.getClass()) {
return false;
}
return Objects.equals(name, ((Role) obj).getName());
}
@Override
public String toString() {
final StringBuilder builder = new StringBuilder();
builder.append("Role [name=").append(name).append("]").append("[id=").append(roleId).append("]");
return builder.toString();
}
}
User.java
This entity maps to the user table in the prime database
package com.javachinna.model.prime;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.ColumnResult;
import javax.persistence.ConstructorResult;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.SqlResultSetMapping;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Entity
@NoArgsConstructor
@SqlResultSetMapping(name = "UserInfoMapping", classes = @ConstructorResult(targetClass = UserInfo.class, columns = {@ColumnResult(name = "user_id", type = String.class),
@ColumnResult(name = "username", type = String.class)}))
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "USER_ID")
private Long id;
private String username;
private String password;
// bi-directional many-to-many association to Role
@ManyToMany
@JoinTable(name = "user_role", joinColumns = {@JoinColumn(name = "USER_ID")}, inverseJoinColumns = {@JoinColumn(name = "ROLE_ID")})
private Set<Role> roles;
/**
* @param username
* @param password
*/
public User(String username, String password, Set<Role> roles) {
this.username = username;
this.password = password;
this.roles = roles;
}
}
ProductInfo.java
This POJO class is used for maping the product info from the native SQL query resultset in product database.
package com.javachinna.model.product;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ProductInfo {
private String id;
private String name;
private String price;
}
Product.java
This entity maps to the product table in the product database
package com.javachinna.model.product;
import javax.persistence.ColumnResult;
import javax.persistence.ConstructorResult;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SqlResultSetMapping;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Entity
@NoArgsConstructor
@SqlResultSetMapping(name = "ProductInfoMapping", classes = @ConstructorResult(targetClass = ProductInfo.class, columns = {@ColumnResult(name = "id", type = String.class),
@ColumnResult(name = "name", type = String.class), @ColumnResult(name = "price", type = String.class)}))
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String price;
/**
* @param name
* @param price
*/
public Product(String name, String price) {
this.name = name;
this.price = price;
}
}
Create Spring Data Repository and DAO Classes
We are gonna create User and Role repositories for the User and Role tables respectively in order to demonstrate CRUD operations on more than one table from one database.
RoleRepository.java
package com.javachinna.repo.prime;
import org.springframework.data.jpa.repository.JpaRepository;
import com.javachinna.model.prime.Role;
public interface RoleRepository extends JpaRepository<Role, Long> {
Role findByName(String name);
}
UserRepository.java
package com.javachinna.repo.prime;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.javachinna.model.prime.User;
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
User findByUsername(String userName);
}
UserInfoRepository.java
This is just a DAO class to demonstrate how the primary entity manager can be injected without specifying the name primeEntityManager using the @Qualifier annotation.
package com.javachinna.repo.prime;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.hibernate.procedure.ProcedureOutputs;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.javachinna.model.prime.UserInfo;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@Repository
@Transactional
public class UserInfoRepository {
@PersistenceContext
private EntityManager entityManager;
@SuppressWarnings("unchecked")
public List<UserInfo> getUerInfo() {
List<UserInfo> list = new ArrayList<>();
Query query = entityManager.createNativeQuery("select user_id, username from user", "UserInfoMapping");
try {
// Execute query
list = query.getResultList();
} catch (Exception e) {
log.error("Error while querying the db", e);
} finally {
try {
query.unwrap(ProcedureOutputs.class).release();
} catch (Exception e) {
}
}
return list;
}
}
Notice that we have annotated this class using @Transactional without specifying the transaction manager name. This means the primary transaction manager will be used by default.
ProductRepository.java
This is a JPA respository class used for Product CRUD operations in the secondary database.
@Transactional indicates that a transaction is required to execute the update query. Here we don’t need to specify the transaction manager explicitly since we have already specified productTransactionManager using @EnableJpaRepositories annotation in the ProductDataSourceConfiguration class.
@Modifying annotation indicates a query method should be considered as modifying query as that changes the way it needs to be executed. This annotation is only considered if used on query methods defined through a @Query annotation.
package com.javachinna.repo.product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.javachinna.model.product.Product;
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
@Transactional
@Modifying
@Query("update Product set name=:name where id=:id")
void updateProduct(String name, Long id);
}
ProductInfoRepository.java
This is just a DAO class to demonstrate how the secondary entity manager can be auto wired with @Qualifier annotation and used for querying the secondary database.
package com.javachinna.repo.product;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import org.hibernate.procedure.ProcedureOutputs;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.javachinna.model.product.ProductInfo;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@Repository
public class ProductInfoRepository {
@Autowired
@Qualifier("productEntityManager")
private EntityManager entityManager;
@SuppressWarnings("unchecked")
public List<ProductInfo> getProductInfo() {
List<ProductInfo> list = new ArrayList<>();
Query query = entityManager.createNativeQuery("select id, name, price from product", "ProductInfoMapping");
try {
// Execute query
list = query.getResultList();
} catch (Exception e) {
log.error("Error while querying the db", e);
} finally {
try {
query.unwrap(ProcedureOutputs.class).release();
} catch (Exception e) {
}
}
return list;
}
@Transactional("productTransactionManager")
public void updateProductInfo(String name, Long id) {
Query query = entityManager.createQuery("update Product set name=:name where id=:id");
try {
// Execute query
query.setParameter("name", name);
query.setParameter("id", id);
query.executeUpdate();
} catch (Exception e) {
log.error("Error while querying the db", e);
} finally {
try {
query.unwrap(ProcedureOutputs.class).release();
} catch (Exception e) {
}
}
};
}
Notice that we have annotated the updateProductInfo() method with @Transactional("productTransactionManager") annotation which means productTransactionManager will be used here.
Create Spring Boot Main Application
MultipleDatasourcesApplication.java
package com.javachinna.multipledatasources;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication(scanBasePackages = "com.javachinna")
public class MultipleDatasourcesApplication {
public static void main(String[] args) {
SpringApplication.run(MultipleDatasourcesApplication.class, args);
}
}
Create JUnit Tests
PrimeDataSourceTests.java
@TestInstance(Lifecycle.PER_CLASS) annotation is used to set the test instance lifecycle mode to PER_CLASS which means the test instance state is shared between test methods in a given test class as well as between non-static @BeforeAll and @AfterAll methods in the test class. If we don’t use this annotation, then we can’t use @BeforeAll annotation on a non-static method. If we make the init method static, then we can’t use the autowired repository in it.
package com.javachinna.multipledatasources;
import static org.assertj.core.api.Assertions.assertThat;
import java.util.List;
import java.util.Set;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestInstance;
import org.junit.jupiter.api.TestInstance.Lifecycle;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import com.javachinna.model.prime.Role;
import com.javachinna.model.prime.User;
import com.javachinna.model.prime.UserInfo;
import com.javachinna.repo.prime.RoleRepository;
import com.javachinna.repo.prime.UserInfoRepository;
import com.javachinna.repo.prime.UserRepository;
@SpringBootTest
@TestInstance(Lifecycle.PER_CLASS)
class PrimeDataSourceTests {
@Autowired
private RoleRepository roleRepository;
@Autowired
private UserRepository userRepository;
@Autowired
private UserInfoRepository userInfoRepository;
@BeforeAll
public void init() {
Role userRole = roleRepository.save(new Role(Role.USER));
userRepository.save(new User("test", "secret", Set.of(userRole)));
}
@Test
public void getUserTest() {
List<User> list = userRepository.findAll();
assertThat(list).isNotEmpty();
}
@Test
public void getUserInfoTest() {
List<UserInfo> list = userInfoRepository.getUerInfo();
assertThat(list).isNotEmpty();
assertThat(list.get(0).getName()).isEqualTo("test");
}
}
ProductDataSourceTests.java
package com.javachinna.multipledatasources;
import static org.assertj.core.api.Assertions.assertThat;
import java.util.List;
import java.util.Optional;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestInstance;
import org.junit.jupiter.api.TestInstance.Lifecycle;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import com.javachinna.model.product.Product;
import com.javachinna.model.product.ProductInfo;
import com.javachinna.repo.product.ProductInfoRepository;
import com.javachinna.repo.product.ProductRepository;
@SpringBootTest
@TestInstance(Lifecycle.PER_CLASS)
class ProductDataSourceTests {
@Autowired
private ProductRepository productRepository;
@Autowired
private ProductInfoRepository productInfoRepository;
@BeforeAll
public void init() {
Product product = new Product("phone", "secret");
product = productRepository.save(product);
}
@Test
public void getProductTest() {
List<Product> list = productRepository.findAll();
assertThat(list).isNotEmpty();
}
@Test
public void getProductInfoTest() {
List<ProductInfo> list = productInfoRepository.getProductInfo();
assertThat(list).isNotEmpty();
assertThat(list.get(0).getName()).isEqualTo("phone");
}
@Test
public void updateProductTest() {
productRepository.updateProduct("smartphone", 1L);
Optional<Product> product = productRepository.findById(1L);
assertThat(product.get().getName()).isEqualTo("smartphone");
}
@Test
public void updateProductInfoTest() {
productInfoRepository.updateProductInfo("cellphone", 1L);
Optional<Product> product = productRepository.findById(1L);
assertThat(product.get().getName()).isEqualTo("cellphone");
}
}
Run Junit Tests
Primary Datasource JUnit Tests Results
Secondary Datasource JUnit Test Results
Source Code
https://github.com/JavaChinna/spring-boot-multiple-datasources
Conclusion
That’s all folks. In this article, we have configured multiple data sources with our Spring Boot application.
Thank you for reading.
