JPA+HIBERNATE: cartesian product in Many-to-Many relationship

Question

I try to explain the problem. I have an entity with ManyToMany relationship

@Entity
@Table(name="TABLE1")
public class Table1 implements Serializable {

...

//bi-directional many-to-many association to Table1
@ManyToMany
@JoinTable(
    name="TABLE2"
    , joinColumns={
        @JoinColumn(name="ID_ELEMENTS1")
        }
    , inverseJoinColumns={
        @JoinColumn(name="ID_ELEMENTS2")
        }
    )
private List<Table1> elements;

//bi-directional many-to-many association to Table1
@ManyToMany(mappedBy="elements")
private List<Table1> elementOf;

...

}

Db table are:

TABLE1
ID      ...
55499   ...
55498   ...
55497   ...

TABLE2
ID_ELEMENTS1    ID_ELEMENTS2
55499           55498
55499           55497

When I try to execute following jpql query

SELECT  
    t  
FROM  
    Table1 t  
    LEFT JOIN FETCH t.elementOf 
WHERE  
    t.id = 55499

result is an arraylist with two elements (with id 55499) and every element has an arraylist of two elements (one with id 55498 and one with id 55497). The result I would like to obtain is one element (with id 55499) with arraylist of two elements (one with id 55498 and one with id 55497). I hope I was clear. Can you help me to optimize the java object result (I vaguely remember QueryHints.BATCH in eclipselink)?

Answer 1

You have specified the join in the query, that's what gets executed. JPA provider will not remove duplicates automatically.

You can just add distinct in the query to remove any duplicates:

SELECT  
    DISTINCT t  
FROM  
    Table1 t  
    LEFT JOIN FETCH t.elementOf 
WHERE  
    t.id = 55499