Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 13 posts ] 
Author Message
 Post subject: Multitenancy using only one connection pool
PostPosted: Tue Nov 01, 2016 4:20 pm 
Newbie

Joined: Fri Dec 04, 2009 4:17 pm
Posts: 18
Hi,

My webapp is Glassfish/Payara4 JEE7 + JSF2.2 + JPA2.1 + Hibernate4.3 + Postgresql9.6.

The same webapp is used for many users from different enterprises but today I have:
1) one connection pool for each enterprise in Glassfish/Payara4
2) ejb-jar.xml: one entry for each JNDI pool name/enterprise, to locate the right pool at runtime when some user logs on
3) persistence.xml: on entry for each persistence unit/enterprise

I would like to have just one database with many schemas, one for each enterprise.
Then, I would have one connection pool, no need for ejb-jar.xml config, and one entry in persistence.xml for the general database.

When the user would log on, I would have a code like this:
@PersistenceContext(unitName = "generalPU") EntityManager em; // generalPU defined in persistence.xml
em.setSchema("enterpriseX"); // I know there isn't a method like this in JPA, but is there in Hibernate something near?
em.createQuery("select t from table t"); // no need to do: select t from schemaX.table t


Top
 Profile  
 
 Post subject: Re: Multitenancy using only one connection pool
PostPosted: Wed Nov 02, 2016 2:47 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1435
You usually want to connect to the right schema from the start. However, you can use the Connection#setSchema for this purpose.

So, you can try to adapt this example we have on our User Guide to use Connection#setSchema whenever you try to get a connection for a particular tenant identifier.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Multitenancy using only one connection pool
PostPosted: Thu Nov 03, 2016 10:11 am 
Newbie

Joined: Fri Dec 04, 2009 4:17 pm
Posts: 18
I made this code with JDBC or JPA. JDBC works fine, but JPA doesn't.
Other problem is that the getConnection() uses a deprecated method from Hibernate sessionFactoryImplementation.getConnectionProvider().

Code:
public class testConnection {
  public static final String DRIVER = "org.postgresql.Driver";
  public static final String ALIAS = "jdbc:postgresql://localhost/SATI";
  public static final String USUARIO = "postgres";
  public static final String SENHA = "mypass";
  public static void main(String[] args) {
    try {
      boolean isJDBC = false;
      if (isJDBC) {
        Class.forName(DRIVER);
        Connection conn = DriverManager.getConnection(ALIAS, USUARIO, SENHA);
        conn.setSchema("clienteX");
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from pais");
        while (rs.next())
          System.out.println(rs.getObject(1));
        stmt.close();
        conn.close();
      }
      else {
        // isJPA
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("satpg");
        EntityManager em = emf.createEntityManager();
        Connection conn = getConnection(getSession());
        conn.setSchema("clienteX");
        Query q;
        q = em.createQuery("select t from Pais t");
        List<Empresa> l = q.getResultList();
        if (l != null && !l.isEmpty()) {
          for (Object o: l)
            System.out.println(o);
        }
        em.close();
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
 
  private static Session getSession(EntityManager em) {
    if (em.getDelegate() instanceof EntityManagerImpl) {
      EntityManagerImpl entityManagerImpl = (EntityManagerImpl) em.getDelegate();
      return entityManagerImpl.getSession();
    } else {
      return (Session) em.getDelegate();
    }
  }

  private static Connection getConnection(Session session) {
    Connection connLocal;
    SessionFactoryImplementor sessionFactoryImplementation = (SessionFactoryImplementor) session.getSessionFactory();
    ConnectionProvider connectionProvider = sessionFactoryImplementation.getConnectionProvider();
    try { connLocal = connectionProvider.getConnection(); } catch (Exception e) { e.printStackTrace(); connLocal = null; }
    return connLocal;
  }

}


The exception from JPA is that it doesn't find "pais" table in DB PG, but it searches for in public schema, if I create "pais" in public, the code runs fine:

Code:
nov 03, 2016 11:14:19 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 0, SQLState: 42P01
nov 03, 2016 11:14:19 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ERRO: relação "pais" não existe
  Posição: 284
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
   at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)
   at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
   at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:458)
   at control.testes.testarConexao.main(testarConexao.java:55)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
   at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
   at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
   at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
   at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
   at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)
   at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)
   at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
   at org.hibernate.loader.Loader.doQuery(Loader.java:910)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
   at org.hibernate.loader.Loader.doList(Loader.java:2554)
   at org.hibernate.loader.Loader.doList(Loader.java:2540)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
   at org.hibernate.loader.Loader.list(Loader.java:2365)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497)
   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
   at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236)
   at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1300)
   at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
   at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
   at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
   ... 1 more
