实战 18 Mybatis-plus多表查询和token验证

目录

一、Mybatis-plus多表查询

1、1对1: 1门课程1个老师

 2、1对多：每个老师多门课程

 3、多对多， 学生和课程

二、 token权限验证

---

一、Mybatis-plus多表查询

1、1对1: 1门课程1个老师

<select id="findPage" resultType="com.example.demo.entity.Course">
    select course.*,user.nickname as teacher from course
        left join user
        on course.teacher_id = user.id
    <where>
        <if test="name!=null and name!=''">
            and name like concat ('%',#{name},'%')
        </if>
    </where>
</select>

 2、1对多：每个老师多门课程

 3、多对多， 学生和课程

<?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="com.example.demo.mapper.UserMapper">
    <resultMap id="pageUser" type="com.example.demo.entity.User">
        <result column="id" property="id"></result>
        <result column="username" property="username"></result>
        <result column="nickname" property="nickname"></result>
        <result column="email" property="email"></result>
        <result column="phone" property="phone"></result>
        <result column="address" property="address"></result>
        <result column="create_time" property="createTime"></result>
        <result column="avatar_url" property="avatarUrl"></result>
        <result column="role" property="role"></result>
        <collection property="courses" javaType="java.util.ArrayList" ofType="com.example.demo.entity.Course">
            <result column="teacherCourseName" property="name"></result>
            <result column="teacherScore" property="score"></result>
        </collection>
        <collection property="stuCourses" javaType="java.util.ArrayList" ofType="com.example.demo.entity.Course">
            <result column="stuCourseName" property="name"></result>
            <result column="stuScore" property="score"></result>
        </collection>
    </resultMap>
    <select id="findPage" resultMap="pageUser">
        select user.*,sc.name as stuCourseName,tc.name as teacherCourseName,tc.score as teacherScore, sc.score as stuScore from user
            left join student_course
            on user.id= student_course.student_id
        left join course sc
        on student_course.course_id = sc.id
               left join  course tc on user.id =tc.teacher_id
        <where>
            <if test="username!=null and username!=''">
                and user.username like concat('%',#{username},'%')
            </if>
            <if test="email!=null and email!=''">
                and user.email like concat('%',#{email},'%')
            </if>
            <if test="address!=null and address!=''">
                and user.address like concat('%',#{address},'%')
            </if>
        </where>
    </select>
</mapper>

二、 token权限验证

定义注解：

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documentedpublic @interface AuthAccess {
}

拦截器判断

@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
    String token = request.getHeader("token");
    if (!(handler instanceof HandlerMethod)){
        return true;
    }else {
        HandlerMethod h  = (HandlerMethod) handler;
        AuthAccess authAccess = h.getMethodAnnotation(AuthAccess.class);
        if (authAccess !=null){
            return true;
        }
    }
    if (StrUtil.isBlank(token)){
        throw new ServiceException(Constants.CODE_401,"无token，请重新登录");
    }
    String userId;
    try {
        userId = JWT.decode(token).getAudience().get(0);
    }catch (JWTDecodeException j){
        throw new ServiceException(Constants.CODE_401,"token验证失败,请重新登录");
    }
    //根据token中userId查询数据库    
User user = userService.getById(userId);
    if (user == null){
        throw new ServiceException(Constants.CODE_401,"用户不存在，请重新登录");
    }
    //验证token    
JWTVerifier jwtVerifer =JWT.require(Algorithm.HMAC256(user.getPassword())).build();
    try{
        jwtVerifer.verify(token);
    }catch (JWTVerificationException e){
        throw new ServiceException(Constants.CODE_401,"token验证失败,请重新登录");
    }
    return true;
}