I'm building an enterprise application (SaaS) with multiple tenant, aka customers. Data between different customers of the same function are stored in the same table and I use a column called "site_id" to define ownership of the data. It looks like this:
PurchaseOrder:
- int id
- int site_id
- String product_name
- int quantity
In each request, a filter processes the session information to determine which site this user has access to. This data is stored in a static thread-local variable which can be retrieved from a static method called Set<Integer> RequestSiteScope.getSiteIds().
Now for those "findAll" queries of auto-created repositories, they will return data of other customers as well.
For example, now I have an interface like this
public interface PurchaseOrderRepository implements CrudRepository<PurchaseOrder, int> {
List<PurchaseOrder> findAll();
}
And I'm handling a request from a user that I know only has access to site_id of 3,4. I want to let the findAll only return data using site_id in (3, 4) criteria. The SQL should look like select * from purchase_order where site_id in (?, ?); with arguments 3, 4.
Of course, I can create each query by hand to always add a where site_id = ? clause, but that is not only tedious but also easy to be forgotten by my future teammates. I looked into the @Query annotation but it won't help because I cannot put a dynamic variable(site_id) into it.
Is there a way I can change the logic of Spring that is responsible for magically implementing the those repository methods, so that I can inject my where clause with a dynamic piece of info (coming from a thread-local class static variable) programmatically?
This concept is a bit like Ruby on Rails ActiveRecord scope concept, with a lamda flavor into it. Ideally, all queries involving table with "site_id" will automatically include this criteria, unless some special procedure is involved (annotation of function block disabling this).
So far, I've been looking into these options but haven't decided an outcome yet:
Update: this article provide all three kinds of solutions to multi-tenancy in Spring: https://medium.com/swlh/multi-tenancy-implementation-using-spring-boot-hibernate-6a8e3ecb251a
Spring Data JPA cannot help here. Generally you are looking for Hibernate Multitenancy. Specifically you are looking for discriminator column multitenancy. But i think even i latest Hibernate versions it's not yet implemented.
Alternatively you can roll your own solution with the @Filter annotation:
@FilterDef(
name = "tenantFilter",
parameters = @ParamDef(name = "tenant", type = "int")
)
@Filter(
name = "tenantFilter",
condition = "tenant_id = :tenant"
)
public class BaseEntity implements Serializable {