jpa 联合查询数据，查询用户信息与部门信息

偏执的太偏执、 2022-01-27 04:39 388阅读 0赞

> 查询用户信息与部门信息部分字段，涉及到User实体、Department实体，新建一个联合信息类接受查询数据

*  User实体：
    
        @Entity
          @Table(name = "pmi_t_user",
                  indexes = { 
                          @Index(name = "PERSON_INDX_0", columnList = "user_name"),
                          @Index(name = "PERSON_INDX_1", columnList = "login_name"),
                          @Index(name = "PERSON_INDX_1", columnList = "login_pwd")  }
          )
          @org.hibernate.annotations.Table(appliesTo = "pmi_t_user",comment="用户信息表")
          @Component
          @ApiModel(value = "用户实体")
          @NamedEntityGraph(name="User.all",attributeNodes={ 
                  @NamedAttributeNode("roles")
          })
          public class User implements Serializable { 
          
              /** * 主键id */
              @ApiModelProperty(value = "主键id")
              @Id
              @GeneratedValue(strategy = GenerationType.IDENTITY)
              private Long id;
          
          
              /** * 用户名 */
              @ApiModelProperty(value = "用户名", required = true)
              @NotBlank(message = "用户名不能为空")
              @Column(name = "user_name", nullable = false,columnDefinition="varchar(100) COMMENT '用户名'")
              private String userName;
          
              /** * 登录名app_t_role */
              @ApiModelProperty(value = "登录名", required = true)
              @NotBlank(message = "登录名不能为空")
              @Column(name = "login_name", nullable = false,columnDefinition="varchar(64) COMMENT '登录名'")
              private String loginName;
          
              /** * 登录密码 */
              @ApiModelProperty(value = "登录密码", required = true)
              @NotBlank(message = "登录密码不能为空")
              @Column(name = "login_pwd", nullable = false,columnDefinition="varchar(64) COMMENT '登录密码'")
              private String loginPwd;
          
              /** * 默认部门 */
              @ApiModelProperty(value = "默认部门")
              @Column(name = "dept_id", nullable = false,columnDefinition="bigint(20) COMMENT '默认部门'")
              private Long deptId;
          
              /** * 用户邮箱 */
              @ApiModelProperty(value = "用户邮箱")
              @Email(message = "邮箱格式不正确")
              @Column(name = "user_mail", columnDefinition="varchar(100) COMMENT '用户邮箱'")
              private String userMail;
          
              /** * 移动电话 */
              @ApiModelProperty(value = "移动电话")
              @Column(name = "user_mobile", columnDefinition="bigint(11) COMMENT '移动电话'")
              private Long userMobile;
          
              /** * 办公电话 */
              @ApiModelProperty(value = "办公电话")
              @Column(name = "user_oph", columnDefinition="varchar(20) COMMENT '办公电话'")
              private String userOph;
          
              /** * 是否管理员 */
              @ApiModelProperty(value = "是否管理员")
              @Column(name = "is_admin", nullable = false,columnDefinition="tinyint(1) default '0' COMMENT '是否管理员'")
              private boolean isAdmin;
          
              /** * 关联角色 */
              @ApiModelProperty(value = "关联角色")
              @ManyToMany(fetch = FetchType.LAZY,targetEntity = Role.class)
              @JoinTable(name = "pmi_t_user_role", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
              private List<Role> roles;
          
              /** * 登录错误次数 */
              @ApiModelProperty(value = "登录错误次数")
              @Column(name = "login_fail_count", columnDefinition="tinyint(1) COMMENT '登录错误次数'")
              private int loginFailCount;
          
          
              /** * 锁定ip */
              @ApiModelProperty(value = "锁定ip")
              @Column(name = "locked_ip", columnDefinition="varchar(20) COMMENT '锁定ip'")
              private String lockedIp;
          
              /** * 锁定时间 */
              @ApiModelProperty(value = "锁定时间")
              @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
              @Column(name = "locked_time", columnDefinition="datetime COMMENT '锁定时间'")
              private Timestamp lockedTime;
          
              /** * 排序号 */
              @ApiModelProperty(value = "排序号")
              @Column(name = "order_no", nullable = false,columnDefinition="int(10) default '0' COMMENT '排序号'")
              private int orderNo;
          
              /** * 备注 */
              @ApiModelProperty(value = "备注")
              @Column(name = "noto",columnDefinition="varchar(2000) COMMENT '备注'")
              private String note;
          
              /** * 创建时间 */
              @ApiModelProperty(value = "创建时间")
              @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
              @Column(name = "create_time", columnDefinition="datetime COMMENT '创建时间'")
              private Timestamp createTime;
          
              /** * 创建用户 */
              @ApiModelProperty(value = "创建用户")
              @Column(name = "create_user_id", nullable = false,columnDefinition="bigint(20) COMMENT '创建用户'")
              private Long createUserId;
          
              /** * 数据状态 1:正常 -1：删除 */
              @ApiModelProperty(value = "数据状态")
              @Column(name = "db_status", nullable = false,columnDefinition="tinyint(1) DEFAULT '1' COMMENT '数据状态 1:正常 -1：删除'")
              private int dbStatus;
              
              。。。。。。省略set get
        }
 *  Department实体:
    
        @Entity
          @Table(name = "pmi_t_department")
          @org.hibernate.annotations.Table(appliesTo = "pmi_t_department",comment="部门")
          @ApiModel(value = "部门")
          public class Department implements Serializable { 
              /** * 主键id */
              @ApiModelProperty(value = "主键id")
              @NotNull(message = "主键id不能为空")
              @Id
              @GeneratedValue(strategy = GenerationType.IDENTITY)
              private Long id;
              /** * 部门名称 */
              @ApiModelProperty(value = "部门名称")
              @NotNull(message = "部门名称不能为空")
              @Length(max = 100,min=1, message = "部门名称长度大于1小于100")
              @Column(name = "dept_name", nullable = false,columnDefinition="varchar(100) COMMENT '部门名称'")
              private String deptName;
              /** * 组织机构编码 */
              @ApiModelProperty(value = "组织机构编码")
              @NotNull(message = "组织机构编码不能为空")
              @Length(max = 100,min=1, message = "组织机构编码长度大于1小于100")
              @Column(name = "dept_code", nullable = false,columnDefinition="varchar(100) COMMENT '组织机构编码'")
              private String deptCode;
              /** * 父级联id */
              @ApiModelProperty(value = "父级联id")
              @Column(name = "parent_id",columnDefinition="int(20) COMMENT '父级联id'")
              private Long parentId;
              /** * 父级联id集合 */
              @ApiModelProperty(value = "父级联id集合")
              @Column(name = "parent_ids",columnDefinition="varchar(100) COMMENT '父级联id集合'")
              private String parent_ids;
              /** * 排序号 */
              @ApiModelProperty(value = "排序号")
              @Column(name = "order_no", columnDefinition="int(4) COMMENT '排序号'")
              private int orderNo;
              /** * 备注 */
              @ApiModelProperty(value = "备注")
              @Length(max = 2000, message = "备注长度不能大于 2000 ")
              @Column(name = "note",columnDefinition="varchar(2000) COMMENT '备注'")
              private String note;
              /** * 行政区划id */
              @ApiModelProperty(value = "行政区划id")
              @Column(name = "area_id",columnDefinition="int(20) COMMENT '行政区划id'")
              private Long areaId;
              /** * 创建时间 */
              @ApiModelProperty(value = "创建时间")
              @JsonFormat(shape=JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
              @Column(name = "create_time", nullable = false,columnDefinition="datetime COMMENT '创建时间'")
              private Timestamp createTime;
              /** * 创建用户 */
              @ApiModelProperty(value = "创建用户")
              @Column(name = "create_user_id",columnDefinition="int(20) COMMENT '创建用户'")
              private Long createUserId;
              /** * 数据状态 1:正常 -1：删除 */
              @ApiModelProperty(value = "数据状态")
              @Column(name = "db_status", nullable = false,columnDefinition="int(4) DEFAULT '1' COMMENT '数据状态 1:正常 -1：删除'")
              private int dbStatus;
             
             。。。。。。省略set get
          }
 *  新建需要的部分信息联合类：
    
        public interface UserUnionInfo { 
              String getUserName();
              String getUserMail();
              String getDeptCode();
              String getDeptName();
          }
 *  在IUserRepository 写查询
    
        @Repository
          public interface IUserRepository extends JpaRepository<User,Long>, JpaSpecificationExecutor<User> { 
          
              @EntityGraph(value = "User.all")
              User findByLoginNameAndLoginPwd(String loginName,String loginPwd);//通过登录名与密码查询用户
          
          
              /** * 用户部分信息联合查询 * @param id 用户id * @return UserUnionInfo * @author 王子様 * date: 2019/5/18 13:35 */
              @Query("select u.userName as userName, u.userMail as userMail, d.deptName as deptName , d.deptCode as deptCode from User u , Department d " +
                      "where u.deptId=d.id and u.id = ?1 ")
              UserUnionInfo findUserInfo(long id);
 *  查询调用：
    
        @Autowired
          IUserRepository userRepository;
    
        UserUnionInfo user =  userRepository.findUserInfo(1);