Caused by: org.postgresql.util.PSQLException: ERRO: relação "pais" não existe
  Posição: 284
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2158)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:291)
   at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:432)
   at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:358)
   at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:171)
   at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:119)
   at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
   ... 17 more


Top
 Profile  
 
 Post subject: Re: Multitenancy using only one connection pool
PostPosted: Thu Nov 03, 2016 12:01 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1435
That's not what I suggested. I said that you should try to adapt the MultiTenancy example we provided in our User Guide. Check out this GitHub repo for more details.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Multitenancy using only one connection pool
PostPosted: Fri Apr 28, 2017 10:37 am 
Newbie

Joined: Fri Dec 04, 2009 4:17 pm
Posts: 18
I came back to this project idea and I got to setup access to different schemas in my webapp, using query.createNativeQuery("SET SCHEMA '" + aSchema + "'") for PostgreSQL. I didn't get to use the Session Factory way to do this (sessionFactory.withOptions().tenantIdentifier(tenant).openSession()) because each EntityManager is injected using Glassfish JTA pool management.

However, if 2 or more users enter the webapp, I don't know why the schema set up by the last one becomes the actual schema for all users sessions. The old code with a PersistenceUnit + ConnectionPool for each separate database was replaced by SET SCHEMA new code, just this. But old code always worked fine setting the EntityManager for the right database of the user session.

I don't know if Hibernate saves the "actual schema" in a shared place of the webapp and all user sessions get the same last schema set up.
If I put SET SCHEMA new code before each call to the database, Hibernate runs the query in the right schema, mas this option is impossible to do here because the webapp has so much calls.


Top
 Profile  
 
 Post subject: Re: Multitenancy using only one connection pool
PostPosted: Mon May 01, 2017 3:44 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1435
The SET SCHEMA in PostgreSQL is always associated with a single Session or Transaction. Also, why do you do it manually when it is the DataSourceBasedMultiTenantConnectionProviderImpl job to do that?

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Multitenancy using only one connection pool
PostPosted: Tue May 02, 2017 11:19 am 
Newbie

Joined: Fri Dec 04, 2009 4:17 pm
Posts: 18
I searched all the web for some code using DataSourceBasedMultiTenantConnectionProviderImpl and JTA, but I didn't find how to use this class.
I would like something like this:

Code:
@PersistenceContext(unitName = "someMultiTenantPU") EntityManager em; // injects the general EntityManager, without setup a specific schema from PostgreSQL
MultiTenantConnectionProvider mt = new DataSourceBasedMultiTenantConnectionProviderImpl(); // there isn't a construtor to say "mt is connected to em"
Connection conn = mt.getConnection("tenant1"); // I don't know if this method is the correct to make the connection uses tne right "tenant1" tenant for my EntityManager... I don't use anyway Connection objects, just EntityManager objects


I look at all methods from https://access.redhat.com/documentation ... rImpl.html and interface https://access.redhat.com/documentation ... vider.html.


Top
 Profile  
 
 Post subject: Re: Multitenancy using only one connection pool
PostPosted: Wed May 03, 2017 5:33 pm 
Newbie

Joined: Fri Dec 04, 2009 4:17 pm
Posts: 18
I tried to follow the ideas in https://docs.jboss.org/hibernate/orm/4. ... /ch16.html (Example 16.3. Implementing MultiTenantConnectionProvider using single connection pool), but I don't know how to change the code for "private final ConnectionProvider connectionProvider = ConnectionProviderUtils.buildConnectionProvider( "master" );" to my @Inject way to get EntityManagers, somethings like the not published ConnectionProviderUtils class get connections.


