Spring Boot JDBC + MySQL – How to Configure Multiple DataSource

In the previous article we saw how to configure a datasource in a spring boot application,  that’s very straight forward.  In this article I will show you how to configure multiple datasources in spring boot application. Unlike single datasource, in order to create multiple datasources we may need to write little configuration, I will show you how.

Directory Structure

Add Spring Boot JDBC Dependency

<!-- Spring boot jdbc -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>    

<!-- MySql -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<!-- Configuration -->
<dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-configuration-processor</artifactId>
     <optional>true</optional>
</dependency>

Final pom.xml

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.java4s</groupId>
    <artifactId>SpringBootMultipleDataSourceExample</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.6.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <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.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>
    <properties>
        <java.version>1.8</java.version>
    </properties>
</project>

Add Datasources Information in application.properties

# Applicationn context name
server.contextPath=/springbootds

# Here 'test' is the database name
spring.datasource.url=jdbc:mysql://localhost/test
spring.datasource.username=java4s
spring.datasource.password=java4s
spring.datasource.driver-class-name=com.mysql.jdbc.Driver


# Here 'test2' is the database name
spring.secondDatasource.url=jdbc:mysql://localhost/test2
spring.secondDatasource.username=java4s2
spring.secondDatasource.password=java4s2
spring.secondDatasource.driver-class-name=com.mysql.jdbc.Driver

SpringBootApp.java

package com.java4s.app;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootApp {
    public static void main(String[] args) {

        SpringApplication.run(SpringBootApp.class, args);

    }
}

SpringJava4sConfig.java

package com.java4s.app.configs;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

@Configuration
public class SpringJava4sConfig {

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

    @Bean
    @ConfigurationProperties(prefix = "spring.secondDatasource")
    public DataSource secondDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public JdbcTemplate jdbcTemplateOne(@Qualifier("firstDataSource") DataSource ds) {
        return new JdbcTemplate(ds);
    }

    @Bean
    public JdbcTemplate jdbcTemplateTwo(@Qualifier("secondDataSource") DataSource ds) {
        return new JdbcTemplate(ds);
    }

}

SpringJava4sController.java

package com.java4s.app.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.java4s.app.repository.SpringJava4sDAO;
import com.java4s.model.Customer;

@RestController
public class SpringJava4sController {

    @Autowired
    public SpringJava4sDAO dao;

    @RequestMapping("/getcustInfo")
    public List < Customer > customerInformation() {
        List < Customer > customers = dao.isData();
        return customers;
    }

    @RequestMapping("/testSecondDatasource")
    public String dSverify() {
        return dao.dsVerification();
    }
}

SpringJava4sDAO.java

package com.java4s.app.repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.java4s.model.Customer;

@Repository
public class SpringJava4sDAO {

    @Autowired
    private JdbcTemplate jdbcTemplateOne;

    @Autowired
    private JdbcTemplate jdbcTemplateTwo;


    public List < Customer > isData() {

        List < Customer > customers = new ArrayList < Customer > ();

        List < Map < String, Object >> rows = jdbcTemplateOne.queryForList("SELECT * FROM CUSTOMERS");

        for (Map < String, Object > row: rows) {
            Customer customer = new Customer();

            customer.setCustNo((int) row.get("Cust_id"));
            customer.setCustName((String) row.get("Cust_name"));
            customer.setCountry((String) row.get("Country"));

            customers.add(customer);
        }

        return customers;

    }

    public String dsVerification() {

        String status = "";

        String query = jdbcTemplateTwo.queryForObject("SELECT COUNT(*) FROM DUAL;", String.class);

        if (query.equals("1")) {
            status = "Datasource connection successful..!";
        } else {
            status = "Datasource connection failed..!";
        }

        return status;

    }
}

Customer.java

package com.java4s.model;

public class Customer {

    private int custNo;
    private String custName;
    private String country;

    public Customer() {

    }

    public Customer(int custNumber, String custName, String country) {
        this.custNo = custNumber;
        this.custName = custName;
        this.country = country;
    }

    public int getCustNo() {
        return custNo;
    }

    public void setCustNo(int custNo) {
        this.custNo = custNo;
    }

    public String getCustName() {
        return custName;
    }

    public void setCustName(String custName) {
        this.custName = custName;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

}

Output:

If you run the app and hit the URL (this will use test database) : http://localhost:8080/springbootds/getcustInfo

And if you hit this URL (this will use test2 database) : http://localhost:8080/springbootds/testSecondDatasource

You Might Also Like

• Spring Boot Configure DataSource Using JNDI with Example
• How to Configure Cache in Spring Boot Applications
• Spring Boot – Display All Beans Available in ApplicationContext
• Spring Boot – RESTful Web Service with POST Request in XML Example
• Spring Boot – RESTful Web Service with POST Request in JSON Example

::. About the Author .::
		Sivateja Kandula - Java/J2EE Full Stack Developer Founder of Java4s - Get It Yourself, A popular Java/J2EE Programming Blog, Love Java and UI frameworks. You can sign-up for the Email Newsletter for your daily dose of Java tutorials.