📝 [PART 1] Spring Boot - Multiple datasource (database) with JDBC and JPA

springboot jpa jdbc mssql postgresql data entities repository database spring datasource hibernate

📅 | ⏰ 3 minutes

Assalamualaikum and hello! Spring Boot’s streamlined development process makes it a popular choice for many developers. However, configuring it for multiple databases can present challenges. While there are various tutorials available, I’ve encountered issues with their completeness and clarity. In this tutorial, I’ll provide a detailed solution to this problem.

Database

We require a connection to two databases: an existing MSSQL database and a new PostgreSQL database. The PostgreSQL database will be used for all standard CRUD operations (create, read, update, delete). The MSSQL database will be accessed in a read-only manner to retrieve specific data without making any modifications to its structure or content.

Database :
 - PostgreSQL (CRUD) using JPA
 - MsSQL (Read only) using JDBC

Application properties

Spring Boot allows configuration through either YAML (*.yml) or properties (*.properties) files. While YAML offers advantages, I prefer the familiarity and readability of properties files for this project.

Here’s my application.properties file configuration:

spring.profiles.active=dev
spring.main.banner-mode=off

spring.application.name=PACKINGmiddleware
server.port=9000
spring.devtools.add-properties=TRUE

# PostgreSQL configuration ===============================================
spring.datasource.postgresql.url=jdbc:postgresql://127.0.0.1:5432/springbootdb
spring.datasource.postgresql.username=robbi
spring.datasource.postgresql.password=password
spring.datasource.postgresql.driver-class-name=org.postgresql.Driver
spring.datasource.postgresql.hikari.poolName=postgresqlPool
spring.datasource.postgresql.hikari.maximum-pool-size=10
# JPA settings for PostgreSQL
spring.jpa.postgresql.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
#spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

# MSSQL configuration ====================================================
spring.datasource.mssql.url=jdbc:sqlserver://;serverName=127.0.0.1;databaseName=TST;encrypt=false;trustServerCertificate=false
spring.datasource.mssql.username=robbi
spring.datasource.mssql.password=password
spring.datasource.mssql.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.mssql.hikari.poolName=mssqlPool
spring.datasource.mssql.hikari.maximum-pool-size=10
spring.datasource.mssql.hikari.read-only=true  # Ensures the datasource is read-only
# MSSQL JPA settings (You can remove the settings from properties, as they are moved to the config class)
#spring.jpa.mssql.show-sql=true
spring.jpa.mssql.hibernate.ddl-auto=none

In order to manage connections to both PostgreSQL and MSSQL databases, we’ve opted to use custom property prefixes: spring.datasource.postgresql.* and spring.datasource.mssql.*. These prefixes aren’t recognized by Spring Boot out-of-the-box, so we’ll need to create custom @Bean definitions to explicitly configure and register these data sources within the Spring application context.

File and folder structure

src
  └ main
    └ java 
      └ com.robbi.demo                          # This directory contains the source code for your application. 
        └ config                                # This package contains configuration classes for your application beans.
            └ DatasourceConfigurationPostgreSQL # This class configures the connection to your PostgreSQL database.
            └ DatasourceConfigurationMsSQL      # This class configures the connection to your MSSQL database.
            └ DataSourceJPAConfigPostgreSQL     # This class configures JPA for the PostgreSQL datasource.
            └ DataSourceJPAConfigMsSQL          # This class configures JPA for the MSSQL datasource.
        └ controller                            # This package contains your application controllers, which define REST API requests.      
            └ MsSqlController
            └ PostgreController
        └ Service                               # This package contains your application service.
            └ MsSqlService                      
            └ PostgreSqlService            
        └ source                                # This package contains your JPA/JDBC related classes. It is further divided into PostgreSQL and MSSQL.  
            └ postgresql
                └ model                         # This subdirectory likely contains entity classes related to your PostgreSQL database
                └ repo                          # This subdirectory likely contains JPA repository classes for your PostgreSQL entities.
            └ mssql
                └ model                         # Normally we don't have model for JDBC
                └ repo                          # This subdirectory likely contains JDBC repository classes for your MSSQL entities
                └ dto                           # Because we don't have entity model, we use DTO to represent query object 
    └ resources                    
        └ application.properties                # where we define configs for our app

POM file

Here my POM for your reference

<?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>3.3.4</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.robbi.demo</groupId>
	<artifactId>PACKINGmiddleware</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>PACKINGmiddleware</name>
	<description>PACKINGmiddleware</description>
	<url/>
	<licenses>
		<license/>
	</licenses>
	<developers>
		<developer/>
	</developers>
	<scm>
		<connection/>
		<developerConnection/>
		<tag/>
		<url/>
	</scm>
	<properties>
		<java.version>21</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</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>
		
		<!-- ## DATABASE ## -->
		<!-- JDBC -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jdbc</artifactId>
		</dependency>
		<!-- Spring Data JPA -->
	    <dependency>
	        <groupId>org.springframework.boot</groupId>
	        <artifactId>spring-boot-starter-data-jpa</artifactId>
	    </dependency>
		<!-- (Optional) Hibernate Validator for JPA entities -->
	    <dependency>
		    <groupId>org.hibernate.validator</groupId>
		    <artifactId>hibernate-validator</artifactId>
		</dependency>
		<!-- PostgreSQL -->
		<dependency>
		    <groupId>org.postgresql</groupId>
		    <artifactId>postgresql</artifactId>
		    <scope>runtime</scope>
		</dependency>
		<!-- MSSQL -->
		<dependency>
		    <groupId>com.microsoft.sqlserver</groupId>
		    <artifactId>mssql-jdbc</artifactId>
		    <scope>runtime</scope>
		</dependency>
		
		<!-- LOMBOK -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
				
		<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-actuator -->
		<dependency>
		    <groupId>org.springframework.boot</groupId>
		    <artifactId>spring-boot-starter-actuator</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-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/com.github.gavlyukovskiy/datasource-proxy-spring-boot-starter -->
		<dependency>
		    <groupId>com.github.gavlyukovskiy</groupId>
		    <artifactId>datasource-proxy-spring-boot-starter</artifactId>
		    <version>1.9.2</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
			
			<!-- This is needed to allow mvn install/package running properly with LOMBOK | reference : https://stackoverflow.com/a/72341767 -->
			<plugin>
		      <groupId>org.apache.maven.plugins</groupId>
		      <artifactId>maven-compiler-plugin</artifactId>
		      <configuration>
		        <annotationProcessors>
		          <annotationProcessor>lombok.launch.AnnotationProcessorHider$AnnotationProcessor</annotationProcessor>
		        </annotationProcessors>
		      </configuration>
		    </plugin>
		    
		</plugins>
	</build>

</project>

This post is getting a bit long, so I’ll break it up into a series, to improve readability. Keep an eye out for Part 2, where we’ll delve deeper into PostgreSQL connecton and configuration. Please refer to PART 2 for a continuation of this discussion.

Posted by: Hugo

Edit

Have some thoughts, discussion or feedback on this post?

💬 Send me an email

What is webmention? How to send interactions!

Below you can find all of webmention with this page. Which means, you also can mentioned this URL on any website that support WebMention. Have you written a response to this post? Let me know the URL:

Do you use a website that don't have WebMention capabilities? You can just use Comment Parade!



⬆️