三表联查,collection一对多使用，association使用

 实体类

import java.util.Date;
import java.util.List;
import javax.validation.constraints.*;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;/*** 用户对象 sys_user* * @author */
public class SysUser extends BaseEntity
{private static final long serialVersionUID = 1L;/** 用户ID */@Excel(name = "用户序号", cellType = ColumnType.NUMERIC, prompt = "用户编号")private String userId;/** 部门ID */@Excel(name = "部门编号", type = Type.IMPORT)private String deptId;/** 用户账号 */@Excel(name = "登录名称")private String userName;/** 用户昵称 */@Excel(name = "用户名称")private String nickName;/** 用户邮箱 */@Excel(name = "用户邮箱")private String email;/** 手机号码 */@Excel(name = "手机号码")private String phonenumber;/** 用户性别 */@Excel(name = "用户性别", readConverterExp = "0=男,1=女,2=未知")private String sex;/** 用户头像 */private String avatar;/** 密码 */private String password;/** 帐号状态（0正常 1停用） */@Excel(name = "帐号状态", readConverterExp = "0=正常,1=停用")private String status;/** 删除标志（0代表存在 2代表删除） */private String delFlag;/** 最后登录IP */@Excel(name = "最后登录IP", type = Type.EXPORT)private String loginIp;/** 最后登录时间 */@Excel(name = "最后登录时间", width = 30, dateFormat = "yyyy-MM-dd HH:mm:ss", type = Type.EXPORT)private Date loginDate;/** 部门对象 */@Excels({@Excel(name = "部门名称", targetAttr = "deptName", type = Type.EXPORT),@Excel(name = "部门负责人", targetAttr = "leader", type = Type.EXPORT)})private SysDept dept;/** 角色对象 */private List roles;/** 角色组 */private String[] roleIds;/** 岗位组 */private String[] postIds;/** 角色ID */private String roleId;public SysUser(){}public SysUser(String userId){this.userId = userId;}public String getUserId(){return userId;}public void setUserId(String userId){this.userId = userId;}public boolean isAdmin(){return isAdmin(this.userId);}public static boolean isAdmin(String userId){return userId != null ;}public String getDeptId(){return deptId;}public void setDeptId(String deptId){this.deptId = deptId;}@Xss(message = "用户昵称不能包含脚本字符")@Size(min = 0, max = 30, message = "用户昵称长度不能超过30个字符")public String getNickName(){return nickName;}public void setNickName(String nickName){this.nickName = nickName;}@Xss(message = "用户账号不能包含脚本字符")@NotBlank(message = "用户账号不能为空")@Size(min = 0, max = 30, message = "用户账号长度不能超过30个字符")public String getUserName(){return userName;}public void setUserName(String userName){this.userName = userName;}@Email(message = "邮箱格式不正确")@Size(min = 0, max = 50, message = "邮箱长度不能超过50个字符")public String getEmail(){return email;}public void setEmail(String email){this.email = email;}@Size(min = 0, max = 11, message = "手机号码长度不能超过11个字符")public String getPhonenumber(){return phonenumber;}public void setPhonenumber(String phonenumber){this.phonenumber = phonenumber;}public String getSex(){return sex;}public void setSex(String sex){this.sex = sex;}public String getAvatar(){return avatar;}public void setAvatar(String avatar){this.avatar = avatar;}public String getPassword(){return password;}public void setPassword(String password){this.password = password;}public String getStatus(){return status;}public void setStatus(String status){this.status = status;}public String getDelFlag(){return delFlag;}public void setDelFlag(String delFlag){this.delFlag = delFlag;}public String getLoginIp(){return loginIp;}public void setLoginIp(String loginIp){this.loginIp = loginIp;}public Date getLoginDate(){return loginDate;}public void setLoginDate(Date loginDate){this.loginDate = loginDate;}public SysDept getDept(){return dept;}public void setDept(SysDept dept){this.dept = dept;}public List getRoles(){return roles;}public void setRoles(List roles){this.roles = roles;}public String[] getRoleIds(){return roleIds;}public void setRoleIds(String[] roleIds){this.roleIds = roleIds;}public String[] getPostIds(){return postIds;}public void setPostIds(String[] postIds){this.postIds = postIds;}public String getRoleId(){return roleId;}public void setRoleId(String roleId){this.roleId = roleId;}@Overridepublic String toString() {return new ToStringBuilder(this,ToStringStyle.MULTI_LINE_STYLE).append("userId", getUserId()).append("deptId", getDeptId()).append("userName", getUserName()).append("nickName", getNickName()).append("email", getEmail()).append("phonenumber", getPhonenumber()).append("sex", getSex()).append("avatar", getAvatar()).append("password", getPassword()).append("status", getStatus()).append("delFlag", getDelFlag()).append("loginIp", getLoginIp()).append("loginDate", getLoginDate()).append("createBy", getCreateBy()).append("createTime", getCreateTime()).append("updateBy", getUpdateBy()).append("updateTime", getUpdateTime()).append("remark", getRemark()).append("dept", getDept()).toString();}
}

 xml

