Spring Data JPA，Hibernate供应商，多租户和PostgresSQL

Question

package com.sd.multitenncy;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.hibernate.MultiTenancyStrategy;
import org.hibernate.Session;
import org.hibernate.cfg.AvailableSettings;
import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.ImprovedNamingStrategy;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.service.ServiceRegistryBuilder;
import org.hibernate.service.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.spi.ServiceRegistryImplementor;
import org.junit.Before;
import org.junit.Test;

import com.sd.config.MultiTenantConnectionProviderImpl;
import com.sd.config.MultiTenantIdentifierResolver;
import com.sd.entity.User;

public class DatabaseBasedMultiTenancyTest
{

  private ServiceRegistryImplementor serviceRegistry;
  private SessionFactoryImplementor sessionFactory;
  private MultiTenantIdentifierResolver currentTenantIdentifierResolver;

  @Before
  public void setUp()
  {
    Configuration config = new Configuration();
    config.getProperties().put(AvailableSettings.DIALECT,"org.hibernate.dialect.PostgreSQLDialect");
    config.getProperties().put(AvailableSettings.SHOW_SQL,"true");
    config.getProperties().put(AvailableSettings.FORMAT_SQL,"true");
    config.getProperties().put(AvailableSettings.HBM2DDL_AUTO,"update");
    config.getProperties().put(AvailableSettings.DEFAULT_SCHEMA,"public");
    config.getProperties().put(AvailableSettings.STATEMENT_BATCH_SIZE,"3000");
    config.getProperties().put(AvailableSettings.USE_SECOND_LEVEL_CACHE,"true");
    config.getProperties().put(AvailableSettings.CACHE_REGION_FACTORY,"org.hibernate.cache.ehcache.EhCacheRegionFactory");
    config.getProperties().put(AvailableSettings.ORDER_UPDATES,"true");
    config.getProperties().put(AvailableSettings.ORDER_INSERTS,"true");
    config.getProperties().put(AvailableSettings.MAX_FETCH_DEPTH,"1");
    config.setNamingStrategy(new ImprovedNamingStrategy());

    Map<String, DataSource> dataSources = new HashMap<String, DataSource>();
    DataSource dataSource1 = createDataSource("jdbc:postgresql://localhost/tenant1","postgres","postgres");
    DataSource dataSource2 = createDataSource("jdbc:postgresql://localhost/tenant2","postgres","postgres");

    dataSources.put("tenant1",dataSource1);
    dataSources.put("tenant2",dataSource2);

    MultiTenantConnectionProviderImpl multiTenantConnectionProvider = new MultiTenantConnectionProviderImpl(dataSources);
    MultiTenantIdentifierResolver currentTenantIdentifierResolver = new MultiTenantIdentifierResolver();
    config.getProperties().put(AvailableSettings.MULTI_TENANT_CONNECTION_PROVIDER,multiTenantConnectionProvider);
    config.getProperties().put(AvailableSettings.MULTI_TENANT,MultiTenancyStrategy.DATABASE);
    config.getProperties().put(AvailableSettings.MULTI_TENANT_IDENTIFIER_RESOLVER,currentTenantIdentifierResolver);
    // JPA annotated classes
    config.addPackage("com.sd.entity");
    config.addAnnotatedClass(User.class);
    serviceRegistry = (ServiceRegistryImplementor)new ServiceRegistryBuilder().applySettings(config.getProperties())
        .addService(MultiTenantConnectionProvider.class,multiTenantConnectionProvider).buildServiceRegistry();

    sessionFactory = (SessionFactoryImplementor)config.buildSessionFactory(serviceRegistry);
  }

  private DataSource createDataSource(String url, String userName, String password)
  {
    final String driver = "org.postgresql.Driver";
    final String validationQuery = "SELECT 1 ";

    final int minIdle = 3;
    final int maxIdle = 3;
    final int maxActive = 10;
    final long maxWait = 6000;
    final boolean removeAbandoned = true;
    final boolean logAbandoned = true;
    final boolean testOnBorrow = true;
    final boolean testOnReturn = false;
    final boolean testWhileIdle = false;
    final long timeBetweenEvictionRunsMillis = 30000;
    final long minEvictableIdleTimeMillis = 30000;

    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName(driver);
    dataSource.setUsername(userName);
    dataSource.setPassword(password);
    dataSource.setValidationQuery(validationQuery);
    dataSource.setUrl(url);
    dataSource.setMaxIdle(minIdle);
    dataSource.setMaxIdle(maxIdle);
    dataSource.setMaxActive(maxActive);
    dataSource.setMaxWait(maxWait);
    dataSource.setRemoveAbandoned(removeAbandoned);
    dataSource.setLogAbandoned(logAbandoned);
    dataSource.setTestOnBorrow(testOnBorrow);
    dataSource.setTestOnReturn(testOnReturn);
    dataSource.setTestWhileIdle(testWhileIdle);
    dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
    dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
    return dataSource;
  }

  protected Session getNewSession(String tenant)
  {
    return sessionFactory.withOptions().tenantIdentifier(tenant).openSession();
  }

  @Test
  public void testTableBasedMultiTenancy()
  {
    // try getting a new session explicitly providing the tenant identifier
    Session session = getNewSession("tenant1");
    session.beginTransaction();
    User user = (User)session.load(User.class,1l);
    System.out.println("************************* (" + user.getEmail() + " ) ***********************************");
    session.getTransaction().commit();
    session.close();
  }

}

这段代码在使用sessionFactory的hibernate中运行良好。我想将这段代码转换为使用entityManager而不是sessionFactory，因为我将spring数据jpa与HibernateJavaEndorapter一起使用。

如果您有spring数据jpa和多租户（每个租户有单独的数据库）的任何示例/示例，请共享或提供信息。

提前谢谢。我们将非常感谢你的帮助。

Answer 1

我已经做了这个。我跟着https://community.jboss.org/message/855264?et=watches.email.thread

我会尽快发布我的解决方案。

Answer 2

这里有一个工作示例：http://blog.sandeepgupta.info/2014/07/making-application-multi-tenant-with.html

希望能有帮助。

Answer 3

如果我的理解是正确的，你只需要在你的persistence.xml中创建持久化单元：

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">
<persistence-unit name="test_persistance_unit"
    transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>

    <properties>
        <property name="hibernate.connection.url"
            value="jdbc:postgresql://localhost:5432/db1" />
        <property name="hibernate.connection.username" value="postgres" />
        <property name="hibernate.connection.password" value="postgres" />
        <property name="hibernate.connection.driver_class" value="org.postgresql.Driver" />
        <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />
        <property name="hibernate.show-sql" value="false" />
        <property name="hibernate.jdbc.batch_size" value="1000"/>                   
    </properties>
</persistence-unit>

<persistence-unit name="test_persistance_unit_2"
    transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>

    <properties>
        <property name="hibernate.connection.url"
            value="jdbc:postgresql://localhost:5432/db2" />
        <property name="hibernate.connection.username" value="postgres" />
        <property name="hibernate.connection.password" value="postgres" />
        <property name="hibernate.connection.driver_class" value="org.postgresql.Driver" />
        <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />
        <property name="hibernate.show-sql" value="false" />
        <property name="hibernate.jdbc.batch_size" value="1000"/>                   
    </properties>
</persistence-unit>

在此之后，您将需要拥有并使用2个EntityManagerFactory（每个db一个）。

EntityManagerFactory emf = Persistence.createEntityManagerFactory("test_persistance_unit");
EntityManagerFactory emf2 = Persistence.createEntityManagerFactory("test_persistance_unit_2");