jpa 聯合查詢資料，查詢使用者資訊與部門資訊

王子様~發表於2019-05-25

查詢使用者資訊與部門資訊部分欄位，涉及到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);

相關文章