Spring Boot: Working with multiple SQL database

August 21, 2016 · by Yogesh Prajapati · in Spring Boot · Leave a response

Problem Statement

In this write up we are going to cover how to use multiple database in Spring Boot Application with minimum configuration and ease of use.

In Ordinary Web application we don’t need multiple databases but when you go for enterprise development it is really required and useful. There are multiple reason behind using multiple databases, may we need to archive out data into some another database or need to push data to some third party database etc.

Developer Perquisites

Should know Java
Spring/Spring MVC
Spring Boot
Basic Database knowledge of any SQL, PostgreSQL and MySQL preferable.
Maven

Tools Perquisites

Spring Tool Suite (or Eclipse with Spring plugin)
Maven
MySQL Database server (better to have with Workbench)
PostgreSQL Database Server (better to have with PGAdmin)

As developer perquisites shows developer must have knowledge of Spring Boot and how to create Boot application using STS (Spring tool Suite). So here we will show how we solve our requirement to connect multiple db.

There are so many ways available to achieve the same and all are working as expectation. The decision is depends on developer about choosing a particular way.

Database Setup

To work with this demo you need to have at least 2 database available with you. Here we used PostgreSQL and MySQL.
Script below shows create and insert statement for both the database provider.

PostgreSQL

Create Statement

CREATE TABLE usermaster
(
 id integer,
 name character varying,
 emailid character varying,
 phoneno character varying(10),
 location character varying
) ;

Insert Statement

INSERT INTO usermaster(
           	id, name, emailid, phoneno, location)
VALUES (1, 'name_postgres', 'email@email.com', '1234567890', 'IN');

MySQL

Create Statement

CREATE TABLE `usermaster` (
 	`id` int(11) NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `emailid` varchar(20) DEFAULT NULL,
 `phoneno` varchar(20) DEFAULT NULL,
 `location` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
)

Insert Statement

INSERT INTO `kode12`.`usermaster` (
`id`, `name`, `emailid`, `phoneno`, `location`) 
VALUES ('1', 'name_mysql', 'test@tset.com', '9876543210', 'IN');

Project Setup

We used STS to show this situation.

To Create Project follow steps:

Click File -> New -> Spring Starter Project option.
In dialog provide Project Name, Group, Artifact, Version, Description and Package information, click Next.
Next dialog will allow user to select boot dependency, Select Web for now client next.
Click Finish, STS will download project from spring repo with your dependency.

Now let’s see all created files one by one with detail.

pom.xml

pom contains mapping and plugins entry for required dependency.

Code

<?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>com.kode12</groupId>
	<artifactId>MultipleDBConnect</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>MultipleDB</name>
	<description>MultipleDB with Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.3.5.RELEASE</version>
		<relativePath />
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<java.version>1.8</java.version>
	</properties>

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

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

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

		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.38</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

application.properties

This is the only file which contains all configuration of boot application, in legacy spring we need to provide the configuration using bunch of XML file.

Code

server.port=6060

spring.ds_post.url =jdbc:postgresql://localhost:5432/kode12
spring.ds_post.username =postgres
spring.ds_post.password =root
spring.ds_post.driverClassName=org.postgresql.Driver

spring.ds_mysql.url = jdbc:mysql://localhost:3306/kode12
spring.ds_mysql.username = root
spring.ds_mysql.password = root
spring.ds_mysql.driverClassName=com.mysql.jdbc.Driver

Explanation

Here server.port=6060 defines that your embedded server will start on 6060 port. server.port is standard property provided by boot.
Other properties started with spring.ds_* is user defined.
Properties starts with spring.ds_post.* is used to define configuration for PostgreSQL database.
Properties starts with spring.ds_mysql.* is used to define configuration for MySQL database.

MultipleDbApplication.java

This file resides under package com.kode12.

Code

Code
package com.kode12;

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

