Multiple Data Sources in Spring Data JPA and Spring Boot MVC

Create Spring MVC Project

On the Eclipse, create a Spring MVC project

Enter Project Information:

• Name: LearnSpringMVCWithRealApps
• Group: com.demo
• Artifact: LearnSpringMVCWithRealApps
• Description: Learn Spring MVC with Real Apps
• Package: com.demo

Select the technologies and libraries to be used:

• Web
• JPA
• MySQL

Click Next button to show Site Information for project

Click Finish button to finish create Spring MVC project

Configure pom.xml

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.6.1</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.demo</groupId>
	<artifactId>LearnSpringMVCWithRealApps</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>LearnSpringMVCWithRealApps</name>
	<description>Learn Spring MVC with Real Apps</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>

		<dependency>
			<groupId>javax.servlet.jsp.jstl</groupId>
			<artifactId>javax.servlet.jsp.jstl-api</artifactId>
			<version>1.2.1</version>
		</dependency>

		<dependency>
			<groupId>taglibs</groupId>
			<artifactId>standard</artifactId>
			<version>1.1.2</version>
		</dependency>

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

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</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>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

	</dependencies>

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

</project>				

Create Databases

Database1 Database

Create new database named database1. In this database create new table named Category as below:

--
-- Table structure for table `category`
--

CREATE TABLE `category` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`name`) VALUES
('Category 1'),
('Category 2'),
('Category 3');

Database2 Database

