使用Hibernate 4.0的多租户和分离模式方法

Question

我正在使用EJB 3.0和Hibernate 4与PostgreSQL作为我的数据库服务器来创建一个多租户系统，其中每个租户将拥有单独但相同的模式。我仍处于试验阶段，在这个阶段中我有3个模式public、company1和company2，它们都有一个名为person的表。现在我想做的是根据用户在运行时更改模式，以便他只能查看他/她公司的数据。

下面是我的示例代码： 实体对象：

    package com.neebal.domain;

        import java.io.Serializable;
        import java.lang.Long;
        import java.lang.String;

        import javax.persistence.*;
        import org.eclipse.persistence.annotations.Multitenant;
        import org.eclipse.persistence.annotations.MultitenantType;

        @Entity

        //@Table(schema = "company1")
        public class Person implements Serializable {


    @Id
    private Long id;
    private String name;
    private static final long serialVersionUID = 1L;

    public Person() {
        super();
    }   
    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }   
    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }  
}

MultiTenantConnectionProvider 类：

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.service.config.spi.ConfigurationService;
import org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider;
import org.hibernate.service.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.spi.ServiceRegistryAwareService;
import org.hibernate.service.spi.ServiceRegistryImplementor;

public class MultiTenantProvider implements MultiTenantConnectionProvider, ServiceRegistryAwareService  {

    private static final long serialVersionUID = 4368575201221677384L;

    private C3P0ConnectionProvider connectionProvider = null;

    @Override
    public boolean supportsAggressiveRelease() {
        return false;
    }

    @Override
    public void injectServices(ServiceRegistryImplementor serviceRegistry) {
        Map lSettings = serviceRegistry.getService(ConfigurationService.class).getSettings();

        connectionProvider = new C3P0ConnectionProvider();
        connectionProvider.injectServices(serviceRegistry);
        connectionProvider.configure(lSettings);
    }

    @Override
    public boolean isUnwrappableAs(Class clazz) {
        return false;
    }

    @Override
    public <T> T unwrap(Class<T> clazz) {
        return null;
    }

    @Override
    public Connection getAnyConnection() throws SQLException {
        final Connection connection = connectionProvider.getConnection();
        return connection;
    }

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        final Connection connection = getAnyConnection();
        try {
            connection.createStatement().execute("SET SCHEMA '" + tenantIdentifier + "'");
        }
        catch (SQLException e) {
            throw new HibernateException("Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]", e);
        }
        return connection;
    }

    @Override
    public void releaseAnyConnection(Connection connection) throws SQLException {
        try {
            connection.createStatement().execute("SET SCHEMA 'public'");
        }
        catch (SQLException e) {
            throw new HibernateException("Could not alter JDBC connection to specified schema [public]", e);
        }
        connectionProvider.closeConnection(connection);
    }

    @Override
    public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
        releaseAnyConnection(connection);
    }
}

CurrentTenantIdentifierResolver 类：

import org.hibernate.context.spi.CurrentTenantIdentifierResolver;

public class SchemaResolver implements CurrentTenantIdentifierResolver {

    @Override
    public String resolveCurrentTenantIdentifier() {
        System.out.println("company1");
        return "company1"; //TODO: Implement service to identify tenant like: userService.getCurrentlyAuthUser().getTenantId();
    }

    @Override
    public boolean validateExistingCurrentSessions() {
        return false;
    }
}

persistence.xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
    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">
    <persistence-unit name="testEJB">
        <jta-data-source>jdbc/testpgsql</jta-data-source>
        <properties>
            <property name="javax.persistence.provider" value="org.hibernate.ejb.HibernatePersistence" />

            <property name="hibernate.connection.username" value="postgres" />
            <property name="hibernate.connection.password" value="root" />
            <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/test" />
            <property name="hibernate.connection.driver_class" value="org.postgresql.Driver" />

            <property name="hibernate.multiTenancy" value="SCHEMA" />
            <property name="hibernate.tenant_identifier_resolver" value="com.neebal.util.multitenancy.SchemaResolver" />
            <property name="hibernate.multi_tenant_connection_provider"
                value="com.neebal.util.multitenancy.MultiTenantProvider" />

            <property name="hibernate.hbm2ddl.auto" value="create-drop" />
        </properties>
    </persistence-unit>
</persistence>

最后是 DAO 类:

import java.util.List;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import com.neebal.domain.Person;

/**
 * Session Bean implementation class PersonDAO
 */
@Stateless
public class PersonDAO implements PersonDAOLocal {

    @PersistenceContext
    EntityManager entityManager;
    /**
     * Default constructor. 
     */
    public PersonDAO() {
        // TODO Auto-generated constructor stub
    }

    @Override
    public void save(Person person) {
        entityManager.persist(person);
    }

    @Override
    public List<Person> getAll() {

        Person person = entityManager.find(Person.class, 2L);
        System.out.println(person.getName());
        return null;
    }

}

在这个例子中，我已经硬编码了模式为company1，但它仍然从公共模式中持久化或检索数据。所以在这个例子中我错在哪里了。

- Manthan Shah

---

是的，我已经按照我的方式使它工作了。我会在短时间内在这里发布我的解决方案。 - Manthan Shah

我以为它已经像这样工作了，嗯。等待您的解决方案，只是为了比较。非常感谢！ - dalvarezmartinez1

你能否请发布一下你提到的解决方案？ - Ascalonian

Answer 1

这个问题已经一年了，但我认为根据某些运行时条件使用不同模式的问题很普遍，所以我还是回答一下。如果我理解正确且租户集很小，那么我认为实现你想要的最简单方法是在persistence.xml中为每个租户定义一个单独的持久性单元。

<persistence-unit name="public">
.. settings for first schema        
</persistence-unit>
<persistence-unit name="company1">
.. settings for first schema        
</persistence-unit>
<persistence-unit name="company2">
.. settings for first schema        
</persistence-unit>

Then have for each one a separate entityManager:

@PersistenceContext(unitName = "public")
private EntityManager emPublic;

@PersistenceContext(unitName = "company1")
private EntityManager emComp1;

@PersistenceContext(unitName = "company2")
private EntityManager emComp1;

Now you can switch between entity managers, given the currently authorized user.

Depending on your exact infrastructure etc, there may be other approaches, too. For instance, if all your schemas are on the same server, then you could also try to pass the schema names directly to your queries.

This is pure JPA and thus portable and not depending on any persistence provider like hibernate nor on your DBMS.

- ferdystschenko