Use join and subquery with criteria in hibernate

Question

I searched lot. But can't find solution for my case. i want create hibernate criteria for following query.

SELECT * FROM patient as p1 LEFT OUTER JOIN (SELECT * FROM patient_caller_admin_map WHERE caller_admin_id='1') as pca ON p1.patient_id=pca.patient_id;

i went through the DetachedCriteria , Criteria and created the following things. But don't know how to use LEFT_JOIN by joining both.

DetachedCriteria inner=DetachedCriteria.forClass(PatientCallerAdminMap.class, "patientCallerAdmin");
Criteria cr1=this.sessionFactory.getCurrentSession().createCriteria(Patient.class,"patient");

PatientCallerAdminMap Entity:

/**
 * PatientCallerAdminMap generated by hbm2java
 */
@Entity
@Table(name = "patient_caller_admin_map", catalog = "test")
public class PatientCallerAdminMap implements java.io.Serializable {

private PatientCallerAdminMapId id;
private CallerAdmin callerAdmin;
private Caller caller;
private Patient patient;
private String notes;
private Integer isArchived;
private Integer patientStatus;
private Set<CallLog> callLogs = new HashSet<CallLog>(0);
private Set<CallLog> callLogs_1 = new HashSet<CallLog>(0);

public PatientCallerAdminMap() {
}

public PatientCallerAdminMap(PatientCallerAdminMapId id,
        CallerAdmin callerAdmin, Patient patient) {
    this.id = id;
    this.callerAdmin = callerAdmin;
    this.patient = patient;
}

public PatientCallerAdminMap(PatientCallerAdminMapId id,
        CallerAdmin callerAdmin, Caller caller, Patient patient,
        String notes, Integer isArchived, Integer patientStatus,
        Set<CallLog> callLogs, Set<CallLog> callLogs_1) {
    this.id = id;
    this.callerAdmin = callerAdmin;
    this.caller = caller;
    this.patient = patient;
    this.notes = notes;
    this.isArchived = isArchived;
    this.patientStatus = patientStatus;
    this.callLogs = callLogs;
    this.callLogs_1 = callLogs_1;
}

@EmbeddedId
@AttributeOverrides({
        @AttributeOverride(name = "patientId", column = @Column(name = "patient_id", nullable = false)),
        @AttributeOverride(name = "callerAdminId", column = @Column(name = "caller_admin_id", nullable = false)) })
public PatientCallerAdminMapId getId() {
    return this.id;
}

public void setId(PatientCallerAdminMapId id) {
    this.id = id;
}

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "caller_admin_id", nullable = false, insertable = false, updatable = false)
public CallerAdmin getCallerAdmin() {
    return this.callerAdmin;
}

public void setCallerAdmin(CallerAdmin callerAdmin) {
    this.callerAdmin = callerAdmin;
}

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "caller_id")
public Caller getCaller() {
    return this.caller;
}

public void setCaller(Caller caller) {
    this.caller = caller;
}

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "patient_id", nullable = false, insertable = false, updatable = false)
public Patient getPatient() {
    return this.patient;
}

public void setPatient(Patient patient) {
    this.patient = patient;
}

@Column(name = "notes", length = 600)
public String getNotes() {
    return this.notes;
}

public void setNotes(String notes) {
    this.notes = notes;
}

@Column(name = "is_archived")
public Integer getIsArchived() {
    return this.isArchived;
}

public void setIsArchived(Integer isArchived) {
    this.isArchived = isArchived;
}

@Column(name = "patient_status")
public Integer getPatientStatus() {
    return this.patientStatus;
}

public void setPatientStatus(Integer patientStatus) {
    this.patientStatus = patientStatus;
}

@OneToMany(fetch = FetchType.LAZY, mappedBy = "patientCallerAdminMap")
public Set<CallLog> getCallLogs() {
    return this.callLogs;
}

public void setCallLogs(Set<CallLog> callLogs) {
    this.callLogs = callLogs;
}

@OneToMany(fetch = FetchType.LAZY, mappedBy = "patientCallerAdminMap")
public Set<CallLog> getCallLogs_1() {
    return this.callLogs_1;
}

