Best practice to apply a universal criteria for multi-tenency in Spring JPA?

Question

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:

SpecificationExecutor (Spring Boot & JPA: Implementing search queries with optional, ranged criteria)
AOP Intercepter (https://www.baeldung.com/hibernate-interceptor)

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

Are you using shared schema for achieving multi tenancy? How are you differentiating each customer — Shiva, Dec 14 '19 at 09:13
@secretsuperstar it's via a column called site_id, which is basically customer ID — Zhongjie Wu, Dec 14 '19 at 12:53

score 2 · Accepted Answer · answered Dec 14 '19 at 22:28

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 {

Thanks a lot for pointing to the right direction. I decided to use the DB-per-tenant multi-tenant strategy, which is much better supported. — Zhongjie Wu, Dec 15 '19 at 14:18

Mohamed Sweelam · Answer 2 · 2019-12-14T16:24:54.500

0

Spring offers two solutions for such kind of requirements, you can use Query natively in your repository, and send bind list to it like this

public interface PurchaseOrderRepository implements CrudRepository<PurchaseOrder, int> {
    @Query(value = "select * from purchase_order where site_id in (?)", nativeQuery = true)
    List<PurchaseOrder> findAll(List<Integer> in);
}

Or if you are looking for a more dynamic way to build your own customizable query, you can use Spring JDBC Template, it has powerful APIs which can handle your case perfectly. Define your own DAO using it and implement some helpers method to be called.

Check this good reference Spring JDBC Template Examples

So you can make query builder which takes the table name and where condition as a parameters ( this if you don’t have same name for the column ) ; and to avoid the confusion in future, choose a good name for the method, for example findAllByTableIn(String tableName, List in)

Also note you have to configure your JDBCTemplate on the target DataSource which will be one of your tenants to access the table on the correct DB.

edited Dec 14 '19 at 16:24

answered Dec 14 '19 at 04:52

Mohamed Sweelam

1,109
8
22

1

This still requires override for every method I implement. Is there a way to do it once and apply it everywhere? for example, leveraging the spring AOP function to add an interceptor predicate those queries. – Zhongjie Wu Dec 14 '19 at 05:59
1

Note that using `in` has limitations depending on database. For example in oracle, you can not exceed that values in `in` more than 1000 – Shiva Dec 14 '19 at 06:11
1

Then you can use jdbcTemplate and create custom generic DAO – Mohamed Sweelam Dec 14 '19 at 06:26
1

@MohamedSweelam that's going to be a lot of DAO to create. A best way would be something like a support of functional calling in `@Where` annotation such as `@Where(clause = "site_id in myapp.SessionSiteId.getId()")` – Zhongjie Wu Dec 14 '19 at 13:08
Please read my answer again, you need only one DAO to achieve this. – Mohamed Sweelam Dec 14 '19 at 16:25

Best practice to apply a universal criteria for multi-tenency in Spring JPA?

2 Answers2