Spring Boot: Working with multiple SQL database
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
|
1 2 3 4 5 6 7 8 |
CREATE TABLE usermaster ( id integer, name character varying, emailid character varying, phoneno character varying(10), location character varying ) ; |
Insert Statement
|
1 2 3 |
INSERT INTO usermaster( id, name, emailid, phoneno, location) VALUES (1, 'name_postgres', 'email@email.com', '1234567890', 'IN'); |
MySQL
Create Statement
|
1 2 3 4 5 6 7 8 |
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
|
1 2 3 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
<?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
|
1 2 3 4 5 6 7 8 9 10 11 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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.
|
1 2 3 4 5 |
@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.
|
1 2 3 4 |
@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
|
1 2 3 4 5 |
@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.
|
1 2 3 4 |
@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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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.
|
1 2 3 |
@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.
