Select count with "where" using CriteriaBuilder

Question

I'm trying to make something like this

SELECT COUNT(*) FROM Tickets WHERE ticketStatus = 3 AND ...

but only using CriteriaBuilder provided by JPA. Here's what I've got:

private class Criteria {
    CriteriaBuilder builder;
    CriteriaQuery<TicketModel> query;
    Root<TicketModel> root;

    public Criteria() {
        builder = em.getCriteriaBuilder();
        query = builder.createQuery(TicketModel.class);
        root = query.from(TicketModel.class);
    }
}
public long getSearchResultsQuantity(SearchModel search) {
    Criteria c = new Criteria();
    List<Predicate> params = new ArrayList<Predicate>();

    if (search.getStartDate() != null && search.getEndDate() != null) {
        params.add(c.builder.between(c.root.get(TicketModel_.ticketDate), search.getStartDate(),
                search.getEndDate()));
    }

    if (search.getStatus() != -1) {
        params.add(c.builder.equal(c.root.get(TicketModel_.ticketStatus), search.getStatus()));
    }

    CriteriaQuery<Long> q = c.builder.createQuery(Long.class);

    return em.createQuery(
            q.select(c.builder.count(q.from(TicketModel.class))).where(params.toArray(new Predicate[] {})))
            .getSingleResult();
}

However, when I try to perform this query I receive such Exception:

org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.ticketStatus' [select count(generatedAlias0) from com.mif.spring.models.TicketModel as generatedAlias0 where generatedAlias1.ticketStatus=0]

I'm a little bit nooby at Spring, so any help would be much appreciated!

Answer 1

The problem is that you are mixing roots.

If you create the same alias for the both roots will work.

For example:

private class Criteria {
    CriteriaBuilder builder;
    CriteriaQuery<TicketModel> query;
    Root<TicketModel> root;


    public Criteria(String alias) {
        builder = em.getCriteriaBuilder();
        query = builder.createQuery(TicketModel.class);
        root = query.from(TicketModel.class);
        root.alias(alias);
    }
}

And then

 Criteria c = new Criteria("someAlias");
 ...
 CriteriaQuery<Long> q = c.builder.createQuery(Long.class);
 Root<TicketModel> root = q.from(TicketModel.class);
 root.alias("someAlias");

 return em.createQuery(
         q.select(c.builder.count(root)).where(
params.toArray(new Predicate[] {})))
                .getSingleResult();

Maybe you might be interested in the class JpaUtils (I wrote it) with which you could simply do:

Long count = JpaUtils.count(em, criteria);

for getting the count from any criteria query.