CODEWITHSUNDEEP
javahibernatejoinhql
Suganthan Madhavan Pillai
Suganthan Madhavan Pillai

Reputation: 5655

Hibernate Query for equivalent sql

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.

Upvotes: 0

Views: 186

Answers (1)

thanhnguyen
thanhnguyen

Reputation: 214

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();

Upvotes: 1

Related Questions