Hibernate self join exception: No row with the given identifier exists

Question

I have a table as following

CREATE TABLE labour_no_pk ( id bigint(20) NOT NULL AUTO_INCREMENT, name varchar(255) DEFAULT NULL, labour_id bigint(20) NOT NULL, contractor_id bigint(20) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY labour_id_UNIQUE (labour_id), KEY FK_SELF_idx (contractor_id), CONSTRAINT FK_SELF FOREIGN KEY (contractor_id) REFERENCES labour_no_pk (labour_id) ON DELETE CASCADE ON UPDATE CASCADE );

Class as

@Entity
@Table(name = "LABOUR_NO_PK")
public class LabourNoPK {
    @Id
    @Column(name = "id")
    @GeneratedValue
    private Long id;

    @Column(name = "firstname")
    private String firstName;

    @ManyToOne(cascade = { CascadeType.ALL })
    @JoinColumn(name = "labour_id")
    private LabourNoPK contractor;

    @OneToMany(mappedBy = "contractor")
    private Set<LabourNoPK> subordinates = new HashSet<LabourNoPK>();
}

DAO as

public static List<LabourNoPK> getLabours(Session session) {
        List<LabourNoPK> labours = null;
        try {
            Query query = session.createQuery("FROM LabourNoPK where contractor_id is null");
            labours = query.list();
            for (LabourNoPK labour : labours) {
                System.out.println("parent=" + labour.toString());
                if (null != labour.getSubordinates() && !labour.getSubordinates().isEmpty()) {
                    for (LabourNoPK subordinate : labour.getSubordinates()) {
                        System.out.println("Sub=" + subordinate.toString());
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return labours;
    }

I have data as

when I run the program, i am getting org.hibernate.ObjectNotFoundException: No row with the given identifier exists: [LabourNoPK#100] but there's a record available in DB. I understood (from exception message) my model class pointing to id instead of contractor_id. How should I map to get the results all parents with childs?

What exactly i am missing? thanks in advance

Answer 1

After a lot of reading and trials I am able achieve what I want. So posting if somebody needs the same way.

@Entity
@Table(name = "LABOUR_NO_PK")
public class LabourNoPK implements Serializable {

    @Id
    @Column(name = "id")
    @GeneratedValue
    private Long id;

    @Column(name = "labour_id")
    @NaturalId
    private Long labourId;

    @Column(name = "firstname")
    private String firstName;

    @OneToMany(mappedBy="labour")
    private Set<LabourNoPK> subordinates = new HashSet<>();

    @ManyToOne
    @JoinColumn(name = "contractor_id", referencedColumnName = "labour_id")
    /* child (subordinate) contractor_id will be matched with parent (labour) labour_id*/
    private LabourNoPK labour;

}