Why does this Hibernate Criteria Query fails with "No explicit selection and an implicit one cold not be determined"?

Question

I have an entity called Bucket, and I'm trying to build a criteria query to determine whether there is a Bucket stored with the "Name" property equals to "Bucket_1". So basically it is an exists query.

There is nothing special about the Bucket class, simpler impossible:

@Entity(name="Bucket")
@Table(name = "BUCKETS")
public class Bucket implements Serializable {
    private static final long serialVersionUID = 1L;

    @Column(name = "BUCKET_NAME", length=200)
    private String Name;

        ...
}

For the query, this is what I go so far:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Boolean> superQuery = criteriaBuilder.createQuery(Boolean.class);

Class<? extends T> scopeClass = Bucket.class;
Root<? extends T> root = superQuery.from(scopeClass);

Path<?> attributePath = root.get("Name");
Predicate pred = criteriaBuilder.equal(attributePath, criteriaBuilder.literal("Bucket_1"));

Subquery<? extends T> subQuery = superQuery.subquery(scopeClass);
subQuery.where(pred);
Predicate where = criteriaBuilder.exists(subQuery);

superQuery = superQuery.select(where);

/* This line fails!! */
TypedQuery<Boolean> typedQuery = em.createQuery(superQuery);

boolean result = typedQuery.getSingleResult();

When I execute the query about, I get the following exception on the last line:

Caused by: java.lang.IllegalStateException: No explicit selection and an implicit one cold not be determined
    at org.hibernate.ejb.criteria.QueryStructure.locateImplicitSelection(QueryStructure.java:296)
    at org.hibernate.ejb.criteria.QueryStructure.render(QueryStructure.java:249)
    at org.hibernate.ejb.criteria.CriteriaSubqueryImpl.render(CriteriaSubqueryImpl.java:281)
    at org.hibernate.ejb.criteria.predicate.ExistsPredicate.render(ExistsPredicate.java:57)
    at org.hibernate.ejb.criteria.predicate.ExistsPredicate.renderProjection(ExistsPredicate.java:62)
    at org.hibernate.ejb.criteria.QueryStructure.render(QueryStructure.java:252)
    at org.hibernate.ejb.criteria.CriteriaQueryImpl.render(CriteriaQueryImpl.java:340)
    at org.hibernate.ejb.criteria.CriteriaQueryCompiler.compile(CriteriaQueryCompiler.java:223)
    at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:441)
    at com.specktro.orchid.io.connection.database.dao.internal.DefaultDAO.has(DefaultDAO.java:426)
    ... 28 more

I researched a lot but couldn't find anyone with the same error having it explained and/or fixed.

I also tried this way:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Boolean> superQuery = criteriaBuilder.createQuery(Boolean.class);

Class<? extends T> scopeClass = Bucket.class;
superQuery.from(scopeClass);
Subquery<? extends T> subQuery = superQuery.subquery(scopeClass);
Root<? extends T> root = subQuery.from(scopeClass);

Path<?> attributePath = root.get("Name");
Predicate pred = criteriaBuilder.equal(attributePath, criteriaBuilder.literal("Bucket_1"));

Subquery<? extends T> subQuery = superQuery.subquery(scopeClass);
subQuery.where(pred);
Predicate where = criteriaBuilder.exists(subQuery);

superQuery = superQuery.select(where);

/* This line fails!! */
TypedQuery<Boolean> typedQuery = em.createQuery(superQuery);
boolean result = typedQuery.getSingleResult();

But I get the same exact exception.

Does anyone know why I get this and how to fix this query?

Thank you!! Eduardo

UPDATE:

I have been able to construct a query using exists the following way:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();

CriteriaQuery<Bucket> query = criteriaBuilder.createQuery(Bucket.class);
Root<Bucket> root = query.from(Bucket.class);
query.select(root);

Subquery<Bucket> subquery = query.subquery(Bucket.class);
Root<Bucket> subRootEntity = subquery.from(Bucket.class);
subquery.select(subRootEntity);

Path<?> attributePath = subRootEntity.get("Name");
Predicate pred = criteriaBuilder.equal(attributePath, criteriaBuilder.literal("Bucket_1"));
subquery.where(pred);
query.where(criteriaBuilder.exists(subquery));

TypedQuery<Bucket> typedQuery = em.createQuery(query);

boolean entityExists = typedQuery.getResultList().size() == 1;

Which resulted in a weird SQL generated like this:

select generatedAlias0 from com.test.Bucket as generatedAlias0 where exists (select generatedAlias1 from com.test.Bucket as generatedAlias1 where generatedAlias1.Name=:param0)

I guess the hard part is to get that select 1 ... going, where the result of the external query is Boolean and not the matching entity.

I know this one I got works, I'm now just trying to learn how to do it "properly", I believe there is a way...

score 2 · Answer 1 · answered Jul 17 '11 at 01:55

2

Can you just do this?

boolean result = (session.createCriteria(Bucket.class)
       .add(Restrictions.eq("Name","Bucket_1"))
       .setProjection(Projections.count("Name"))
       .uniqueResult() > 0);

answered Jul 17 '11 at 01:55

Alex Gitelman

24,429
7
52
49

This solution does work but I want to use exists construction for performance matters. I'll make many, many million records to check for. The exists construction should perform better as the DBMS stops looking for records once the first one matching the restrictions is found. Using the count it has to check for all of them, then separate the distinct ones, and only then complete the query. – Edy Bourne Jul 17 '11 at 23:04
How would your SQL query look? Something like (for Oracle) `select 1 from dual where exists (select * from buckets where name='BUCKET_1')`? I'll see if I get an idea and update the answer. I think detached query should work. – Alex Gitelman Jul 18 '11 at 00:19
And by the way, following this performance logic, you can always do query with with `setMaxResults(1)`. – Alex Gitelman Jul 18 '11 at 00:23
You have a great point on the setMaxResult(1), I might end up doing that way combining the suggested solution with your suggestion. The query would look like the one you wrote, yeah! – Edy Bourne Jul 18 '11 at 04:25
1

actually it will, probably, be even simpler as you don't need `count` projection. Instead of `uniqueResult` do `list` and compare it's size with 0. It may worth setting `id` projection so you don't have to transfer all columns. – Alex Gitelman Jul 18 '11 at 04:50

Why does this Hibernate Criteria Query fails with "No explicit selection and an implicit one cold not be determined"?

1 Answers1