Skip to content

JPA Criteria multiselect with fetch

I have following model:

@Table(name = "SAMPLE_TABLE")
public class SampleModel implements Serializable {

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private Long id;

    @Column(name = "NAME", nullable = false)
    private String name;

    @Column(name = "SHORT_NAME", nullable = true)
    private String shortName;

    @ManyToOne(fetch = FetchType.LAZY, optional = true)
    @JoinColumn(name = "MENTOR_ID")
    private User mentor;

//other fields here

//omitted getters/setters


Now I would like to query only columns: id, name, shortName and mentor which referes to User entity (not complete entity, because it has many other properties and I would like to have best performance).

When I write query:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<SampleModel> query = builder.createQuery(SampleModel.class);
Root<SampleModel> root = query.from(SampleModel.class);;
root.fetch(SampleModel_.mentor, JoinType.LEFT);

query.multiselect(root.get(, root.get(, root.get(SampleModel_.shortName), root.get(SampleModel_.mentor));
TypedQuery<SampleModel> allQuery = em.createQuery(query);
return allQuery.getResultList();

I have following exception:

Caused by: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=generatedAlias1,role=com.sample.SampleModel.model.SampleModel.mentor,tableName=USER_,tableAlias=user1_,origin=SampleModel SampleModel0_,columns={SampleModel0_.MENTOR_ID ,className=com.sample.credential.model.User}}]
    at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(
    at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(
    at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(
    ... 138 more

Query before exception:

SELECT DISTINCT NEW com.sample.SampleModel.model.SampleModel(,, generatedAlias0.shortName, generatedAlias0.mentor)
FROM com.sample.SampleModel.model.SampleModel AS generatedAlias0
LEFT JOIN FETCH generatedAlias0.mentor AS generatedAlias1

I know that I can replace fetch with join but then I will have N+1 problem. Also I do not have back reference from User to SampleModel and I do not want to have..



I ran into this same issue, and found that I was able to work around it by using:

CriteriaQuery<Tuple> crit = builder.createTupleQuery();

instead of

CriteriaQuery<X> crit = builder.createQuery(X.class);

A little extra work has to be done to produce the end result, e.g. in your case:

return allQuery.getResultList().stream()
    map(tuple -> {
        return new SampleModel(tuple.get(0, ...), ...));
10 People found this is helpful