How to configure multiple datasource in spring boot

By
Rakesh
-

In this tutorial, we will see how to configure multiple datasource in spring boot using Tomcat. First, we will configure multiple datasource providing the details in application.properties files and we will use embedded tomcat to deploy our application. We will also see spring boot dynamic datasource example.

We will also cover how to configure multiple datasource in spring boot using an external tomcat. We will define Datasource details in the tomcat context.xml file and we need to deploy our spring boot application on external tomcat.

Configure multiple datasource using application.properties


#primary db
spring.primary.jdbcUrl=jdbc:mysql://localhost:3306/springbootcrudexample
spring.primary.username=root
spring.primary.password=root
spring.primary.driverClassName=com.mysql.jdbc.Driver

#secondory db
spring.secondary.jdbcUrl=jdbc:mysql://localhost:3306/springbootdb
spring.secondary.username=root
spring.secondary.password=root
spring.secondary.driverClassName=com.mysql.jdbc.Driver

server.port = 9092

JPA configuration

@Configuration
@EnableJpaRepositories(basePackages = "com.javatute.repository")
public class Config {

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

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

}

Configuring multiple Datasource in Spring Boot using Tomcat

Let’s see how to configure multiple Datasource using Tomcat and Spring boot application. We will provide the JNDI datasource details in context.xml and will deploy the war file on an external tomcat.

Note – In this approach, we are not going to use an application.properties file.

We need to do the below changes in context.xml

How to configure multiple datasource in spring boot

context.xml

<!-- The contents of this file will be loaded for each web application -->
<Context>

    <!-- Default set of monitored resources. If one of these changes, the    -->
    <!-- web application will be reloaded.                                   -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
	<Resource name="jdbc/springbootcrudexample" auth="Container" type="javax.sql.DataSource"
               maxActive="50" maxIdle="30" maxWait="10000"
               username="root" password="root" 
               driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/springbootcrudexample"/>
			   
	<Resource name="jdbc/springbootdb" auth="Container" type="javax.sql.DataSource"
               maxActive="50" maxIdle="30" maxWait="10000"
               username="root" password="root" 
               driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/springbootdb"/>		   
			   
    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <!--
    <Manager pathname="" />
    -->
</Context>

JPA configuration

@Configuration
@EnableJpaRepositories(basePackages = "com.javatute.repository")
public class Config {

    @Bean
    @Primary
    public DataSource secondaryDataSource() {
        DataSource dataSource = null;

        Context ctx = null;
        try {
            ctx = new InitialContext();
            dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/springbootdb");
        } catch (NamingException e) {
            e.printStackTrace();
        }
        return dataSource;
    }

    @Bean
    public DataSource primaryDatasource() {
        DataSource dataSource = null;

        Context ctx = null;
        try {
            ctx = new InitialContext();
            dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/springbootcrudexample");
        } catch (NamingException e) {
            e.printStackTrace();
        }
        return dataSource;
    }
}

Spring Boot multiple dynamic Datasource configuration

<Context>

    <!-- Default set of monitored resources. If one of these changes, the    -->
    <!-- web application will be reloaded.                                   -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
	<!--<Resource name="jdbc/springbootcrudexample" auth="Container" type="javax.sql.DataSource"
               maxActive="50" maxIdle="30" maxWait="10000"
               username="root" password="root" 
               driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/springbootcrudexample"/>-->
			   
	<Resource name="jdbc/myoracle" auth="Container"
              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
              url="jdbc:oracle:thin:@localhost:1521"
              username="SYSTEM" password="oracle2" maxActive="20" maxIdle="10"
              maxWait="-1"/>		   
			   
	<Resource name="jdbc/springbootdb" auth="Container" type="javax.sql.DataSource"
               maxActive="50" maxIdle="30" maxWait="10000"
               username="root" password="root" 
               driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/springbootdb"/>   
			   
    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <!--
    <Manager pathname="" />
    -->
</Context>

Define two separate Datasource configuration files, one for MySql and another one is for Oracle.

