Multiple Transaction Managers in Spring Boot for different EntityManagers

Question

I need to connect to two different databases from a single application. The trouble is that my appEntityManager does not have a transaction manager associated with it and I am not sure how to do it. The @Primary adminEntityManager is able to use the one provided by spring boot without any trouble as described here.

The configuration above almost works on its own. To complete the picture you need to configure TransactionManagers for the two EntityManagers as well. One of them could be picked up by the default JpaTransactionManager in Spring Boot if you mark it as @Primary. The other would have to be explicitly injected into a new instance. Or you might be able to use a JTA transaction manager spanning both.

I have annoted the configuration with

@EnableTransactionManagement

And here is the relavant beans

@Bean
@ConfigurationProperties(prefix = "datasource.app")
public DataSource appDataSource() {
    return DataSourceBuilder.create().build();
}

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

@Bean
public LocalContainerEntityManagerFactoryBean appEntityManagerFactory(
        final EntityManagerFactoryBuilder builder) {
    return builder
            .dataSource(appDataSource())
            .packages("au.com.mycompany.app.bomcommon.domain")
            .persistenceUnit("appPersistentUnit")
            .build();
}

@Bean
@Primary
public LocalContainerEntityManagerFactoryBean adminEntityManagerFactory(
        final EntityManagerFactoryBuilder builder) {
    return builder
            .dataSource(adminDataSource())
            .packages("au.com.mycompany.app.bombatch")
            .persistenceUnit("adminPersistentUnit")
            .build();
}

//I thought this would do it but I am getting an exception
//No qualifying bean of type [org.springframework.transaction.PlatformTransactionManager] is defined: expected single matching bean but found 2: appTransactionManager,transactionManager
@Bean
public JpaTransactionManager appTransactionManager(@Qualifier("appEntityManagerFactory") final EntityManagerFactory emf) {
    JpaTransactionManager transactionManager = new JpaTransactionManager();
    transactionManager.setEntityManagerFactory(emf);
    return transactionManager;
}

Update

I ended up doing it a different way. see here.

Answer 1