public void setCallLogs_1(Set<CallLog> callLogs_1) {
    this.callLogs_1 = callLogs_1;
}

}

Patient Entity Class:

@Entity
@Table(name = "patient", catalog = "test")
public class Patient implements java.io.Serializable {

    private String patientId;
    private String addedDate;
    private String name;
    private String dateOfBirth;
    private String gender;
    private String address;
    private String phoneNumber;
    private Integer tier;
    private Integer patientStatus;
    private Integer status;
    private Set<PatientCallerAdminMap> patientCallerAdminMaps = new HashSet<PatientCallerAdminMap>(
            0);

    public Patient() {
    }

    public Patient(String patientId) {
        this.patientId = patientId;
    }

    public Patient(String patientId,String addedDate, String timeOfCrash,
             String name, String dateOfBirth, String gender,
            String address,
            String phoneNumber,Integer tier, Integer patientStatus,
             Integer status,
            Set<PatientCallerAdminMap> patientCallerAdminMaps,
            ) {
        this.patientId = patientId;
        this.addedDate = addedDate;
        this.name = name;
        this.dateOfBirth = dateOfBirth;
        this.gender = gender;
        this.address = address;
        this.phoneNumber = phoneNumber;
        this.tier=tier;
        this.patientStatus = patientStatus;
        this.status = status;
        this.patientCallerAdminMaps = patientCallerAdminMaps;
    }

    @Id
    @Column(name = "patient_id", unique = true, nullable = false)
    public String getPatientId() {
        return this.patientId;
    }

    public void setPatientId(String patientId) {
        this.patientId = patientId;
    }
    @Column(name = "added_date", length = 45)
    public String getAddedDate() {
        return addedDate;
    }

    public void setAddedDate(String addedDate) {
        this.addedDate = addedDate;
    }

    @Column(name = "name", length = 100)
    public String getName() {
        return this.name;
    }

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

    @Column(name = "date_of_birth", length = 45)
    public String getDateOfBirth() {
        return this.dateOfBirth;
    }

    public void setDateOfBirth(String dateOfBirth) {
        this.dateOfBirth = dateOfBirth;
    }

    @Column(name = "gender", length = 5)
    public String getGender() {
        return this.gender;
    }

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

    @Column(name = "address", length = 200)
    public String getAddress() {
        return this.address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Column(name = "phone_number", length = 20)
    public String getPhoneNumber() {
        return this.phoneNumber;
    }

    public void setPhoneNumber(String phoneNumber) {
        this.phoneNumber = phoneNumber;
    }


    @Column(name = "tier")
    public Integer getTier() {
        return this.tier;
    }

    public void setTier(Integer tier) {
        this.tier = tier;
    }

    @Column(name = "patient_status")
    public Integer getPatientStatus() {
        return this.patientStatus;
    }

    public void setPatientStatus(Integer patientStatus) {
        this.patientStatus = patientStatus;
    }

    @Column(name = "status")
    public Integer getStatus() {
        return this.status;
    }

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

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "patient")
    public Set<PatientCallerAdminMap> getPatientCallerAdminMaps() {
        return this.patientCallerAdminMaps;
    }

    public void setPatientCallerAdminMaps(
            Set<PatientCallerAdminMap> patientCallerAdminMaps) {
        this.patientCallerAdminMaps = patientCallerAdminMaps;
    }


}

Please help to solve this.

Answer 1

Maybe you can achieve this without using subquery so the query become simpler :

Criteria cr1=this.sessionFactory.getCurrentSession().createCriteria(Patient.class,"patient");
cr2=cr1.createCriteria("patientCallerAdminMaps ",CriteriaSpecification.LEFT_JOIN);
cr3= cr2.createCriteria("callerAdmin",CriteriaSpecification.LEFT_JOIN);
cr3.add(Restrictions.eq("id", "1"));

For the "select *" you can't do it with criteria. This criteria will return a list of Patient entity.

If really want * you will have to add alias on subcriteria and use Projection to select explicitly the fields that you want