How to Configure Multiple DataSources and JdbcTemplate in Spring Boot

Introduction

The article explains how to create multiple DataSource instances and corresponding JdbcTemplate beans in a Spring Boot 2.2.2 application, enabling separate read/write operations on two MySQL databases.

Dependencies

The required Maven dependencies are the same as those for a single‑data‑source setup:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- Database connection -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </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>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
</dependencies>

Application Properties

Define two sets of datasource properties, each prefixed with spring.datasource.primary and spring.datasource.secondary. Note that the key for the URL is jdbc-url, not the single‑datasource url property.

spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/spring?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.primary.username=root
spring.datasource.primary.password=root_123
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/spring1?serverTimezone=UTC&characterEncoding=utf-8&useSSL=true
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root_123
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver

Java Configuration

Create a configuration class that declares the two DataSource beans and the two JdbcTemplate beans. The @Primary annotation marks the primary datasource, while @ConfigurationProperties binds the prefixed properties to each bean.

@Configuration
public class DataSourceConfig {

    @Primary
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "primaryJdbcTemplate")
    public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "secondaryJdbcTemplate")
    public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

Entity Class

The simple Order entity uses Lombok’s @Data to generate getters, setters, and other boilerplate methods.

@Data
public class Order {
    private int id;
    private String orderNo;
    private int amount;
}

Database DDL

Both databases contain an identical tb_order table.

CREATE TABLE `tb_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amount` int(11) NOT NULL DEFAULT '1',
  `order_no` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Service Interface and Implementation

The OrderService interface defines two save methods: one that uses the default (primary) JdbcTemplate and another that accepts a specific JdbcTemplate to allow explicit routing.

public interface OrderService {
    int save(Order order);
    int save(Order order, JdbcTemplate jdbcTemplate);
}

The implementation injects the primary JdbcTemplate with @Qualifier("primaryJdbcTemplate"). The overloaded method checks whether a secondary template is supplied and uses it; otherwise it falls back to the primary.

@Service("orderService")
public class OrderServiceImpl implements OrderService {

    @Resource
    @Qualifier("primaryJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Override
    public int save(Order order) {
        return jdbcTemplate.update("insert into tb_order(order_no, amount) values(?, ?)",
                order.getOrderNo(), order.getAmount());
    }

    @Override
    public int save(Order order, JdbcTemplate secJdbcTemplate) {
        if (secJdbcTemplate != null) {
            return secJdbcTemplate.update("insert into tb_order(order_no, amount) values(?, ?)",
                    order.getOrderNo(), order.getAmount());
        } else {
            return jdbcTemplate.update("insert into tb_order(order_no, amount) values(?, ?)",
                    order.getOrderNo(), order.getAmount());
        }
    }
}

Unit Test

A Spring Boot test class injects both JdbcTemplate beans and the OrderService. It creates an Order instance and calls the overloaded save method with each template, verifying that a row is inserted into each database.

@Slf4j
@SpringBootTest
class OrderServiceTest {

    @Resource
    private OrderService orderService;

    @Resource
    @Qualifier("primaryJdbcTemplate")
    private JdbcTemplate primaryJdbcTemplate;

    @Resource
    @Qualifier("secondaryJdbcTemplate")
    private JdbcTemplate secondaryJdbcTemplate;

    @Test
    void save() {
        Order order = new Order();
        order.setOrderNo("N003");
        order.setAmount(10000);
        orderService.save(order, primaryJdbcTemplate);
        orderService.save(order, secondaryJdbcTemplate);
    }
}

Running the test inserts one record into tb_order of each configured database. Additional CRUD operations can be added by extending the service methods.