Shulou(Shulou.com)06/01 Report--

This article is about how to use Mybatis-plus to implement a multi-tenant architecture. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Multi-tenancy (Multi-Tenant) is an important concept in SaaS, it is a software architecture technology, in the environment of multiple tenants, share the same system instance, and the data between tenants is isolated, that is to say, a tenant can not access the data of other tenants. Based on different isolation levels, there are usually three implementations:

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

2. Share DataBase between tenants and use independent Schema

3. Share Schema among tenants, and add tenant field to the table, with the highest degree of shared data and the lowest level of isolation.

Mybatis-plus provides a multi-tenancy solution based on paging plug-ins at the layer 3 isolation level, which we introduce. Before you officially begin, first make preparations to create two tables, adding the tenant field tenant_id 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`))

Import the required dependencies in the project:

Com.baomidou mybatis-plus-boot-starter 3.3.2 com.github.jsqlparser jsqlparser 3.1

Mybatis-plus configuration class:

@ EnableTransactionManagement (proxyTargetClass = true) @ Configurationpublic class MybatisPlusConfig {@ Bean public PaginationInterceptor paginationInterceptor () {PaginationInterceptor paginationInterceptor = new PaginationInterceptor (); List 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 () {return "tenant_id";} @ Override public boolean doTableFilter (String tableName) {return false;}}); sqlParserList.add (tenantSqlParser); paginationInterceptor.setSqlParserList (sqlParserList); return paginationInterceptor;}}

The main functions implemented here are:

Create an SQL parser collection

Create a tenant SQL parser

Set up the tenant processor to deal with the tenant logic

For the time being, the tenant's id is fixed as 3 for testing. The test executes full table statements:

Public List getUserList () {return userMapper.selectList (new LambdaQueryWrapper (). IsNotNull (User::getId));}

Using the plug-in to parse the executed SQL statement, you can see that the tenant filter condition is automatically added to the query condition:

So in the actual project, how to pass the tenant information to the tenant processor? depending on the situation, we can obtain it from the cache or request header, taking the Request request header as an example:

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

When the front end initiates the http request, the tenantId field is added to the Header. After the backend obtains it in the processor, it is set to the tenant filter condition of the current request.

If it is based on the request header carrying tenant information, then you may encounter a pit in use, if the new asynchronous thread will not automatically carry the Request request of the current thread when using multi-thread.

@ Overridepublic List getUserListByFuture () {Callable getUser= ()-> userMapper.selectList (new LambdaQueryWrapper (). IsNotNull (User::getId)); FutureTask future=new FutureTask (getUser); new Thread (future). Start (); try {return future.get ();} catch (Exception e) {e.printStackTrace ();} return null;}

If you execute the above method, you can see that the current Request request cannot be obtained, so the tenant id cannot be obtained, which will result in a subsequent error null pointer exception:

If you modify it, it is also very simple. Turn on child thread sharing of RequestAttributes and modify the above code:

@ Overridepublic List getUserListByFuture () {ServletRequestAttributes sra = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes (); Callable getUser= ()-> {RequestContextHolder.setRequestAttributes (sra, true); return userMapper.selectList (new LambdaQueryWrapper (). IsNotNull (User::getId));}; FutureTask future=new FutureTask (getUser); new Thread (future). Start (); try {return future.get ();} catch (Exception e) {e.printStackTrace () } return null;}

After this modification, the tenant information can be obtained normally in the asynchronous thread.

Then, some partners may want to ask, in the business, not all queries need to filter tenant conditions, ah, in view of this situation, there are two ways to deal with it.

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

@ Overridepublic boolean doTableFilter (String tableName) {List IGNORE_TENANT_TABLES= Arrays.asList ("dept"); return IGNORE_TENANT_TABLES.stream () .anyMatch (e-> e.equalsIgnoreCase (tableName));}

In this way, all queries in the dept table are not filtered:

2. If there are some specific SQL statements that do not want to be filtered by tenants, you can open them in the form of @ SqlParser annotation. Note that annotations can only be added to the methods of the Mapper API:

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

Or specify the method to be filtered in the paging interceptor:

@ Beanpublic PaginationInterceptor paginationInterceptor () {PaginationInterceptor paginationInterceptor = new PaginationInterceptor (); paginationInterceptor.setSqlParserFilter (metaObject- > {MappedStatement ms = SqlParserHelper.getMappedStatement (metaObject); / / corresponding to the method if in Mapper and dao ("com.cn.tenant.dao.UserMapper.selectUserByPhone" .equals (ms.getId () {return true;} return false;});.}

The functions of the above two methods are the same, but if you need to filter a lot of SQL statements, then the second method will be more troublesome to configure, so it is recommended to filter through annotations.

In addition, another trap that is easy to step on is not to copy the tenant id field when copying Bean, otherwise it will cause an error in the SQL statement:

Public void createSnapshot (Long userId) {User user = userMapper.selectOne (new LambdaQueryWrapper (). Eq (User::getId, userId)); UserSnapshot userSnapshot=new UserSnapshot (); BeanUtil.copyProperties (user,userSnapshot); userSnapshotMapper.insert (userSnapshot);}

If you check the error report, you can see that if the tenant field of Bean is not empty, SQL automatically adds another tenant query condition, which results in an error:

We can modify the copy Bean statement to manually ignore the tenant id field, which is the BeanUtil utility class of hutool, and you can add the ignore field.

BeanUtil.copyProperties (user,userSnapshot, "tenantId")

After ignoring the copy of the tenant id, the query can be executed normally.

Finally, let's take a look at the support for join table queries. First, take a look at the SQL that contains subqueries:

Select ("select * from user where id in (select id from user_snapshot)") List selectSnapshot ()

Looking at the execution results, you can see the tenant query conditions that are also automatically added inside the subquery:

Let's take a look at the join table query using Join:

@ Select ("select u.* from user u left join user_snapshot us on u.id=us.id") List selectSnapshot ()

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

Take another look at the common join table query that does not use Join:

@ Select ("select u.* from user u, user_snapshot us,dept d where u.id=us.id and d.id is not null") List selectSnapshot ()

Looking at the execution result, you can see that in this case, only the tenant filter condition is added to the first table after the FROM keyword, so if you use this query method, you need to note that the user needs to manually add the tenant filter to the SQL statement.

Thank you for reading! This is the end of the article on "how to use Mybatis-plus to achieve multi-tenant architecture". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.