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

Dear 丶 2023-10-13 15:19 4阅读 0赞

实体类

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<SysRole> 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<SysRole> getRoles()
        {
            return roles;
        }
    
        public void setRoles(List<SysRole> 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;
        }
    
        @Override
        public 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

<resultMap type="SysUser" id="SysUserResult">
            <id     property="userId"       column="user_id"      />
            <result property="deptId"       column="dept_id"      />
            <result property="userName"     column="user_name"    />
            <result property="nickName"     column="nick_name"    />
            <result property="email"        column="email"        />
            <result property="phonenumber"  column="phonenumber"  />
            <result property="sex"          column="sex"          />
            <result property="avatar"       column="avatar"       />
            <result property="password"     column="password"     />
            <result property="status"       column="status"       />
            <result property="delFlag"      column="del_flag"     />
            <result property="loginIp"      column="login_ip"     />
            <result property="loginDate"    column="login_date"   />
            <result property="createBy"     column="create_by"    />
            <result property="createTime"   column="create_time"  />
            <result property="updateBy"     column="update_by"    />
            <result property="updateTime"   column="update_time"  />
            <result property="remark"       column="remark"       />
            <association property="dept"    column="dept_id" javaType="SysDept" resultMap="deptResult" />
            <collection  property="roles"   column="role_id" javaType="java.util.List"           resultMap="RoleResult" />
        </resultMap>
    	
        <resultMap id="deptResult" type="SysDept">
            <id     property="deptId"    column="dept_id"     />
            <result property="parentId"  column="parent_id"   />
            <result property="deptName"  column="dept_name"   />
            <result property="ancestors" column="ancestors"   />
            <result property="orderNum"  column="order_num"   />
            <result property="leader"    column="leader"      />
            <result property="status"    column="dept_status" />
        </resultMap>
    	
        <resultMap id="RoleResult" type="SysRole">
            <id     property="roleId"       column="role_id"        />
            <result property="roleName"     column="role_name"      />
            <result property="roleKey"      column="role_key"       />
            <result property="roleSort"     column="role_sort"      />
            <result property="dataScope"     column="data_scope"    />
            <result property="status"       column="role_status"    />
        </resultMap>
    	
        
        <select id="selectUserList" parameterType="SysUser" resultMap="SysUserResult">
    		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 u
    		left 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_id
    		where u.del_flag = '0'
    		<if test="userId != null and userId != ''">
    			AND u.user_id = #{userId}
    		</if>
    		<if test="userName != null and userName != ''">
    			AND u.user_name like concat('%', #{userName}, '%')
    		</if>
    		<if test="status != null and status != ''">
    			AND u.status = #{status}
    		</if>
    		<if test="phonenumber != null and phonenumber != ''">
    			AND u.phonenumber like concat('%', #{phonenumber}, '%')
    		</if>
    		<if test="params.beginTime != null and params.beginTime != ''">
    			AND date_format(u.create_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')
    		</if>
    		<if test="params.endTime != null and params.endTime != ''">
    			AND date_format(u.create_time,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')
    		</if>
    		<if test="deptId != null and deptId != ''">
    			AND (u.dept_id = #{deptId} OR u.dept_id IN ( SELECT t.dept_id FROM sys_dept t WHERE find_in_set(#{deptId}, ancestors) ))
    		</if>
    		
    		${params.dataScope}
    	</select>

输出结果：

{
        "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"
            }
        ]
    }