Access Multiple Databases inside one spring application

[rowatt]

Is it possible to setup multiple database sources inside of one Spring app when using Annotation Driven Configuration Contexts?

A single database is dead easy with a JavaConfig class like below that we use. Now our app needs to connect off to another postgres instance on a different machine with different set of JPA entities. So, how do you create two of the DatabaseConfig instances below using different property sources to read in their specific db config and then associate the correct set of JPA Entities that only refer to each particular data source?

@Configuration
@PropertySource("${DATABASE_PROPERTIES:}")
@EnableTransactionManagement
public class DatabaseConfig {
  @Value("${JDBC_URL:jdbc:postgresql://localhost:5432/echo}")
  private String url;
  @Value("${JDBC_USER:postgres}")
  private String username;
  @Value("${JDBC_PASSWORD:password0}")
  private String password;
  @Value("${JDBC_DIALECT:org.hibernate.dialect.PostgreSQLDialect}")
  private String dialect;
  @Value("${JDBC_DRIVER_CLASS:org.postgresql.Driver}")
  private String driverClass;
  @Value("${JDBC_SHOW_SQL:false}")
  private boolean showSql;
  @Value("${JDBC_CREATE_SCHEMA:false}")
  private boolean createSchema;
  @Value("${HIBERNATE_C3P0_MIN_SIZE:5}")
  private int hibernateC3p0MinSize;
  @Value("${HIBERNATE_C3P0_MAX_SIZE:20}")
  private int hibernateC3p0MaxSize;
  @Value("${HIBERNATE_C3P0_TIMEOUT:300}")
  private int hibernateC3p0Timeout;
  @Value("${HIBERNATE_C3P0_MAX_STATEMENTS:50}")
  private int hibernateMaxStatement;
  @Value("${HIBERNATE_C3P0_IDLE_TEST_PERIOD:3000}")
  private int hibernateC3p0IdleTestPeriod;

  private DriverManagerDataSource dataSource;
  private LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean;
  private JpaTransactionManager jpaTransactionManager;
  private HibernateJpaVendorAdapter hibernateJpaVendorAdapter;

  @Bean
  public DataSource getDataSource() {
    if (dataSource == null) {
      dataSource = new DriverManagerDataSource(url, username, password);
      dataSource.setDriverClassName(driverClass);
    }
    return dataSource;
  }

  @Bean
  public LocalContainerEntityManagerFactoryBean getEntityManagerFactory() {
    if (localContainerEntityManagerFactoryBean == null) {
      localContainerEntityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
      localContainerEntityManagerFactoryBean.setPackagesToScan("com.innovisionsystems");
      localContainerEntityManagerFactoryBean.setDataSource(getDataSource());
      localContainerEntityManagerFactoryBean.setJpaVendorAdapter(getJpaVendorAdapter());

      Properties properties = new Properties();
      properties.put("javax.persistence.validation.mode", "none");

      properties.put("hibernate.c3p0.min_size", String.valueOf(hibernateC3p0MinSize));
      properties.put("hibernate.c3p0.max_size", String.valueOf(hibernateC3p0MaxSize));
      properties.put("hibernate.c3p0.timeout", String.valueOf(hibernateC3p0Timeout));
      properties.put("hibernate.c3p0.max_statements", String.valueOf(hibernateMaxStatement));
      properties.put("hibernate.c3p0.idle_test_period", String.valueOf(hibernateC3p0IdleTestPeriod));

      if (createSchema) {
        properties.put("hibernate.hbm2ddl.auto", "create");
      }
      properties.put("hibernate.show_sql", String.valueOf(showSql));
      properties.put("hibernate.format_sql", "true");

      localContainerEntityManagerFactoryBean.setJpaProperties(properties);
    }
    return localContainerEntityManagerFactoryBean;
  }
  
  @Bean
  public JpaTransactionManager getTransactionManager() {
    if (jpaTransactionManager == null) {
      jpaTransactionManager = new JpaTransactionManager();
      jpaTransactionManager.setEntityManagerFactory(getEntityManagerFactory().getNativeEntityManagerFactory());
      jpaTransactionManager.setDataSource(getDataSource());
    }
    return jpaTransactionManager;
  }
  
  @Bean
  public HibernateJpaVendorAdapter getJpaVendorAdapter() {
    if (hibernateJpaVendorAdapter == null) {
      hibernateJpaVendorAdapter = new HibernateJpaVendorAdapter();
      hibernateJpaVendorAdapter.setGenerateDdl(false);
      hibernateJpaVendorAdapter.setDatabasePlatform(dialect);
    }
    return hibernateJpaVendorAdapter;
  }

[Marten Deinum]

Your thinking is wrong... You don't construct 2 instances of the config the config is just that config. So you create another class containing that configuration (just as you normally would create a new xml file).

[rowatt]

So even the code below won't work because PropertySources are effectively merged together such that the last PropertySource that defines a property value wins (by default)?

@PropertySource("classpath:firstdb.properties")
@Configuration
public class FirstDatabaseConfig extends DatabaseConfig {}

@PropertySource("classpath:seconddb.properties")
@Configuration
public class SecondDatabaseConfig extends DatabaseConfig {}

@EnableTransactionManagement
public class DatabaseConfig {
* @Value("${JDBC_URL:jdbc:postgresql://localhost:5432/db}")
* private String url;
* // ... etc ...
}

It would be nice if you could tie the @PropertySource just to the @Configuration class that it is defined with.

So to clarify I must duplicate the DatabaseConfig.java class I had in my first post and change the properties names to be globally unique for each new database I connect to (like below)?

@PropertySource("classpath:firstdb.properties")
@Configuration
@EnableTransactionManagement
public class FirstDatabaseConfig {
* @Value("${FIRSTDB_JDBC_URL:jdbc:postgresql://localhost:5432/firstdb}")
* private String url;

* // ... etc ...
}

@PropertySource("classpath:seconddb.properties")
@Configuration
@EnableTransactionManagement
public class SecondDatabaseConfig {

* @Value("${SECONDDB_JDBC_URL:jdbc:postgresql://localhost:5432/seconddb}")
* private String url;

* // ... etc ...
}

[Marten Deinum]

Exactly...They have to be globally unique.

Also be aware that the bean names also have to be different! If not the last one wins. A final note if you need distributed transactions then your setup isn't going to work as you are using a JpaTransactionManager you possibly need a JTA transactionmanager.

[rowatt]

Much appreciated.