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

查询用户信息与部门信息部分字段，涉及到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);