select u.user_id, u.dept_id, u.nick_name, u.user_name, u.email, u.avatar, u.phonenumber, u.sex, u.status, u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time, u.remark, d.dept_name, d.leader,c.role_id,b.role_name from sys_user uleft join sys_dept d on u.dept_id = d.dept_id left join sys_user_role c on u.user_id=c.user_id left join sys_role b on c.role_id = b.role_idwhere u.del_flag = '0'AND u.user_id = #{userId}AND u.user_name like concat('%', #{userName}, '%')AND u.status = #{status}AND u.phonenumber like concat('%', #{phonenumber}, '%')AND date_format(u.create_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')AND date_format(u.create_time,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')AND (u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE find_in_set(#{deptId}, ancestors) ))${params.dataScope}

 输出结果：

{"code": 0,"msg": "成功","data": [{"nickName": "测试1","sex": "1","roles": [{"deptCheckStrictly": false,"flag": false,"roleId": "1","menuCheckStrictly": false,"roleName": "超级管理员","admin": true},{"deptCheckStrictly": false,"flag": false,"roleId": "2","menuCheckStrictly": false,"roleName": "普通角色","admin": true}],"deptId": "103","phonenumber": "15888888888","loginDate": "2023-07-11T09:23:21.000+08:00","admin": true,"remark": "管理员","avatar": "","dept": {"deptName": "研发部门","leader": "测试1","children": [],"deptId": "103"},"userName": "admin","delFlag": "0","userId": "1","createBy": "admin","createTime": "2023-07-10 16:51:52","loginIp": "127.0.0.1","email": "ces1@163.com","status": "0"},{"nickName": "李四","sex": "1","roles": [{"deptCheckStrictly": false,"flag": false,"roleId": "2","menuCheckStrictly": false,"roleName": "普通角色","admin": true}],"deptId": "105","phonenumber": "15666666666","loginDate": "2023-07-10T14:12:18.000+08:00","admin": true,"remark": "测试员","avatar": "","dept": {"deptName": "测试部门","leader": "测试1","children": [],"deptId": "105"},"userName": "ls","delFlag": "0","userId": "2","createBy": "admin","createTime": "2023-07-10 16:51:52","loginIp": "127.0.0.1","email": "ls@qq.com","status": "0"},{"nickName": "ww","sex": "0","roles": [{"deptCheckStrictly": false,"flag": false,"roleId": "1","menuCheckStrictly": false,"roleName": "超级管理员","admin": true}],"deptId": "0001","phonenumber": "","loginDate": "2023-07-10T15:39:41.000+08:00","admin": true,"avatar": "","dept": {"deptName": "技术部","children": [],"deptId": "0001"},"userName": "ww","delFlag": "0","userId": "2f73b4ad816840ac996d32d29f0124ed","createBy": "admin","createTime": "2023-06-15 14:24:17","loginIp": "127.0.0.1","email": "","status": "0"},{"nickName": "kb","sex": "0","roles": [],"deptId": "103","phonenumber": "","loginDate": "2023-07-10T15:33:29.000+08:00","admin": true,"avatar": "","dept": {"deptName": "研发部门","leader": "测试1","children": [],"deptId": "103"},"userName": "kb","delFlag": "0","userId": "3","createBy": "admin","createTime": "2023-06-14 15:08:28","loginIp": "127.0.0.1","email": "","status": "0"},{"nickName": "zl","sex": "0","roles": [{"deptCheckStrictly": false,"flag": false,"roleId": "1","menuCheckStrictly": false,"roleName": "超级管理员","admin": true}],"deptId": "0002","phonenumber": "","loginDate": "2023-06-21T10:09:39.000+08:00","admin": true,"avatar": "","dept": {"deptName": "生产车间","children": [],"deptId": "0002"},"userName": "zl","delFlag": "0","userId": "8f6cfa5457534e0ebb14e496af9f1ac6","createBy": "admin","createTime": "2023-06-15 14:27:31","loginIp": "127.0.0.1","email": "","status": "0"},{"nickName": "mq","sex": "0","roles": [],"deptId": "0001","phonenumber": "","loginDate": "2023-07-10T11:45:30.000+08:00","admin": true,"avatar": "","dept": {"deptName": "技术部","children": [],"deptId": "0001"},"userName": "mq","delFlag": "0","userId": "9397e0df41724492b9c1c0e0b4198f17","createBy": "admin","createTime": "2023-06-16 14:57:04","loginIp": "127.0.0.1","email": "","status": "0"}]
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！