Multiple Datasource in Spring Boot and JdbcTemplate - Spring Boot Tutorials for Beginners - Learning to Write code for Beginners with Tutorials

Overview

This sample demonstrates how to configure multiple datasources with multiple databases and JdbcTemplate in Spring Boot Application.
In this example, we will retrieve all users from the user1 table in database 1, then append to all users retrieved from user2 table in database 2.
Finally, we display them to jsp file. We'll integrate Spring Boot with Bootstrap 4 & jQuery using Web Jar.

Multiple Datasource in Spring Boot and JdbcTemplate

Prerequisites

Eclipse Oxygen and Install Spring Tool Suite for Eclipse IDE
Spring Boot v2.0.1.RELEASE
spring-boot-starter-web
spring-boot-starter-jdbc
spring-boot-devtools
spring-boot-configuration-processor
jstl
org.webjars Bootstrap 4
tomcat-embed-jasper
Java 1.8+

Create database and tables in MySQL Databases

We'll create two databases called db1 and db2.

CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */;

Let's create two tables called user1 in db1 and user2 in db2.

DROP TABLE IF EXISTS `db1`.`user1`;
CREATE TABLE  `db1`.`user1` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(100) NOT NULL default '',
  `email` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `db2`.`user2`;
CREATE TABLE  `db2`.`user2` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(100) NOT NULL default '',
  `email` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

Project Directory Structure

The following screenshot shows final structure of the project.

Start to create a web application with Spring Boot

Launch Eclipse IDE. Go to File -> New -> Other... Select Spring Starter Project under Spring Boot category then click Next as shown below.

Enter the content as shown below
Click Next

In the next step

Click Finish.

Project Dependencies

We will add the following dependencies to the pom.xml file.
jstl

<dependency>
 <groupId>javax.servlet</groupId>
 <artifactId>jstl</artifactId>
</dependency>

Bootstrap 4.0.0

  <dependency>
   <groupId>org.webjars</groupId>
   <artifactId>bootstrap</artifactId>
   <version>4.0.0</version>
  </dependency>

apache.tomcat.embed

  <dependency>
   <groupId>org.apache.tomcat.embed</groupId>
   <artifactId>tomcat-embed-jasper</artifactId>
   <scope>provided</scope>
  </dependency>

mysql-connector-java

  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.46</version>
  </dependency>

spring-boot-configuration-processor

  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-configuration-processor</artifactId>
   <optional>true</optional>
  </dependency>

The updated pom.xml file will have the following 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.jackrutorial</groupId>
 <artifactId>SpringBootMultipleDatabases</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <packaging>jar</packaging>

 <name>SpringBootMultipleDatabases</name>
 <description>Demo project for Spring Boot</description>

 <parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>2.1.0.BUILD-SNAPSHOT</version>
  <relativePath/> <!-- lookup parent from repository -->
 </parent>

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

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

  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-devtools</artifactId>
   <scope>runtime</scope>
  </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-configuration-processor</artifactId>
   <optional>true</optional>
  </dependency>
  <dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>jstl</artifactId>
  </dependency>
  <dependency>
   <groupId>org.webjars</groupId>
   <artifactId>bootstrap</artifactId>
   <version>4.0.0</version>
  </dependency>
  <dependency>
   <groupId>org.apache.tomcat.embed</groupId>
   <artifactId>tomcat-embed-jasper</artifactId>
   <scope>provided</scope>
  </dependency>
  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.46</version>
  </dependency>
 </dependencies>

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

 <repositories>
  <repository>
   <id>spring-snapshots</id>
   <name>Spring Snapshots</name>
   <url>https://repo.spring.io/snapshot</url>
   <snapshots>
    <enabled>true</enabled>
   </snapshots>
  </repository>
  <repository>
   <id>spring-milestones</id>
   <name>Spring Milestones</name>
   <url>https://repo.spring.io/milestone</url>
   <snapshots>
    <enabled>false</enabled>
   </snapshots>
  </repository>
 </repositories>

 <pluginRepositories>
  <pluginRepository>
   <id>spring-snapshots</id>
   <name>Spring Snapshots</name>
   <url>https://repo.spring.io/snapshot</url>
   <snapshots>
    <enabled>true</enabled>
   </snapshots>
  </pluginRepository>
  <pluginRepository>
   <id>spring-milestones</id>
   <name>Spring Milestones</name>
   <url>https://repo.spring.io/milestone</url>
   <snapshots>
    <enabled>false</enabled>
   </snapshots>
  </pluginRepository>
 </pluginRepositories>


