mybatis注解版实现多表联查
需求:
用户有角色,角色有权限,需要一次取用户信息包含角色信息及其对应权限
实体类:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package cn.zytao.taosir.common.model.user;
import java.io.Serializable;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;
import com.baomidou.mybatisplus.annotations.TableField;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
@Data
@ApiModel(value="用户实体")
public class User implements Serializable{
/**
*
*/
private static final long serialVersionUID = -2389902440625641568L;
@ApiModelProperty(name ="id",value = "ID主键")
private String id;
@ApiModelProperty(name ="username",value = "用户名")
private String username;
@ApiModelProperty(name ="password",value = "密码")
private String password;
@ApiModelProperty(name ="nickname",value = "昵称")
private String nickname;
@ApiModelProperty(name ="isInsiders",value = "是否内部人员")
private int isInsiders;
@ApiModelProperty(name ="phone",value = "联系电话")
private String phone;
@ApiModelProperty(name ="email",value = "邮箱")
private String email;
@ApiModelProperty(name ="qq",value = "QQ")
private String qq;
@ApiModelProperty(name ="wechat",value = "微信")
private String wechat;
@ApiModelProperty(name ="question",value = "找回密码问题")
private String question;
@ApiModelProperty(name ="answer",value = "找回密码答案")
private String answer;
@ApiModelProperty(name ="createTime",value = "创建时间")
private Date createTime;
@ApiModelProperty(name ="updateTime",value = "更新时间")
private Date updateTime;
@TableField(exist = false)
private Set roles=new HashSet();
}
用户
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package cn.zytao.taosir.common.model.user;
import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
import com.baomidou.mybatisplus.annotations.TableField;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
*
* @author taosir
*
*/
@Data
@ApiModel(value="角色实体")
public class Role implements Serializable{
/**
*
*/
private static final long serialVersionUID = 4710406435745633366L;
@ApiModelProperty(name ="id",value = "ID主键")
private String id;
@ApiModelProperty(name ="roleCode",value = "角色代码")
private String roleCode;
@ApiModelProperty(name ="roleName",value = "角色名称")
private String roleName;
@TableField(exist = false)
private Set permissions=new HashSet();
}
角色
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package cn.zytao.taosir.common.model.user;
import java.io.Serializable;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
@Data
@ApiModel(value="权限实体")
public class Permission implements Serializable{
/**
*
*/
private static final long serialVersionUID = 1545962188193104351L;
@ApiModelProperty(name ="id",value = "ID主键")
private String id;
@ApiModelProperty(name ="permissionCode",value = "权限代码")
private String permissionCode;
@ApiModelProperty(name ="permissionName",value = "权限名称")
private String permissionName;
@ApiModelProperty(name="path",value="映射路径")
private String path;
}
权限
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package cn.zytao.taosir.common.model.user;
import java.io.Serializable;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
@Data
@ApiModel(value="用户角色关系表")
public class UserRole implements Serializable{
/**
*
*/
private static final long serialVersionUID = 6163369825048118489L;
@ApiModelProperty(name ="id",value = "ID主键")
private String id;
@ApiModelProperty(name ="username",value = "用户名")
private String username;
@ApiModelProperty(name ="roleCode",value = "角色代码")
private String roleCode;
}
用户角色关系
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package cn.zytao.taosir.common.model.user;
import java.io.Serializable;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
@Data
@ApiModel(value="角色权限关系表")
public class RolePermission implements Serializable{
/**
*
*/
private static final long serialVersionUID = -7522662317811377590L;
@ApiModelProperty(name ="id",value = "ID主键")
private String id;
@ApiModelProperty(name ="roleCode",value = "角色代码")
private String roleCode;
@ApiModelProperty(name ="permissionCode",value = "权限代码")
private String permissionCode;
}
角色权限关系
注解是swagger2的,无需关注。另外有个 @TableField(exist = false) 该注解标识这个字段非数据库字段
持久层处理:
@Select("select * from role where role_code in(select role_code from user_role where username = #{username})")
Set getUserRoles(String username);
@Select("select * from permission where permission_code in (select permission_code from role_permission where role_code = #{roleCode})")
Set getRolePermissions(String roleCode);
第一个sql为获取用户所拥有角色,第二个sql为获取角色所拥有权限
yml的配置上:
mybatis-plus:
type-aliases-package: cn.zytao.taosir.common.model.user
configuration:
map-underscore-to-camel-case: true
映射实体类,开起对下划线转大写的处理
业务处理上:
public User findByUsername(String username) {
User user = userMapper.findByUsername(username);
Set roles = userMapper.getUserRoles(username);
for (Role role : roles) {
role.setPermissions(roleMapper.getRolePermissions(role.getRoleCode()));
}
user.setRoles(roles);
return user;
}
这样就可以很方便的解决多表的联查~因为本人比较懒,不想写配置版,也不喜欢在注解上用@Results来标示结果集
仍在不断学习中,如有不妥还望指教~
作者:涛先森の日常
出处:https://www.cnblogs.com/it-taosir/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接,否则保留追究法律责任的权利。
|