Multitenancy with hibernate filters
swenvogel Apr 21, 2011 4:15 AMHi,
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.