</project>

Configuring Spring Boot for MySQL and view resolvers

In the sources folder, we will look for this project's a resource file under src/main/resources/application.properties. Open application.properties file and add the following properties.

#first db
spring.datasource.jdbcUrl=jdbc:mysql://localhost:3306/db1
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driverClassName=com.mysql.jdbc.Driver

#second db
spring.second-db.jdbcUrl=jdbc:mysql://localhost:3306/db2
spring.second-db.username=root
spring.second-db.password=root
spring.second-db.driverClassName=com.mysql.jdbc.Driver

#jsp
spring.mvc.view.prefix=/WEB-INF/jsp/
spring.mvc.view.suffix=.jsp

WebConfig

Create a WebConfig class under com.jackrutorial.config package with the following code.

WebConfig.java

package com.jackrutorial.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

@Configuration
public class WebConfig {
 
 @Bean(name = "db1")
 @ConfigurationProperties(prefix = "spring.datasource")
 public DataSource dataSource1() {
  return DataSourceBuilder.create().build();
 }

 @Bean(name = "jdbcTemplate1")
 public JdbcTemplate jdbcTemplate1(@Qualifier("db1") DataSource ds) {
  return new JdbcTemplate(ds);
 }
 
 @Bean(name = "db2")
 @ConfigurationProperties(prefix = "spring.second-db")
 public DataSource dataSource2() {
  return  DataSourceBuilder.create().build();
 }

 @Bean(name = "jdbcTemplate2")
 public JdbcTemplate jdbcTemplate2(@Qualifier("db2") DataSource ds) {
  return new JdbcTemplate(ds);
 }
}

Model

Create a User class under com.jackrutorial.model package with the following code.

User.java

package com.jackrutorial.model;

public class User {
 private Integer id;
 private String username;
 private String email;
 
 public Integer getId() {
  return id;
 }
 public void setId(Integer id) {
  this.id = id;
 }
 public String getUsername() {
  return username;
 }
 public void setUsername(String username) {
  this.username = username;
 }
 public String getEmail() {
  return email;
 }
 public void setEmail(String email) {
  this.email = email;
 }
}

DAO Layer

Create a UserDaoImpl class under com.jackrutorial.dao package and write the following code in it.
UserDaoImpl.java

package com.jackrutorial.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.jackrutorial.model.User;

@Transactional
@Repository
public class UserDaoImpl {
 
 @Autowired
 @Qualifier("jdbcTemplate1")
 private JdbcTemplate jdbcTemplate1;
 
 @Autowired
 @Qualifier("jdbcTemplate2")
 private JdbcTemplate jdbcTemplate2;

 public List getAllUser() {
  String sql1 = "select username,email from user1";
  //get users list from db1
  List list1 = jdbcTemplate1.query(sql1, new UserRowMapper());
  
  String sql2 = "select username,email from user2";
  //get users list from db2
  List list2 = jdbcTemplate2.query(sql2, new UserRowMapper());
  
  List listAll = Stream.concat(list1.stream(), list2.stream())
       .collect(Collectors.toList());
  return listAll;
 }
 
 class UserRowMapper implements RowMapper{

  @Override
  public User mapRow(ResultSet rs, int rowNum) throws SQLException {
   User user = new User();
   user.setUsername(rs.getString("username"));
   user.setEmail(rs.getString("email"));
   
   return user;
  }
  
 }

}

