Hi,

Can anyone please help me in quering entities with many to many association. I have an Employee, Meeting and Employee_Meeting tables in DB. Employee and Meeting has many to many relation and Employee_Meeting holds emp_id and meet_id.

Similarly I have two entities:

@Entity(name="Employee")
@Table(name="Employee")
public class Employee {

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE)
@Column(name="ID")
private long id;

@Column(name="FIRST_NAME")
private String firstName;

@Column(name="LAST_NAME")
private String lastName;

@ManyToMany(targetEntity=Meeting.class)
@JoinTable(name="EMPLOYEE_MEETING",
joinColumns=@JoinColumn(name="EMPLOYEE_ID", referencedColumnName="ID"),
inverseJoinColumns=@JoinColumn(name="MEETING_ID", referencedColumnName="ID"))
private Set<Meeting> meetings;

public long getId() {
return id;
}

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

public String getFirstName() {
return firstName;
}

public void setFirstName(String firstName) {
this.firstName = firstName;
}

public String getLastName() {
return lastName;
}

public void setLastName(String lastName) {
this.lastName = lastName;
}

public Set<Meeting> getMeetings() {
return meetings;
}

public void setMeetings(Set<Meeting> meetings) {
this.meetings = meetings;
}


}


@Entity
@Table(name="METTING")
public class Meeting {

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE)
@Column(name="ID")
private long id;

@Column(name="SUBJECT")
private String subject;

@Column(name="LOCATION")
private String location;

@ManyToMany(targetEntity = Employee.class, mappedBy="meetings")
private Set<Employee>employee;

public long getId() {
return id;
}

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

public String getSubject() {
return subject;
}

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

public String getLocation() {
return location;
}

public void setLocation(String location) {
this.location = location;
}

public Set<Employee> getEmployee() {
return employee;
}

public void setEmployee(Set<Employee> employee) {
this.employee = employee;
}


}

I want to select an employee with first name = 'Hello' and with meeting subject = 'Local'. I am trying with the below query:

Query query = session.createQuery("Select DISTINCT emp FROM Meeting meets JOIN meets.employee emp WHERE meets.subject='LOCAL' AND emp.firstName = 'Hello'");

But it returns all the meeting locations for employee first name as Hello. Why is it not considering the AND condition at all? Am I doing something wrong here? Can anyone please help, have already spent too much time on this.

Thanks ,
Amit

I am seeing that you anticipate subject=Hello and passing subject=HELLO

In your Meeting class did you intend to have @Table(name="METTING")

Hi MikeHT, by mistake i created the table name in DB as METTING instead of MEETING so I have mentioned the exact table name in Meeting class.

But the problem is hibernate does not consider the second AND condition where I say subject = 'blah'. I think what happens is, hibernate will return Employee entities based on the name and whenever you want to refer to Meetings associated with those employees hibernate will use their PK and returns meetings. I am not sure about the logic but it looks like that. But my requirement is to get only those employees for whom meeting subject is Local (or any value).

No Akash, there is no issue with HELLO and Hello. Name is 'Hello' and subject is 'LOCAL'

Hello,

can anyone please help on this issue? Since there is no response so I am not sure if i have posted it under wrong categroy.

Thanks,
A

Yes, to get only those employees for whom meeting subject is Local.

Hi

Are you query right?

Have you tried this:
Select DISTINCT emp FROM Employee emp WHERE emp.meetings.subject='LOCAL' AND emp.firstName = 'Hello'


Br

Jesper

Hi Jesper,

thanks for your response, I had already tried this query and tried again now but no luck.

Thanks,
Amit