多租户插件
-
配置类
@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);
}
}