@SpringBootApplication
public class MultipleDbApplication {

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

MultipleDBConfig.java

This file resides under package com.kode12.config.

Code

package com.kode12.config;

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 MultipleDBConfig {

	@Bean(name = "mysqlDb")
	@ConfigurationProperties(prefix = "spring.ds_mysql")
	public DataSource oracleDataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean(name = "mysqlJdbcTemplete")
	public JdbcTemplate jdbcTemplate(@Qualifier("mysqlDb") DataSource dsMySQL) {
		return new JdbcTemplate(dsMySQL);
	}
	
	@Bean(name = "postgresDb")
	@ConfigurationProperties(prefix = "spring.ds_post")
	public DataSource postgresDataSource() {
		return  DataSourceBuilder.create().build();
	}

	@Bean(name = "postgresJdbcTemplete")
	public JdbcTemplate postgresJdbcTemplate(@Qualifier("postgresDb") DataSource dsPostgres) {
		return new JdbcTemplate(dsPostgres);
	}
	
}

Explanation

The functions and annotations is responsible to load a respected configuration for PostgreSQL and MySQL. It is responsible to create JDBCTemplate object for both also.
Let’s look into all 4 functions one by one.

@Bean(name = "mysqlDb")
@ConfigurationProperties(prefix = "spring.ds_mysql")
public DataSource mysqlDataSource() {
	return DataSourceBuilder.create().build();
}

Line 1 of above snippet is responsible to create bean with name mysqlDb.
Line 2 helps @Bean to load all properties whose prefix id ds_mysql
Line 4 is actually creating initializing DataSource class and create Object with name mysqlDb.

@Bean(name = "mysqlJdbcTemplete")
public JdbcTemplate jdbcTemplate(@Qualifier("mysqlDb") DataSource dsMySQL) {
	return new JdbcTemplate(dsMySQL);
}

Line 1 again creating a new bean of type JdbcTemplate with name mysqlJdbcTemplete
In Line 2 we are passing DataSource as an argument, here we passed mysqlDB as a qualifier which is created in Line 1 of first code snippet.
Line 3 initializing JdbcTemplate class with the help of DataSource

@Bean(name = "postgresDb")
@ConfigurationProperties(prefix = "spring.ds_post")
public DataSource postgresDataSource() {
	return  DataSourceBuilder.create().build();
}

Line 1 of above snippet is responsible to create bean with name postgresDb.
Line 2 helps @Bean to load all properties whose prefix id ds_post
Line 4 is actually creating initializing DataSource class and create Object with name postgresDb.

@Bean(name = "postgresJdbcTemplete")
	public JdbcTemplate postgresJdbcTemplate(@Qualifier("postgresDb") DataSource dsPostgres) {
		return new JdbcTemplate(dsPostgres);
	}

Line 1 again creating a new bean of type JdbcTemplate with name postgresJdbcTemplete
In Line 2 we are passing DataSource as an argument, here we passed postgresDb as a qualifier which is created in Line 1 of above code snippet.
Line 3 initializing JdbcTemplate class with the help of DataSource

DemoController.java

This file resides under com.kode12.controller package.

Code

package com.kode12.controller;

import java.util.HashMap;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class DemoController {

	@Autowired
	@Qualifier("postgresJdbcTemplete")
	private JdbcTemplate postgresTemplate;

	@Autowired
	@Qualifier("mysqlJdbcTemplete")
	private JdbcTemplate mysqlTemplate;

	@RequestMapping(value = "/getPGUser")
	public String getPGUser() {
		Map<String, Object> map = new HashMap<String, Object>();
		String query = " select * from usermaster";
		try {
			map = postgresTemplate.queryForMap(query);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "PostgreSQL Data: " + map.toString();
	}

	@RequestMapping(value = "/getMYUser")
	public String getMYUser() {
		Map<String, Object> map = new HashMap<String, Object>();
		String query = " select * from usermaster";
		try {
			map = mysqlTemplate.queryForMap(query);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "MySQL Data: " + map.toString();
	}

}

Explanation

This class declares with @RestController annotation means all methods declared in this class is producing a Response in body by default.

@Autowired
@Qualifier("postgresJdbcTemplete")
private JdbcTemplate postgresTemplate;

The code snippet shown above is responsible to create object of JdbcTemplate. @Qualifier helps to generate template of given type, here we provided postgresJdbcTemplete as Qualifier argument so it tries to load Bean which is created by jdbcTemplate(…) method of MultipleDBConfig class.

Now spring will invoke different jdbc template depend on your request. On invocation of url /getPGUser spring will use Postgres Template and on invocation of /getMYUser spring will use MySQL Template.

Here we used queryForMap(String query) method to get data from database using jdbc template, queryForMap(…) method is return a map where key is column name and value is actual column value.

Demo

To execute a demo execute main(…) method from class MultipleDbApplication. And hit below url in your favourite browser to get output
Url: http://localhost:6060/getMYUser
Once you hit above url it will query to user database of MySQL and return data in string format.

Url: http://localhost:6060/getPGUser
Once you hit above url it will query to user database of PostgreSQL and return data in string format.

Share current post by copy: http://goo.gl/kDjOaY

Thanks for reading.

Tags: Java, MicroServices, Spring Boot, SQL

Spring Boot: Working with multiple SQL database

Problem Statement

Developer Perquisites

Tools Perquisites

Database Setup

PostgreSQL

Create Statement

Insert Statement

MySQL

Create Statement

Insert Statement

Project Setup

pom.xml

Code

application.properties

Code

Explanation

MultipleDbApplication.java

Code

MultipleDBConfig.java

Code

Explanation

DemoController.java

Code

Explanation

Demo

Leave a Reply Click here to cancel reply.

Recent Posts

Promo

Tags

Viewer’s Map

Archives