在需要进行安全保护的系统中,认证和授权需要反复查询用户、角色、资源授权等信息。这里以用户(User)、角色(Role)为例,介绍级联查询和Mybatis缓存配置方法。
一、数据模型
缓存类要求所有被序列化的对象必须实现Serializable,因此User、Role类需实现 Serializable接口。
User.java
package chhq.web.jwtrbac.meteshow.model;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
/**
* 用户
*/
@Data
@JsonIgnoreProperties(value = {"handler"}) //避免空值引起Json序列化异常
public class User implements Serializable { //缓存类要求所有被序列化的对象必须实现Serializable接口
private Integer id;
private String username;
private String password;
private Date createDate;
private String realName;
private String email;
private String demo;
private List<Role> roleList; //用户扮演的角色列表
}
Role.java
package chhq.web.jwtrbac.meteshow.model;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import lombok.Data;
import java.util.List;
//角色
@Data
@JsonIgnoreProperties(value = {"handler"}) //避免空值引起Json序列化异常
public class Role {
private Integer id;
private String roleName;
private String description;
private List<Privilege> privilegeList; //角色可访问的资源列表
public Role() {
}
public Role(String roleName, String description) {
this.roleName = roleName;
this.description = description;
}
}
二、Mapper接口
UserMapper.java
package chhq.web.jwtrbac.meteshow.mapper;
import chhq.web.jwtrbac.meteshow.model.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.Date;
import java.util.List;
@Mapper
@Repository
public interface UserMapper {
User findByName(@Param("username") String username, @Param("containPassword") Boolean containPassword);
}
RoleMapper.java
package chhq.web.jwtrbac.meteshow.mapper;
import chhq.web.jwtrbac.meteshow.model.Role;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Mapper
@Repository
public interface RoleMapper {
Role findByName(String roleName);
}
三、XML配置
使用缓存可以使应用更快地获取数据,避免频繁的数据库交互,尤其是在查询越多、缓存命中率越高的情况下,使用缓存的作用就越明显。MyBatis一级缓存默认会启用,无需进行额外配置,但它只存在于SqlSession的生命周期中。二级缓存不同于一级缓存,可以理解为存在于SqlSessionFactory生命周期中。在MyBatis的全局配置settings中有一个参数cacheEnabled,这个参数是二级缓存的全局开关,默认值是true,初始状态为启用状态。因此,在保证二级缓存的全局配置开启的情况下,只需要在xml文件中添加<cache/>元素即可开启二级缓存。
一个用户(User)扮演0..N个角色(Role),每个角色可以访问0...N个资源(Privilege),当使用RBAC模型进行安全控制时,它们之间的关联信息需使用级联查询,例如查询用户的角色列表。这里采用fetchType="lazy",当真正获取roleList时,才向数据库发起SQL查询。
UserMapper.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="chhq.web.jwtrbac.meteshow.mapper.UserMapper"><!-- Mapper命名空间,select、insert的id=“XX”对应mapper接口名称“XX” -->
<cache/> <!-- 允许使用二级缓存 -->
<resultMap id="userMap" type="chhq.web.jwtrbac.meteshow.model.User"><!-- 对应model数据模型 -->
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<result property="createDate" column="createDate"/>
<result property="realName" column="realName"/>
<result property="demo" column="demo"/>
</resultMap>
<!-- 级联角色信息 -->
<resultMap id="userRoleListMap" extends="userMap" type="chhq.web.jwtrbac.meteshow.model.User">
<collection property="roleList"
select="chhq.web.jwtrbac.meteshow.mapper.RoleMapper.findRolesByUserId"
column="{userId=id}"
/>
</resultMap>
<select id="findByName" resultMap="userRoleListMap">
select
<choose>
<when test="containPassword==true">
id, username, password, createDate, realName, email, place, demo
</when>
<otherwise>
id, username, createDate,realName, email, demo
</otherwise>
</choose>
from User
where username=#{username}
</select>
</mapper>
RoleMapper.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="chhq.web.jwtrbac.meteshow.mapper.RoleMapper"><!-- Mapper命名空间,select、insert的id=“XX”对应mapper接口名称“XX” -->
<resultMap id="roleMap" type="chhq.web.jwtrbac.meteshow.model.Role"><!-- 对应model数据模型 -->
<id property="id" column="id"/>
<result property="roleName" column="roleName"/>
<result property="description" column="description"/>
</resultMap>
<resultMap id="rolePrivilegeListMap" extends="roleMap"
type="chhq.web.jwtrbac.meteshow.model.Role">
<!-- column属性配置的{roleId=id},roleId是select指定方法
selectPrivilegeByRoleId查询中的参数,id是当前查询selectRoleByUserId中查询出的角色id
-->
<collection property="privilegeList"
column="{roleId=id}"
select="chhq.web.jwtrbac.meteshow.mapper.PrivilegeMapper.findPrivilegesByRoleId"/>
</resultMap>
<select id="findByName" resultMap="rolePrivilegeListMap">
select id, roleName, description
from Role
where roleName = #{roleName}
</select>
</mapper>
四、测试缓存
编写测试类测试缓存使用情况
package chhq.web.jwtrbac.meteshow.mapper;
import chhq.web.jwtrbac.meteshow.model.User;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.Date;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTest {
@Autowired
private UserMapper userMapper;
/**
* 测试缓存
*/
@Test
public void cache() {
User user1 = userMapper.findByNamePrincipal("admin");
User user2 = userMapper.findByNamePrincipal("admin");
Assert.assertEquals(user1.getUsername(), user2.getUsername());
}
}
运行上面的测试代码,发现MyBatis只执行了一次SQL查询,说明缓存已经生效。
2020-11-27 15:42:06.574 DEBUG 7416 --- [ main] org.mybatis.spring.SqlSessionUtils : Creating a new SqlSession
2020-11-27 15:42:06.578 DEBUG 7416 --- [ main] org.mybatis.spring.SqlSessionUtils : SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@61d011e] was not registered for synchronization because synchronization is not active
2020-11-27 15:42:06.580 DEBUG 7416 --- [ main] c.w.jwtrbac.meteshow.mapper.UserMapper : Cache Hit Ratio [chhq.web.jwtrbac.meteshow.mapper.UserMapper]: 0.0
2020-11-27 15:42:06.580 DEBUG 7416 --- [ main] o.s.jdbc.datasource.DataSourceUtils : Fetching JDBC Connection from DataSource
2020-11-27 15:42:06.614 DEBUG 7416 --- [ main] o.m.s.t.SpringManagedTransaction : JDBC Connection [HikariProxyConnection@320706431 wrapping com.mysql.cj.jdbc.ConnectionImpl@73afe2b7] will not be managed by Spring
2020-11-27 15:42:06.614 DEBUG 7416 --- [ main] c.w.j.m.m.U.findByNamePrincipal : ==> Preparing: select id, username, password, email from User where username=?
2020-11-27 15:42:06.615 DEBUG 7416 --- [ main] c.w.j.m.m.U.findByNamePrincipal : ==> Parameters: admin(String)
2020-11-27 15:42:06.638 DEBUG 7416 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@5a26c047
2020-11-27 15:42:06.654 DEBUG 7416 --- [ main] c.w.j.m.m.U.findByNamePrincipal : <== Total: 1
2020-11-27 15:42:06.664 DEBUG 7416 --- [ main] org.mybatis.spring.SqlSessionUtils : Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@61d011e]
2020-11-27 15:42:06.664 DEBUG 7416 --- [ main] org.mybatis.spring.SqlSessionUtils : Creating a new SqlSession
2020-11-27 15:42:06.664 DEBUG 7416 --- [ main] org.mybatis.spring.SqlSessionUtils : SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2b9ecd05] was not registered for synchronization because synchronization is not active
2020-11-27 15:42:06.668 DEBUG 7416 --- [ main] c.w.jwtrbac.meteshow.mapper.UserMapper : Cache Hit Ratio [chhq.web.jwtrbac.meteshow.mapper.UserMapper]: 0.5
2020-11-27 15:42:06.668 DEBUG 7416 --- [ main] org.mybatis.spring.SqlSessionUtils : Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2b9ecd05]
把UserMapper.xml的<cache/>元素删除,再运行测试,由于一级缓存只存在于SqlSession的生命周期中,则同样的SQL查询执行了2次。
2020-11-27 15:39:02.302 DEBUG 8248 --- [ main] org.mybatis.spring.SqlSessionUtils : Creating a new SqlSession
2020-11-27 15:39:02.305 DEBUG 8248 --- [ main] org.mybatis.spring.SqlSessionUtils : SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@212e39ee] was not registered for synchronization because synchronization is not active
2020-11-27 15:39:02.307 DEBUG 8248 --- [ main] o.s.jdbc.datasource.DataSourceUtils : Fetching JDBC Connection from DataSource
2020-11-27 15:39:02.328 DEBUG 8248 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@47980b5b
2020-11-27 15:39:02.338 DEBUG 8248 --- [ main] o.m.s.t.SpringManagedTransaction : JDBC Connection [HikariProxyConnection@2068191651 wrapping com.mysql.cj.jdbc.ConnectionImpl@34414ffc] will not be managed by Spring
2020-11-27 15:39:02.338 DEBUG 8248 --- [ main] c.w.j.m.m.U.findByNamePrincipal : ==> Preparing: select id, username, password, email from User where username=?
2020-11-27 15:39:02.339 DEBUG 8248 --- [ main] c.w.j.m.m.U.findByNamePrincipal : ==> Parameters: admin(String)
2020-11-27 15:39:02.378 DEBUG 8248 --- [ main] c.w.j.m.m.U.findByNamePrincipal : <== Total: 1
2020-11-27 15:39:02.379 DEBUG 8248 --- [ main] org.mybatis.spring.SqlSessionUtils : Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@212e39ee]
2020-11-27 15:39:02.379 DEBUG 8248 --- [ main] org.mybatis.spring.SqlSessionUtils : Creating a new SqlSession
2020-11-27 15:39:02.379 DEBUG 8248 --- [ main] org.mybatis.spring.SqlSessionUtils : SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e681097] was not registered for synchronization because synchronization is not active
2020-11-27 15:39:02.379 DEBUG 8248 --- [ main] o.s.jdbc.datasource.DataSourceUtils : Fetching JDBC Connection from DataSource
2020-11-27 15:39:02.379 DEBUG 8248 --- [ main] o.m.s.t.SpringManagedTransaction : JDBC Connection [HikariProxyConnection@354980344 wrapping com.mysql.cj.jdbc.ConnectionImpl@34414ffc] will not be managed by Spring
2020-11-27 15:39:02.379 DEBUG 8248 --- [ main] c.w.j.m.m.U.findByNamePrincipal : ==> Preparing: select id, username, password, email from User where username=?
2020-11-27 15:39:02.379 DEBUG 8248 --- [ main] c.w.j.m.m.U.findByNamePrincipal : ==> Parameters: admin(String)
2020-11-27 15:39:02.413 DEBUG 8248 --- [ main] c.w.j.m.m.U.findByNamePrincipal : <== Total: 1
2020-11-27 15:39:02.414 DEBUG 8248 --- [ main] org.mybatis.spring.SqlSessionUtils : Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5e681097]
cache可以配置eviction、flushInterval、size、readOnly等参数,详见《MyBatis从入门到精通》(刘增辉著)第七章Mybatis缓存配置。