Spring Boot with Spring Data JPA provides quick and easy way to configure single datasource and accessing database using repository. But have you wondered if you have to get data from multiple databases or even different DBMS?
Lucky for us spring provides a solution for this also.
Add additional data source configuration in your application.properties
prd.datasource.jdbcUrl=jdbc:mysql://localhost:3306/productdb
prd.datasource.username=root
prd.datasource.password=root
usr.datasource.jdbcUrl=jdbc:mysql://localhost:3306/userdb
usr.datasource.username=root
usr.datasource.password=root
Create Java package for each data source
(For this example we will use two database User and Product.)
Creating Database configs
LocalContainerEntityManagerFactoryBean
FactoryBean that creates a JPA EntityManagerFactory according to JPA's standard container bootstrap contract.
HibernateJpaVendorAdapter
Exposes Hibernate's persistence provider and Hibernate's Session as extended EntityManager interface, and adapts AbstractJpaVendorAdapter's common configuration settings.
HikariConfig
HikariConfig is the configuration class used to initialize a data source. It comes with four well-known, must-use parameters: username, password, jdbcUrl, and dataSourceClassName.
PlatformTransactionManager
The PlatformTransactionManager helps the template to create, commit or roll back transactions. When using Spring Boot, an appropriate bean of type PlatformTransactionManager will be automatically registered, so we just need to simply inject it.
User Database Config
@Configuration
@EnableJpaRepositories(
basePackages = "com.shail.multidb.usr",
entityManagerFactoryRef = "userEntityManager",
transactionManagerRef = "userTransactionManager"
)
public class UserConfig {
@Autowired
private Environment env;
@Bean
public LocalContainerEntityManagerFactoryBean userEntityManager() {
final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(userDataSource());
em.setPackagesToScan("com.shail.multidb.usr");
final HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
final HashMap<String, Object> properties = new HashMap<>();
properties.put("hibernate.hbm2ddl.auto", env.getProperty("hibernate.ddl-auto"));
properties.put("hibernate.dialect", env.getProperty("hibernate.dialect"));
properties.put("hibernate.show_sql", env.getProperty("hibernate.show_sql"));
properties.put("hibernate.format_sql", env.getProperty("hibernate.format_sql"));
em.setJpaPropertyMap(properties);
return em;
}
@Bean
public DataSource userDataSource() {
HikariConfig config = new HikariConfig();
config.setDriverClassName(Preconditions.checkNotNull(env.getProperty("mysql.datasource.driver_class_name")));
config.setJdbcUrl(env.getProperty("usr.datasource.jdbcUrl"));
config.setUsername(env.getProperty("usr.datasource.username"));
config.setPassword(env.getProperty("usr.datasource.password"));
config.setMaximumPoolSize(Integer.parseInt(env.getProperty("hikariCP.datasource.maximum_pool_size")));
config.setMaxLifetime(Long.parseLong(env.getProperty("hikariCP.datasource.max_lifetime")));
config.setPoolName(env.getProperty("hikariCP.datasource.pool_name"));
config.setConnectionTimeout(Long.parseLong(env.getProperty("hikariCP.datasource.connection_timeout")));
config.setMinimumIdle(Integer.parseInt(env.getProperty("hikariCP.datasource.minimum_idle")));
config.setIdleTimeout(Long.parseLong(env.getProperty("hikariCP.datasource.idle_timeout")));
return new HikariDataSource(config);
}
@Bean
public PlatformTransactionManager userTransactionManager() {
final JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(userEntityManager().getObject());
return transactionManager;
}
}
Product Database config
@Configuration
@EnableJpaRepositories(
basePackages = "com.shail.multidb.prd",
entityManagerFactoryRef = "productEntityManager",
transactionManagerRef = "productTransactionManager"
)
public class ProductConfig {
@Autowired
private Environment env;
@Primary
@Bean
public LocalContainerEntityManagerFactoryBean productEntityManager() {
final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(productDataSource());
em.setPackagesToScan("com.shail.multidb.prd");
final HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
final HashMap<String, Object> properties = new HashMap<String, Object>();
properties.put("hibernate.hbm2ddl.auto", env.getProperty("hibernate.ddl-auto"));
properties.put("hibernate.dialect", env.getProperty("hibernate.dialect"));
properties.put("hibernate.show_sql", env.getProperty("hibernate.show_sql"));
properties.put("hibernate.format_sql", env.getProperty("hibernate.format_sql"));
em.setJpaPropertyMap(properties);
return em;
}
@Primary
@Bean
public DataSource productDataSource() {
HikariConfig config = new HikariConfig();
config.setDriverClassName(Preconditions.checkNotNull(env.getProperty("mysql.datasource.driver_class_name")));
config.setJdbcUrl(env.getProperty("prd.datasource.jdbcUrl"));
config.setUsername(env.getProperty("prd.datasource.username"));
config.setPassword(env.getProperty("prd.datasource.password"));
config.setMaximumPoolSize(Integer.parseInt(env.getProperty("hikariCP.datasource.maximum_pool_size")));
config.setMaxLifetime(Long.parseLong(env.getProperty("hikariCP.datasource.max_lifetime")));
config.setPoolName(env.getProperty("hikariCP.datasource.pool_name"));
config.setConnectionTimeout(Long.parseLong(env.getProperty("hikariCP.datasource.connection_timeout")));
config.setMinimumIdle(Integer.parseInt(env.getProperty("hikariCP.datasource.minimum_idle")));
config.setIdleTimeout(Long.parseLong(env.getProperty("hikariCP.datasource.idle_timeout")));
return new HikariDataSource(config);
}
@Bean
@Primary
public PlatformTransactionManager productTransactionManager() {
final JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(productEntityManager().getObject());
return transactionManager;
}
}
Creating Entity and Repo
ProductEntity.java
@Entity
@Table(name = "product")
public class Product {
@Id
private Integer id;
private String name;
private String price;
@Column(length = 5000)
private String description;
private String weight;
private String brand;
private String onForSale;
private String status;
//Getter & Setters & Constructor
}
ProductRepo.java
public interface ProductRepo extends JpaRepository<Product, Integer> {
}
UserEntity.java
@Entity
@Table(name = "users")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
private String name;
@Column(unique = true, nullable = false)
private String email;
private int age;
//Getter & Setters & Constructor
}
UserRepo.java
public interface UserRepo extends JpaRepository<User, Integer> {
}
Testing Implementation
To test our implementation create a simple rest controller with two methods; one for saving and other for fetching data.
TestController.java
@RestController
public class TestController {
@Autowired
ProductRepo prdRepo;
@Autowired
UserRepo usrRepo;
@Autowired
private SecretKeyConstant keyConstant;
@PostMapping(value = "/save")
public String saveData() {
prdRepo.save(new Product(1, "Nokia Lumia 300", "450.0", "Mobile", "56.0", "Nokia", "Y", "Y"));
usrRepo.save(new User(1, "John", "john@mail.com", 45));;
return "success";
}
@GetMapping(value = "/fetch")
public String fetchData() throws JsonProcessingException {
String prds = new ObjectMapper().writeValueAsString(prdRepo.findAll());
String usrs = new ObjectMapper().writeValueAsString(usrRepo.findAll());
return "Products: " + prds + "\nUsers: " + usrs;
}
}
Summary
In this example, we learned to configure two MySQL databases in spring boot. Using the same approach if needed we can also configure databases created in different DBMS.
As always you can find the source code for the example @ Github