Define Datasource Configuration file for MySql

@Configuration
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactory",
        transactionManagerRef = "transactionManager", basePackages = "com.javatute.repository.mysql")
public class MySqlDatasourceConfig {

    @Bean("mysqlDatasource")
    @Primary
    public DataSource dataSource() {
        DataSource dataSource = null;

        Context ctx = null;
        try {
            ctx = new InitialContext();
            dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/springbootdb");
        } catch (NamingException e) {
            e.printStackTrace();
        }
        return dataSource;
    }

    @Bean(name = "entityManager")
    public EntityManager entityManager() {
        return entityManagerFactory().createEntityManager();
    }

    @Primary
    @Bean(name = "entityManagerFactory")
    public EntityManagerFactory entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(dataSource());
        emf.setJpaVendorAdapter(vendorAdaptor());
        emf.setPackagesToScan("com.javatute");
        emf.setPersistenceUnitName("mysql");
        emf.afterPropertiesSet();
        return emf.getObject();
    }
    
    @Primary
    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager() {
        JpaTransactionManager tm = new JpaTransactionManager();
        tm.setEntityManagerFactory(entityManagerFactory());
        return tm;
    }

    private HibernateJpaVendorAdapter vendorAdaptor() {
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setShowSql(true);
        return vendorAdapter;
    }
}

Define Datasource configuration file for Oracle

@Configuration
@EnableJpaRepositories(entityManagerFactoryRef = "oracleEntityManagerFactory",
        transactionManagerRef = "oracleTransactionManager", basePackages = "com.javatute.repository.oracle")
public class OracleDatasourceConfig {

    @Bean("oracleDatasource")
    public DataSource dataSource() {
        DataSource dataSource = null;

        Context ctx = null;
        try {
            ctx = new InitialContext();
            dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/myoracle");
        } catch (NamingException e) {
            e.printStackTrace();
        }
        return dataSource;
    }

    @Bean(name = "oracleEntityManager")
    public EntityManager entityManager() {
        EntityManager entityManager = null;
        entityManager = entityManagerFactory().createEntityManager();
        return entityManager;
    }

    @Bean(name = "oracleEntityManagerFactory")
    public EntityManagerFactory entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(dataSource());
        emf.setJpaVendorAdapter(vendorAdaptor());
        emf.setPackagesToScan("com.javatute");
        emf.setPersistenceUnitName("oracle");
        emf.afterPropertiesSet();

        return emf.getObject();
    }

    @Bean(name = "oracleTransactionManager")
    public PlatformTransactionManager transactionManager() {
        JpaTransactionManager tm = new JpaTransactionManager();
        tm.setEntityManagerFactory(entityManagerFactory());
        return tm;
    }

    private HibernateJpaVendorAdapter vendorAdaptor() {
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setShowSql(true);
        return vendorAdapter;
    }

}

We need to define two separate repositories. One for MySql and another for Oracle.

MySqlRepository.java

@Repository
public interface MySqlRepository extends JpaRepository<Student, Serializable> {

}

OracleRepository.java

@Repository
public interface OracleRepository extends JpaRepository<Student, Serializable> {

}

Directory structure for spring boot dynamic datasource

spring boot dynamic datasource

ServiceImpl changes

@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private MySqlRepository mySqlRepository;

    //Our another datasource
    @Autowired
    private OracleRepository oracleRepository;


}

Download spring boot dynamic datasource complete example from GitHub.

Let’s see a complete example from scratch that demonstrates How to configure multiple datasource in spring boot using tomcat. In this example, we will define multiple datasources in a single file. One datasource would be considered as primary and another one considered as secondary.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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>jndiexampleusingtomcat</groupId>
    <artifactId>jndiexampleusingtomcat</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>jndiexampleusingtomcat</name>
    <packaging>war</packaging>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.1.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-tomcat</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    <!--    <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>-->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <scope>runtime</scope>
        </dependency>

    </dependencies>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

</project>

Define entity

package com.javatute.entity;

import javax.persistence.*;

