-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: Join-Query with 1:n childList returns to much equals Objects
PostPosted: Fri May 07, 2010 6:03 am 
Newbie

Joined: Sun Aug 24, 2008 3:21 pm
Posts: 8
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:
Code:
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);

Code:
@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;
        }

Code:
@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.
Code:
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 one sql (named query with left outer join).
Code:
Query q = em.createNamedQuery("person.loadAll");
Collection<Person> c = (List<Person>)q.getResultList();

The resulting sql (correct) gets 2 rows.
Code:
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


Top
 Profile  
 
 Post subject: Re: Join-Query with 1:n childList returns to much equals Objects
PostPosted: Sun May 09, 2010 12:20 pm 
Newbie

Joined: Sun Aug 24, 2008 3:21 pm
Posts: 8
Nobody with a suggestion?

I think, i can put the Objects in a Set oder i can use a Criteria like "SELECT distinct p from person ....".
Whats the best way do anyone a better way?
thanks.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.