Transform SQL to HQL with criteria AND NOT EXISTS

Question

I need to transform this SQL statement HQL but I think the NOT EXISTS not working in HQL Please help me!!!

SELECT doctor.idUser, schedule.idSchedule, schedule.timeStart, schedule.day
FROM doctor, schedule
    WHERE schedule.day='LUNES'
        AND schedule.timeStart > '08:00:00'
        AND doctor.idUser= '1'
        AND doctor.idUser = schedule.idUserDoctor
AND NOT EXISTS( SELECT *    FROM appointment
    WHERE schedule.idSchedule = appointment.idSchedule
        AND doctor.idUser = schedule.idUserDoctor
        AND appointment.appointmentDate ='2012-09-06')
AND NOT EXISTS ( SELECT * FROM temporaryschedule
        WHERE schedule.idSchedule = temporaryschedule.idSchedule
        AND doctor.idUser = schedule.idUserDoctor"
        AND temporaryschedule.appointmentDate='201-09-06')
ORDER BY schedule.timeStart ASC

Regarding `NOT EXISTS` this [question](http://stackoverflow.com/questions/3672444/where-exists-in-hibernate-hql) might be helpful. You would have to determine the objects (or their ids) from your `NOT EXISTS` clauses seperately and insert them like this: `AND doctor.idUser NOT IN (1,2,3)`. — Pao, Sep 12 '12 at 00:18

score 4 · Answer 1 · answered Sep 12 '12 at 05:19

Unfortunately you do not provide any info about your domain model, so we have to make a quite few assumptions here... First, I am not accounting for any mapped association between doctor and schedule, though you'd likely want to map it. Per just good design I am using parameters over literals. I am assuming all referenced tables are mapped and am using "logical name mappings" for the classes. Lastly, I am using your column names as domain model attribute names...

select ...
from Doctor d, Schedule s
where s.day = :day
  and s.timeStart > :startTime
  and d.idUser = :doctorId
  and d.idUser = s.idUserDoctor
  and not exists (
      select *
      from Appointment appt
      where s.idSchedule = appt.idSchedule
        and d.idUser = s.idUserDoctor
        and apt.appointmentDate = :apptDate
  )
  and not exists (
      select *
      from TemporarySchedule ts
      where s.idSchedule = ts.idSchedule
        and d.idUser = s.idUserDoctor
        and ts.appointmentDate = tempSchedDate
  )
order by s.startTime asc

Transform SQL to HQL with criteria AND NOT EXISTS

1 Answers1