Getting total records count in server side pagination without effecting the perfomance

Question

Had written a service for searching, where query is formed based on search input data. Here Im using pagination, only fetch the mentioned start and max recored each time.

Since client need to get total record count, used the below code

inPaginationDto.setTotalRecords(q.getResultList().size());

which in turn fetching all the records each time, big performace impact.

Is there any way to get the count in better way using the same query approach in JPQL.

public class IncidentReportingRepoCustomImpl implements IncidentReportingRepoCustom{


    private static final MohLogger logger = MohLogFactory.getLoggerInstance(IncidentReportingRepoCustomImpl.class.getName());

    @PersistenceContext
    private EntityManager em;

    @Override
    public PaginationDto fetchIncidents(IncidentHdrSearchDto incidentHdrSearchDto) throws BusinessException {

        PaginationDto inPaginationDto = new PaginationDto();
        List<IncidentHdr> incidentHdrs = null;
        StringBuilder query = null;
        try{
            query = new StringBuilder();
            query.append(ReposJPQL.GET_INCIDENT_DETAILS);

            Map<String, Object> parameters = new HashMap<String, Object>();
            List<String> criteria = new ArrayList<String>();

            createIncidentSearchQuery(parameters,criteria, incidentHdrSearchDto);

            //append the query criteria          
            if (criteria.size() > 0) {
                for (int i = 0; i < criteria.size(); i++) {
                    query.append(" AND ");
                    query.append(criteria.get(i));
                }
            }   

            Query q = em.createQuery(query.toString());
            //logger.info("@@@@@@@@@@@@@@ QUERY @@@@@@@@@@@@@@ "+ query.toString());

            //set parameters
            for (Entry<String, Object> entry : parameters.entrySet()) {
                q.setParameter(entry.getKey(), entry.getValue());
            }

                inPaginationDto.setTotalRecords(q.getResultList().size());

            //pagination
            if(inPaginationDto.getTotalRecords() > incidentHdrSearchDto.getMaxResult() && incidentHdrSearchDto.getFirstResult() != null && incidentHdrSearchDto.getMaxResult() != null){
                q.setFirstResult(incidentHdrSearchDto.getFirstResult());        
                q.setMaxResults(incidentHdrSearchDto.getMaxResult());
            }

            incidentHdrs = q.getResultList();
        }catch(IllegalArgumentException | IllegalStateException | DataAccessException | EntityNotFoundException e){
            logger.error(e.getMessage());
            throw new BusinessException(e);
        }
        inPaginationDto.setPaginatedList(incidentHdrs);
        return inPaginationDto;
    }

/**
 * To generate query for incident search
 * @param parameters
 * @param criteria
 * @param incidentHdrDto
 */


private void createIncidentSearchQuery(Map<String, Object> parameters, List<String> criteria, IncidentHdrSearchDto incidentHdrSearchDto) {
        if(incidentHdrSearchDto.getIncidentSrNo() != null) {
            criteria.add("upper(inc.incidentSrNo) like :incidentSrNo");
            parameters.put("incidentSrNo" ,"%"+ incidentHdrSearchDto.getIncidentSrNo().toUpperCase()+"%");
        }

        if(incidentHdrSearchDto.getCategoryMast() != null) {
            criteria.add("inc.categoryMast.catCode = :catCode");
            parameters.put("catCode", incidentHdrSearchDto.getCategoryMast().getCatCode());
        }   


        if(incidentHdrSearchDto.getLocationMast() != null) {
            criteria.add("inc.locationMast.locCode = :location");
            parameters.put("location", incidentHdrSearchDto.getLocationMast().getLocCode());
        }

        if(incidentHdrSearchDto.getPatientId() != null) {
            criteria.add("inc.patientId = :patientId");
            parameters.put("patientId", incidentHdrSearchDto.getPatientId());
        }

        if(incidentHdrSearchDto.getAmsWorkOrderNo() != null) {
            criteria.add("inc.amsWorkOrderNo = :amsWorkOrderNo");
            parameters.put("amsWorkOrderNo", incidentHdrSearchDto.getAmsWorkOrderNo());
        }

        if(incidentHdrSearchDto.getTypeMast() != null){
            criteria.add("cm.typeCode = :typeCode");
            parameters.put("typeCode", incidentHdrSearchDto.getTypeMast().getTypeCode());
        }

        if(incidentHdrSearchDto.getSentinelYn() != null){
            if(incidentHdrSearchDto.getSentinelYn()){
                criteria.add("inc.sentinelYn = :sentinelYn");
                parameters.put("sentinelYn", "Y");
            }else{
                criteria.add("inc.sentinelYn = :sentinelYn");
                parameters.put("sentinelYn", "N");
            }
        }


        //get incidents which are reported with in 'x' hours.
        if(incidentHdrSearchDto.getReportedWithIn() != null){
            criteria.add(" (24 *(inc.createdTime - inc.incidentTime )) <= :withInHours");
            parameters.put("withInHours", incidentHdrSearchDto.getReportedWithIn());
        }



        //severity based search, data need to fetch from the view.
        if(incidentHdrSearchDto.getSeverityCode() != null){
            criteria.add("inc.categoryMast.catCode IN ("+ReposJPQL.GET_CAT_SEVERITY+")"
                    + " OR inc.subCategoryMast.subCatCode IN ("+ReposJPQL.GET_SUB_CAT_SEVERITY+")");
            parameters.put("severityCode", incidentHdrSearchDto.getSeverityCode());
        }
    }
}

This is the initial select query

public String GET_INCIDENT_DETAILS = "SELECT inc FROM IncidentHdr inc LEFT JOIN inc.categoryMast cm LEFT JOIN inc.subCategoryMast sub LEFT OUTER JOIN inc.incidentDtls  WHERE 1 = 1" ;

Answer 1

Change your select to count like:

Criteria API

long count(final Class<T> type){
    CriteriaBuilder qb=getEntityManager().getCriteriaBuilder();
    CriteriaQuery<Long> cq=qb.createQuery(Long.class);
    cq.select(qb.count(cq.from(type)));
    return getEntityManager().createQuery(cq).getSingleResult();
}

JPQL

public String GET_INCIDENT_DETAILS = "SELECT count(inc) FROM IncidentHdr inc LEFT JOIN inc.categoryMast cm LEFT JOIN inc.subCategoryMast sub LEFT OUTER JOIN inc.incidentDtls  WHERE 1 = 1" ;

By SELECT count(inc) FROM the result is numbers of records. You can reuse your query, but remove getFirstResult and getMaxResult() and change q.getResultList() to q.getSingleResult()