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"]
}
]