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

We start with postgreSQL because this our primary database connection and we do all CRUD stuff from here. We need 2 class, which use for connection and configuration of datasource.

DatasourceConfigurationPostgreSQL

package com.robbi.demo.config.postgresql;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
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 com.robbi.demo.utils.LoggerFactoryUtil;

@Configuration
public class DatasourceConfigurationPostgreSQL {
	private static final Logger LOG = LoggerFactoryUtil.getLogger();
	
    @Bean
    @ConfigurationProperties("spring.datasource.postgresql")
    public DataSourceProperties dataSourcePropertiesPostgreSQL() {
		LOG.info("spring.datasource.postgresql");
        return new DataSourceProperties();
    }
	
    @Bean
    @Primary
    public DataSource dataSourcePostgreSQL() {
		LOG.info("Initializing PostgreSQL DataSource: {}", dataSourcePropertiesPostgreSQL().getUrl());
	    return dataSourcePropertiesPostgreSQL()
	      .initializeDataSourceBuilder()
	      .build();
    }
}

Take note on anotation @ConfigurationProperties and @Primary that I applied here.

With @ConfigurationProperties("spring.datasource.postgresql"), this tells Spring to bind configuration properties starting with spring.datasource.postgresql to this bean. This @Primary is to tells Spring that if multiple DataSource beans exist, this one should be preferred for injection.

Overall, this code defines two beans :

• dataSourcePropertiesPostgreSQL - Holds the configuration properties for the PostgreSQL database connection, retrieved from the application.properties file.
• dataSourcePostgreSQL - The actual DataSource object used for connecting to the PostgreSQL database.

DataSourceJPAConfigPostgreSQL

package com.robbi.demo.config.postgresql;

import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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;

import com.robbi.demo.utils.LoggerFactoryUtil;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = "com.robbi.demo.source.postgresql.repo",
        entityManagerFactoryRef = "postgresqlEntityManagerFactory",
        transactionManagerRef = "postgresqlTransactionManager"
)
public class DataSourceJPAConfigPostgreSQL {
	private static final Logger LOG = LoggerFactoryUtil.getLogger();
	
	@Value("${spring.jpa.postgresql.hibernate.ddl-auto}")
	private String hibernateDdlAuto;
	
	@Bean
	@Primary
    public LocalContainerEntityManagerFactoryBean postgresqlEntityManagerFactory(
      @Qualifier("dataSourcePostgreSQL") DataSource dataSource,
      EntityManagerFactoryBuilder builder) {
		LOG.info("postgresqlEntityManagerFactory with dataSourcePostgreSQL : {}",dataSource);
		
		// Configure properties for Hibernate, including DDL auto-generation
	    Map<String, String> properties = new HashMap<>();
	    properties.put("hibernate.hbm2ddl.auto", hibernateDdlAuto); // Or "update", "create-drop" based on your requirement
	    properties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
	    
        return builder
          .dataSource(dataSource)
          .packages("com.robbi.demo.source.postgresql.model")
          .persistenceUnit("postgresql")  // Added this line for clarity
          .properties(properties)  // Add the Hibernate properties here
          .build();
    }

    @Bean
    public PlatformTransactionManager postgresqlTransactionManager(
      @Qualifier("postgresqlEntityManagerFactory") LocalContainerEntityManagerFactoryBean postgresqlEntityManagerFactory) {
    	LOG.info("postgresqlTransactionManager with postgresqlEntityManagerFactory : {}",postgresqlEntityManagerFactory);
        return new JpaTransactionManager(Objects.requireNonNull(postgresqlEntityManagerFactory.getObject()));
    }
}

This code configures JPA (Java Persistence API) for the PostgreSQL datasource defined earlier.

The class is named DataSourceJPAConfigPostgreSQL. This aligns with its purpose: configuring JPA for the PostgreSQL datasource.

It has three annotations:

• @Configuration: Marks it as a Spring configuration class.
• @EnableTransactionManagement: Enables transaction management for the application.
• @EnableJpaRepositories: Enables JPA repositories for a specific base package (com.robbi.demo.source.postgresql.repo). It also specifies references to the custom-named EntityManagerFactory and TransactionManager beans.

Hibernate DDL Auto-generation:

• This @Value("${spring.jpa.postgresql.hibernate.ddl-auto}") injects the value of the spring.jpa.postgresql.hibernate.ddl-auto property from the application.properties file. This property controls how Hibernate creates or updates database tables based on our entity classes

Method postgresqlEntityManagerFactory() :

• Uses @Bean to declare a bean of type LocalContainerEntityManagerFactoryBean. This bean factory builds the JPA EntityManagerFactory used for interacting with the database.
• I mark and annotates it with @Primary. This indicates it should be preferred if multiple EntityManagerFactory beans exist.
• Takes two arguments:
  • @Qualifier("dataSourcePostgreSQL") DataSource dataSource: Injects the dataSourcePostgreSQL bean defined in DatasourceConfigurationPostgreSQL.
  • EntityManagerFactoryBuilder builder: Provides a builder object to configure the EntityManagerFactory.

If you notice, i also create a HashMap named properties to store configuration properties for Hibernate, i currently insert 2 value here :

• hibernate.hbm2ddl.auto : This controls how Hibernate creates or updates tables based on your entity classes (“none”, “create”, “create-drop”, or “update”), which i pull from *.properties file.
• hibernate.dialect : set as org.hibernate.dialect.PostgreSQLDialect, specifying the dialect to use for interacting with PostgreSQL.

This method return builder to configure the EntityManagerFactory:

• Sets the dataSource property to the injected dataSource bean.
• Sets the packages property to scan for JPA entity classes in the package com.markono.tnf.source.postgresql.model. This is where your entity classes representing database tables reside.
• Sets the persistenceUnit : property to “postgresql”
• Builds and returns the LocalContainerEntityManagerFactoryBean object.

Method postgresqlTransactionManager() :

• Uses @Bean to declare a bean of type PlatformTransactionManager. This bean manages database transactions.
• Takes one argument:
  • @Qualifier("postgresqlEntityManagerFactory") LocalContainerEntityManagerFactoryBean postgresqlEntityManagerFactory: Injects the postgresqlEntityManagerFactory bean created earlier.
• Creates a new JpaTransactionManager object, passing in the retrieved EntityManagerFactory. This ensures transactions are managed within the context of the configured EntityManagerFactory.
• Returns the JpaTransactionManager bean.

The next part of this series will delve into the configuration of the MSSQL database. Stay tuned for Part 3 😁