JPA native query result returns duplicate children

Question

I have a parent table and a child table in my database, and for them there is one ToToMany mapping in the corresponding entity classes. The children’s table has a foreign key parent_id. I am using JPA 2 with Hibernate and MySQL DB.

I want to get all the parent objects and their corresponding child objects based on some parent property using SQL Native Query.

For this, I have SqlResultSetMapping as follows:

@SqlResultSetMapping(name="ParentsWithChildren", entities={ @EntityResult(entityClass = Parent.class), @EntityResult(entityClass = Child.class)}) 

I am requesting the following:

 String queryString = "select p.*, c.* from parent p left join child c on p.id = c.parent_id where p.property = <some_property>"; Query query = entityManager.createNativeQuery(queryString, "ParentsWithChildren"); List<Object[]> resultList = query.getResultList(); 

When I click on the list of results, I find duplicate children for different rows in my child table, as shown in the output:

 for(Object obj[]: resultList){ Parent parent = (Parent) obj[0]; Child child = (Child) obj[1]; System.out.println("Parent: " + parent + ", Child: " + child); } 

Output:

 Parent: Parent@3966c600 , Child: Child@1 Parent: Parent@3966c600 , Child: Child@1 Parent: Parent@3966c600 , Child: Child@1 Parent: Parent@3966c600 , Child: Child@1 Parent: Parent@3966c600 , Child: Child@1 Parent: Parent@3966c600 , Child: Child@1 Parent: Parent@3966c600 , Child: Child@1 Parent: Parent@3966c600 , Child: Child@1 

I do not understand why this is so. Is there a way (matching) to get all (different) child objects using my own query. Retrieving with column names may work and does not require matching objects, but I want to get all the columns of the child table, and therefore prefer to c. * In sql query.

Answer 1

I would use a regular HQL query instead of my own query. In HQL, you can use the fetch join:

 "select p.*, c.* from parent p left join fetch child c on p.id = c.parent_id where p.property = <some_property>" 

With collections, collections of selections can be initialized along with their parent objects with a single selection.