I populating a Primefaces datatable lazily with a custom complex dynamic query built using CriteriaBuilder, performing a SQL query on a database.
For that I need to perform a record count using this query and I also need to run the query itself to get the record in the specified interval for the datatable.
So I thought I could do something like this:
CriteriaBuilder criteriaBuilder = this.getEntityManager().getCriteriaBuilder();
CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
Root<RSip> from = criteriaQuery.from(RSip.class);
Path<Long> eSipss = from.join("idESip").get("ss");
CriteriaQuery<Object> select = criteriaQuery.select(from);
List<Predicate> predicates = new ArrayList();
//Many predicates added according to user end
predicates.add(...);
predicates.add(...);
predicates.add(...);
//The query is now ready
select.where(predicates.toArray(new Predicate[predicates.size()]));
//but I need also to perform the record count using SQL Count as the dataset returned can be very large
CriteriaQuery<Object> selectCount = criteriaQuery.select(criteriaBuilder.count(fromCount));
//and then perform both selects like this:
//Record Count:
TypedQuery<Object> typedQueryCount = this.getEntityManager().createQuery(selectCount);
List<Object> recordCount = typedQueryCount.getResultList();
//Query:
TypedQuery<Object> typedQuery = this.getEntityManager().createQuery(select);
List<Object> records = typedQuery.getResultList();
The problem is that on this second query, it returns me the record count also and not the actual records...what can I be doing wrong?
If there is some other way to do this I'm happy to read your answer!