Connect multiple databases from Springboot application
Connect multiple databases, build and run Springboot application
The database connection is an important aspect of any application and nearly all production-grade applications connect to one or more databases. In this article, we will create a Spring boot application and connect it with two individual MySQL databases.
TL;DR
Creating a Spring Boot Application
In this section, we will create a Spring boot application and expose the following endpoints:
GET /customer/ : List all books
GET /product/: Get a book resource
Step 1: Creating a Spring Boot Project
Browse to your favorite IDE and create a Spring boot project with web, data-jpa, mysql-connector-java, and Lombok dependencies. Following is the pom.xml file:
<?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.3.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example.multipledb</groupId>
<artifactId>multiple-db-connection</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>multiple-db-connection</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>Step 2: Configuring CUSTOMERDB database
In this application, we will use the MySQL database as our backing database. Add the following configuration in the application.properties file to configure CUSTOMERDB database:
spring.jpa.hibernate.ddl-auto=update
spring.datasource.jdbc-url=jdbc:mysql://192.168.1.100:6605/customerdb?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=Password1
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8DialectStep 3: Configuring PRODUCTDB database
Append the following configuration in the application.properties file to configure PRODUCTDB database:
spring.productdb-datasource.jdbc-url=jdbc:mysql://192.168.1.100:6603/productdb?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&serverTimezone=UTC
spring.productdb-datasource.username=root
spring.productdb-datasource.password=Password1Step 4: Create Customer and Product POJO/entity
We will be creating different packages to store CUSTOMERDB and PRODUCTDB specific Entities and Repositories. This is important and the reason for this will be explained in further steps. Also, note that we are providing DB schema and table name in Table annotation.
Hear we are managing Customer information. We have created the following Customer entity:
package com.example.multipledb.multipledbconnection.entity.customer;
//imports@Entity
@Table(schema = "customerdb", name = "customer")
@Getter
@Setter
@ToString
@NoArgsConstructor
public class Customer {
@Id
private int id;
private String name;
private int age;
public Customer(String name, int age) {
this.name = name;
this.age = age;
}
}
Hear we are managing Product information. We have created the following Product entity:
package com.example.multipledb.multipledbconnection.entity.product;
//imports
@Entity
@Table(schema = "productdb", name = "product")
@Getter
@Setter
@ToString
@NoArgsConstructor
public class Product {
@Id
private int id;
private String name;
private int price;
public Product(String name, int price) {
this.name = name;
this.price = price;
}
}Step 4: Create Repositories
Note: Create two packages to keep CUSTOMERDB and PRODUCTDB repositories
Repository for the Customer entity
package com.example.multipledb.multipledbconnection.repository.customer;
//imports
@Repository
public interface CustomerRepository extends JpaRepository<Customer,Integer> {
}Repository for the Product entity
package com.example.multipledb.multipledbconnection.repository.product;
//imports
@Repository
public interface ProductRepository extends JpaRepository<Product,Integer> {
}Step 5: Create Springboot Configurations for database connections
Here we will be creating the Springboot configuration class to create necessary Beans which will be used to connect CUSTOMERDB and PRODUCTDB databases. Since we are going to have more than one DB connection, we need to define one of them as Primary.
CUSTOMERDB Configuration: We need to specify which entity and repository packages are to be scanned to create Beans required to connect CUSTOMERDB database. This is the reason why we kept entities and repositories in database-specific packages.
package com.example.multipledb.multipledbconnection.config;
//imports
@Configuration
@PropertySource({"classpath:application.properties"})
@EnableJpaRepositories(
basePackages = "com.example.multipledb.multipledbconnection.repository.customer",
entityManagerFactoryRef = "customerEntityManager",
transactionManagerRef = "customerTransactionManager"
)
public class CustomerPersistenceConfiguration {
@Autowired
private Environment env;
@Primary
@Bean
@ConfigurationProperties(prefix="spring.datasource")
public DataSource customerDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@Primary
public LocalContainerEntityManagerFactoryBean customerEntityManager() {
final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(customerDataSource());
em.setPackagesToScan("com.example.multipledb.multipledbconnection.entity.customer");
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.hbm2ddl.auto"));
properties.put("hibernate.dialect", env.getProperty("hibernate.dialect"));
em.setJpaPropertyMap(properties);
return em;
}
@Bean
@Primary
public PlatformTransactionManager customerTransactionManager() {
final JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(customerEntityManager().getObject());
return transactionManager;
}
}PRODUCT Configuration: We need to specify which entity and repository packages are to be scanned to create Beans required to connect PRODUCTDB database.
package com.example.multipledb.multipledbconnection.config;
//imports
@Configuration
@PropertySource({"classpath:application.properties"})
@EnableJpaRepositories(
basePackages = "com.example.multipledb.multipledbconnection.repository.product",
entityManagerFactoryRef = "productEntityManager",
transactionManagerRef = "productTransactionManager"
)
public class ProductPersistenceConfiguration {
@Autowired
private Environment env;
@Bean
@ConfigurationProperties(prefix="spring.productdb-datasource")
public DataSource productDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public LocalContainerEntityManagerFactoryBean productEntityManager() {
final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(productDataSource());
em.setPackagesToScan("com.example.multipledb.multipledbconnection.entity.product");
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.hbm2ddl.auto"));
properties.put("hibernate.dialect", env.getProperty("hibernate.dialect"));
em.setJpaPropertyMap(properties);
return em;
}
@Bean
public PlatformTransactionManager productTransactionManager() {
final JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(productEntityManager().getObject());
return transactionManager;
}
}Step 5: Run the app and verify connectivity
Once the application is built and is running. Open the below URLs to verify the DB connectivity to CUSTOMERDB and PRODUCTDB.
http://localhost:8080/customer/
http://localhost:8080/product/
Wrapping Up
As we can see above, we will need to make Springboot database Configuration files and provide schema-table information in entities to connect multiple databases. You can also refer to this for more comprehensive details on the subject.
