0

I am new in JPA, I have try to make Dynamic query with Criteria Query.

my code :

    CriteriaBuilder cb = crudService.getEm().getCriteriaBuilder();
    // query 1
    CriteriaQuery<MsCountry> cq = cb.createQuery(MsCountry.class);
    Root<MsCountry> myObj = cq.from(MsCountry.class);

    Join<MsCountry, MsCurrency> join = myObj.join(MsCountry_.currencyId, JoinType.INNER);

    // query 2
    CriteriaQuery<Long> cqCount = cb.createQuery(Long.class);
    Root<MsCountry> rootCount = cqCount.from(MsCountry.class);
    cqCount.select(cb.count(rootCount));

    //filtering
    List<Predicate> predicates = new ArrayList<Predicate>();
    for (Entry<String, String> filter : filters.entrySet()) {
        String[] filterField = filter.getKey().split("\\.", 2);
        Path path = filterField.length == 1 ? myObj.get(filterField[0]) : join.get(filterField[1]);
        predicates.add(filter.getValue().matches("[0-9]+")
                ? cb.equal(path, Long.valueOf(filter.getValue()))
                : cb.like(path, "%" + filter.getValue() + "%"));
    }
    //use same where for both query
    cq.where(predicates.toArray(new Predicate[0]));
    cqCount.where(predicates.toArray(new Predicate[0]));


    List<MsCountry> = entityManager.createQuery(cq).setFirstResult(first).setMaxResults(pageSize).getResultList();
    int countResult = entityManager.createQuery(cqCount).getSingleResult().intValue();

//query 1 = the result is correct (for a sample get 4 row)

SELECT t1.country_id AS a1, t1.country_code AS a2, t1.country_description AS a3, t1.country_name AS a4, t1.currency_id AS a5 FROM ms_currency t0, ms_country t1 WHERE (t1.country_code LIKE ? AND (t0.currency_id = t1.currency_id)) LIMIT ?, ?

//query 2 = the result is wrong  (not 4 but 72)

SELECT COUNT(t0.country_id) FROM ms_country t0, ms_country t2, ms_currency t1 WHERE (t2.country_code LIKE ? AND (t1.currency_id = t2.currency_id))

I need the count data to setRowCount.

How to solve this or any other idea , is there posibble two queries be combined to one call to the database?

Thank you in advance for your help

Baby Groot
  • 4,637
  • 39
  • 52
  • 71
user3156313
  • 1
  • I agree on your solution. Two db queries are really necessary. See also this answer: http://stackoverflow.com/questions/13972193/how-to-query-data-for-primefaces-datatable-with-lazy-loading-and-pagination/13973903#13973903 – perissf Jan 03 '14 at 15:37
  • CriteriaBuilders are not meant to be shared amongst queries; each query should have its own CriteriaBuilder. – Chris Jan 06 '14 at 15:28

0 Answers0