When developing enterprise applications we are frequently confronted with the challenge of accessing multiple databases. Perhaps our application must archive data into some data warehouse, or maybe it must propagate data to some third party database. With Spring it is easy enough to define a common data source, but once we introduce multiple data sources it gets a bit tricky.

In this article we will demo a technique for accessing multiple databases in Spring Boot applications easily and with minimum configuration, by developing a SpringMVC application using Spring Boot.

Database Setup

To follow this demo we suggest you have two databases available; here we are using PostgreSQL and MySQL.

The script below shows a create and insert statement for both database providers.

PostgreSQL

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

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

MySQL

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 INTO `kode12`.`usermaster` 
  (`id`, `name`, `emailid`, `phoneno`, `location`)
VALUES
  ('1', 'name_mysql', 'test@tset.com', '9876543210', 'IN');

Project Setup

We will use Spring Tool Suite (STS) to build this demo:

• Click File -> New -> Spring Starter Project option.
• In the dialog provide project name, Maven coordinate, description and package information, and click Next.
• For the boot dependency, select Web and click next.
• Click Finish. STS will download the project from the Spring repo with your dependency.

The project should look like this:

Now let’s take a closer look at each file in detail.

pom.xml

pom contains all required dependency and plugin mappings.

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.aegis</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>

Explanation

The table below details all dependencies:

application.properties

Contains all configuration for the boot application; (in traditional Spring we would provide this configuration using multiple XML files.)

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 declares that your embedded server will start on 6060. (port.server.port is standard property provided by boot.)

Other properties, prefixed with spring.ds_* are user defined:

Properties prefixed with spring.ds_post.* are used to define configuration for PostgreSQL database.

Properties prefixed with spring.ds_mysql.* are used to define configuration for MySQL database.

MultipleDbApplication.java

package com.aegis;

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

@SpringBootApplication
public MultipleDbApplication {

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

This file contains our main method for launching our boot application. The @SpringBootApplication annotation is a combination of other Spring annotations and java annotations including:

@Configuration
@EnableAutoConfiguration
@ComponentScan
@Target(value={TYPE})
@Retention(value=RUNTIME)
@Documented
@Inherited

Additional annotations:

@Configuration
@EnableAutoConfiguration
@ComponentScan

These are Spring annotations that tell the container to scan this class and load our configuration.

MultipleDBConfig.java

package com.aegis.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 mysqlDataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean(name = "mysqlJdbcTemplate")
	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 = "postgresJdbcTemplate")
	public JdbcTemplate postgresJdbcTemplate(@Qualifier("postgresDb") 
                                              DataSource dsPostgres) {
		return new JdbcTemplate(dsPostgres);
	}
}

Explanation

This is the annotated configuration class, containing functions and annotations for loading our PostgreSQL and MySQL configuration. It is also responsible for creating a JDBCTemplate instance for each.

Let’s look at each of those four functions:

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

Line 1 of the above snippet creates the mysqlDb bean.
Line 2 helps @Bean load all properties with prefix spring.ds_mysql
Line 4 creates and initializes the DataSource class and creates the mysqlDb DataSource object.

1   @Bean(name = "mysqlJdbcTemplate")
2   public JdbcTemplate jdbcTemplate(@Qualifier("mysqlDb") DataSource dsMySQL) {
3   	return new JdbcTemplate(dsMySQL);
4   }

Line 1 creates a new bean of type JdbcTemplate with name mysqlJdbcTemplate
Line 2 accepts a DataSource argument and mysqlDB as a qualifier, which was created in Line 1 of the first code snippet.
Line 3 initializes a JdbcTemplate instance with the help of the DataSource object.

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

Line 1 of above snippet creates the postgresDb DataSource instance.
Line 2 helps @Bean load all properties with prefix spring.ds_post
Line 4 creates and initializes the postgresDb DataSource instance.

1  @Bean(name = "postgresJdbcTemplate")
2  public JdbcTemplate postgresJdbcTemplate(@Qualifier("postgresDb")
                                          DataSource dsPostgres) {
3    return new JdbcTemplate(dsPostgres);
4  }

Line 1 creates a new bean of type JdbcTemplate with name postgresJdbcTemplate
Line 2 accepts a DataSource as an argument, and a postgresDb as a qualifier, both created above.
Line 3 initializes a JdbcTemplate instance with the help of the DataSource object.

DemoController.java

package com.aegis.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("postgresJdbcTemplate")
	private JdbcTemplate postgresTemplate;

	@Autowired
	@Qualifier("mysqlJdbcTemplate")
	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 @RestController class annotation indicates that the results of all methods declared in this class will be bound to the response body by default.

The code snippet shown above creates a JdbcTemplate instance. @Qualifier helps to generate a template of the specified type. Here we provide postgresJdbcTemplate as the Qualifier argument so it tries to load the Bean created by the jdbcTemplate(…) method of the MultipleDBConfig instance.

Now Spring will invoke the appropriate jdbc template depending on your request. On invocation of the url /getPGUser Spring will use the Postgres template; on invocation of /getMYUser Spring will use the MySQL Template.

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

Here we used the queryForMap(String query) method to get data from database using jdbc template, queryForMap(…) method returns a map with column name as the key and mapped to the actual column value.

Demo

To launch the demo execute the main (…) method from class MultipleDbApplication. Then hit the URL below in your favourite browser:

Url: http://localhost:6060/getMYUser

Invoking that URL will query the user database of MySQL and return data as a string.

Url: http://localhost:6060/getPGUser

Invoking that URL will query the user database of PostgreSQL and return data as a string.

About the Author

Aaron Jacobson is a veteran of the Java web development space, having worked as a Java developer in outsourcing/consulting firm Technoligent for the last 10 years. His major contributions include a collection of web solutions for Java, Python, Asp.Net, and mobile apps, among others. You can follow Aaron on twitter @Techno_Ligent and Facebook @TechnoLigent.