Duplicate Parent record when joined with one-to-many

rdonasco · **Joined:** Wed Jan 11, 2006 5:21 am **Posts:** 14

I'm having problem retrieving parent data with one-to-many relationship. Whenever i retrieve parents with more than one child, the list returned contains duplicate parent records, although the physical records in database is unique

The parent record has one ScoreCard

The child records have two TestChunk

Code:

            String scoreCardQuery = "select s from ScoreCard as s left join fetch s.testChunks where s.reviewer.id = :reviewerPKID";
            Query query = session.createQuery(scoreCardQuery);
            query.setString("reviewerPKID",reviewer.getId());
            
            scoreCardList = query.list();

This returns two identical ScoreCard objects.

The mapping for ScoreCard is:

Code:

                <!-- Childrens here -->                
                <bag name="testChunks" fetch="join" cascade="all,delete-orphan">
                    <key>
                        <column name="scoreCardPKID"/>
                    </key>
                    <one-to-many class="TestChunk"
                        not-found="ignore"/>
                </bag>

The mapping for TestChunk is:

Code:

                <!-- Parents here -->
                <many-to-one name="scoreCard" class="ScoreCard"
                        not-null="true" 
                        lazy="false" unique-key="uniqueChunk">
                    <column name="scoreCardPKID"/>
                </many-to-one>                                                                                                

Please help me make the list return unique ScoreCard records only.

Any help would be appreciated with credit.

Kind regards,
Roy

rdutertre · **Joined:** Wed Feb 01, 2006 6:49 am **Posts:** 5

To avoid duplicate rows with that one-to-many relationship, use a Set.

Code:

Query query = session.createQuery(...); 
Set results = new HashSet(query.list()); 

rdonasco · **Joined:** Wed Jan 11, 2006 5:21 am **Posts:** 14

I agree, but is there a way we can configure Hibernate not to return duplicate rows?

caveden · **Posted:** Tue Feb 14, 2006 10:14 am

I'm having the same problem, and I'm using set instead of bag. (in fact, aren't the elements inside of the mapped set which are being duplicated, but entities returned in the query)

Here's my mapping of the set in the parent entity:

Code:

<set name="emailsNotificacao" inverse="true" lazy="false"
   fetch="join">
   <key column="ID_TIPO_ERRO" not-null="true" />
   <one-to-many
      class="com.matera.inss.entity.EmailNotificacao" />
</set>

Here's the mapping of the reference to the parent in the child:

Code:

<many-to-one name="tipoErro" column="ID_TIPO_ERRO" lazy="false"
   fetch="select" />

In my tables I have two parent lines, each with two associated child lines. I expected that Hibernate would return me, in a loadAll, 2 entities with their's set containing 2 child entities each.
But in fact it's returning a list of 4 elements, being them the duplication of two distinct entities, wich have their's sets properly populated.
4 is also the number of lines returned by the join made by Hibernate to query for such data. Maybe it's not just coincidence.

Does anyone have a clue of what is happening?

Thanks,
Tiago.

caveden · **Posted:** Wed Feb 15, 2006 3:33 pm

A friend of mine found a weird solution.
He simply substituted the loadAll that I was using from Spring API by a find call with "from ClassName" as parameter.

That's very weird, because both calls should return the same result.

I've inspected the Spring API, and it doesn't seem to be doing anything wrong. The code it executes is the following:

Code:

Criteria criteria = session.createCriteria(entityClass);
prepareCriteria(criteria);
return criteria.list();

This is the code for the prepareCriteria method:

Code:

if (isCacheQueries()) {
   criteria.setCacheable(true);
   if (getQueryCacheRegion() != null) {
      criteria.setCacheRegion(getQueryCacheRegion());
   }
}
if (getFetchSize() > 0) {
   criteria.setFetchSize(getFetchSize());
}
if (getMaxResults() > 0) {
   criteria.setMaxResults(getMaxResults());
}
SessionFactoryUtils.applyTransactionTimeout(criteria, getSessionFactory());

And this is the code for the aplyTransactionTimeout:

Code:

Assert.notNull(criteria, "No Criteria object specified");
SessionHolder sessionHolder =
    (SessionHolder) TransactionSynchronizationManager.getResource(sessionFactory);
if (sessionHolder != null && sessionHolder.hasTimeout()) {
   criteria.setTimeout(sessionHolder.getTimeToLiveInSeconds());
}

It seems that the problem is inside the Criteria.list() method... could someone say if it is so? Is this a bug?

Thank you,
Tiago.

caveden · **Posted:** Mon Feb 20, 2006 12:30 pm

I've opened an issue at Hibernate JIRA.
http://opensource2.atlassian.com/projec ... e/HHH-1513
Let's see what they say... ;-)

[]'s
Tiago.

caveden · **Posted:** Fri Apr 07, 2006 5:23 pm

The issued I opened was closed at Feb 22 (I took some time to come here again :-P) by Gavin King, who provided the following message: "This is a FAQ and has been discussed *many* times."

I've searched the FAQ and the closest thing I found was this topic. (I did the search long ago).
The unique tip it gives is that I can use a Set to eliminate the duplicated entities. This I could already figure out and use as workaround, but saying this behavior is not an issue doens't sound reasonable to me.
If you (Hibernate team) think is something so easy to workaround, that doesn't diserve to be fixed when faced against other issues, ok, but at least keep the issue with a very low priority, don't say such behavior is expected, because it's not. (or at least show me why I'm wrong).

