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.
