Hi everyone! Today I want to show you how to configure multiple datasources in a Spring Boot application using Spring Data JPA and the Oracle Spring Boot Starter for Universal Connection Pool (UCP).
This is a pattern you’ll need when you have a single application that needs to connect to multiple databases. Maybe you have different domains in separate databases, or you’re working with legacy systems, or you need to separate read and write operations across different database instances. Whatever the reason, Spring Boot makes this pretty straightforward once you understand the configuration pattern.
I’ve put together a complete working example on GitHub at https://github.com/markxnelson/spring-multiple-jpa-datasources, and in this post I’ll walk you through how to build it from scratch.
The Scenario
For this example, we’re going to build a simple application that manages two separate domains:
- Customers – stored in one database
- Products – stored in a different database
Each domain will have its own datasource, entity manager, and transaction manager. We’ll use Spring Data JPA repositories to interact with each database, and we’ll show how to use both datasources in a REST controller.
I am assuming you have a database with two users called customer and product and some tables. Here’s the SQL to set that up:
$ sqlplus sys/Welcome12345@localhost:1521/FREEPDB1 as sysdba
alter session set container=freepdb1;
create user customer identified by Welcome12345;
create user product identified by Welcome12345;
grant connect, resource, unlimited tablespace to customer;
grant connect, resource, unlimited tablespace to product;
commit;
$ sqlplus customer/Welcome12345@localhost:1521/FREEPDB1
create table customer (id number, name varchar2(64));
insert into customer (id, name) values (1, 'mark');
commit;
$ sqlplus product/Welcome12345@localhost:1521/FREEPDB1
create table product (id number, name varchar2(64));
insert into product (id, name) values (1, 'coffee machine');
commit;
Step 1: Dependencies
Let’s start with the Maven dependencies. Here’s what you’ll need in your pom.xml:
<dependencies>
<!-- Spring Boot Starter Web for REST endpoints -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Spring Boot Starter Data JPA -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- Oracle Spring Boot Starter for UCP -->
<dependency>
<groupId>com.oracle.database.spring</groupId>
<artifactId>oracle-spring-boot-starter-ucp</artifactId>
<version>25.3.0</version>
</dependency>
</dependencies>
The key dependency here is the oracle-spring-boot-starter-ucp, which provides autoconfiguration for Oracle’s Universal Connection Pool. UCP is Oracle’s high-performance connection pool implementation that provides features like connection affinity, Fast Connection Failover, and Runtime Connection Load Balancing.
Step 2: Configure the Datasources in application.yaml
Now let’s configure our two datasources in the application.yaml file. We’ll define connection properties for both the customer and product databases:
spring:
application:
name: demo
jpa:
customer:
properties:
hibernate.dialect: org.hibernate.dialect.OracleDialect
hibernate.hbm2ddl.auto: validate
hibernate.format_sql: true
hibernate.show_sql: true
product:
properties:
hibernate.dialect: org.hibernate.dialect.OracleDialect
hibernate.hbm2ddl.auto: validate
hibernate.format_sql: true
hibernate.show_sql: true
datasource:
customer:
url: jdbc:oracle:thin:@localhost:1521/freepdb1
username: customer
password: Welcome12345
driver-class-name: oracle.jdbc.OracleDriver
type: oracle.ucp.jdbc.PoolDataSourceImpl
oracleucp:
connection-factory-class-name: oracle.jdbc.pool.OracleDataSource
connection-pool-name: CustomerConnectionPool
initial-pool-size: 15
min-pool-size: 10
max-pool-size: 30
shared: true
product:
url: jdbc:oracle:thin:@localhost:1521/freepdb1
username: product
password: Welcome12345
driver-class-name: oracle.jdbc.OracleDriver
type: oracle.ucp.jdbc.PoolDataSourceImpl
oracleucp:
connection-factory-class-name: oracle.jdbc.pool.OracleDataSource
connection-pool-name: CustomerConnectionPool
initial-pool-size: 15
min-pool-size: 10
max-pool-size: 30
shared: true
Notice that we’re using custom property prefixes (spring.datasource.customer and product) instead of the default spring.datasource. This is because Spring Boot’s autoconfiguration will only create a single datasource by default. When you need multiple datasources, you need to create them manually and use custom configuration properties.
In this example, both datasources happen to point to the same database server but use different schemas (users). In a real-world scenario, these would typically point to completely different database instances.
Step 3: Configure the Customer Datasource
Now we need to create the configuration classes that will set up our datasources, entity managers, and transaction managers. Let’s start with the customer datasource.
Create a new package called customer and add a configuration class called CustomerDataSourceConfig.java:
package com.example.demo.customer;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
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;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "customerEntityManagerFactory", transactionManagerRef = "customerTransactionManager", basePackages = {
"com.example.demo.customer" })
public class CustomerDataSourceConfig {
@Bean(name = "customerProperties")
@ConfigurationProperties("spring.datasource.customer")
public DataSourceProperties customerDataSourceProperties() {
return new DataSourceProperties();
}
/**
* Creates and configures the customer DataSource.
*
* @param properties the customer datasource properties
* @return configured DataSource instance
*/
@Primary
@Bean(name = "customerDataSource")
@ConfigurationProperties(prefix = "spring.datasource.customer")
public DataSource customerDataSource(@Qualifier("customerProperties") DataSourceProperties properties) {
return properties.initializeDataSourceBuilder().build();
}
/**
* Reads customer JPA properties from application.yaml.
*
* @return Map of JPA properties
*/
@Bean(name = "customerJpaProperties")
@ConfigurationProperties("spring.jpa.customer.properties")
public java.util.Map<String, String> customerJpaProperties() {
return new java.util.HashMap<>();
}
/**
* Creates and configures the customer EntityManagerFactory.
*
* @param builder the EntityManagerFactoryBuilder
* @param dataSource the customer datasource
* @param jpaProperties the JPA properties from application.yaml
* @return configured LocalContainerEntityManagerFactoryBean
*/
@Primary
@Bean(name = "customerEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean customerEntityManagerFactory(EntityManagerFactoryBuilder builder,
@Qualifier("customerDataSource") DataSource dataSource,
@Qualifier("customerJpaProperties") java.util.Map<String, String> jpaProperties) {
return builder.dataSource(dataSource)
.packages("com.example.demo.customer")
.persistenceUnit("customers")
.properties(jpaProperties)
.build();
}
@Bean
@ConfigurationProperties("spring.jpa.customer")
public PlatformTransactionManager customerTransactionManager(
@Qualifier("customerEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory.getObject());
}
}
Let’s break down what’s happening here:
- @EnableJpaRepositories – This tells Spring Data JPA where to find the repositories for this datasource. We specify the base package (
com.example.multidatasource.customer), and we reference the entity manager factory and transaction manager beans by name. - @Primary – We mark the customer datasource as the primary one. This means it will be used by default when autowiring a datasource, entity manager, or transaction manager without a
@Qualifier. You must have exactly one primary datasource when using multiple datasources. - customerDataSource() – This creates the datasource bean using Spring Boot’s
DataSourceBuilder. The@ConfigurationPropertiesannotation binds the properties from ourapplication.yaml(with thecustomer.datasourceprefix) to the datasource configuration. - customerEntityManagerFactory() – This creates the JPA entity manager factory, which is responsible for creating entity managers. We configure it to scan for entities in the customer package and set up Hibernate properties.
- customerTransactionManager() – This creates the transaction manager for the customer datasource. The transaction manager handles transaction boundaries and ensures ACID properties.
Step 4: Configure the Product Datasource
Now let’s create the configuration for the product datasource. Create a new package called product and add ProductDataSourceConfig.java:
package com.example.demo.product;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
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;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "productEntityManagerFactory", transactionManagerRef = "productTransactionManager", basePackages = {
"com.example.demo.product" })
public class ProductDataSourceConfig {
@Bean(name = "productProperties")
@ConfigurationProperties("spring.datasource.product")
public DataSourceProperties productDataSourceProperties() {
return new DataSourceProperties();
}
@Bean(name = "productDataSource")
@ConfigurationProperties(prefix = "spring.datasource.product")
public DataSource productDataSource(@Qualifier("productProperties") DataSourceProperties properties) {
return properties.initializeDataSourceBuilder().build();
}
/**
* Reads product JPA properties from application.yaml.
*
* @return Map of JPA properties
*/
@Bean(name = "productJpaProperties")
@ConfigurationProperties("spring.jpa.product.properties")
public java.util.Map<String, String> productJpaProperties() {
return new java.util.HashMap<>();
}
/**
* Creates and configures the product EntityManagerFactory.
*
* @param builder the EntityManagerFactoryBuilder
* @param dataSource the product datasource
* @param jpaProperties the JPA properties from application.yaml
* @return configured LocalContainerEntityManagerFactoryBean
*/
@Bean(name = "productEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean productEntityManagerFactory(@Autowired EntityManagerFactoryBuilder builder,
@Qualifier("productDataSource") DataSource dataSource,
@Qualifier("productJpaProperties") java.util.Map<String, String> jpaProperties) {
return builder.dataSource(dataSource)
.packages("com.example.demo.product")
.persistenceUnit("products")
.properties(jpaProperties)
.build();
}
@Bean
@ConfigurationProperties("spring.jpa.product")
public PlatformTransactionManager productTransactionManager(
@Qualifier("productEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory.getObject());
}
}
The product configuration is almost identical to the customer configuration, with a few key differences:
- No @Primary annotations – Since we already designated the customer datasource as primary, we don’t mark the product beans as primary.
- Different package – The
@EnableJpaRepositoriespoints to the product package, and the entity manager factory scans the product package for entities. - Different bean names – All the beans have different names (productDataSource, productEntityManagerFactory, productTransactionManager) to avoid conflicts.
Step 5: Create the Domain Models
Now let’s create the JPA entities for each datasource. First, in the customer package, create Customer.java:
package com.example.demo.customer;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
@Entity
public class Customer {
@Id
public int id;
public String name;
public Customer() {
this.id = 0;
this.name = "";
}
}
And in the product package, create Product.java:
package com.example.demo.product;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
@Entity
public class Product {
@Id
public int id;
public String name;
public Product() {
this.id = 0;
this.name = "";
}
}
Step 6: Create the Repositories
Now let’s create Spring Data JPA repositories for each entity. In the customer package, create CustomerRepository.java:
package com.example.demo.customer;
import org.springframework.data.jpa.repository.JpaRepository;
public interface CustomerRepository extends JpaRepository<Customer, Integer> {
}
And in the product package, create ProductRepository.java:
package com.example.demo.product;
import org.springframework.data.jpa.repository.JpaRepository;
public interface ProductRepository extends JpaRepository<Product, Integer> {
}
Step 7: Create a REST Controller
Finally, let’s create a REST controller that demonstrates how to use both datasources. Create a controller package and add CustomerController.java:
package com.example.demo.controllers;
import java.util.List;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import com.example.demo.customer.Customer;
import com.example.demo.customer.CustomerRepository;
@RestController
public class CustomerController {
final CustomerRepository customerRepository;
public CustomerController(CustomerRepository customerRepository) {
this.customerRepository = customerRepository;
}
@GetMapping("/customers")
public List<Customer> getCustomers() {
return customerRepository.findAll();
}
}
A few important things to note about the controller:
- Transaction Managers – When you have multiple datasources, you need to explicitly specify which transaction manager to use. Notice the
@Transactional("customerTransactionManager")and@Transactional("productTransactionManager")annotations on the write operations. If you don’t specify a transaction manager, Spring will use the primary one (customer) by default. - Repository Autowiring – The repositories are autowired normally. Spring knows which datasource each repository uses based on the package they’re in, which we configured in our datasource configuration classes.
- Cross-datasource Operations – The
initializeData()method demonstrates working with both datasources in a single method. However, note that these operations are not in a distributed transaction – if one fails, the other won’t automatically roll back. If you need distributed transactions across multiple databases, you would need to use JTA (Java Transaction API).
Let’s also create ProductController.java:
package com.example.demo.controllers;
import java.util.List;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import com.example.demo.product.Product;
import com.example.demo.product.ProductRepository;
@RestController
public class ProductController {
final ProductRepository productRepository;
public ProductController(ProductRepository productRepository) {
this.productRepository = productRepository;
}
@GetMapping("/products")
public List<Product> getProducts() {
return productRepository.findAll();
}
}
Testing the Application
Now you can run your application! Make sure you have two Oracle database users created (customer and product), or adjust the configuration to point to your specific databases.
Start the application:
mvn spring-boot:run
Then you can test it with some curl commands:
# Get all customers
$ curl http://localhost:8080/customers
[{"id":1,"name":"mark"}]
# Get all products
$ curl http://localhost:8080/products
[{"id":1,"name":"coffee machine"}]
Wrapping Up
And there you have it! We’ve successfully configured a Spring Boot application with multiple datasources using Spring Data JPA and Oracle’s Universal Connection Pool. The key points to remember are:
- Custom configuration properties – Use custom prefixes for each datasource in your
application.yaml - Manual configuration – Create configuration classes for each datasource with beans for the datasource, entity manager factory, and transaction manager
- Primary datasource – Designate one datasource as primary using
@Primary - Package organization – Keep entities and repositories for each datasource in separate packages
- Explicit transaction managers – Specify which transaction manager to use for write operations with
@Transactional
This pattern works great when you need to connect to multiple databases, whether they’re different types of databases or different instances of the same database. Oracle’s Universal Connection Pool provides excellent performance and reliability for your database connections.
I hope this helps you work with multiple datasources in your Spring Boot applications! The complete working code is available on GitHub at https://github.com/markxnelson/spring-multiple-jpa-datasources.
Happy coding!
RedStack 