Hibernate Query for equivalent sql

Question

Can someone help me in writing equivalent hql for this query

SELECT IFNULL(oea.`organization_name`,'') FROM `consultation_appointment` ca 
JOIN `organization_employee_association` oea ON ca.`consultation_id` = oea.id
JOIN professional_profile pp ON oea.`employee_profile_id` = pp.profile_id

I could able to join the first JOIN like this

select ca.name from ConsultationAppointment ca join ca.consultation oea

Because the ConsultationAppointment class having organization_employee_association variable so it easier to join, difficulty is organization_employee_association not having direct mapping to organization_employee_association class.

Even GORM criteria Query is helpful.

You're aware that you need an association between entities in order to create a join between those entities. So add the necessary association. I can't be more precise without knowing any of your entities and tables. — JB Nizet
– JB Nizet, Commented Nov 22, 2013 at 8:24

thanhnguyen · Accepted Answer · 2013-11-22 09:58:22Z

1

HQL does not allow joining two unassociated entity, you should use cartesian product instead.

String query = "SELECT ca.name FROM consultation_appointment ca JOIN organization_employee_association oea, professional_profile pp WHERE oea.employee_profile_id = pp.profile_id";
List<String> caNames = session.createQuery(query).list();

One another possibility is to use the method createSQLQuery(). It provides more flexibility to execute an arbitrary join.

String query = "SELECT ca.name FROM consultation_appointment ca JOIN organization_employee_association oea ON ca.consultation_id = oea.id JOIN professional_profile pp ON oea.employee_profile_id = pp.profile_id";
List<String> caNames = session.createSQLQuery(query).list();

edited Nov 22, 2013 at 9:58

answered Nov 22, 2013 at 7:30

thanhnguyen

2142 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Suganthan Madhavan Pillai Over a year ago

but only if there is no possibility we can try this. But there should be some ways in hql we have to dig that

Suganthan Madhavan Pillai Over a year ago

thanks for your effort, but there is error, also we already tried like this, error is on using "on" clause in the query

Collectives™ on Stack Overflow

Hibernate Query for equivalent sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related