HQL query for the unary table

Question

I have an user_info table as below

As you see the supervisor column refers the same table.

I am using the Netbean for my development. My POJO created the following class

    package database.hibernate_pojo;
// Generated May 31, 2013 12:50:13 AM by Hibernate Tools 3.2.1.GA


import java.util.HashSet;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import static javax.persistence.GenerationType.IDENTITY;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;

/**
 * UserInfo generated by hbm2java
 */
@Entity
@Table(name="user_info"
    ,catalog="bit_final_year_project"
    , uniqueConstraints = @UniqueConstraint(columnNames="user_name") 
)
public class UserInfo  implements java.io.Serializable {


     private Integer userId;
     private UserInfo userInfo;
     private String userName;
     private String firstName;
     private String secondName;
     private char department;
     private String password;
     private char privilege;
     private int invalidLogin;
     private char status;
     private String signaturePath;
     private Set<Shipping> shippings = new HashSet<Shipping>(0);
     private Set<Audit> audits = new HashSet<Audit>(0);
     private Set<UserInfo> userInfos = new HashSet<UserInfo>(0);


    public UserInfo() {
    }


    public UserInfo(String userName, String firstName, String secondName, char department, String password, char privilege, int invalidLogin, char status) {
        this.userName = userName;
        this.firstName = firstName;
        this.secondName = secondName;
        this.department = department;
        this.password = password;
        this.privilege = privilege;
        this.invalidLogin = invalidLogin;
        this.status = status;
    }
    public UserInfo(UserInfo userInfo, String userName, String firstName, String secondName, char department, String password, char privilege, int invalidLogin, char status, String signaturePath, Set<Shipping> shippings, Set<Audit> audits, Set<UserInfo> userInfos) {
       this.userInfo = userInfo;
       this.userName = userName;
       this.firstName = firstName;
       this.secondName = secondName;
       this.department = department;
       this.password = password;
       this.privilege = privilege;
       this.invalidLogin = invalidLogin;
       this.status = status;
       this.signaturePath = signaturePath;
       this.shippings = shippings;
       this.audits = audits;
       this.userInfos = userInfos;
    }

     @Id @GeneratedValue(strategy=IDENTITY)

    @Column(name="user_id", unique=true, nullable=false)
    public Integer getUserId() {
        return this.userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }
@ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name="supervisor")
    public UserInfo getUserInfo() {
        return this.userInfo;
    }


    public void setUserInfo(UserInfo userInfo) {
        this.userInfo = userInfo;
    }

    @Column(name="user_name", unique=true, nullable=false, length=12)
    public String getUserName() {
        return this.userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    @Column(name="first_name", nullable=false, length=15)
    public String getFirstName() {
        return this.firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    @Column(name="second_name", nullable=false, length=15)
    public String getSecondName() {
        return this.secondName;
    }

    public void setSecondName(String secondName) {
        this.secondName = secondName;
    }

    @Column(name="department", nullable=false, length=1)
    public char getDepartment() {
        return this.department;
    }

    public void setDepartment(char department) {
        this.department = department;
    }

    @Column(name="password", nullable=false, length=45)
    public String getPassword() {
        return this.password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Column(name="privilege", nullable=false, length=1)
    public char getPrivilege() {
        return this.privilege;
    }

    public void setPrivilege(char privilege) {
        this.privilege = privilege;
    }

    @Column(name="invalid_login", nullable=false)
    public int getInvalidLogin() {
        return this.invalidLogin;
    }

    public void setInvalidLogin(int invalidLogin) {
        this.invalidLogin = invalidLogin;
    }

    @Column(name="status", nullable=false, length=1)
    public char getStatus() {
        return this.status;
    }

    public void setStatus(char status) {
        this.status = status;
    }

    @Column(name="signature_path", length=45)
    public String getSignaturePath() {
        return this.signaturePath;
    }

    public void setSignaturePath(String signaturePath) {
        this.signaturePath = signaturePath;
    }
@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY, mappedBy="userInfo")
    public Set<Shipping> getShippings() {
        return this.shippings;
    }

    public void setShippings(Set<Shipping> shippings) {
        this.shippings = shippings;
    }
@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY, mappedBy="userInfo")
    public Set<Audit> getAudits() {
        return this.audits;
    }

    public void setAudits(Set<Audit> audits) {
        this.audits = audits;
    }
@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY, mappedBy="userInfo")
    public Set<UserInfo> getUserInfos() {
        return this.userInfos;
    }

    public void setUserInfos(Set<UserInfo> userInfos) {
        this.userInfos = userInfos;
    }




}

I want to show the name of the supervisor in my application. I have written the SQL command it worked perfectly in the Workbench.

select e.user_name from user_info e, user_info s where e.user_id=s.supervisor

But the issue was when I tried to write it in HQL there is no respective methods were created by the Netbean in UserInfo class.

Can anyone help me out of this issue to find a solution?

Answer 1

I've found the solution for my issue by my own. It's simply object within another object. Thus I need to query the first object, then the second object can be accessed

public Iterator getCustomSupervisor(){
    String hqlQuery = "FROM UserInfo";
    Query query = session.createQuery(hqlQuery);
    while(query.list().iterator().hasNext()){
        //the first object
        UserInfo ui = (UserInfo) query.list().iterator().next();
        //access the second object
        System.out.println(ui.getUserInfo().getUserName());
    }
}