See the below changes. It works for me. Created 3 Data Sources, 3 Session Factories, and 3 Transaction Managers. Added these transaction managers in chainedTransaction as per below:

                    @Configuration
                    @EnableTransactionManagement
                    public class HibernateConfiguration implements TransactionManagementConfigurer {


                        @Bean("chainedTransactionManager")
                        public PlatformTransactionManager transactionManager(
                                @Qualifier("transactionManager1") final HibernateTransactionManager transactionManager1,
                                @Qualifier("transactionManager2") final HibernateTransactionManager transactionManager2,
                                @Qualifier("transactionManager3") final HibernateTransactionManager transactionManager3) {

                            return new ChainedTransactionManager(transactionManager1, transactionManager2, transactionManager3);
                        }

                        @Override
                        public PlatformTransactionManager annotationDrivenTransactionManager() {
                            // TODO Auto-generated method stub
                            return transactionManager(oneTransactionManager(), twoTransactionManager(), threeTransactionManager());
                        }


                @Bean(name = "oneDataSource", destroyMethod="")
                      public DataSource oneDataSource() throws IllegalArgumentException, NamingException, SQLException {
                            HikariConfig hkConfig = new HikariConfig();
                            System.out.println(env.getRequiredProperty("spring1-datasource.jdbcUrl"));
                            hkConfig.setJdbcUrl(env.getRequiredProperty("spring1-datasource.jdbcUrl"));
                            hkConfig.setUsername(env.getRequiredProperty("spring1-datasource.username"));
                            hkConfig.setPassword(env.getRequiredProperty("spring1-datasource.password"));
                            hkConfig.setDriverClassName(env.getRequiredProperty("spring1-datasource.driverClassName"));
                            return new HikariDataSource(hkConfig);
                        }

                @Bean(name="oneSessionFactory")
                @Qualifier("oneSessionFactory") 
               public LocalSessionFactoryBean oneSessionFactory() throws IllegalArgumentException, NamingException, SQLException {
                LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
                sessionFactory.setDataSource(oneDataSource());
                  sessionFactory.setPackagesToScan(env.getRequiredProperty("entitymanager.packagesToScan"));
                  Properties hibernateProperties = new Properties();
                  hibernateProperties.put("hibernate.dialect", env.getRequiredProperty("hibernate.dialect"));
                  hibernateProperties.put("hibernate.show_sql", env.getRequiredProperty("hibernate.show_sql"));
                  sessionFactory.setHibernateProperties(hibernateProperties);
                return sessionFactory;

              }



        @Bean(name="transactionManager1")
          public HibernateTransactionManager oneTransactionManager() {
              HibernateTransactionManager oneTransactionManager = new HibernateTransactionManager();
              try {
                oneTransactionManager.setSessionFactory(oneSessionFactory().getObject());
                } catch (IllegalArgumentException | NamingException | SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
              return oneTransactionManager 
          }





    @Bean(name = "twoDataSource", destroyMethod="")
          public DataSource twoDataSource() throws IllegalArgumentException, NamingException, SQLException {
                HikariConfig hkConfig = new HikariConfig();
                System.out.println(env.getRequiredProperty("spring2-datasource.jdbcUrl"));
                hkConfig.setJdbcUrl(env.getRequiredProperty("spring2-datasource.jdbcUrl"));
                hkConfig.setUsername(env.getRequiredProperty("spring2-datasource.username"));
                hkConfig.setPassword(env.getRequiredProperty("spring2-datasource.password"));
                hkConfig.setDriverClassName(env.getRequiredProperty("spring2-datasource.driverClassName"));
                return new HikariDataSource(hkConfig);
            }



@Bean(name="twoSessionFactory")
    @Qualifier("twoSessionFactory") 
   public LocalSessionFactoryBean twoSessionFactory() throws IllegalArgumentException, NamingException, SQLException {
    LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
    sessionFactory.setDataSource(twoDataSource());
      sessionFactory.setPackagesToScan(env.getRequiredProperty("entitymanager.packagesToScan"));
      Properties hibernateProperties = new Properties();
      hibernateProperties.put("hibernate.dialect", env.getRequiredProperty("hibernate.dialect"));
      hibernateProperties.put("hibernate.show_sql", env.getRequiredProperty("hibernate.show_sql"));
      sessionFactory.setHibernateProperties(hibernateProperties);
    return sessionFactory;

  }





  @Bean(name="transactionManager2")
  public HibernateTransactionManager twoTransactionManager() {
      HibernateTransactionManager twoTransactionManager = new HibernateTransactionManager();
      try {
        twoTransactionManager.setSessionFactory(twoSessionFactory().getObject());
        } catch (IllegalArgumentException | NamingException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
      return twoTransactionManager
  }


@Bean(name = "threeDataSource", destroyMethod="")
      public DataSource threeDataSource() throws IllegalArgumentException, NamingException, SQLException {
            HikariConfig hkConfig = new HikariConfig();
            System.out.println(env.getRequiredProperty("spring3-datasource.jdbcUrl"));
            hkConfig.setJdbcUrl(env.getRequiredProperty("spring3-datasource.jdbcUrl"));
            hkConfig.setUsername(env.getRequiredProperty("spring3-datasource.username"));
            hkConfig.setPassword(env.getRequiredProperty("spring3-datasource.password"));
            hkConfig.setDriverClassName(env.getRequiredProperty("spring3-datasource.driverClassName"));
            return new HikariDataSource(hkConfig);
        }


    @Bean(name="threeSessionFactory")
    @Qualifier("threeSessionFactory") 
   public LocalSessionFactoryBean threeSessionFactory() throws IllegalArgumentException, NamingException, SQLException {
    LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
    sessionFactory.setDataSource(threeDataSource());
      sessionFactory.setPackagesToScan(env.getRequiredProperty("entitymanager.packagesToScan"));
      Properties hibernateProperties = new Properties();
      hibernateProperties.put("hibernate.dialect", env.getRequiredProperty("hibernate.dialect"));
      hibernateProperties.put("hibernate.show_sql", env.getRequiredProperty("hibernate.show_sql"));
      sessionFactory.setHibernateProperties(hibernateProperties);
    return sessionFactory;

  }





  @Bean(name="transactionManager3")
  public HibernateTransactionManager threeTransactionManager() {
      HibernateTransactionManager threeTransactionManager = new HibernateTransactionManager();
      try {
        threeTransactionManager.setSessionFactory(threeSessionFactory().getObject());
        } catch (IllegalArgumentException | NamingException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
      return threeTransactionManager
  }

                           }

Answer 2

See if this works:

@Bean
@Primary
@ConfigurationProperties(prefix = "datasource.admin")
public DataSource adminDS() { ... }

@Bean
@Primary
public LocalContainerEntityManagerFactoryBean adminEMF(...) { ... }

@Bean
@Primary
public JpaTransactionManager adminTM(...) { ... }

@Bean
public LocalContainerEntityManagerFactoryBean appEMF(...) { ... }

@Bean
public JpaTransactionManager appTM(...) { ... }

The only change I have made from your configuration is to declare a transaction manager for the admin side explicitly and marked that transaction manager as the default.