Top
 Profile  
 
 Post subject: Re: Multitenancy using only one connection pool
PostPosted: Thu May 04, 2017 1:05 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1435
Why do you need the EntityManager inside the ConnectionProvider?

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Multitenancy using only one connection pool
PostPosted: Thu May 04, 2017 6:05 pm 
Newbie

Joined: Fri Dec 04, 2009 4:17 pm
Posts: 18
I use Glassfish/Payara to inject EntityManagers from PostgreSQL, I don't know how to get pure Connections that MultiTenantConnectionProvider needs.
After look forums like Stackoverflow, I didn't find a complete idea, some people using DataSource injected by Spring, others using Hibernate Session to get underlying Connection.
My environment is all using EntityManager injected by Payara Server Connection Pool.
How do I do for the EntityManager I injected to be set up by MultiTenantConnectionProvider implementation to change to a specific schema?
Code:
public class MyDAO {
  @PersistenceContext(unitName = "myPU")
  EntityManager em;
  ...
}
public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider, Stoppable {
  // the code below will do the magic in the Chapter 16 from Hibernate
  private final ConnectionProvider connectionProvider = ConnectionProviderUtils.buildConnectionProvider( "master" );
  @Override
  public Connection getAnyConnection() throws SQLException {
    return connectionProvider.getConnection();
  }
  // how to adapt the code something like this...
  private final ConnectionProvider connectionProvider = MyProviderUtils.buildConnectionFromMyEntityManagerFromMyDAO( " master " );
  @Override
  public Connection getAnyConnection() throws SQLException {
    return connectionProvider.getConnection();
  }
}


Can't I use @Inject anymore and change my strategy?
I also had tried to get the EntityManager via JNDI lookup (ejb-jar.xml and persistence.xml) directly in MultiTenantConnectionProvider implementation, but after this, I tried this code to get the underlying Connection, but doWork closed the connection after execute method:
Code:
  private static Connection getConnectionFromEntityManager(EntityManager em) {
    Session session = em.unwrap(Session.class);
    MyWork myWork = new MyWork();
    session.doWork(myWork);
    return myWork.getConnection();
  }
 
  private static class MyWork implements Work {
    Connection conn;
    @Override
    public void execute(Connection conn) throws SQLException {
      this.conn = conn;
    }
    Connection getConnection() {
      return conn; // UNFORTUNATELLY IT IS CLOSED
    }
  } 


Top
 Profile  
 
 Post subject: Re: Multitenancy using only one connection pool
PostPosted: Fri May 05, 2017 1:34 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1435
Quote:
I also had tried to get the EntityManager via JNDI lookup (ejb-jar.xml and persistence.xml) directly in MultiTenantConnectionProvider implementation, but after this, I tried this code to get the underlying Connection, but doWork closed the connection after execute method:


That doesn't work because the ConnectionProvider is bootstrap prior to the EntityManagerFactory. You just need a DataSource in the ConnectionProvider.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Multitenancy using only one connection pool
PostPosted: Fri May 05, 2017 5:30 am 
Newbie

Joined: Wed Apr 26, 2017 11:20 am
Posts: 4
Hi,
I implemented the Multitenancy in hibernate successfully. I used "Database" strategy but for you, schema strategy should work too in same way. I used JPA with container managed persistence. Below is the implementation I did. I would not advice to createEntityManagers because you would need to handle the life cycle of entity managers yourself. I would suggest to use container managed persistence if you are using JPA.

1. Create a class say XX extending AbstractDataSourceBasedMultiTenantConnectionProviderImpl . Override two methods selectAnyDataSource and selectDataSource. Call selectDataSource from selectAnyDataSource. The selectDataSource operation should have the implementation to get the datasource object. The easiest way to get it will be from Initial Context (from the container). Look up the datasource based on tenant identifier. This will be the datasource returned. The datasource you are looking up should be created on the server beforehand. The tenant identifier will be selected dynamically based on the logic described in bullet 2.

