MyBatis级联查询和缓存配置

在需要进行安全保护的系统中,认证和授权需要反复查询用户、角色、资源授权等信息。这里以用户(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缓存配置。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值