Announcement Announcement Module
Collapse
No announcement yet.
Access Multiple Databases inside one spring application Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Access Multiple Databases inside one spring application

    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?

    Code:
    @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;
      }

  • #2
    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).

    Comment


    • #3
      just to clarify

      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)?
      Code:
      @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)?
      Code:
      @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 ...
      }
      Last edited by rowatt; Dec 10th, 2012, 07:33 AM.

      Comment


      • #4
        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.

        Comment


        • #5
          Thank you very much for your help.

          Much appreciated.

          Comment

          Working...
          X