JPQL non join query

Question

I am using EclipseLink and MySQL. I am trying to retrieve a value from the Room entity without joining it.

Here is my NamedQuery on my Schedule entity:

@NamedQuery(name = "Schedule.findUnusedRoomForCourseLectureOnly",
query = "SELECT r FROM Schedule s, Room r WHERE s.day = :sday AND (:stime NOT BETWEEN s.startTime AND s.endTime) AND r.id <> s.room.id")

I am getting a null value from my JPA controller.

Here is the contoller's code:

    public Room findUnusedRoomForCourseLectureOnly(int day, Calendar startTime) {
        EntityManager em = getEntityManager();
        Query q = em.createNamedQuery("Schedule.findUnusedRoomForCourseLectureOnly")

                .setParameter("sday", day)
                .setParameter("stime", startTime);
        q.setMaxResults(1);
        try {
            return (Room) q.getResultList().get(0);
        } catch (IndexOutOfBoundsException e) {
            return null;
        } finally {
            em.close();
        }
    }

Here is my Schedule entity:

@Entity    
public class Schedule implements Serializable, Comparable<Schedule> {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "scheduledDay")
    private int day;
    @Temporal(javax.persistence.TemporalType.TIME)
    private Calendar startTime; // 24 hr format
    @Temporal(javax.persistence.TemporalType.TIME)
    private Calendar endTime; // 24 hr format

    @ManyToOne
    private Faculty faculty;

    @ManyToOne
    private Room room;

    @ManyToOne
    private Subject subject;

    private boolean lab;

    @ManyToOne
    private Section section;

    public Schedule() {
        this.startTime = Calendar.getInstance();
        this.endTime = Calendar.getInstance();
    }

    public Schedule(int day, Calendar startTime, Calendar endTime, Faculty faculty, Room room, Subject subject, boolean lab, Section section) {
        this.day = day;
        this.startTime = startTime;
        this.endTime = endTime;
        this.faculty = faculty;
        this.room = room;
        this.subject = subject;
        this.lab = lab;
        this.section = section;
    }

    public Long getId() {
        return id;
    }

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

    public int getDay() {
        return day;
    }

    public void setDay(int day) {
        this.day = day;
    }

    public Calendar getStartTime() {
        return startTime;
    }

    public final void setStartTime(int hour) {
        this.startTime.set(Calendar.HOUR_OF_DAY, hour);
        this.startTime.set(Calendar.MINUTE, 0);
        this.startTime.set(Calendar.SECOND, 0);
    }

    public Calendar getEndTime() {
        return endTime;
    }

    public final void setEndTime(int hour) {
        this.endTime.set(Calendar.HOUR_OF_DAY, hour);
        this.endTime.set(Calendar.MINUTE, 0);
        this.endTime.set(Calendar.SECOND, 0);
    }

    public Faculty getFaculty() {
        return faculty;
    }

    public void setFaculty(Faculty faculty) {
        this.faculty = faculty;
    }

    public Room getRoom() {
        return room;
    }

    public void setRoom(Room room) {
        this.room = room;
    }

    public Subject getSubject() {
        return subject;
    }

    public void setSubject(Subject subject) {
        this.subject = subject;
    }

    public Section getSection() {
        return section;
    }

    public void setSection(Section section) {
        this.section = section;
    }

    public boolean isLab() {
        return lab;
    }

    public void setLab(boolean lab) {
        this.lab = lab;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Schedule)) {
            return false;
        }
        Schedule other = (Schedule) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "ph.edu.bulsusc.planner.entity.Schedule[ id=" + id + " ]";
    }

    @Override
    public int compareTo(Schedule schedule) {
        // return
        // -1 ahead
        // 0 overlapping
        // 1 behind
//        int timeComparison = compareTime(schedule);
//        boolean isTimeConflict = timeComparison == 0;
//
//        if (this.room.getId() == schedule.getRoom().getId() && !isTimeConflict) {
//            // if they have same room and time is not conflict
//            return timeComparison;
//        } else {
//            // same room; time conflict
//            return 0;
//        }
        throw new UnsupportedOperationException("Not implemented yet.");
    }

