Java Hibernate Count Rows

Question

I am using Java8 with Hibernate5 and JPA2. I would like to count the number of rows in a result set. I have the following code that works, however, I would like to know if there's a more efficient way of doing it? I think the code below first queries the entire result set and the counts the rows.

    final EntityManagerFactory entityManagerFactory = entityManager.getEntityManagerFactory();
    final CriteriaBuilder criteriaBuilder = entityManagerFactory.getCriteriaBuilder();
    CriteriaQuery<Rating> criteria = criteriaBuilder.createQuery(Rating.class);
    Root<Rating> root = criteria.from(Rating.class);
    ParameterExpression<Job> param = criteriaBuilder.parameter(Job.class);

    TypedQuery<Rating> queryRating = entityManager.createQuery(criteria);
    queryRating.setParameter(param, job);
    int results = queryRating.getResultList().size();

Is there a way of rather making the SQL do a count(*)?

UPDATE

Thanks to @chsdk below, I have a revised version of code:

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

Question

How do I set the where clause with the Job parameter?

More info:

+--------+   +------------+    +-----+
| rating |   | rating_job |    | job |
+--------+   +------------+    +-----+
|   ID   |   |   RAT_ID   |    |  ID |
|        |   |   JOB_ID   |    |     |
+--------+   +------------+    +-----+

Rating.java

@ManyToOne(fetch=FetchType.EAGER)
@JoinTable
(
    name="rating_job",
    joinColumns={ @JoinColumn(name="RAT_ID", referencedColumnName="ID") },
    inverseJoinColumns={ @JoinColumn(name="JOB_ID", referencedColumnName="ID") }
)
private Job job;

UPDATE

Thanks to @chsdk, here is my version that works:

    final EntityManagerFactory entityManagerFactory = entityManager.getEntityManagerFactory();
    final CriteriaBuilder criteriaBuilder = entityManagerFactory.getCriteriaBuilder();
    CriteriaQuery<Long> criteria = criteriaBuilder.createQuery(Long.class);
    Root<Rating> root = criteria.from(Rating.class);
    ParameterExpression<Job> param = criteriaBuilder.parameter(Job.class);
    criteria.select(criteriaBuilder.count(root)).where(criteriaBuilder.equal(root.get("job"), param));
    TypedQuery<Long> queryRating = entityManager.createQuery(criteria);
    queryRating.setParameter(param, job);
    Long results = queryRating.getSingleResult();

    return results;

if you want to constrain to some "Job" then join to that entity and apply the where clause, otherwise you aren't using your parameter. And the log tells you what SQL is generated so that should be the basis for what is efficient — Neil Stockton, Apr 18 '17 at 10:05
Thanks Neil. That's my question, how do I do the `where` clause? How do I add the `Job` object to the `where` clause? `cq.where(/*rating is equal to job*/)` — Richard, Apr 18 '17 at 10:07

score 4 · Accepted Answer · edited May 23 '17 at 10:31

4

In JPA2 with CriteriaQuery you can do it this way:

final CriteriaBuilder criteriaBuilder = entityManagerFactory.getCriteriaBuilder();
CriteriaQuery<Long> criteria = qb.createQuery(Long.class);
Root<Country> root = criteria.from(Rating.class);
criteria.select(criteriaBuilder.count(root));

ParameterExpression<Job> param = criteriaBuilder.parameter(Job.class);
criteria.where(criteriaBuilder.equal(root.get("job"), param));

Make a CriteriaQuery<Long> instead of CriteriaQuery<Rating> so it gives you a row count when you count the rows of your criteria.from(Rating.class) result.

Edit:

I edited the answer code to include testing over Job given parameter in the query, respecting your entities mapping.

Then to execute your query you will need to write:

TypedQuery<Country> query = em.createQuery(criteria);
query.setParameter(param, yourJobObject);
Long resultsCount = query.getSingleResult();

Note that you need to wrap the query.getSingleResult() in a try ..catch block because it may throw an error.

Refernce:

Please check the answer here and this JPA Criteria API Queries tutorial for further reading.

edited May 23 '17 at 10:31

Community

1
1

answered Apr 18 '17 at 09:42

cнŝdk

31,391
7
56
78

Thanks, but where do you put the Job parameter? – Richard Apr 18 '17 at 09:46
Thanks, but I am not sure if this is possible, as my `Rating` table uses a join table. I will update the description above. – Richard Apr 18 '17 at 09:58
I think it's almost there. I now get: `Named parameter [param0] not set`. Sorry, I see your edit, this may fix it. – Richard Apr 18 '17 at 10:21
@Richard did you used `query.setParameter(param, yourJobObject);` as I suggested in my Edit? – cнŝdk Apr 18 '17 at 10:22

score 1 · Answer 2 · answered Apr 18 '17 at 09:24

Just an example, I use that to check the existence of entity.

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Long> cQuery = builder.createQuery(Long.class);
Root<Tag> from = cQuery.from(Tag.class);
cQuery.where(from.get("ID").in(ids));

CriteriaQuery<Long> nbTags = cQuery.select(builder.count(from));

return em.createQuery(nbTags).getSingleResult();

It just send me an 0 if their isn't any tags, otherwise the number of existing tags.

score 1 · Answer 3 · answered Apr 18 '17 at 09:30

1

Just add projection using rowCount like this :

return (Long) criteria.setProjection(Projections.rowCount()).uniqueResult();

The result will be in Long type object of number of rows

answered Apr 18 '17 at 09:30

Angga

2,305
1
17
21

Thanks, that looks useful. However, I do get the following compile error: `The method setProjection(Projection) is undefined for the type CriteriaQuery` – Richard Apr 18 '17 at 09:33
since it is NOT JPA Criteria (the answer is for Hibernates own API, so throw away portability!) – Neil Stockton Apr 18 '17 at 09:34
I guess this also ignores the `Job` parameter? – Richard Apr 18 '17 at 09:35
Ah, my bad, this answer is for org.hibernate.Criteria(hibernate) not javax.persistence.criteria.CriteriaQuery(JPA). – Angga Apr 18 '17 at 09:38

Java Hibernate Count Rows

3 Answers3

Linked