Best regards,
Tiago Rinck Caveden.

ge0ffrey · **Joined:** Thu Oct 13, 2005 4:19 am **Posts:** 98

I am having the same problem and found this through google, so what's the solution?

Thomson · **Joined:** Wed Apr 26, 2006 4:40 am **Posts:** 24

ge0ffrey wrote:

I am having the same problem and found this through google, so what's the solution?

As it is written in the FAQ:

Code:

Set set = new LinkedHashSet();
set.addAll(query.list());   
return set;

caveden · **Posted:** Wed Apr 26, 2006 9:20 am

Thomson wrote:

As it is written in the FAQ:

Code:

Set set = new LinkedHashSet();
set.addAll(query.list());   
return set;

I still think this is a workaround, not a solution (see my previous post).
query.list() should not return duplicate rows....

ge0ffrey · **Joined:** Thu Oct 13, 2005 4:19 am **Posts:** 98

I transformed my usage of loadAll() to a named query ("from Person") and the problem looks solved, but according to the info here it looks like it might not be solved...

Anyway:
- Why does loadAll() duplicate records in the first place? There were 4 instead of 24 records in my database. What's the use of loadAll() anyway in that case?
- I experience the problem only with certain records: some doubled their one-to-many's, other triplicated, others didn't. That seemed fishy.
- Should I put "distinct" in the named query although I am sure there aren't duplicates in the database itself?

I am using surrogate keys and my equals/hashcode bases itself on the surrogate key, which is generated by hibernate's "native".

kimwong1 · **Joined:** Wed Apr 26, 2006 12:27 pm **Posts:** 1

Thanks for helping me with JobQueen and China Sales Reps. I'm with them 2 month now and they, thanks god, really pay. I'm already getting more than 5000 per month and looking for more coworkers. If you change your mind let me know. You can use my promotion code to sign in. mailto:kimwongshu@yahoo.com

azidkore · **Joined:** Thu Apr 27, 2006 12:20 pm **Posts:** 1

Thomson wrote:

ge0ffrey wrote:

I am having the same problem and found this through google, so what's the solution?

As it is written in the FAQ:

Code:

Set set = new LinkedHashSet();
set.addAll(query.list());   
return set;

This is definetly not a solution.
What if you want to page your results?
Say...

Code:

Set set = new LinkedHashSet();
query.setMaxResults(10);
set.addAll(query.list());   
return set;

If query.list() contains duplicates the returning set will contain less than the 10 expected.

I'm still wating for an elegant solution.

bv · **Joined:** Fri Nov 18, 2005 1:50 pm **Posts:** 5

I am also waiting for a solution here.

Thomson · **Joined:** Wed Apr 26, 2006 4:40 am **Posts:** 24

azidkore wrote:

Thomson wrote:

ge0ffrey wrote:

I am having the same problem and found this through google, so what's the solution?

As it is written in the FAQ:

Code:

Set set = new LinkedHashSet();
set.addAll(query.list());   
return set;

This is definetly not a solution.
What if you want to page your results?
Say...

Code:

Set set = new LinkedHashSet();
query.setMaxResults(10);
set.addAll(query.list());   
return set;

If query.list() contains duplicates the returning set will contain less than the 10 expected.

I'm still wating for an elegant solution.

The problem is, that there is no elegant solution. Since HQL is nothing more than a wrapper for SQL statements (well a pretty elaborate one indeed), it can only return the data it gets from the underlaying SQL database.

Therefore, hibernate gives you the same data as JDBC would. If you want to implement paging, you have to use counts and/or adjust the queries so that you get exactly one record per item.

Otherwise you have to allways retreive the complete set and determine the number of results of the base entity with the exact same method as given in the FAQ. (SQL has no way to determine how many distinct rows of table X are within the result of a join of table X and Y)

The easiest way to solve that problem is to just retrieve a Set of your main class by the query, and let hibernate get the values of the associated collections either from its cache or additional selects.