How to use Hibernate with multiple databases on a single server

Question

Our application allows our customer to have multiple databases, all running on one instance of the database server.

For example, databases might be dbcommon, dbLive, dbStaging, dbUAT, dbDev, dbSandbox. The common database and the Production database always exists, but the others are optional (and there is no limit). In dbcommon there is a table that tells us all the databases....so that's where I would need to start. The tables in common are different from the others, and the others all have the same schema (subscriber data)

Using Hibernate, how can I dynamically create/use a connection to either Live or Staging (or any of the others)? I am using Spring if that helps.

I have come across answers that suggest creating different connections in configuration, but because the number of subscriber databases can vary (per install, not while the app is running), this isn't an option for me.

I have to do this in an app I have. I'm using Spring and repositories to manage though. I could explain it that way. — kevingreen, Mar 28 '16 at 20:06
For me Spring handles the connections, and it runs via profile. The profile determines test-stage-prod etc. I have 3 databases that need to by wired in at any given time. I have to swap out environments, and the 3 databases need to point somewhere else. I can show you my configuration, but if you're not using Spring it might not be very helpful. It is implementing JPA over Hibernate, so it might lead to some ideas. — kevingreen, Mar 28 '16 at 20:07
We use the repository pattern too. Since posting the question I've come across Hibernate's Multi Tenancy documentation page...I'm seeing if that will work too. — Adam Erstelle, Mar 28 '16 at 20:13
I will eventually be using Spring JPA with Hibernate...so that's fine with me! — Adam Erstelle, Mar 28 '16 at 20:14
You can treat each database as a separate tenant (customer). Then use [multi-tenancy support in Hibernate](https://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch16.html) to point individual queries at the correct database. — manish, Mar 29 '16 at 04:05

score 1 · Accepted Answer · edited May 23 '17 at 10:30

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

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

Setting up a MultiTenantConnectionProvider using Hibernate 4.2 and Spring 3.1.1

Multi-Tenancy with Spring + Hibernate: "SessionFactory configured for multi-tenancy, but no tenant identifier specified"

I'm still looking for a way to be able to reference the common (shared) database at the same time as tenant databases...and will try to add that to this answer when complete.

You can use two `SessionFactory`s. One for the common database that uses the default Hibernate infrastructure and another for the other database that uses multi-tenant support. — manish, Mar 30 '16 at 02:59

score -1 · Answer 2 · answered Mar 28 '16 at 20:30

The simplest way I can see to do this is to manage everything via profiles in Spring.

I accomplished this by using application.yml. I'm also using a Hikari connection pool, but that doesn't effect the configuration too much.

Here is an example of a application.yml with 3 profiles listed, and I've defined two of them as an example.

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

In my DB config class, I set the JPA repositories, and tell it what entityManager to use. I also setup the configuration properties to pull frmo the application.yml . This means it will swap out the details based on the profile the app is using on launch.

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

    }

The entities and repositories are defined normally, there's nothing special there. The DB will switch connection based on whatever profile I tell it to run.

I just switch out the active profile in the application.yml to whichever one I need.

Safety note: Define a production profile, don't have production as a default profile.

We have 100s of installs, and I don't think this will work for us (managing each customer install locally and changing it so that the app can work for them upon install) — Adam Erstelle, Mar 28 '16 at 22:44
Progamatically build the DataSource and entity manager. You will still need to set up one DataSource on install to point to the table with database names in it. That initial DataSource could be in the application.yml. — kevingreen, Mar 28 '16 at 23:04

How to use Hibernate with multiple databases on a single server

2 Answers2