1 Reply Latest reply on Apr 29, 2011 2:04 AM by Swen Vogel

    Multitenancy with hibernate filters

    Swen Vogel Newbie

      Hi,


      i want to user the Hibernate Filter feature to separate the data of different tenants.
      Therefor i created a base class TenantEntity.


      @FilterDef(name = "tenantFilter",
          defaultCondition = "_tenantId = :tenantId",
          parameters = @ParamDef(name = "tenantId", type = "long"))
      @Filter(name = "tenantFilter")
      @MappedSuperclass
      public class TenantEntity extends BaseEntity {
          ...
          private Tenant tenant;
      
          @ManyToOne(fetch = FetchType.LAZY)
          @JoinColumn(name = "_tenantId")
          public Tenant getTenant() {
              return tenant;
          }
      }
      



      The problem is that for some mappings i get an Column '_tenantId' in where clause is ambiguous error.
      For example for the class TemplateConfiguration that has a one to many association to the class
      TemplatePage.



      @Entity
      @Table(name = "templateconfigurations")
      public class TemplateConfiguration extends TenantEntity {
          ...
         private List<TemplatePage> pages = new ArrayList<TemplatePage>();
      
         @OneToMany(mappedBy = "configuration", fetch = FetchType.EAGER)
         @Cascade(CascadeType.ALL)
         public List<TemplatePage> getPages() {
             return pages;
         }
      }
      
      @Entity
      @Table(name = "templatepages")
      public class TemplatePage extends TenantEntity {
         ...
         private TemplateConfiguration configuration;
      
         @ManyToOne
         @JoinColumn(name = "_configurationId")
         public TemplateConfiguration getConfiguration() {
             return configuration;
         }
      }
      



      After further investigation i found out that the problem arises because the TemplatePages are
      loaded with an outer join and the result set contains the _tenantId columns of both tables.


      A workaroung would be using


      defaultCondition = "this_._tenantId = :tenantId"
      instead of
      defaultCondition = "_tenantId = :tenantId"
      



      This solves the duplicate column problem, but the disadvantage is that all queries must be
      executed with the criteria API, or every HQL-Query must use this_ as alias.


      A solution would be if the alias would be replayed dynamically:


      defaultCondition = "{alias}._tenantId = :tenantId
      



      but unfortunately it does not! This only works with the criteria API.


      I know that this is more Hibernate related, but i think it would be interesting for
      everyone that work on a multi tenancy system with SEAM.