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
– Neil Stockton, Commented Apr 18, 2017 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
– Richard, Commented Apr 18, 2017 at 10:07

Community · Accepted Answer · 2017-05-23 10:31:02Z

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, 2017 at 10:31

CommunityBot

11 silver badge

answered Apr 18, 2017 at 9:42

cнŝdk

32.2k7 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Richard Over a year ago

Thanks, but where do you put the Job parameter?

Richard Over a year ago

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 Over a year ago

I think it's almost there. I now get: Named parameter [param0] not set. Sorry, I see your edit, this may fix it.

cнŝdk Over a year ago

@Richard did you used query.setParameter(param, yourJobObject); as I suggested in my Edit?

Zorglube · Accepted Answer · 2017-04-18 09:24:11Z

1

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.

answered Apr 18, 2017 at 9:24

Zorglube

7371 gold badge7 silver badges17 bronze badges

Comments

Angga · Accepted Answer · 2017-04-18 09:30:19Z

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, 2017 at 9:30

Angga

2,3331 gold badge19 silver badges21 bronze badges

4 Comments

Richard Over a year ago

Thanks, that looks useful. However, I do get the following compile error: The method setProjection(Projection) is undefined for the type CriteriaQuery<Rating>

Neil Stockton Over a year ago

since it is NOT JPA Criteria (the answer is for Hibernates own API, so throw away portability!)

Richard Over a year ago

I guess this also ignores the Job parameter?

Angga Over a year ago

Ah, my bad, this answer is for org.hibernate.Criteria(hibernate) not javax.persistence.criteria.CriteriaQuery(JPA).

Collectives™ on Stack Overflow

Java Hibernate Count Rows

3 Answers 3

4 Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related