Spring Data - JpaRepository using non-Primary EntityManager

Question

I've got two connections to the database in my application, one for Postgres, one for SQL Server. They use the same entity model and I want to use them to store same data in both databases.

I specify as beans appropriate LocalContainerEntityManagerFactoryBean s, PlatformTransactionManager s, and DataSources. I've got two set of repositories and I'd like one of them to use one database, and the other repository to use the second database. Yet both of my repositories are always use the @Primary set of the beans

I specify the package that contains either first or the second repository in the @EnableJpaRepositories annotation of the @Configuration class containing the beans like below. I was hoping this will make sure that the wiring will happen correctly. What am I doing incorrectly?

@EnableJpaRepositories(basePackages="dao.postgres",
    entityManagerFactoryRef = "postgresEntityManagerFactory"
)
public class PostgresDataSourceConfigurations {


  @Bean
  @ConfigurationProperties("spring.datasource.postgresql")
  public DataSourceProperties postgresqlDataSourceProperties() {
    return new DataSourceProperties();
  }


  @Qualifier("postgresDataSource")
  @Bean
  @Primary
  public DataSource postgresqlDataSource() {
    return postgresqlDataSourceProperties().initializeDataSourceBuilder().build();
  }

  @Bean(name = "postgresEntityManagerFactory")
  @Primary
  public LocalContainerEntityManagerFactoryBean entityManagerFactory(
      EntityManagerFactoryBuilder builder, @Qualifier("postgresDataSource") DataSource dataSource) {
    return builder
        .dataSource(dataSource)
        .packages("profiles", "outbox")
        .persistenceUnit("postgresPersistenceUnit")
        .build();
  }
  @Bean(name = "postgresTransactionManager")
  @Primary
  public PlatformTransactionManager transactionManager(
      @Qualifier("postgresEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);
  }

package dao.postgres;

import domain.Address;
import org.springframework.data.repository.CrudRepository;

import java.util.UUID;

public interface PostgresAddressDao extends CrudRepository<Address, UUID> {

}

Answer 1

I think you need to specify the transaction manager to be used by each @EnableJpaRepositories annotation. Additionally, ensure that the transaction managers have distinct names!

So first of all, update your PostgresDataSourceConfigurations class

@EnableJpaRepositories(
    basePackages = "dao.postgres",
    entityManagerFactoryRef = "postgresEntityManagerFactory",
    transactionManagerRef = "postgresTransactionManager" //specify transaction manager
)
public class PostgresDataSourceConfigurations {
    //your codes in here
    
    @Bean(name = "postgresTransactionManager") //rename method to have a distinct name
    @Primary
    public PlatformTransactionManager postgresTransactionManager(
        @Qualifier("postgresEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
    
    //your codes in here
}

then create a separate configuration class for the SQL Server:

@EnableJpaRepositories(
    basePackages = "dao.sqlserver",
    entityManagerFactoryRef = "sqlServerEntityManagerFactory",
    transactionManagerRef = "sqlServerTransactionManager" //specify transaction manager
)
public class SqlServerDataSourceConfigurations {
    //similar to PostgresDataSourceConfigurations
    
    @Bean(name = "sqlServerTransactionManager") //rename the method to have a distinct name
    public PlatformTransactionManager sqlServerTransactionManager(
        @Qualifier("sqlServerEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
    
    //similar to PostgresDataSourceConfigurations
}

as you see,each repository package will use the appropriate transaction manager, ensuring that the correct database connection is used!