JPA native query does not support setting list parameters

    you might want to do the following highlighted code as:

    whereClause.append("f.id IN (:fleetIdsList)");

     .....    

     query.setParameter("fleetIdsList", fleetIdsList);    

 

     But it only works for JPQL not for Native queries, since JPA native query does not support setting list parameters

 

-----------------------

        StringBuilder query = new StringBuilder();

        query.append("select count(distinct m.id) ");

 

        query.append("from ");

        query.append("central.machine m ");

        query.append("join central.organisation_has_machines ohm on ohm.machine_id = m.id and ohm.end_date is null ");

        query.append("join central.fleet_has_machines fhm on fhm.machine_id=m.id ");

        query.append("join central.organisation o on ohm.organisation_id = o.id ");

        query.append("join central.fleet f on fhm.fleet_id = f.id ");

        if(userId != null) {

            query.append("join central.user_has_fleets uhf on uhf.fleet_id=f.id ");

        }

        query.append("join central.ebox eb on eb.current_machine_id = m.id ");

        query.append("join (select distinct omhs.organisation_machine_id from central.service_feature sf, central.service_plan_has_feature sphf, central.service_plan sp, central.organisation_machine_has_services omhs where sf.name in ('AVL', 'CURRENT_LOCATION') and sphf.service_feature_id = sf.id and sp.id = sphf.service_plan_id and omhs.service_id = sp.id) allowed_service on allowed_service.organisation_machine_id = ohm.id ");

        query.append("left join central.sensor_board sb on sb.ebox_id = eb.id ");

        query.append("left join central.ebox_status ebs on eb.ebox_status_id=ebs.id ");

        query.append("left join central.machine_latest_event_cache le on le.machine_id=m.id ");

 

        query.append("where ");

        

        if(!criteria.getFleetIds().isEmpty()) {

            if(log.isDebugEnabled()) {

                log.debug("Count query. New impl takes precedence: criteria.getFleetIdList()=" + criteria.getFleetIds());

            }

            query.append("f.id in (");

            List<Long> fleetIdList = criteria.getFleetIds();

            int index = 0;

            for(Long fleetId : fleetIdList) {

                query.append(":fleetId").append(fleetId);

                if(++index < fleetIdList.size()) {

                    query.append(",");

                }

            }

            query.append(")");

            query.append(" and ");

        }

        else if(criteria.getFleetId()!=null){

            if(log.isDebugEnabled()) {

                log.debug("Count query. Existing impl: criteria.getFleetId()=" + criteria.getFleetId());

            }

            query.append("f.id = ");

            query.append(":fleetId");

            query.append(" and ");

        }

        

        if(userId != null) {

            query.append("uhf.user_id = ");

            query.append(":userId");

            query.append(" and ");

        }

        if(!StringUtils.isEmpty(criteria.getSearchString())) {

            query.append("(lower(ohm.display_name) like :display_name");

            

            query.append(" or ");

            query.append("lower(ohm.registration_plate) like :registration_plate ");

            query.append(") and ");

        }

        if(criteria.getMachineType()!=null) {

            query.append("m.machine_type = :machine_type ");

        }

        List<String> excludeMacTypes = this.getExcludeMacTypeList(criteria);

        if(excludeMacTypes != null && !excludeMacTypes.isEmpty()) {

            query.append("m.machine_type not in (");

            query.append(":machine_types_to_exclude");

            query.append(") and ");

        }

        if(StringUtils.isNotBlank(criteria.getOrganisationCommonId())){

            query.append("o.common_identifier = :orgCommonId "); 

        } else {

            query.append("true ");

        }

 

        if(log.isDebugEnabled()){

            log.debug("countMachineViewForCriteria Native SQL: "+query.toString());

        }

        

        Query q = em.createNativeQuery(query.toString());