javahibernatejpahibernate-criteriacriteria-api

Fetch a collection in Criteria API multiselect


I have those entities

@Entity
public class Parent {
    @Id
    private Long id;

    @OneToMany(mappedBy = "parent", fetch = FetchType.LAZY)
    private List<Child> children;

    @OneToOne
    private Foo foo;

    // getters and setters
}

@Entity
public class Child {
    @Id
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "parent_id")
    private Parent parent;

    // getters and setters
}

@Entity
public class Foo {
    @Id
    private Long id;

    // ....
}

And I want to select them into view like that. Please notice that in real example I have much more joins and much more fields like from entity Foo

public class ParentView {

      private Long parentId;
      
      private Long fooId;

      private List<Child> children

}

I'm trying to do that with this Criteria API code

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<ParentView> query = cb.createQuery(ParentView.class);
Root<Parent> parentRoot = query.from(Parent.class);
Join<Parent, Foo> fooJoin = parentRoot.join("children", JoinType.LEFT);
parentRoot.fetch("children")

query.multiselect(
        parentRoot.get("id"),
        fooJoin.get("id"),
        parentRoot.get("children")
);

But getting error

query specified join fetching, but the owner of the fetched association was not present in the select list 

If I include root into multiselect call as a first parameter (as an owner of fetched join) I'm getting the same error.

I've tried some workarounds with subquery or simple join without fetch but they all results in some error. Is there a way to map query results to the collection field of view in Hibernate or some workaround to do that?


Solution

  • JPA lets you fetch entities, but when fetching data, you get the data as it is returned from the database - row by row. This means you cannot get parsed collections of children like what gets built into the Parent entity.

    To fetch the data from the original question (ids, and child instance)

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery cquery = cb.createQuery();
    Root<Parent> parentRoot = query.from(Parent.class);
    Join<Parent, Foo> fooJoin = parentRoot.join("foo", JoinType.LEFT);
    Join<Parent, Child> childJoin = parentRoot.join("children", JoinType.LEFT);
    
    cquery.multiselect(
            parentRoot.get("id"),
            fooJoin.get("id"),
            childJoin
    );
    ..
    cquery.orderBy(parentRoot.get("id"));
    Query query = em.createQuery(cquery);
    List<Object[]> scalarResults = query.getResultList();
    
    Map<Long,ParentView> map = new HashMap<Long,ParentView>();
    for (Object[] row : scalarResults) {
      if (!map.contains(row[0]) {
        map.put(row[0], new ParentView(row[0], row[1], row[2]);
      } else {
        map.get(row[0]).addChild(row[2]);
      }
    }
    return map.values();