One-To-Many relationship gets duplicate objects using left join

Question

I have 2 classes in a One-To-Many relationship and a JPQL query that is now working as I expected. Even after read some post about it, it does not seem clear to me.

@Entity
@Table(name = "context_entity")
public class ContextEntity {
    @Id
    private Long id;

    @OneToMany(cascade = CascadeType.ALL, fetch=FetchType.LAZY)
    @JoinColumn(name = "entity_id")
    private List<TypeSpecCharacteristicValue> metadata = null;
}

@Entity
@Table(name = "type_spec_characteristic_value")
public class TypeSpecCharacteristicValue {
    @Id
    private Long id;

    private String value;
}

BTW I cut only the important part of my code.

If I run the following query I get duplicates TypeSpecCharacteristicValue objects:

session.createQuery("select e.metadata from ContextEntity e left join e.metadata where e.id=:contextId")

Then I check the SQL statement generated by hibernate, which resulted in the following:

select metadata2_.id as id1_5_, metadata2_.value as value3_5_ from context_entity contextent0_ left outer join type_spec_characteristic_value metadata1_ on contextent0_.id=metadata1_.entity_id inner join type_spec_characteristic_value metadata2_ on contextent0_.id=metadata2_.entity_id where contextent0_.id=[some_context_id];

Why is hibernate generating a second join with the table type_spec characteristic_value? Should I use a distinct?

Thank you in advance.

score 0 · Answer 1 · answered Sep 03 '18 at 19:22

I am guessing that the second (inner) join comes implicitly from the statement:

select e.metadata from ContextEntity e..

By using the dot operator on the entity you invoke the relation between the tables since you are requiring all fields from the other table as well.

score 0 · Accepted Answer · answered Sep 06 '18 at 18:54

Maybe I fell into a very common misunderstanding of JPA.

According to what I understood from Hibernate FAQs and this question, that has some similarities to mine, this is the expected behavior of JPA and it is necessary to use other resources to eliminate duplicates from result.

Despite this behavior, I discovered that with the following optimization in my query, the second JOIN is avoided and I get no duplicate results.

select m from ContextEntity e left join e.metadata m where e.id=:contextId

Now the generated query results in:

select metadata1_.id as id1_5_, metadata1_.value as value3_5_ from context_entity contextent0_ left outer join type_spec_characteristic_value metadata1_ on contextent0_.id=metadata1_.entity_id where contextent0_.id=[some_context_id]

I guess that the use of alias in the right side of join in somehow tells to hibernate to fetch directly the metadata object and because of that some optimizations are applied.

One-To-Many relationship gets duplicate objects using left join

2 Answers2