2 Replies Latest reply on May 9, 2010 6:22 PM by seamalex42

    oneToMany with to much elements in Join-Query

    seamalex42

      Hi.
      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