BT

Ready for InfoQ 3.0? Try the new design and let us know what you think!

Working with Multiple Databases in Spring

| Posted by Aaron Jacobson Follow 0 Followers on Jul 30, 2016. Estimated reading time: 7 minutes |

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:

Dependency

Detail

spring-boot-starter-web

Provides support for web development and MVC.

spring-boot-starter-test

Provides test dependencies like JUnit, Mockito etc.

spring-boot-starter-jdbc

Provide support for JDBC

Postgresql

JDBC drivers for PostgreSQL database

mysql-connector-java

JDBC drivers for MySQL database

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.

Rate this Article

Adoption Stage
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

It needs @Primary by Farrukhjon Sattorov

Good post.
But I should notice two things. First, two data source bean were not initialized in my case without @Primary annotation on the bean declaration. Second, if you apply @Primary on one of the data source bean declaration you can get rid from some @Qualifier.
Here is my solution:
---
@Configuration
public class MultiDataSourceConfiguration {

@Bean
@Primary
@ConfigurationProperties(prefix = "app.datasource.mysql")
public DataSource mysqlDataSource() {
DataSource dataSource = DataSourceBuilder.create().build();
return dataSource;
}

@Bean
@ConfigurationProperties(prefix = "app.datasource.postgres")
public DataSource postgresDataSource() {
return DataSourceBuilder.create().build();
}

@Bean
public JdbcTemplate mysqlJdbcTemplate(DataSource mysqlDataSource) {
return new JdbcTemplate(mysqlDataSource);
}

@Bean
public JdbcTemplate postgresJdbcTemplate(@Qualifier("postgresDataSource") DataSource postgresDataSource) {
return new JdbcTemplate(postgresDataSource);
}

}
---
Here: github.com/Farrukhjon/experimental-samples/comm...
---
Thank you!

JDBCTemplate is not the hard part by Josh Wand

It should be pretty easy to understand creating simple jdbctemplates that can be swapped about.. when you're writing raw sql many things are possible. Show me how to do this with Spring Data @Repositories and/or JPA and it'll have more value in the real world.

Spring Multiple Database Two Way Handle by Aaron Jacobson

It is not handle on JTA operation but rather than just merging with separate multiple database and spring database.

Example using EntityManager by Bruno Duarte

Hi Aaron, thanks for the article, your example helped me a lot.
But what if i need to use multiple EntityManager instead of JdbcTemplate ?

I have a GenericDAO file that has one EntityManager with the @PersistenceContext annotation.
And i have multiple DAOs that extend from this GenericDAO. Each one of these extended DAOs must access a different database.


GenericDAO.class:
public abstract class GenericDAO<T extends Serializable> {
@PersistenceContext
protected EntityManager entityManager;
................
}

UserDAO.class:
@Repository
public class UserDAO extends GenericDAO<User> {
............... must persist to primary data source
}

ProductDAO.class:
@Repository
public class ProductDAO extends GenericDAO<Product> {
............... must persist to secondary data source
}

Problem is that when i invoke the " "entityManager.persist()" method it always use the @Primary datasource.
How can i specify which DataSource to use with the EntityManager ?


Again, thank you !

Re: It needs @Primary by zhu guowei

Actually if you explicitly set spring.datasource.initialize=false you do not need use @primary . Please see stackoverflow.com/questions/44757388/why-qualif...

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

5 Discuss
BT