@Entity
@Table(name = "student")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "roll_number")
    private String rollNumber;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getRollNumber() {
        return rollNumber;
    }

    public void setRollNumber(String rollNumber) {
        this.rollNumber = rollNumber;
    }

}

Define Service and repository interfaces

StudentService.java

package com.javatute.service;

import com.javatute.entity.Student;
import org.springframework.stereotype.Component;

@Component
public interface StudentService {
    public Student save(Student student);

    public Student update(Student student);

    public Student get(Long id);

    public void delete(Student student);
}

StudentRepository.java

package com.javatute.repository;

import com.javatute.entity.Student;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.io.Serializable;

@Repository
public interface StudentRepository extends JpaRepository<Student, Serializable> {

}

StudentServiceImpl.java

package com.javatute.serviceimpl;

import com.javatute.entity.Student;
import com.javatute.repository.StudentRepository;
import com.javatute.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.Optional;

@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentRepository studentRepository;

    @Transactional
    public Student save(Student student) {
        Student createResponse = studentRepository.save(student);
        return createResponse;
    }

    @Transactional
    public Student update(Student student) {
        Student updateResponse = studentRepository.save(student);
        return updateResponse;
    }

    @Transactional
    public Student get(Long id) {
        Optional<Student> studentResponse = studentRepository.findById(id);
        Student getResponse = studentResponse.get();
        return getResponse;
    }

    @Transactional
    public void delete(Student student) {
        studentRepository.delete(student);
    }
}

StudentController.java

package com.javatute.controller;

import com.javatute.entity.Student;
import com.javatute.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/student")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @PostMapping("/create")
    public Student createStudent(@RequestBody Student student) {
        Student createResponse = studentService.save(student);
        return createResponse;
    }

    @PutMapping("/update")
    public Student updateStudent(@RequestBody Student student) {
        Student updateResponse = studentService.update(student);
        return updateResponse;
    }

    @GetMapping("/{id}")
    public Student getStudent(@PathVariable Long id) {
        Student getReponse = studentService.get(id);
        return getReponse;
    }

    @DeleteMapping("/delete")
    public String deleteStudent(@RequestBody Student student) {
        studentService.delete(student);
        return "Record deleted succesfully";
    }
}

Config.java

package com.javatute.config;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

@Configuration
@EnableJpaRepositories(basePackages = "com.javatute.repository")
public class Config {

    @Bean
    @Primary
    public DataSource primaryDataSource() {
        DataSource dataSource = null;

        Context ctx = null;
        try {
            ctx = new InitialContext();
            dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/springbootdb");
        } catch (NamingException e) {
            e.printStackTrace();
        }
        return dataSource;
    }

    @Bean
    public DataSource secondaryDatasource() {
        DataSource dataSource = null;

        Context ctx = null;
        try {
            ctx = new InitialContext();
            dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/springbootcrudexample");
        } catch (NamingException e) {
            e.printStackTrace();
        }
        return dataSource;
    }


}

Springmain.java

package com.javatute.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.boot.builder.SpringApplicationBuilder;
import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;
import org.springframework.context.annotation.ComponentScan;

@ComponentScan(basePackages = "com.javatute")
@SpringBootApplication
@EntityScan("com.javatute.entity")
public class SpringMain extends SpringBootServletInitializer {

    public static void main(String[] args) {
        SpringApplication.run(SpringMain.class, args);
    }

    @Override
    public SpringApplicationBuilder configure(SpringApplicationBuilder application) {
        return application.sources(SpringMain.class);
    }
}

let’s run application and test.

That’s all about How to configure multiple datasource in spring boot using tomcat and how to configure spring boot dynamic datasource.

See docs.

Download source code from GitHub.

Related tutorial

Deploy Spring boot war in JBoss EAP server.
Jboss 7 EPA Datasource configuration using oracle and spring boot.
Deploy Spring Boot application on external Tomcat.
Deploy multiple war files in JBoss to a different port.

JPA tutorials

Hibernate tutorials

RELATED ARTICLESMORE FROM AUTHOR