Applying Tenant Id for jbdcTemplate Multi-tenancy in single database

I probably wouldn't go down the path of rewriting the queries, etc. I think that this would also be quite a maintenance issue, especially trying to debug when things aren't working and you have fairly hidden code rewriting your queries.

What I would suggest is to have a class, eg. TenantUtils, that has static methods, setTenantId() and getTenantId(). These can operate on a static field (or ThreadLocal if multithreading is required). Now you can call the set method in your controller and the get method in your DAO and not rely on parameter passing. But it is still obvious from the code what the real query is.

Spring follows a similar pattern with their TransactionUtils and DataSourceUtils classes, for example.

Can we have a tenantFilter kind of class to apply the tenant Id as global interceptor? we can use ThreadLocal to store the variable, however injecting would be repeated in each DAO class which i want to avoid. Do you know a generic way to append the tenant id to any query which gets executed ? also, let me know how to skip few queries for the table which doesn't have tenant id column

Ok, maybe I wasn't direct enough in my initial comment. So to your question, I believe the answer is NO, there is no way that I know of that is "built-in" to be able to intercept the queries and modify them. Your only potential option would be to write a class that proxies your JdbcTemplate, i.e. you implement the JdbcOperations interface and you delegate all the calls on to your real JdbcTemplate but modifying the query and parameters along the way.

HOWEVER, I would STRONGLY recommend NOT doing this. I can tell you now that you will spend FAR more time trying to do this than you would ever spend just doing it the way I mentioned before. Some of the reasons... well you would need to make sure this works for quite a number of delegated JdbcOperations methods, you would have to deal with a number of different ways that parameters are passed to the database, but the BIG reason... you would pretty much have to write your own database query parser to make sure you alter the query in just the right way to do what you want. Think about all the different types of queries that would need to be altered in different ways, select, insert, merge, subqueries/joins, tables with aliases, queries with and without existing WHERE conditions, etc, etc, etc. Yes, you may say that it only needs to cover a couple of those scenarios, but that is where you have just added a huge maintenance nightmare. Think about the next person to come along and work on the code, they write a DAO method using a query scenario that you interceptor wasn't coded for. Because this code is hidden away in a totally different class, they don't where to look for this really strange bug that is happening.

we can use ThreadLocal to store the variable, however injecting would be repeated in each DAO class which i want to avoid

I'm not sure you truly got what I was saying in the first comment, you don't have to "inject" anything. e.g. say this is your DAO method now...

public String getNameForId(String id) {
    return jdbcTemplate.queryForObject("select name from people where id = ?", String.class, id);
}

Select allOpen in new window

... all you have to do is change it to this ...

public String getNameForId(String id) {
    return jdbcTemplate.queryForObject("select name from people where id = ? and tenantId = ?", String.class, id, TenantUtils.getTenantId());
}

Select allOpen in new window

Nothing injected, and the query is much clearer as to what it is REALLY doing, no behind the scenes black magic. As you also said, you have some tables that don't have tenantId, so this also makes it clearer which ones do and which don't. And as I said above, when you get to the point of some more complex queries, the extra criteria for tenantId can easily be placed exactly where it should be rather than trying to code something to try and somehow work it out.

How about write a wrapper for JdbcTemplate and injecting tenantId in the wrapper class as a parameter at the end for all queries based on whether tenantId should be injected as parameter. In that case no matter whether the signature of the method changes, it will be changed only in one place. ex:

CompJbbcTemplate extends JdbcTemplate() {

     queryForObject(.....applyTenantId) {

     }
}

Will the above will be a good pattern or we are creating unncessary complexity.

I agree that TenantUtils can be written in a separate utitility class.

Does spring has any interceptor for JdbcTemplate or it doesn't have? like - preHandle prePost like RequestInterceptors

Any comments on having a wrapper for JdbcTemplate and apply the tenant id in one single place?