JPA query for one to one relation

Question

I have an EJB application in where I am using Entity beans for database. I have to Entity beans having unidirectional one to one relation, JobPositionEntity and CandidateEntity.

Here is CandidateEntity @Entity public class CandidateEntity extends BaseEntity {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private Long meritNumber;

    private String seatNumber;

    private String candidateName;

    private String gender;

    public Long getMeritNumber() {
        return meritNumber;
    }

    public void setMeritNumber(Long meritNumber) {
        this.meritNumber = meritNumber;
    }

    public String getSeatNumber() {
        return seatNumber;
    }

    public void setSeatNumber(String seatNumber) {
        this.seatNumber = seatNumber;
    }

    public String getCandidateName() {
        return candidateName;
    }

    public void setCandidateName(String candidateName) {
        this.candidateName = candidateName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public Long getId() {
        return id;
    }

    @Override
    public void setId(Long id) {
        this.id = id;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof CandidateEntity)) {
            return false;
        }
        CandidateEntity other = (CandidateEntity) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "com.nisheeth.config.ejb.entity.CandidateEntity[ id=" + id + " ]";
    }

}

Here is JobPositionEntity @Entity public class JobPositionEntity extends BaseEntity {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    @OneToOne(fetch = FetchType.EAGER, orphanRemoval = true, cascade = CascadeType.ALL)
    private CandidateEntity candidate;

    @ManyToOne(fetch = FetchType.EAGER)
    private SeasonEntity season;

    public SeasonEntity getSeason() {
        return season;
    }

    public void setSeason(SeasonEntity season) {
        this.season = season;
    }

    public CandidateEntity getCandidate() {
        return candidate;
    }

    public void setCandidate(CandidateEntity candidate) {
        this.candidate = candidate;
    }

    @Override
    public Long getId() {
        return id;
    }

    @Override
    public void setId(Long id) {
        this.id = id;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof JobPositionEntity)) {
            return false;
        }
        JobPositionEntity other = (JobPositionEntity) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "com.nisheeth.config.ejb.entity.JobPositionEntity[ id=" + id + " ]";
    }

}

I want to select candidates which are not in JobPositionEntity. I have this query which did not work for me:

select ce.candidateName, ce.id from JobPositionEntity jp left join  jp.candidate ce where ce <> null

Can anyone help write this query? Thanks in advance.

Answer 1

you can use a SubQuery

select c from Candidate c where c.id not in
(select jp.candidate.id from JobPositionEntity jp)

for more information: https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-subqueries