//    private int compareTime(Schedule schedule) {
//        if (this.day.compareTo(schedule.getDay()) < 0) { // ahead/before of scheduled day
//            return -1;
//        } else if (this.day.compareTo(schedule.getDay()) == 0) { // overlapping day
//            if (this.endTime.get(Calendar.HOUR_OF_DAY) < schedule.getStartTime().get(Calendar.HOUR_OF_DAY)) {
//                return -1;
//            } else if (this.startTime.get(Calendar.HOUR_OF_DAY) > schedule.getEndTime().get(Calendar.HOUR_OF_DAY)) {
//                return 1;
//            } else {
//                return 0;
//            }
//        } else { // behind/after of scheduled day
//            return 1;
//        }
//    }

}

Here is my Room entity:

@Entity
public class Room implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String number;
    private boolean lab;
    @OneToMany
    private List<Course> prioritizedCourses;
    @OneToMany
    private List<Subject> prioritizedSubjects;

    public Room() {
    }

    public Room(String number, boolean lab) {
        this.number = number;
        this.lab = lab;
    }

    public Room(String number, boolean lab, List<Course> prioritizedCourses, List<Subject> prioritizedSubjects) {
        this.number = number;
        this.lab = lab;
        this.prioritizedCourses = prioritizedCourses;
        this.prioritizedSubjects = prioritizedSubjects;
    }

    public Long getId() {
        return id;
    }

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

    public boolean isLab() {
        return lab;
    }

    public void setLab(boolean lab) {
        this.lab = lab;
    }

    public List<Course> getPrioritizedCourses() {
        return prioritizedCourses;
    }

    public void setPrioritizedCourses(List<Course> prioritizedCourses) {
        this.prioritizedCourses = prioritizedCourses;
    }

    public List<Subject> getPrioritizedSubjects() {
        return prioritizedSubjects;
    }

    public void setPrioritizedSubjects(List<Subject> prioritizedSubjects) {
        this.prioritizedSubjects = prioritizedSubjects;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

//    public List<Schedule> getSchedules() {
//        return schedules;
//    }
//
//    /**
//     * Rewrites schedule with this method
//     * @param schedules
//     */
//    public void setSchedules(List<Schedule> schedules) {
//        this.schedules = schedules;
//    }
    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Room)) {
            return false;
        }
        Room other = (Room) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "ph.edu.bulsusc.planner.entity.Room[ id=" + id + " ]";
    }

}

Am I doing it wrong?

score 1 · Answer 1 · answered Jan 29 '14 at 14:15

The problem with the "r.id <> s.room.id" clause is that using "s.room.id" causes an inner join between schedule and Room. You might try using "r <> s.room" to avoid the join, but I don't think it will help with what you seem to be after. The query will have Room and Schedule unjoined in the same query causing a cartesian join - N rows in schedule multiplied by M rows in Schedule get returned, which then a few are filtered out that will match the r.id <> s.room.id clause. So if you have more than one schedule, all Rooms will be returned.

You might want to use a not exist ontop of a subquery. something like:

"SELECT r FROM Room r WHERE not exists (SELECT s from Schedule s where :stime NOT BETWEEN s.startTime AND s.endTime) AND r = s.room))"

score 0 · Answer 2 · edited May 23 '17 at 10:31

0

This JPQL query worked for me. Just made some modification from this answer.

"SELECT r FROM Room r WHERE r.id NOT IN (SELECT s.room.id FROM Schedule s WHERE s.day = :sday AND (:stime BETWEEN s.startTime AND s.endTime))"

edited May 23 '17 at 10:31

Community

1
1

answered Jan 29 '14 at 13:34

ltlynx

51
1
10

JPQL non join query

2 Answers2