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;
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With