Newsletter

Spring Boot JDBC + MySQL – How to Configure Multiple DataSource

Spring-Boot-Tutorials » on Apr 5, 2018 { 5 Comments } By Sivateja

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

  ::. About the Author .::

Java4s_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.

Comments

5 Responses to “Spring Boot JDBC + MySQL – How to Configure Multiple DataSource”
  1. krishna reddy says:

    I just gone through all of your spring boot articles, those are well written. I got understand in one shot of reading. please be post further articles on spring boot.

    Thank you

  2. Gayathri says:

    Hi Shiva,

    Why we need multiple Data sources, we can decided from which data source we wanna get data is it? for example "customer" is the table present in both the data sources then we can decide from what should i get the data,,is my understanding correct.

    Please correct me.

  3. Gayathri says:

    Very well explained easy to understand

  4. Meenakshi Kumari says:

    Hello
    In the multiple data source configuration code, you have used @primary annotation for firstDataSource. Since, we are using @qualifier in the consumer method like jdbcTemplateOne then why we need to add @Primary annotation for the datasource. If I remove the @Primary annotation, it's showing error:

    Description:

    Parameter 0 of method jdbcTemplateOne in com.example.SpringJavaConfig required a single bean, but 2 were found:
    – firstDataSource: defined by method 'firstDataSource' in class path resource [com/example/SpringJavaConfig.class]
    – secondDataSource: defined by method 'secondDataSource' in class path resource [com/example/SpringJavaConfig.class]

    Action:

    Consider marking one of the beans as @Primary, updating the consumer to accept multiple beans, or using @Qualifier to identify the bean that should be consumed

    In the action, they have mentioned that you can do one of the following like Consider marking one of the beans as @Primary, updating the consumer to accept multiple beans, or using @Qualifier to identify the bean that should be consumed.

    So, since Qualifier is used in the jdbcTemplateOne, why it's throwing the error. Please help me to understand this.

Name*
Mail*
Website



By posting your answer, you agree to our comments policy.
Most Recent Posts from Top Categories
Spring Boot Hibernate Spring
Contact | About Us | Privacy Policy | Advertise With Us

© 2010 - 2019 Java4s - Get It Yourself.
The content is copyrighted to Sivateja Kandula and may not be reproduced on other websites.