In code snippet above, we use jdbcTemplate1.query(sql1, new UserRowMapper()) method to query data from user1 table in database1 and jdbcTemplate2.query(sql2, new UserRowMapper()) method to query data from user2 table in database2.

Service Layer

Create a UserService class under com.jackrutorial.service package and write the following code in it.

package com.jackrutorial.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jackrutorial.dao.UserDaoImpl;
import com.jackrutorial.model.User;

@Service
public class UserService {

 @Autowired
 private UserDaoImpl userDao;
 
 public List<User> getAllUser() {
  return userDao.getAllUser();
 }
}

UserController Controller

We create a controller class named UserController to integrate with the Database using the UserService class.
Create a UserController class under com.jackrutorial.controller package and write the following code in it.

package com.jackrutorial.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import com.jackrutorial.model.User;
import com.jackrutorial.service.UserService;


@Controller
@RequestMapping("/user")
public class UserController {

 @Autowired
 private UserService userService;
 
 @RequestMapping(value = { "/", "" }, method = RequestMethod.GET)
 public ModelAndView getAllUsers() {
  ModelAndView model = new ModelAndView();
  
  List>User> usersList = userService.getAllUser();
  model.addObject("usersList", usersList);
  
  model.setViewName("users_list");
  return model;
 }
}

In code snippet above, we set @RequestMapping("/user") and RequestMethod = GET. If a URI pattern /user is requested, it will map to this UserController, and handle the request with getAllUsers() method.

View Layer

Create jsp folder under src\main\webapp\WEB-INF\ folder.
Create users_list.jsp file under src\main\webapp\WEB-INF\jsp\ folder and write the following code in it. users_list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
 pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://www.springframework.org/tags" prefix="spring"%>
<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form" %> 

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <title>Users</title>
 <link href="../webjars/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet" />
 <script src="../webjars/bootstrap/4.0.0/js/bootstrap.min.js"></script>
 <script src="../webjars/jquery/3.0.0/jquery.min.js"></script>
</head>
<body>
 <div class="container">
  <div class="card-deck mt-2">
   <div class="card">
    <div class="card-header">
        <span class="font-weight-bold">Users List</span>
    </div>
    <div class="card-body">
     <table class="table table-striped" cellspacing="0" width="100%">
      <thead>
       <tr>
        <th scope="row">Username</th>
        <th scope="row">Email</th>
       </tr>
      </thead>
      <tbody>
       <c:forEach items="${usersList }" var="user" >
        <tr>
         <td>${user.username }</td>
         <td>${user.email }</td>
        </tr>
       </c:forEach>
      </tbody>
     </table>
    </div>
   </div>
  </div>
 </div>
</body>
</html>

Run Spring Boot Application

Right click to the Project and follow the below steps:

select Run As -> Maven clean.
select Run As -> Maven install.
select Run As -> Spring Boot App.

View console output in eclipse, you will see following output:

INFO 27056 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
INFO 27056 --- [  restartedMain] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 1502 ms
INFO 27056 --- [  restartedMain] o.s.b.w.servlet.ServletRegistrationBean  : Servlet dispatcherServlet mapped to [/]
INFO 27056 --- [  restartedMain] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'characterEncodingFilter' to: [/*]
INFO 27056 --- [  restartedMain] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]
INFO 27056 --- [  restartedMain] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'httpPutFormContentFilter' to: [/*]
INFO 27056 --- [  restartedMain] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'requestContextFilter' to: [/*]
INFO 27056 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
INFO 27056 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
INFO 27056 --- [  restartedMain] j.SpringBootMultipleDatabasesApplication : Started SpringBootMultipleDatabasesApplication in 2.397 seconds (JVM running for 3.296)

Demo

Type the following URLs in browser's address bar to open user page

http://localhost:8080/user

Multiple Datasource in Spring Boot and JdbcTemplate - Spring Boot Tutorials for Beginners