Hibernate multiple connections dynamically changing

Question

I know there are similar questions about this scenario, however none of them I have found matches my case and I'll like to have a solution that doesn't impact performance. I have to do multiple connections to different databases (all postgresql) and the problem is that the databases can be many as they are continuously being created.

At the moment I will only have one known database which will be used to store the connection strings from the other databases. These databases can be 1, 2, 5, 10 or N, the tricky part is that from my end I'll never know how many they will be and what their location and credentials will be (all stored on my central DB). The use case works in a way where in order to do an operation on one of these databases, I have to first fetch from the central database the location for the DB we need and then perform the operation.

At the moment, I have been able to perform the operation by sing SessionFactory, but the operation is too slow even for a simple select/update, and my concern is that when multiple requests are made, we might get from Hibernate an Out of Memory Exception.

Any ideas on what would be the best approach for this case?

Answer 1

We have similar things: 1..N databases as different customer. Each customer has same schema so we have only 1 entity manager. SO you need to provide for hibernete via spi 2 implementation class and handen via properties:

hibernate.multi_tenant_connection_provider org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider

and hibernate.tenant_identifier_resolver org.hibernate.context.spi.CurrentTenantIdentifierResolver

"hibernate.multiTenancy", "SCHEMA"

Here is example how to do it with spring framework

  LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
    emf.setJpaVendorAdapter(jpaVendorAdapter());
    emf.setPersistenceUnitName("security");
    Properties hibernateProperties = new Properties();
    hibernateProperties.put("hibernate.cache.use_second_level_cache", "true");
    hibernateProperties.put("hibernate.multiTenancy", "SCHEMA");
    // do not load all metadata from standard db speadup startup
    hibernateProperties.put("hibernate.temp.use_jdbc_metadata_defaults", "false");
    hibernateProperties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQL9Dialect");
    hibernateProperties.put("hibernate.multi_tenant_connection_provider", multiTenantConnectionProvider());
    hibernateProperties.put("hibernate.tenant_identifier_resolver", currentTenantIdentifierResolver(tenant));
    hibernateProperties.put("hibernate.show_sql", true);
    emf.setJpaProperties(hibernateProperties);
    emf.setJpaDialect(new HibernateJpaDialect());
    LOG.info("LocalContainerEntityManagerFactoryBean bean created");
    return emf;