2. Create a class say YY implementing CurrentTenantIdentifierResolver. The operation resolveCurrentTenantIdentifier should just have the logic to tell hibernate which jndi it needs to look up in your XX implementation (looking up data source).

3. update persistence.xml file to include your XX and YY reference.

<property name="hibernate.multi_tenant_connection_provider" value="<package>.XX"></property>
<property name="hibernate.tenant_identifier_resolver" value="<package>.YY"></property>
4. Inject the entity manager with persitence unit names. Do not have JTA datasource referred in your persistent unit configuration in persistence.xml file. You datasource will be selected dynamically from multitenat implementation.
Note : If you want to get the connection object, the most genreric way is to look up DataSource object. After you look it up, you can get the connection object from Datasource.

I have not implemented this on Jboss , but this should work on any application server as the solution is generic and not bound to any Application server.


Top
 Profile  
 
 Post subject: Re: Multitenancy using only one connection pool
PostPosted: Fri May 05, 2017 4:39 pm 
Newbie

Joined: Fri Dec 04, 2009 4:17 pm
Posts: 18
Solved. My step by step solution:

1. persistence.xml: into persistence-unit "myPersistenceUnit"
Code:
      <property name="hibernate.multiTenancy" value="SCHEMA"/>
      <property name="hibernate.multi_tenant_connection_provider" value="util.MultiTenantConnectionProviderImpl"/>
      <property name="hibernate.tenant_identifier_resolver" value="util.MultiTenantIdentifierResolverImpl"/>


2. ConnectionProviderImpl
Code:
public class ConnectionProviderImpl implements ConnectionProvider {

  @Override
  public Connection getConnection() throws SQLException {
    String jndiName = "myPersistenceUnit";
    DataSource ds = null;
    try {
      InitialContext ic = new InitialContext();
      ds = (DataSource) ic.lookup(jndiName);
      return ds.getConnection();
    } catch (NamingException e) {
      return null;
    }
  }

  @Override
  public void closeConnection(Connection conn) throws SQLException {
    conn.close();
  }

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

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

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


3. MultiTenantConnectionProviderImpl
Code:
public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider, Stoppable {

  private final ConnectionProvider connectionProvider = new ConnectionProviderImpl();

  @Override
  public void stop() {
  }

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

  @Override
  public void releaseAnyConnection(Connection conn) throws SQLException {
    connectionProvider.closeConnection(conn);
  }

  @Override
  public Connection getConnection(String tenantIdentifier) throws SQLException {
    final Connection conn = getAnyConnection();
    setSchema(tenantIdentifier, conn);
    return conn;
  }

  @Override
  public void releaseConnection(String tenantIdentifier, Connection conn) throws SQLException {
    setSchema("public", conn);
    releaseAnyConnection(conn);
  }
 
  private void setSchema(String tenantIdentifier, Connection conn) {
    try {
      conn.createStatement().execute("SET SCHEMA '" + tenantIdentifier + "'");
    } catch (SQLException e) {
      throw new HibernateException("MultiTenantConnectionProviderImpl::Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]",e);
     }
  }

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

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

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

}


4. MultiTenantIdentifierResolverImpl
Code:
public class MultiTenantIdentifierResolverImpl implements CurrentTenantIdentifierResolver {
  public static ThreadLocal<String> tenantIdentifier = new ThreadLocal<>();

  @Override
  public String resolveCurrentTenantIdentifier() {
    String currentTenantIdentifier = tenantIdentifier.get();
    if (currentTenantIdentifier == null)
      currentTenantIdentifier = "public";
    return currentTenantIdentifier;
  }

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


5. Filter to set up the actual tenantIdentifier
Code:
public class OpenSessionInViewFilter implements Filter {
  ...
  @Override
  public void doFilter(final ServletRequest req, final ServletResponse res, final FilterChain chain)
    throws IOException, ServletException {
      ...
      HttpSession session = ((HttpServletRequest)req).getSession(false);
      if (session != null) {
        String currentTenant = (String) session.getAttribute("currentTenant");
        if (!Geral.StrEmpty(currentTenant))
          MultiTenantIdentifierResolverImpl.tenantIdentifier.set(currentTenant);
      }
      ...   
  }
}


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 13 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.