JPA native query result returns duplicate child objects

Question

I have a parent table and a child Table in my DB, and have a OneToMany mapping for them in their corresponding entity classes. The child table has a foreign key parent_id. I am using JPA 2 with Hibernate and MySQL DB.

I wish to retrieve all the parent objects and their corresponding child objects based on some parent property with an SQL Native Query.

For that I have an SqlResultSetMapping as follows:

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

I am querying as follows:

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();

On traversing through the result list, I find duplicate child objects 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 don't understand why is this. Is there any way (mapping) to get all the (different) child objects with a native query. Fetching with column names could work and wouldn't require a corresponding object mapping, but I want to fetch all the columns of the child table and hence would prefer to user c.* in the sql query.

did you try to add "distinct' in your select query ? – Laabidi Raissi Mar 15 '13 at 16:52 — Laabidi Raissi, Mar 15 '13 at 16:52

Donato Szilagyi · Answer 1 · 2013-03-16T17:27:00.150

0

I would use a normal HQL query instead of native query. In HQL you can use 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 fetch join collections can be initialized along with their parent objects using a single select.

edited Mar 16 '13 at 17:27

answered Mar 15 '13 at 18:42

Donato Szilagyi

4,279
4
36
53

Why do you want to use native query? In HQL this problem can be solved with a fetch join. I modified the answer. – Donato Szilagyi Mar 16 '13 at 17:28
I want to use native query because I would also like to fetch grandchildren, and grand-grandchildren, etc of the parent in one query. HQL/JPQL gives gives exception "Cannot simultaneously fetch multiple bags" for such eager fetches. – dumbcoder Mar 19 '13 at 06:34

JPA native query result returns duplicate child objects

1 Answers1