JavaDream

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