JPA 2 No explicit selection and an implicit one cold not be determined

Question

I am trying to fetch all users for a folder where the user was created after a certain date. the relationship between the user and the folder lives in a separate table.

This is the query I came up with but it throws the exception

No explicit selection and an implicit one cold not be determined

The code

@Override
public List<RetailPostUserTbl> getNewUsersForSiteSince( Date date, Integer siteId )
{
    List<RetailPostUserTbl> toReturn = new ArrayList<RetailPostUserTbl>();
    EntityManager em = getEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();

    Class<RpUserFolderMapTbl> userFolderPC = userFolderMapDAO.getPersistentClass();

    CriteriaQuery<RpUserFolderMapTbl> mapQuery = cb.createQuery( userFolderPC );
    Root<RpUserFolderMapTbl> root = mapQuery.from( userFolderPC );
    Path<Integer> folderIdPath = root.get( RpUserFolderMapTbl_.folder ).get( FolderTbl_.folderId );

    Predicate folderCondition = cb.equal( folderIdPath, siteId );

    Subquery<RetailPostUserTbl> rpSubQ = mapQuery.subquery( persistentClass );
    Root<RetailPostUserTbl> subQRoot = rpSubQ.from( persistentClass );
    Path<UserTbl> userPath = subQRoot.get( RetailPostUserTbl_.user );
    Path<Date> userCreatedPath = userPath.get( UserTbl_.userCreateDate );
    Predicate userCreateDateCondition = cb.greaterThanOrEqualTo( userCreatedPath, date );
    rpSubQ.where( userCreateDateCondition );

    mapQuery.where( cb.and( folderCondition, cb.exists( rpSubQ ) ) );

    TypedQuery<RpUserFolderMapTbl> query = em.createQuery( mapQuery );
    List<RpUserFolderMapTbl> results = query.getResultList();
    for ( RpUserFolderMapTbl result : results )
    {
        RetailPostUserTbl rpuser = result.getUser().getRetailPostUser();
        toReturn.add( rpuser );
    }
    return toReturn;
}

Anyone know why this is not working?

score 32 · Answer 1 · edited Feb 02 '19 at 14:48

32

You should set explicitly selection also for "subqueries".

rpSubQ.select(subQRoot);

edited Feb 02 '19 at 14:48

Willi Mentzel

27,862
20
113
121

answered Sep 16 '14 at 08:46

gokhansari

2,379
1
27
33

score 8 · Answer 2 · answered Apr 19 '11 at 21:58

I had the exact same error today. The funny thing is that I grabbed my example from Hibernate 3.6.3.Final docs. Their example is:

CriteriaQuery query = builder.createQuery();
Root<Person> men = query.from( Person.class );
Root<Person> women = query.from( Person.class );
Predicate menRestriction = builder.and(
    builder.equal( men.get( Person_.gender ), Gender.MALE ),
    builder.equal( men.get( Person_.relationshipStatus ), RelationshipStatus.SINGLE )
);
Predicate womenRestriction = builder.and(
    builder.equal( women.get( Person_.gender ), Gender.FEMALE ),
    builder.equal( women.get( Person_.relationshipStatus ), RelationshipStatus.SINGLE )
);
query.where( builder.and( menRestriction, womenRestriction ) );

What I did to "fix" the error is explicitly select the root. Note I had to create one root to solve this. Here is my example:

CriteriaQuery query = builder.createQuery();
Root<Person> personRoot = query.from( Person.class );
Predicate menRestriction = builder.and(
    builder.equal( personRoot.get( Person_.gender ), Gender.MALE ),
    builder.equal( personRoot.get( Person_.relationshipStatus ), RelationshipStatus.SINGLE )
);
Predicate womenRestriction = builder.and(
    builder.equal( personRoot.get( Person_.gender ), Gender.FEMALE ),
    builder.equal( personRoot.get( Person_.relationshipStatus ), RelationshipStatus.SINGLE )
);
query.select(personRoot);
query.where( builder.and( menRestriction, womenRestriction ) );

What I can't figure out is why an implicit selection could not be made. In Hibernate's example the only class that is used is Person.class. I'll update my response when I dig in a little further.

The first query from the hibernate docs would generate a SQL query similar to SELECT men.*, women.* FROM person as men, person as women WHERE men.gender = 'MALE' AND men.relationship_status = 'SINGLE' AND women.gender='FEMALE' AND women.relationship_status = 'SINGLE'. You are going to get back a row for each Male/Female combination, and hibernate does not know which columns you are actually trying to select out (male ones? female ones? some combination?) because the tables have overlapping column names (all of the columns in this case overlap) — jmkelm08, Oct 17 '18 at 22:05

score 2 · Answer 3 · answered Apr 28 '22 at 11:22

Query roots specify the domain objects on which the query is evaluated. Query root is an instance of the Root interface and must explicitly declared, so instead of this

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(type);
list.forEach(i -> entityManager
         .createQuery(criteriaQuery.where(
              criteriaBuilder.equal(criteriaQuery.from(type).get(field), i))).getResultList());

You must explicitly define root as

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(type);
Root<T> root = criteriaQuery.from(type);
list.forEach(i -> entityManager
        .createQuery(criteriaQuery.where(
            criteriaBuilder.equal(root.get(field), i))).getResultList());

JPA 2 No explicit selection and an implicit one cold not be determined

3 Answers3

Linked

Related