-
-
Save dipold/5700724 to your computer and use it in GitHub Desktop.
package yourpackage.util.hibernate.multitenancy; | |
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); | |
} | |
} |
<?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="default"> | |
<properties> | |
<property name="javax.persistence.provider" value="org.hibernate.ejb.HibernatePersistence" /> | |
<property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" /> | |
<property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/mydatabase" /> | |
<property name="javax.persistence.jdbc.user" value="postgres" /> | |
<property name="javax.persistence.jdbc.password" value="" /> | |
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" /> | |
<property name="hibernate.archive.autodetection" value="class, hbm" /> | |
<property name="hibernate.show_sql" value="true" /> | |
<property name="hibernate.format_sql" value="true" /> | |
<property name="hibernate.multiTenancy" value="SCHEMA"/> | |
<property name="hibernate.tenant_identifier_resolver" value="yourpackage.util.hibernate.multitenancy.SchemaResolver"/> | |
<property name="hibernate.multi_tenant_connection_provider" value="yourpackage.util.hibernate.multitenancy.MultiTenantProvider"/> | |
<property name="hibernate.hbm2ddl.auto" value="create-drop" /> | |
</properties> | |
</persistence-unit> | |
</persistence> |
package yourpackage.util.hibernate.multitenancy; | |
import org.hibernate.context.spi.CurrentTenantIdentifierResolver; | |
public class SchemaResolver implements CurrentTenantIdentifierResolver { | |
@Override | |
public String resolveCurrentTenantIdentifier() { | |
return "master"; //TODO: Implement service to identify tenant like: userService.getCurrentlyAuthUser().getTenantId(); | |
} | |
@Override | |
public boolean validateExistingCurrentSessions() { | |
return false; | |
} | |
} |
I am using Oracle.I am getting the below exception
Caused by: java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option
It is coming at
connection.createStatement().execute("SET SCHEMA 'public'");
and even commenting the above line, the same exception is coming at
connection.createStatement().execute("SET SCHEMA '" + tenantIdentifier + "'");
I'm trying to test your solution with wildfly 8.1.0 but I allways get
{"JBAS014671: Failed services" => {"jboss.persistenceunit."Test.war#TestPU"" => "org.jboss.msc.service.StartException in service jboss.persistenceunit."Test.war#TestPU": org.hibernate.service.spi.ServiceException: Unable to instantiate specified multi-tenant connection provider [com.test.util.hibernate.multitenancy.MultiTenantProvider]
Caused by: org.hibernate.service.spi.ServiceException: Unable to instantiate specified multi-tenant connection provider [com.test.util.hibernate.multitenancy.MultiTenantProvider]"}}
Can anyone help me find out what am I doing wrong?
This is great. However, what if I need to change databases instead of schema?
and you might want to have specific JDBC connection per database. so it will have connection pool for each tenant, from hibernate documentation:
Each tenant’s data is kept in a physically separate database instance. JDBC Connections would point specifically to each database so any pooling would be per-tenant. A general application approach, here, would be to define a JDBC Connection pool per-tenant and to select the pool to use based on the tenant identifier associated with the currently logged in user.
Can u post one entity with where you have used multi-tenancy