oneToMany with to much elements in Join-Query
seamalex42 May 7, 2010 8:32 AMHi.
Can anybody explain me, why i get dublicate Objects (same instanzes) from the resultList of an namedQuery and Joint Fetch?
I have a Object Person with a list of Phones. If the personA has 2 phones, i execute the namedQuery with fetch join and get the correct count of 2 Rows (outer join) from the Database.
My resultset has now 2 items of the same persons. But i have only one person with 2 phones. The problem seams the Join. I think, the Query should identify the same objects (to call
hashCode or equals) and should only get one item back.
Example:
create table person ( person_id bigint not null AUTO_INCREMENT, firstname varchar(50), primary key (person_id) ) engine=innodb default charset=utf8 ; create table phone ( phone_id bigint not null AUTO_INCREMENT, phonenumber varchar(25), person_id bigint not null, primary key (phone_id) ) engine=innodb default charset=utf8 ; alter table phone add constraint phone_person_fk foreign key (person_id) references person (person_id);
@Table(name="phone") public class Phone { @Id @GeneratedValue(strategy=GenerationType.IDENTITY) @Column(name="phone_id") private Long phoneId; @Column(name="phonenumber") private String phoneNumber; public long getPhoneId() {return phoneId;} public void setPhoneId(long phoneId) {this.phoneId = phoneId;} public String getPhoneNumber() {return phoneNumber;} public void setPhoneNumber(String phoneNumber) {this.phoneNumber = phoneNumber;} @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((phoneId == null) ? 0 : phoneId.hashCode()); result = prime * result + ((phoneNumber == null) ? 0 : phoneNumber.hashCode()); return result; } @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; Phone other = (Phone) obj; if (phoneId == null) { if (other.phoneId != null) return false; } else if (!phoneId.equals(other.phoneId)) return false; if (phoneNumber == null) { if (other.phoneNumber != null) return false; } else if (!phoneNumber.equals(other.phoneNumber)) return false; return true; }
@Entity @Table(name="person") @NamedQueries( { @NamedQuery(name = "person.loadAll", query = "SELECT p FROM Person p LEFT JOIN FETCH p.phoneList") }) public class Person { @Id @GeneratedValue(strategy=GenerationType.IDENTITY) @Column(name="person_id") private Long personId; @Column(name="firstname") private String firstName; @OneToMany(cascade=CascadeType.ALL) @JoinColumn(name="person_id") private List<Phone> phoneList; public Long getPersonId() {return personId;} public void setPersonId(Long personId) {this.personId = personId;} public String getFirstName() {return firstName;} public void setFirstName(String firstName) {this.firstName = firstName; } public List<Phone> getPhoneList() {return phoneList;} public void setPhoneList(List<Phone> phoneList) {this.phoneList = phoneList;} @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((firstName == null) ? 0 : firstName.hashCode()); result = prime * result + ((personId == null) ? 0 : personId.hashCode()); return result; } @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; Person other = (Person) obj; if (firstName == null) { if (other.firstName != null) return false; } else if (!firstName.equals(other.firstName)) return false; if (personId == null) { if (other.personId != null) return false; } else if (!personId.equals(other.personId)) return false; return true; }
I persist one person with 2 phones sucessesfully.
Inside table person exist one row with personid 1;
Inside table phone exists two rows with phoneid 1 and 2 and both with personid 1.
Person person = new Person(); person.setFirstName("jon"); Phone phone1 = new Phone(); phone1.setPhoneNumber("020-020222"); Phone phone2 = new Phone(); phone2.setPhoneNumber("0178-2221020222"); List<Phone> phoneList = new ArrayList(); phoneList.add(phone1); phoneList.add(phone2); person.setPhoneList(phoneList); em.persist(person);
After this, i will load the person with phoneList with only sql (named query with left outer join).
Query q = em.createNamedQuery("person.loadAll"); Collection<Person> c = (List<Person>)q.getResultList();
The resulting sql (correct) gets 2 rows.
select person0_.person_id as person1_1_0_, phonelist1_.phone_id as phone1_7_1_, person0_.firstname as firstname1_0_, phonelist1_.phonenumber as phonenum2_7_1_, phonelist1_.person_id as person3_0__, phonelist1_.phone_id as phone1_0__ from person person0_ left outer join phone phonelist1_ on person0_.person_id=phonelist1_.person_id
The List c now has 2 same Objects inside! I think, it should be only one person. Whats wrong?
Thanks for every tip.
alex