9.5 Pro Tips — Master-Slave (Read/Write) Multi-DataSource Routing
Learn how to manually configure a DataSource using Java code when bypassing Spring Boot's Auto-Configuration, and how to connect to two or more databases within a single application.
1. Manual DataSource Configuration via Java
Using Java configuration classes is useful for compatibility with specific libraries or when settings need to be changed dynamically at runtime.
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties("spring.datasource.hikari") // Bind settings from yml
public DataSource dataSource() {
// Create manually using DataSourceBuilder
return DataSourceBuilder.create()
.type(HikariDataSource.class)
.build();
}
}
2. Multi-DataSource Configuration
In practical implementations, as service scale increases, it's common to configure multiple databases to distribute traffic or separate domains. Here are the two most representative real-world patterns.
2.1 [Pattern 1] Domain-Based Database Separation (User DB / Order DB)
This approach is used when different domains access completely different database systems(e.g., storing User data in MySQL and Order data in PostgreSQL). When using Spring Data JPA, you must configure the EntityManagerFactory and TransactionManager independently for each package.
1) application.yml Setup
spring:
# Exclude default auto-configuration
autoconfigure:
exclude: org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration
app:
datasource:
user:
jdbc-url: jdbc:mysql://localhost:3306/user_db
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
order:
jdbc-url: jdbc:postgresql://localhost:5432/order_db
username: user
password: password
driver-class-name: org.postgresql.Driver
💡 Why exclude DataSourceAutoConfiguration?
By default, if Spring Boot detects a database driver (e.g.,
mysql-connector-j) on the classpath, it attempts to automatically configure a singleDataSource. However, in a multi-database environment, developers must manually register two or more DataSources as beans with precise configurations (including separate JPA EntityManagers and TransactionManagers).Therefore, we explicitly exclude the auto-configuration using the
excludeproperty to prevent Spring Boot's single-DB default configuration from interfering, creating unintended default DataSource beans, or causing bean definition conflicts.
2) User Database Configuration (Primary)
Repositories and Entities under com.example.user will connect to the User DB.
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
basePackages = "com.example.user.repository",
entityManagerFactoryRef = "userEntityManagerFactory",
transactionManagerRef = "userTransactionManager"
)
public class UserDataSourceConfig {
@Primary
@Bean(name = "userDataSource")
@ConfigurationProperties(prefix = "app.datasource.user")
public DataSource userDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Primary
@Bean(name = "userEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean userEntityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier("userDataSource") DataSource dataSource) {
return builder
.dataSource(dataSource)
.packages("com.example.user.entity")
.persistenceUnit("user")
.build();
}
@Primary
@Bean(name = "userTransactionManager")
public PlatformTransactionManager userTransactionManager(
@Qualifier("userEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory);
}
}
3) Order Database Configuration (Secondary)
Repositories and Entities under com.example.order will connect to the Order DB.
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
basePackages = "com.example.order.repository",
entityManagerFactoryRef = "orderEntityManagerFactory",
transactionManagerRef = "orderTransactionManager"
)
public class OrderDataSourceConfig {
@Bean(name = "orderDataSource")
@ConfigurationProperties(prefix = "app.datasource.order")
public DataSource orderDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean(name = "orderEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean orderEntityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier("orderDataSource") DataSource dataSource) {
return builder
.dataSource(dataSource)
.packages("com.example.order.entity")
.persistenceUnit("order")
.build();
}
@Bean(name = "orderTransactionManager")
public PlatformTransactionManager orderTransactionManager(
@Qualifier("orderEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory);
}
}
💡 When using MyBatis Instead of
EntityManagerFactoryandJpaTransactionManager, registerSqlSessionFactoryandDataSourceTransactionManager, and specify the package using@MapperScan.@Configuration
@MapperScan(
basePackages = "com.example.user.mapper",
sqlSessionFactoryRef = "userSqlSessionFactory"
)
public class UserMyBatisConfig {
@Primary
@Bean(name = "userSqlSessionFactory")
public SqlSessionFactory userSqlSessionFactory(@Qualifier("userDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/user/*.xml"));
return sessionFactory.getObject();
}
@Primary
@Bean(name = "userTransactionManager")
public PlatformTransactionManager userTransactionManager(@Qualifier("userDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
// Order Domain MyBatis Configuration (Secondary)
@Configuration
@MapperScan(
basePackages = "com.example.order.mapper",
sqlSessionFactoryRef = "orderSqlSessionFactory"
)
public class OrderMyBatisConfig {
@Bean(name = "orderSqlSessionFactory")
public SqlSessionFactory orderSqlSessionFactory(@Qualifier("orderDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/order/*.xml"));
return sessionFactory.getObject();
}
@Bean(name = "orderTransactionManager")
public PlatformTransactionManager orderTransactionManager(@Qualifier("orderDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
4) Practical Code Usage Example
// User Domain Service (Primary Transaction Manager applied automatically)
@Service
@RequiredArgsConstructor
public class UserService {
// Repository located under basePackages = "com.example.user.repository"
private final UserRepository userRepository;
@Transactional // Automatically applies the Primary Transaction Manager (userTransactionManager)
public User createUser(User user) {
return userRepository.save(user);
}
}
// Order Domain Service (Secondary Transaction Manager declared explicitly)
@Service
@RequiredArgsConstructor
public class OrderService {
// Repository located under basePackages = "com.example.order.repository"
private final OrderRepository orderRepository;
@Transactional("orderTransactionManager") // Explicitly targets the Order DB transaction manager
public Order createOrder(Order order) {
return orderRepository.save(order);
}
}
2.2 [Pattern 2] Master (Write) / Replica (Read) Separation
To alleviate database load in high-traffic web services, write operations (Insert/Update/Delete) are routed to the Master DB, while read operations (Select) are routed to the Replica (Slave) DB. This can be implemented using AbstractRoutingDataSource.
1) application.yml Setup
spring:
autoconfigure:
exclude: org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration
app:
datasource:
master:
jdbc-url: jdbc:mysql://master-db:3306/main_db
username: admin
password: master_password
replica:
jdbc-url: jdbc:mysql://replica-db:3306/main_db
username: admin
password: replica_password
2) RoutingDataSource Implementation
Checks if the current transaction is read-only and returns the corresponding identifier.
public class ReplicationRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
// Return 'replica' if @Transactional(readOnly = true), otherwise 'master'
boolean isReadOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
return isReadOnly ? "replica" : "master";
}
}
3) DataSource and Proxy Configuration
@Configuration
public class RoutingDataSourceConfig {
@Bean(name = "masterDataSource")
@ConfigurationProperties(prefix = "app.datasource.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean(name = "replicaDataSource")
@ConfigurationProperties(prefix = "app.datasource.replica")
public DataSource replicaDataSource() {
return DataSourceBuilder.create().type(HikariDataSource.class).build();
}
@Bean(name = "routingDataSource")
public DataSource routingDataSource(
@Qualifier("masterDataSource") DataSource masterDataSource,
@Qualifier("replicaDataSource") DataSource replicaDataSource) {
ReplicationRoutingDataSource routingDataSource = new ReplicationRoutingDataSource();
Map<Object, Object> dataSourceMap = new HashMap<>();
dataSourceMap.put("master", masterDataSource);
dataSourceMap.put("replica", replicaDataSource);
routingDataSource.setTargetDataSources(dataSourceMap);
routingDataSource.setDefaultTargetDataSource(masterDataSource);
return routingDataSource;
}
/**
* When using JPA, wrap the DataSource with a LazyConnectionDataSourceProxy.
* This prevents acquiring a connection until the actual query starts execution,
* allowing routing to dynamically fetch the correct connection.
*/
@Primary
@Bean(name = "dataSource")
public DataSource dataSource(@Qualifier("routingDataSource") DataSource routingDataSource) {
return new LazyConnectionDataSourceProxy(routingDataSource);
}
}
💡 Master/Replica Routing with MyBatis If your Transaction Manager (e.g.,
DataSourceTransactionManager) points to the proxydataSourcebean configured above, its operation in the Service layer is exactly the same as in JPA. Simply using@Transactional(readOnly = true)will seamlessly route queries to the Replica (Slave). Note that configuringLazyConnectionDataSourceProxyas shown above is absolutely mandatory in MyBatis as well, to dynamically delay DB connection acquisition until the actual query starts.
4) Practical Code Usage Example
In your business logic (Service classes), simply using the @Transactional(readOnly = true) annotation automatically routes read-only queries to the Replica (Slave) DB. Developers can focus entirely on core business logic without worrying about the underlying infrastructure.
@Service
@RequiredArgsConstructor
public class ProductService {
private final ProductRepository productRepository;
// Create, Update, Delete: Uses default @Transactional -> Routed to Master DB
@Transactional
public Product saveProduct(Product product) {
return productRepository.save(product);
}
// Read: Uses @Transactional(readOnly = true) -> Routed to Replica (Slave) DB
@Transactional(readOnly = true)
public Product getProduct(Long id) {
return productRepository.findById(id).orElseThrow();
}
}
3. Practical Tips and Considerations
- Mandatory @Primary: If multiple beans of the same type exist, you must specify which one Spring should inject by default to avoid errors.
- ConfigurationProperties Note: In Spring Boot 2.x and later, when binding Hikari settings directly, you often need to use
jdbc-urlinstead ofurlin your property names. - Transaction Managers: When using multi-DB setups, you should also register separate Transaction Managers (
PlatformTransactionManager) and specify them where needed (e.g.,@Transactional("mainTransactionManager")).
4. Optimizing Large-Scale Data Insertion (Bulk/Batch Insert)
When inserting tens of thousands of records into a database at once, using a simple for loop to call save() or insert repeatedly causes severe performance degradation due to network round-trips and transaction overhead. In these cases, you must implement Batch Processing.
4.1 The Fastest and Most Reliable Method: JdbcTemplate batchUpdate
Even if your project primarily uses JPA, leveraging JdbcTemplate or MyBatis purely for bulk inserts is considered a practical industry Best Practice for peak performance.
@Repository
@RequiredArgsConstructor
public class BulkInsertRepository {
private final JdbcTemplate jdbcTemplate;
@Transactional
public void saveAllInBatch(List<User> userList) {
String sql = "INSERT INTO users (name, email, created_at) VALUES (?, ?, ?)";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
User user = userList.get(i);
ps.setString(1, user.getName());
ps.setString(2, user.getEmail());
ps.setTimestamp(3, Timestamp.valueOf(user.getCreatedAt())); // May require java.sql.Timestamp conversion
}
@Override
public int getBatchSize() {
// The size of the chunk to be sent to the DB at once
// It is recommended to partition the list into sub-lists of 1000~5000 records for optimal memory usage.
return userList.size();
}
});
}
}
4.2 Must use JPA? (Performance Tuning is Mandatory)
If you absolutely must use JPA's saveAll(), all three of the following conditions must be met for actual bulk insert queries to operate under the hood:
- DB URL Option: For MySQL, you must add the
rewriteBatchedStatements=trueparameter so that the driver rewrites and bundles the statements. - Batch Size Setting: You must set
spring.jpa.properties.hibernate.jdbc.batch_size=1000(or similar) in yourapplication.yml. GenerationType.IDENTITYis Prohibited: If your entity's ID generation strategy isIDENTITY(Auto Increment), Hibernate cannot batch the queries and will fire them one by one immediately. You must useSEQUENCE,TABLEstrategy, or assign IDs manually (using only@Id).
💡 Practical Pro Tip: Because the 3rd condition (
IDENTITYprohibition) is a very common dealbreaker in MySQL environments, modern practical projects typically mix inJdbcTemplateor MyBatis purely for bulk insert workloads, creating a Polyglot Persistence Layer, while relying on JPA for standard business logic.