count * with where clause using JPA criteria query

5
votes

I want to find records for related_elements table, where relationId belongs to a list

suppose a Set tempSet contains[2,3,4] I have to check for these value contains in related_element table using jpa criteria query

CriteriaBuilder cb1=entityManager.getCriteriaBuilder();
        CriteriaQuery<RelatedElements> cq1=cb1.createQuery(RelatedElements.class);
        Root<RelatedElements> RelatedElementsRoot=cq1.from(RelatedElements.class);
    for (Integer tSet : tempSet) {
            ParameterExpression<Integer> pRelatedElement=cb1.parameter(Integer.class);
            cq1.multiselect(cb1.count(RelatedElementsRoot.<RelatedElements>get("relatedElementsPk").<Integer>get("relationId"))).where(cb1.equal(RelatedElementsRoot.get("relationId"), pRelatedElement));
            TypedQuery<RelatedElements> qry = entityManager.createQuery(cq1);
            qry.setParameter(pRelatedElement, tSet);
            count = entityManager.createQuery(cq1).getSingleResult().getRelationId();
        }

but its now working...any suggessions

second try 

CriteriaBuilder cb1=entityManager.getCriteriaBuilder();
        CriteriaQuery<Integer> cq1 = cb1.createQuery(Integer.class);
        Root<RelatedElements> RelatedElementsRoot=cq1.from(RelatedElements.class);
        for (Integer tSet : tempSet) {
            ParameterExpression<Integer> pRelatedElement=cb1.parameter(Integer.class);
            cq1.multiselect(cb1.count(cq1.from(RelatedElements.class)));
            cq1.where((cb1.equal(RelatedElementsRoot.get("relatedElementsPk").get("relationId"), pRelatedElement)));
            TypedQuery<Integer> qry = entityManager.createQuery(cq1);
            qry.setParameter(pRelatedElement, tSet);
            count =qry.getSingleResult();
        }

its giving exception at qry.setParameter

Unable to locate appropriate constructor on class [java.lang.Integer] [select new java.lang.Integer(count(*)) from com.mcd.webex.model.RelatedElements as generatedAlias0, com.mcd.webex.model.RelatedElements as generatedAlias1 where generatedAlias0.relatedElementsPk.relationId=:param0]

3
jpa-2.0criteria-api
Could you show your cq1 string data? - Peerapat A

3 Answers

2
votes

As documented, CriteriaBuilder.count returns Expression<java.lang.Long>. Consequently type argument to CriteriaQuery and TypedQuery should be Long as well. Same holds true for type of count variable.

When there is only one value to be selected, then it makes sense to use CriteriaQuery.select instead of multiselect, because then such an error is catched already in compile time.

Long count;
...
CriteriaQuery<Long> cq1 = cb1.createQuery(Long.class);
...
cq1.select(cb1.count(cq1.from(RelatedElements.class)));
...
TypedQuery<Long> qry = entityManager.createQuery(cq1);
8
votes
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
Root<Dzialy> root = cq.from(Dzialy.class);
EntityType <Dzialy> Dzialy_ = root.getModel();

cq.select((cb.countDistinct(root)));
cq.where( cb.equal( root.get(Dzialy_.getSingularAttribute("DZI_id")), 1) ); 

long l = em.createQuery(cq).getSingleResult();

em - EntityManager
DZI_id - column name
1 - searching value
Dzialy - entity class

0
votes
CriteriaBuilder qb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> cq = qb.createQuery(Long.class);
cq.select(qb.count(cq.from(YourEntity.class)));
cq.where(/*your stuff*/);
return entityManager.createQuery(cq).getSingleResult();