How to use multiple database in single Spring Boot Application

Home / How to use multiple database in single Spring Boot Application

we know database connectivity is the most interesting part of any web application. As we already know how to use Mysql Database in Spring Boot application ( CLICK ME ), but sometime we need more than one database to interact with in our single application. So it is very easy to use multiple database in a single application. In this blog we see how to connect two different MySql database in Spring boot application. For that we have to follow the below steps:

  1. Add required dependency in your pom.xml file.
  2. Create Two Database named database1, database2 and create a table named testing in both the databases.
  3. Define MySql property for both the database in your application.properties file.
  4. Define a configuration class and initialise jdbctemplate for both the databases.
  5. Create a Service class and Autowired both the initialize jdbctemplate in that class and perform query to save data in different databases tables.
  6. Create a controller class that is used to declare an rest endPoint .

Follow Above steps:

1- Add required dependency in your pom.xml file.

<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-devtools</artifactId>
 <scope>runtime</scope>
 <optional>true</optional>
</dependency>

<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <scope>runtime</scope>
</dependency>

2- Create Two Database named database1, database2 and create a table names testing in both the databases.

create database1;

use database1;

CREATE TABLE `testing` (
  `id` int(2) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

create database2;

use database2;

CREATE TABLE `testing` (
  `id` int(2) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)


3- Define MySql property for both the database in your application.properties file.

#Configuration for First Database
database1.datasource.jdbc-url=jdbc:mysql://localhost:3306/database1
database1.datasource.username=vasu
database1.datasource.password=12345@
database1.datasource.driver-class-name=com.mysql.jdbc.Driver


#Configuration For Second Database
database2.datasource.jdbc-url=jdbc:mysql://localhost:3306/database2
database2.datasource.username=vasu
database2.datasource.password=12345@
database2.datasource.driver-class-name=com.mysql.jdbc.Driver

4- Define a configuration class and initialise jdbctemplate for both the databases.

package com.example.demo.config;

import javax.sql.DataSource;

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

@org.springframework.context.annotation.Configuration
public class Configuration {

	/* ********* For Database 1 *********** */
	@Bean(name = "datasource1")
	@ConfigurationProperties(prefix = "database1.datasource")
	@Primary
	public DataSource dataSource1() {
		return DataSourceBuilder.create().build();
	}

	@Bean(name = "db1_template")
	public JdbcTemplate database_1_template(@Qualifier("datasource1") DataSource db1) {
		return new JdbcTemplate(db1);
	}
	
	
	/* ********* For Database 2 *********** */
	@Bean(name = "datasource2")
	@ConfigurationProperties(prefix = "database2.datasource")
	public DataSource dataSource2() {
		return DataSourceBuilder.create().build();
	}
	
	@Bean(name = "db2_template")
	public JdbcTemplate database_2_template(@Qualifier("datasource2") DataSource db2) {
		return new JdbcTemplate(db2);
	}
}

5- Create a Service class and Autowired both the initialize jdbctemplate in that class and perform query to save data in different databases tables.

package com.example.demo.service;

import javax.transaction.Transactional;

import org.hibernate.annotations.common.util.impl.LoggerFactory;
import org.slf4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class MainService {
	
	private static final Logger logger = org.slf4j.LoggerFactory.getLogger(MainService.class);
	
	private static final String query = "insert into testing(id,name) values(?,?)";

	@Autowired
	@Qualifier("db1_template")
	private JdbcTemplate template_database_1;
	
	@Autowired
	@Qualifier("db2_template")
	private JdbcTemplate template_database_2;
	
	@Transactional
	public int save() {
		try {
			int insertDB1Status = template_database_1.update(query, new Object[] {1,"vasu rajput"});
			logger.info("insertDB1Status= "+insertDB1Status);
			
			int insertDB2Status = template_database_2.update(query, new Object[] {2,"vishu rajput"});
			logger.info("insertDB1Status= "+insertDB1Status);
			
			return 1;
		} catch (Exception e) {
			logger.error("Exception: "+e);
			return 0;
		}
	}
}

6- Create a controller class that is used to declare an rest endPoint .

package com.example.demo.controller;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import com.example.demo.service.MainService;

@RestController
public class MainController {

	private static final Logger logger = LoggerFactory.getLogger(MainController.class);

	@Autowired
	private MainService service;

	@GetMapping("/")
	public String test() {
		try {
			int saveStatus = service.save();
			logger.info("saveStatus= " + saveStatus);
			if (saveStatus == 1) {
				return "Nice Try Champ !!";
			} else {
				return "Some Technical Error Please Try again";
			}
		} catch (Exception e) {
			logger.error("Exception: " + e);
			return "Some Technical Error Please Try again";
		}
	}
}

Complete Code on Github: Multiple Database SpringBoot

Help Others, Please Share

About Author

Leave a Reply

Your email address will not be published. Required fields are marked *