java - 在hibernate中编写内连接

Question

我有三类用户、部门和名称

User.java

public class User implements Serializable{  
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long userId;

    @Column(name="emailId")
    private String emailId;

    @Column(name="userName")
    private String userName;

    @Column(name="address")
    private String address;

    @Column(name="mobNo")
    private BigDecimal mobNo;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "deptId")
    private Department department;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "desgId")
    private Designation designation;

    @Column(name="grpId")
    private int grpId ;

    @Column(name="userLevel")
    private int userLevel ;

    @Column(name="upw1")
    private String upw1 ;

    @Column(name="upw2")
    private String upw2 ;

    @Column(name="upw3")
    private String upw3 ;

    @Column(name="createdBy")
    private String createdBy ;

    @Column(name="creationDate")
    private Timestamp creationDate;

    @Column(name="loginStatus")
    private String loginStatus;

    @Column(name="hintId")
    private int hintId ;

    @Column(name="hintAns")
    private String hintAns ;

    @Column(name="otp")
    private String otp ;
}

部门.java

public class Department implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int deptid;

    @Column(name="deptnm")
    private String deptnm;

    @Column(name="shrtnm")
    private String shrtnm;

    @Column(name="creationdt")
    private Timestamp creationdt;

    @Column(name="status")
    private String status;
}

Designation.java

public class Designation implements Serializable{

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int desgid;

    @Column(name="desgnm")
    private String desgnm;

    @Column(name="shrtnm")
    private String shrtnm;

    @Column(name="creationdt")
    private Timestamp creationdt;

    @Column(name="status")
    private String status;

    public Designation() {
    }
}

SQL

select u.username, u.mobno, dp.deptnm, ds.desgnm, u.loginstatus from  usermst u
    inner join  deptmst dp ON u.deptid=dp.deptid 
    INNER JOIN  desigmst ds on u.desgid=ds.desgid

我想将查询写为如何在hibernate中编写这个sql查询？我按照标准尝试过，hql 但没有工作

Answer 1

在这种情况下，您实际上并不需要 hql 加入，您只需浏览部门和职位关联即可。如果您确实希望结果作为数组集合，请尝试此操作

select
    u.username, u.mobno, u.department.deptnm, u.designation.desgnm, u.loginstatus
from
    User u

使用此类查询

Query query=sessionFactory.getCurrentSession().createQuery("select u.userName, u.mobNo, u.department.deptnm, u.designation.desgnm, " + "u.loginStatus from User u");
List<Object[]> list=query.list(); 

但是如果您只想获取用户列表

Query query=sessionFactory.getCurrentSession().createQuery("from User u");
List<User> list=query.list();