Create new database named database2. In this database create new table named Product as below:

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `price` double NOT NULL,
  `status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`name`, `price`, `status`) VALUES
('Product 1', 4.5, 1),
('Product 2', 7.8, 0),
('Product 3', 11, 0),
('Product 4', 22, 1);

Configure application.properties

spring.datasource.jdbc-url=jdbc:mysql://localhost:3306/database1
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.seconddatasource.jdbc-url=jdbc:mysql://localhost:3306/database2
spring.seconddatasource.username=root
spring.seconddatasource.password=
spring.seconddatasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.jpa.database=default
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true	

server.port=9596

spring.mvc.view.prefix=/WEB-INF/views/
spring.mvc.view.suffix=.jsp
spring.mvc.static-path-pattern=/resources/**

Models Classes

Create models classes for tables in database1 and database2 as below:

Category Model

Create new package named com.demo.database1.models. In this package, create new java class
named Category.java as below:

package com.demo.database1.models;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "category")
public class Category {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private int id;

	private String name;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

}					

Product Model

Create new package named com.demo.database2.models. In this package, create new java class
named Product.java as below:

package com.demo.database2.models;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "product")
public class Product {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private int id;

	private String name;

	private double price;

	private boolean status;

	public double getPrice() {
		return price;
	}

	public void setPrice(double price) {
		this.price = price;
	}

	public boolean isStatus() {
		return status;
	}

	public void setStatus(boolean status) {
		this.status = status;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

}						

Create Repository Interfaces

Create new repository interfaces as below:

CategoryRepository Interface

Create new package named com.demo.database1.repositories. In this package create
CategoryRepository interface implements from CrudRepository interface as below:

package com.demo.database1.repositories;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.demo.database1.models.Category;

@Repository
public interface CategoryRepository extends CrudRepository<Category, Integer> {
}	

ProductRepository Interface

Create new package named com.demo.database2.repositories. In this package create
ProductRepository interface implements from CrudRepository interface as below:

package com.demo.database2.repositories;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.demo.database2.models.Product;

@Repository
public interface ProductRepository extends CrudRepository<Product, Integer> {
}	

Create Services

Create new package named com.demo.services. In this package, create new services as below:

CategoryService Interface

In com.demo.services package, create CategoryService interface as below:

package com.demo.services;

import com.demo.database1.models.Category;

public interface CategoryService {

	public Iterable<Category> findAll();
	
}	

CategoryServiceImpl Class

In com.demo.services package, create CategoryServiceImpl class implements from
CategoryService interfaces as below:

package com.demo.services;

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

import com.demo.database1.models.Category;
import com.demo.database1.repositories.CategoryRepository;

@Service
public class CategoryServiceImpl implements CategoryService {

	@Autowired
	private CategoryRepository categoryRepository;
	
	@Override
	public Iterable<Category> findAll() {
		return categoryRepository.findAll();
	}

}	

ProductService Interface

In com.demo.services package, create ProductService interface as below:

package com.demo.services;

import com.demo.database2.models.Product;

public interface ProductService {

	public Iterable<Product> findAll();
	
}	

ProductServiceImpl Class

In com.demo.services package, create ProductServiceImpl class implements from
ProductService interfaces as below:

package com.demo.services;

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

import com.demo.database2.models.Product;
import com.demo.database2.repositories.ProductRepository;

@Service
public class ProductServiceImpl implements ProductService {

	@Autowired
	private ProductRepository productRepository;
	
	@Override
	public Iterable<Product> findAll() {
		return productRepository.findAll();
	}

}	

Create Controllers

Create new package named com.demo.controllers. In this package, create controllers as below:

DemoController

Create new java class, named DemoController.java

package com.demo.controllers;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.demo.services.CategoryService;
import com.demo.services.ProductService;

@Controller
@RequestMapping(value = { "", "demo" })
public class DemoController {

	@Autowired
	private ProductService productService;
	
	@Autowired
	private CategoryService categoryService;
	
	@RequestMapping(value = { "", "index" }, method = RequestMethod.GET)
	public String index(ModelMap modelMap) {
		modelMap.put("products", productService.findAll());
		modelMap.put("categories", categoryService.findAll());
		return "demo/index";
	}
	
}					

Create Views

Create new folders with path webapp\WEB-INF\views in src\main. In views folder,
create views as below:

Index View

Create new folder named demo. Create new jsp file named index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1" isELIgnored="false"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
	<head>
		<meta charset="ISO-8859-1">
		<title>Insert title here</title>
	</head>
	<body>

		<h3>Categories List</h3>
		<table border="1">
			<tr>
				<th>Id</th>
				<th>Name</th>
			</tr>
			<c:forEach var="category" items="${categories }">
				<tr>
					<td>${category.id }</td>
					<td>${category.name }</td>
				</tr>
			</c:forEach>
		</table>
		
		<h3>Product List</h3>
		<table border="1">
			<tr>
				<th>Id</th>
				<th>Name</th>
				<th>Price</th>
				<th>Status</th>
			</tr>
			<c:forEach var="product" items="${products }">
				<tr>
					<td>${product.id }</td>
					<td>${product.name }</td>
					<td>${product.price }</td>
					<td>${product.status }</td>
				</tr>
			</c:forEach>
		</table>

	</body>
</html>

Create Configurations

Create new package named com.demo.configurations, create new configurations as below:

Database1 Configuration

In com.demo.configurations package, create new java class named Database1Configuration.java as below:

package com.demo.configurations;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableAutoConfiguration
@EnableTransactionManagement
@EnableJpaRepositories(
	entityManagerFactoryRef = "entityManagerFactory", 
	transactionManagerRef = "transactionManager", 
	basePackages = { "com.demo.database1.repositories" }
)
public class Database1Configuration {

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

	@Primary
	@Bean(name = "entityManagerFactory")
	public LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean(EntityManagerFactoryBuilder entityManagerFactoryBuilder, @Qualifier("dataSource") DataSource dataSource) {
		return entityManagerFactoryBuilder.dataSource(dataSource).packages("com.demo.database1.models")
				.persistenceUnit("database1").build();
	}

	@Primary
	@Bean(name = "transactionManager")
	public PlatformTransactionManager platformTransactionManager(@Qualifier("entityManagerFactory") EntityManagerFactory entityManagerFactory) {
		return new JpaTransactionManager(entityManagerFactory);
	}

}				

Database2 Configuration

In com.demo.configurations package, create new java class named Database2Configuration.java as below:

package com.demo.configurations;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableAutoConfiguration
@EnableTransactionManagement
@EnableJpaRepositories(
	entityManagerFactoryRef = "database2EntityManagerFactory", 
	transactionManagerRef = "database2TransactionManager", 
	basePackages = { "com.demo.database2.repositories" }
)
public class Database2Configuration {

	@Bean(name = "secondDataSource")
	@ConfigurationProperties(prefix = "spring.seconddatasource")
	public DataSource dataSource() {
		return DataSourceBuilder.create().build();
	}

	@Bean(name = "database2EntityManagerFactory")
	public LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean(EntityManagerFactoryBuilder entityManagerFactoryBuilder, @Qualifier("secondDataSource") DataSource dataSource) {
		return entityManagerFactoryBuilder.dataSource(dataSource).packages("com.demo.database2.models")
				.persistenceUnit("database2").build();
	}

	@Bean(name = "database2TransactionManager")
	public PlatformTransactionManager platformTransactionManager(@Qualifier("database2EntityManagerFactory") EntityManagerFactory entityManagerFactory) {
		return new JpaTransactionManager(entityManagerFactory);
	}

}					

Structure of Spring MVC Project

Run Application

Select LearnSpringMVCWithRealAppsApplication.java file in com.demo package, right click
and select Run As/Spring Boot App menu

Access index method in demo controller with following url:
http://localhost:9596/demo/index

Output