Spring Boot – Working with multiple SQL database

Datetime:2016-08-22 23:14:47          Topic: MySQL  PostgreSQL           Share

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

<?xmlversion="1.0" encoding="UTF-8"?>
<projectxmlns="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>MultipleDBwithSpringBoot</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 DataSourceoracleDataSource() {
 return DataSourceBuilder.create().build();
 }
 
 @Bean(name = "mysqlJdbcTemplete")
 public JdbcTemplatejdbcTemplate(@Qualifier("mysqlDb") DataSourcedsMySQL) {
 return new JdbcTemplate(dsMySQL);
 }
 
 @Bean(name = "postgresDb")
 @ConfigurationProperties(prefix = "spring.ds_post")
 public DataSourcepostgresDataSource() {
 return  DataSourceBuilder.create().build();
 }
 
 @Bean(name = "postgresJdbcTemplete")
 public JdbcTemplatepostgresJdbcTemplate(@Qualifier("postgresDb") DataSourcedsPostgres) {
 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 DataSourcemysqlDataSource() {
 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 JdbcTemplatejdbcTemplate(@Qualifier("mysqlDb") DataSourcedsMySQL) {
 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 DataSourcepostgresDataSource() {
 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 JdbcTemplatepostgresJdbcTemplate(@Qualifier("postgresDb") DataSourcedsPostgres) {
 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 JdbcTemplatepostgresTemplate;
 
 @Autowired
 @Qualifier("mysqlJdbcTemplete")
 private JdbcTemplatemysqlTemplate;
 
 @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 JdbcTemplatepostgresTemplate;

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.





About List