Geek Answers Handbook

How to use Hibernate with multiple databases on the same server

Our application allows our client to have several databases, all of which work on one instance of the database server.

For example, databases can be dbcommon, dbLive, dbStaging, dbUAT, dbDev, dbSandbox. A common database and a Production database always exist, but the rest are optional (and there are no restrictions). There is a table in dbcommon that tells us about all the databases .... so that where I need to start. Shared tables are different from others, and the rest have the same schema (subscriber data).

Using Hibernate, how can I dynamically create / use a connection to Live or Staging (or any other)? I use Spring if this helps.

I came across answers that suggest the creation of different connections in the configuration, but since the number of subscriber databases can change (during installation, and not during application operation), this is not an option for me.

+4
source share
2 answers

As I discovered after posting this question, and as the user suggested, support for Hibernate Multi Tenancy (using the MultiTenancyStrategy database) works for me. I had to build a solution using various resources (listed below).

http://www.ticnfae.co.uk/blog/2014/07/16/hibernate-multi-tenancy-with-spring/

Configuring MultiTenantConnectionProvider using Hibernate 4.2 and Spring 3.1.1

Multi-Tenancy Spring + Hibernate: " SessionFactory ,

() ... .

+1

, , - Spring.

application.yml. Hikari, .

application.yml , .

spring:
  profiles:
    include: dev,test,production
    active: dev
---
spring:
  profiles: dev
oms:
  omsDataSource:
    driverClassName: com.informix.jdbc.IfxDriver
    jdbcUrl: jdbc:informix-sqli://devdb:9000/hol:INFORMIXSERVER=m_tcp_1;client_deve=en_US.8859-1;db_deve=en_US.8859-1;LOBCACHE=-1
    password: oms
    username: oms
    connectionTestQuery: select count(*) from systables
    maximumPoolSize: 5

---
spring:
  profiles: test
oms:
  omsDataSource:
    driverClassName: com.informix.jdbc.IfxDriver
    jdbcUrl: jdbc:informix-sqli://testdb:9000/hol:INFORMIXSERVER=m_tcp_1;client_deve=en_US.8859-1;db_deve=en_US.8859-1;LOBCACHE=-1
    password: oms
    username: oms
    connectionTestQuery: select count(*) from systables
    maximumPoolSize: 5

DB JPA , entityManager. , application.yml. , , .

@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryOms",
        transactionManagerRef = "transactionManagerOms",
        basePackages= "persistence.oms")
@Configuration
@ConfigurationProperties(prefix = "oms.omsDataSource")
public class omsDbConfig extends HikariConfig {

//This will automatically fill in the required fields from the application.yml. 
    @Bean
    public HikariDataSource orcaDataSource() throws SQLException {
        return new HikariDataSource(this);
    }

//I use that datasource to define my entityMangerFactory
@Bean(name = "entityManagerFactoryOms")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryOrca() throws SQLException {
        JpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        Properties props = new Properties();
        props.setProperty("hibernate.dialect","org.hibernate.dialect.InformixDialect");
        LocalContainerEntityManagerFactoryBean emfb =
                new LocalContainerEntityManagerFactoryBean();
        emfb.setDataSource(orcaDataSource());
        emfb.setPackagesToScan("persistence.oms");
        emfb.setJpaProperties(props);
        emfb.setJpaVendorAdapter(adapter);
        return emfb;
    }

    }

, . , .

active application.yml , .

. , .

0

More articles:


All Articles