多租户插件

• 官方文档

• 配置类

@Configuration
@MapperScan("org.example.demo15.mapper")
public class MybatisPlusConfig {
 
    /**
     * 新多租户插件配置,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存万一出现问题
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
            @Override
            public Expression getTenantId() {
                return new LongValue(1);
            }
 
            // 这是 default 方法,默认返回 false 表示所有表都需要拼多租户条件
            @Override
            public boolean ignoreTable(String tableName) {
                return !"user".equalsIgnoreCase(tableName);
            }
        }));
        // 如果用了分页插件注意先 add TenantLineInnerInterceptor 再 add PaginationInnerInterceptor
        // 用了分页插件必须设置 MybatisConfiguration#useDeprecatedExecutor = false
//        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
 
//    @Bean
//    public ConfigurationCustomizer configurationCustomizer() {
//        return configuration -> configuration.setUseDeprecatedExecutor(false);
//    }
}

• 实体类

@Data
@Accessors(chain = true)
public class User {
    private Long id;
    /**
     * 租户 ID
     */
    private Long tenantId;
    private String name;
 
    @TableField(exist = false)
    private String addrName;
 
}

• 持久层接口

public interface UserMapper extends BaseMapper<User> {
 
    /**
     * 自定义SQL：默认也会增加多租户条件
     * 参考打印的SQL
     * @return
     */
    Integer myCount();
 
    List<User> getUserAndAddr(@Param("username") String username);
 
    List<User> getAddrAndUser(@Param("name") String name);
}

• xml映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="org.example.demo15.mapper.UserMapper">
 
    <select id="myCount" resultType="java.lang.Integer">
        select count(1) from user
    </select>
 
    <select id="getUserAndAddr" resultType="org.example.demo15.entity.User">
        select u.id, u.name, a.name as addr_name
        from user u
        left join USER_ADDR a on a.user_id=u.id
        <where>
            <if test="username!=null">
                u.name like concat(concat('%',#{username}),'%')
            </if>
        </where>
    </select>
 
    <select id="getAddrAndUser" resultType="org.example.demo15.entity.User">
        select a.name as addr_name, u.id, u.name
        from USER_ADDR a
        left join user u on u.id=a.user_id
        <where>
            <if test="name!=null">
                a.name like concat(concat('%',#{name}),'%')
            </if>
        </where>
    </select>
</mapper>

• 测试

@SpringBootTest
public class TenantTest {
 
    @Resource
    private UserMapper mapper;
 
    /**
     * INSERT INTO user (id, name, tenant_id) VALUES (?, ?, 1)
     * 1549655815066677250(Long), 一一(String)
     *
     * SELECT id, tenant_id, name FROM user WHERE id = ? AND tenant_id = 1
     * 1549655815066677250(Long)
     */
    @Test
    public void aInsert() {
        User user = new User();
        user.setName("一一");
        Assertions.assertTrue(mapper.insert(user) > 0);
        user = mapper.selectById(user.getId());
        Assertions.assertTrue(1 == user.getTenantId());
    }
 
    /**
     * DELETE FROM user WHERE tenant_id = 1 AND id = ?
     * 3(Long)
     */
    @Test
    public void bDelete() {
        Assertions.assertTrue(mapper.deleteById(3L) > 0);
    }
 
    /**
     * UPDATE user SET name = ? WHERE tenant_id = 1 AND id = ?
     * mp(String), 1(Long)
     */
    @Test
    public void cUpdate() {
        Assertions.assertTrue(mapper.updateById(new User().setId(1L).setName("mp")) > 0);
    }
 
    /**
     * SELECT id, tenant_id, name FROM user WHERE tenant_id = 1
     */
    @Test
    public void dSelect() {
        List<User> userList = mapper.selectList(null);
        userList.forEach(u -> Assertions.assertTrue(1 == u.getTenantId()));
    }
 
    /**
     * 自定义SQL：默认也会增加多租户条件
     * 参考打印的SQL
     *
     * SELECT count(1) FROM user WHERE tenant_id = 1
     */
    @Test
    public void manualSqlTenantFilterTest() {
        System.out.println(mapper.myCount());
    }
 
    /**
     * SELECT a.name AS addr_name, u.id, u.name FROM USER_ADDR a LEFT JOIN user u ON u.id = a.user_id AND u.tenant_id = 1
     *
     * SELECT a.name AS addr_name, u.id, u.name FROM USER_ADDR a LEFT JOIN user u ON u.id = a.user_id AND u.tenant_id = 1 WHERE a.name LIKE concat(concat('%', ?), '%')
     * add(String)
     *
     * SELECT u.id, u.name, a.name AS addr_name FROM user u LEFT JOIN user_addr a ON a.user_id = u.id WHERE u.tenant_id = 1
     *
     * SELECT u.id, u.name, a.name AS addr_name FROM user u LEFT JOIN USER_ADDR a ON a.user_id = u.id WHERE u.name LIKE concat(concat('%', ?), '%') AND u.tenant_id = 1
     * J(String)
     */
    @Test
    public void testTenantFilter(){
        mapper.getAddrAndUser(null).forEach(System.out::println);
        mapper.getAddrAndUser("add").forEach(System.out::println);
        mapper.getUserAndAddr(null).forEach(System.out::println);
        mapper.getUserAndAddr("J").forEach(System.out::println);
    }
}