Multi-tenant architecture based on Mybatis- Plus | Java swipe card

This article is participating in “Java Theme Month – Java Swipe Card”, see the activity link for details

Multi-tenant is an important concept in SaaS. It is a software architecture technology in which multiple tenants share the same system instance and the data between tenants is isolated, meaning that one Tenant cannot access the data of other tenants. Based on different isolation levels, there are usually three implementations:

1. Each tenant uses an independent DataBase, high isolation level, good performance, but high cost

2. Tenants share the DataBase and use an independent Schema

3. Tenants share schemas and add tenant fields to the table. The highest level of shared data is achieved and the lowest isolation level is achieved.

Mybatis- Plus provides a multi-tenant solution based on paging plugins at the layer 3 isolation level, as described here. Create two tables and add the tenant field tenant_ID to each table after the base field:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `address` varchar(64) DEFAULT NULL,
  `tenant_id` bigint(20) DEFAULT NULL.PRIMARY KEY (`id`)
)
CREATE TABLE `dept` (
  `id` bigint(20) NOT NULL,
  `dept_name` varchar(64) DEFAULT NULL,
  `comment` varchar(128) DEFAULT NULL,
  `tenant_id` bigint(20) DEFAULT NULL.PRIMARY KEY (`id`)
)
Copy the code

Import the required dependencies into the project:

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.2 rainfall distribution on 10-12</version>
</dependency>
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>3.1</version>
</dependency>
Copy the code

Mybatis- Plus configuration class:

@EnableTransactionManagement(proxyTargetClass = true)
@Configuration
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor(a) {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();

        List<ISqlParser> sqlParserList=new ArrayList<>();
        TenantSqlParser tenantSqlParser=new TenantSqlParser();
        tenantSqlParser.setTenantHandler(new TenantHandler() {
            @Override
            public Expression getTenantId(boolean select) {               
                String tenantId = "3";
                return new StringValue(tenantId);
            }

            @Override
            public String getTenantIdColumn(a) {
                return "tenant_id";
            }

            @Override
            public boolean doTableFilter(String tableName) {
                return false; }}); sqlParserList.add(tenantSqlParser); paginationInterceptor.setSqlParserList(sqlParserList);returnpaginationInterceptor; }}Copy the code

The main functions are as follows:

Create the SQL parser collection
Create a tenant SQL parser
Set the tenant processor to process tenant logic

For the time being, the tenant id is fixed as 3 to test. Test execution of full sentence:

public List<User> getUserList(a) {
    return userMapper.selectList(new LambdaQueryWrapper<User>().isNotNull(User::getId));
}
Copy the code

Using the plugin to parse the executed SQL statement, you can see that the tenant filter criteria are automatically added after the query criteria:

In the actual project, how to transfer tenant information to tenant processor? Depending on the situation, we can get it from cache or Request header. Take the Request header as an example:

@Override
public Expression getTenantId(boolean select) {
    ServletRequestAttributes attributes=(ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
    HttpServletRequest request = attributes.getRequest();
    String tenantId = request.getHeader("tenantId");
    return new StringValue(tenantId);
}
Copy the code

When the front-end sends an HTTP request, the tenantId field is added to the Header. The back-end obtains the tenantId field from the processor and sets it as the tenant filtering condition for the current request.

If it is based on the Request header carrying tenant information, you may encounter a loophole in usage. When using multiple threads, new asynchronous threads do not automatically carry requests from the current thread.

@Override
public List<User> getUserListByFuture(a) {
    Callable getUser=()-> userMapper.selectList(new LambdaQueryWrapper<User>().isNotNull(User::getId));
    FutureTask<List<User>> future=new FutureTask<>(getUser);
    new Thread(future).start();
    try {
        return future.get();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}
Copy the code

If the preceding method is executed, it can be seen that the current Request cannot be obtained. Therefore, the tenant ID cannot be obtained, resulting in a null pointer exception.

Change is as simple as opening the RequestAttributes child thread share and modifying the code above:

@Override
public List<User> getUserListByFuture(a) {
    ServletRequestAttributes sra = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
    Callable getUser=()-> {
        RequestContextHolder.setRequestAttributes(sra, true);
        return userMapper.selectList(new LambdaQueryWrapper<User>().isNotNull(User::getId));
    };
    FutureTask<List<User>> future=new FutureTask<>(getUser);
    new Thread(future).start();
    try {
        return future.get();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}
Copy the code

After this modification, the asynchronous thread can also obtain tenant information normally.

So, some partners may want to ask, in the business, not all queries need to filter tenant conditions ah, for this case, there are two ways to handle.

If all SQL operations on the entire table do not need to be performed on the tenant, then filter the table, modify the doTableFilter method, add the name of the table:

@Override
public boolean doTableFilter(String tableName) {
    List<String> IGNORE_TENANT_TABLES= Arrays.asList("dept");
    return IGNORE_TENANT_TABLES.stream().anyMatch(e->e.equalsIgnoreCase(tableName));
}
Copy the code

Thus, all queries in the DEPT table are not filtered:

If there are certain SQL statements that do not want to be filtered by the tenant, this can be enabled with the @sqlParser annotation. Note that the annotation can only be applied to methods on the Mapper interface:

@SqlParser(filter = true)
@Select("select * from user where name =#{name}")
User selectUserByName(@Param(value="name") String name);
Copy the code

Or specify the method to filter in the page blocker:

@Bean
public PaginationInterceptor paginationInterceptor(a) {
    PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
    paginationInterceptor.setSqlParserFilter(metaObject->{
        MappedStatement ms = SqlParserHelper.getMappedStatement(metaObject);
        // Maps to methods in Mapper and DAO
        if("com.cn.tenant.dao.UserMapper.selectUserByPhone".equals(ms.getId())){
            return true;
        }
        return false; }); . }Copy the code

The above two methods provide the same functions. However, if many SQL statements need to be filtered, the second method is more difficult to configure. Therefore, filtering through annotations is recommended.

Do not copy the tenant ID field when you copy the Bean. Otherwise, the SQL statement will report error:

public void createSnapshot(Long userId){
    User user = userMapper.selectOne(new LambdaQueryWrapper<User>().eq(User::getId, userId));
    UserSnapshot userSnapshot=new UserSnapshot();
    BeanUtil.copyProperties(user,userSnapshot);
    userSnapshotMapper.insert(userSnapshot);
}
Copy the code

If the tenant field of the Bean is not empty, SQL automatically adds the tenant query condition again.

We can modify the copy Bean statement to manually ignore the tenant ID field, using huTool’s BeanUtil utility class, by adding the ignore field.

BeanUtil.copyProperties(user,userSnapshot,"tenantId");
Copy the code

After the copy of the vstore ID is ignored, the query can be performed normally.

Finally, look at the support for couplet table queries. First, look at SQL containing subqueries:

@Select("select * from user where id in (select id from user_snapshot)")
List<User> selectSnapshot(a);
Copy the code

After you view the execution result, you can see that the tenant query conditions are automatically added in the subquery:

Let’s see how to use Join to query tables:

@Select("select u.* from user u left join user_snapshot us on u.id=us.id")
List<User> selectSnapshot(a);
Copy the code

Similarly, the filter criteria for the tenant are added to both the left and right tables:

Take a look at a normal Join table query without a Join:

@Select("select u.* from user u ,user_snapshot us,dept d where u.id=us.id and d.id is not null")
List<User> selectSnapshot(a);
Copy the code

In this case, only the first table after the “FROM” keyword is added with tenant filtering conditions. Therefore, you need to pay extra attention to this query mode. Users need to manually add tenant filtering conditions in the SQL statement.

Multi-tenant architecture based on Mybatis- Plus | Java swipe card

Related Posts

MATLAB uses CNN fitting regression model to predict the rotation Angle of handwritten numbers (convolutional neural network)

Filters and listeners in SpringBoot

GridLayout layout