CODEWITHSUNDEEP
hibernatespring-data-jpajpa-2.0
mba12
mba12

Reputation: 2792

Hibernate JPA CriteriaQuery with count and where predicate

I am trying to replicate the results of this query in JPA/Hibernate with a CriteriaQuery.

select count(*) from tbl_survey where CREATED_DATE > to_date('2020-04-01', 'yyyy-mm-dd')

The daysBack value is passed in as a parameter.

    Calendar cal = Calendar.getInstance();
    cal.add(Calendar.DATE, daysBack);
    Date daysAgo = cal.getTime();
    try {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Long> cq = cb.createQuery(Long.class);
        Root<Survey> root = cq.from(Survey.class);
        Path<Date> dateCreated = root.<Date>get("createdDate");
        Predicate datePredicate = cb.greaterThanOrEqualTo(dateCreated, daysAgo);
        cq.where(datePredicate);
        cq.select(cb.count(cq.from(Survey.class)));

        long count = entityManager.createQuery(cq).getSingleResult();
        JSONObject resultJson = new JSONObject();
        resultJson.put(SURVEY_COUNT, count);

        logger.info("Count for Survey table is: {}", count);
        return new ResponseEntity<>(resultJson.toString(), HttpStatus.OK);
    } catch (Exception e) {
        logger.error(e.getLocalizedMessage(), e);
        return new ResponseEntity(e.getLocalizedMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
    }

The log output is: Count for Survey table is: 36

However there are only 6 rows in the table which suggests to me some sort of self-join or cross product is being generated to create the output of 36. What should I be doing differently to get the correct count?

Upvotes: 1

Views: 523

Answers (1)

Eklavya
Eklavya

Reputation: 18480

Self joining occurs for this line where you use new root

cq.select(cb.count(cq.from(Survey.class)));

root used for where condition and count query is different that caused self joining. Use same root for count query also

cq.select(cb.count(root));

Upvotes: 1

Related Questions