Spring-Data @Query with JOIN FETCH, returns Cartesian

Viewed 149

I've got Cartesian when run @Query with JOIN FETCH

I have two simple Entities: Parent and Child joining by field 'parent_id'. Parent has @ElementCollection field witch supposed to contain all child's names (see code). The SpringData repository contains just simple findAll method with @Query which I'm trying to fetch Parents with Child together. When I use just plain findAll() method, it returns expected amount of the Parents, each of them with collection of child_names in it as expected. But it produces SQL query by the each parent. When I'm trying to optimize SQL queries, I'm adding the @Query with "JOIN FETCH" predicate , it produces only one query as expected, BUT instead of one parent it returns parent for each child entry. Could you please give me any idea what is wrong with my @Query, why it returns Cartesian instead of just join two entries and return only one Parent ?

@Entity
@Table(name = "PARENT")
public class Parent {
    @Id @Column(name = "parent_id") private String parentId;
    @Column(name = "parent_name") private String parentName;

    @ElementCollection (fetch = FetchType.LAZY)
    @CollectionTable(name = "CHILD",
            joinColumns = @JoinColumn(name = "parent_id", referencedColumnName = "parent_id")
    )
    @Column(name = "child_name")
    private List<String> childNames;
@Entity
@Table(name = "CHILD")
public class Child {
    @EmbeddedId private ChildKey key;
    @Column(name = "child_name") private String childName;
... getters-setters are omitted ...
}
@Repository
public interface ParentRepo extends JpaRepository<Parent, String> {

    @Query("from Parent s JOIN FETCH s.childNames names")
    List<Parent> findAll();
}
@RestController
@RequestMapping("/api")
public class MyController {

    @Autowired private ParentRepo repo;

    @GetMapping("/findAll")
    List<Parent> findAll() {
        return repo.findAll();
    }
}

I expect the output when configure JPA repository with @Query with JOIN FETCH:

[ {
"parentId": "000001",
"parentName": "parent1",
"childNames": ["child1.1","child1.2","child1.3"]
} ]

But the actual result is:

[ {
"parentId": "000001",
"parentName": "parent1",
"childNames": ["child1.1","child1.2","child1.3"] 
},
{
"parentId": "000001",
"parentName": "parent1",
"childNames": ["child1.1","child1.2","child1.3"]
},
{
"parentId": "000001",
"parentName": "parent1",
"childNames": ["child1.1","child1.2","child1.3"]
}
]
0 Answers
Related