0

I want to convert the following query into HQL:

SELECT C.ID, E.DESCRIPTION as STATUS, E1.DESCRIPTION as SUBJECT
FROM CRED C 
join CODE_EVN E
ON E.CODE = C.STATUS_CODE
AND E.SUBCODE =  C.STATUS_SUBCODE 
join CODE_EVN E1
ON E1.CODE = C.SUBJECT_CODE
AND E1.SUBCODE =  C.SUBJECT_SUBCODE

I have two classes User and Codes with no mapping in between them, so how do I execute the following query in Hibernate? I have tried out many things but nothing seems to work

These are my 2 bean classes:

User class:

@Entity
@Table(name="CRED")
public class User {

    private String id;
    private String STATUS_CODE;
    private String STATUS_SUBCODE;
    private String SUBJECT_CODE;
    private String SUBJECT_SUBCODE;


    @Id
    @Column(name="ID")  
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }

    public void setSTATUS_CODE(String sTATUS_CODE) {
        STATUS_CODE = sTATUS_CODE;
    }
    public String getSTATUS_CODE() {
        return STATUS_CODE;
    }

    public void setSTATUS_SUBCODE(String sTATUS_SUBCODE) {
        STATUS_SUBCODE = sTATUS_SUBCODE;
    }
    public String getSTATUS_SUBCODE() {
        return STATUS_SUBCODE;
    }

    public void setSUBJECT_CODE(String sUBJECT_CODE) {
        SUBJECT_CODE = sUBJECT_CODE;
    }
    public String getSUBJECT_CODE() {
        return SUBJECT_CODE;
    }

    public void setSUBJECT_SUBCODE(String sUBJECT_SUBCODE) {
        SUBJECT_SUBCODE = sUBJECT_SUBCODE;
    }
    public String getSUBJECT_SUBCODE() {
        return SUBJECT_SUBCODE;
    }

}

Codes class:

 @Entity
    @Table(name="CODE_EVN")
public class Codes {
    @Id
    private String CODE;

    private String SUBCODE;
    private String DESCRIPTION;

    public void setCODE(String cODE) {
        CODE = cODE;
    }
    public String getCODE() {
        return CODE;
    }

    public void setSUBCODE(String sUBCODE) {
        SUBCODE = sUBCODE;
    }
    public String getSUBCODE() {
        return SUBCODE;
    }

    public void setDESCRIPTION(String dESCRIPTION) {
        DESCRIPTION = dESCRIPTION;
    }
    public String getDESCRIPTION() {
        return DESCRIPTION;
    }
}
user2077648
  • 951
  • 7
  • 27
  • 42
  • possible duplicate of [Inner join with select on HQL](http://stackoverflow.com/questions/11776932/inner-join-with-select-on-hql) – Denis Tulskiy Mar 24 '13 at 11:53
  • It is not a duplicate, here I want to perform 2 joins in HQL without any mapping and I do not want to use native SQL – user2077648 Mar 24 '13 at 14:51
  • ok, then, what have you tried? – Denis Tulskiy Mar 24 '13 at 15:02
  • The only thing that works is List rows = session.createQuery("from User C, Codes E where E.CODE = C.STATUS_CODE AND E.SUBCODE = C.STATUS_SUBCODE ").list(); But even this does not give what result I'm looking for as I want to perform 2 joins as I have mentioned in the SQL query – user2077648 Mar 24 '13 at 15:23
  • what if you add another `Codes E1`? – Denis Tulskiy Mar 24 '13 at 16:58
  • I want seperate DESCRIPTION columns for each join I perform but in the above query I will get only 1 DESCRIPTION column and Hibernate does not take any query in the form SELECT C.ID, E.DESCRIPTION as STATUS, E1.DESCRIPTION as SUBJECT So I dont know what to do – user2077648 Mar 25 '13 at 02:51

0 Answers0