At least 2 parameter(s) provided but only 1 parameter(s) present in query by Use Specification and Sort

Question

When I use the JAP interface JpaSpecificationExecutor, I typed the following code and throws an error "At least 2 parameter(s) provided but only 1 parameter(s) present in query", I don't know why.

the invoke code

@Override
public Result findSightsWithConditions(String cityId, Map<String, String> filter_map) {
    //scoreOrder priceOrder minScore maxScore minPrice maxPrice
    Specification<Sight> specification = new Specification<Sight>() {

        @Override
        public Predicate toPredicate(Root<Sight> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
            List<Predicate> predicates = Lists.newArrayList();
            //predicates.add(criteriaBuilder.equal(root.get("cityId"), cityId));
            if (null != filter_map.get("maxScore")) {
                double max = Integer.parseInt(filter_map.get("maxScore"));
                predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("score"), max));
            }
            if (null != filter_map.get("maxPrice")) {
                double max = Integer.parseInt(filter_map.get("maxPrice"));
                predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("price"), max));
            }
            if (null != filter_map.get("minScore")) {
                double min = Double.parseDouble(filter_map.get("minScore"));
                predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("score"), min));
            }

            if (null != filter_map.get("minPrice")) {
                double min = Double.parseDouble(filter_map.get("minPrice"));
                predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("price"), min));
            }
            return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
        }
    };
    Sort sort = new Sort(Sort.Direction.DESC, "id");
    String scoreOrder = filter_map.get("scoreOrder");
    String priceOrder = filter_map.get("priceOrder");

    if (scoreOrder != null) {
        // 1 ASE 2DESC
        sort = sort.and(new Sort(Integer.parseInt(scoreOrder) == 1 ? Sort.Direction.ASC : Sort.Direction.DESC, "score"));
    }
    if (priceOrder != null) {
        sort = sort.and(new Sort(Integer.parseInt(scoreOrder) == 1 ? Sort.Direction.ASC : Sort.Direction.DESC, "price"));
    }

    List<Sight> sights = sightDao.findAllByCityId(cityId, specification, sort);
    List<SightSimpleVO> sightSimpleVOS = sights.stream().map(sight -> {
        SightSimpleVO vo = new SightSimpleVO();
        BeanUtils.copyProperties(sight, vo, "pics", "labels");
        vo.setPics(StringUtil.getList(sight.getPics(), ","));
        vo.setLabels(StringUtil.getList(sight.getLabels(), ","));
        return vo;
    }).collect(Collectors.toList());
    return Result.success().message("success!").withData(sightSimpleVOS);
}

Repository code:

@Repository
public interface SightRepository extends PagingAndSortingRepository<Sight, Long>, JpaSpecificationExecutor {

    List<Sight> findAllByCityId(String cityIds, Specification<Sight> specification, Sort var1);

}

I just want to obtain the results under dynamic condition. The Exception message is as following:

java.lang.IllegalArgumentException: At least 2 parameter(s) provided but only 1 parameter(s) present in query.
    at org.springframework.util.Assert.isTrue(Assert.java:136)
    at org.springframework.data.jpa.repository.query.QueryParameterSetterFactory$CriteriaQueryParameterSetterFactory.create(QueryParameterSetterFactory.java:290)
    at org.springframework.data.jpa.repository.query.ParameterBinderFactory.lambda$createQueryParameterSetter$1(ParameterBinderFactory.java:139)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.Spliterators$ArraySpliterator.tryAdvance(Spliterators.java:958)
    at java.util.stream.ReferencePipeline.forEachWithCancel(ReferencePipeline.java:126)
    at java.util.stream.AbstractPipeline.copyIntoWithCancel(AbstractPipeline.java:498)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:485)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.FindOps$FindOp.evaluateSequential(FindOps.java:152)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.findFirst(ReferencePipeline.java:464)
    at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createQueryParameterSetter(ParameterBinderFactory.java:141)
    at org.springframework.data.jpa.repository.query.ParameterBinderFactory.lambda$createSetters$0(ParameterBinderFactory.java:131)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1382)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499)
    at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createSetters(ParameterBinderFactory.java:132)
    at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createSetters(ParameterBinderFactory.java:124)
    at org.springframework.data.jpa.repository.query.ParameterBinderFactory.createCriteriaBinder(ParameterBinderFactory.java:75)
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery$QueryPreparer.getBinder(PartTreeJpaQuery.java:248)
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery$QueryPreparer.createQuery(PartTreeJpaQuery.java:164)
    at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.doCreateQuery(PartTreeJpaQuery.java:92)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:210)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:129)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:605)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy159.findAllByCityId(Unknown Source)

Solution

First of all, your code should read implements JpaSpecificationExecutor<Sight>.

Secondly, the whole point of implementing JPASpecificationExecutor is to make use of the methods it provides. You should be calling the findAll(Specification, Sort) method, while filtering by cityId should be part of the specification.

Comments

[Valijon]

The problem is your code uses Sort and JpaSpecification thinks its part of query. You need to define Pageable. Look this example: https://stackoverflow.com/questions/52687061/spring-data-jpa‌​-method-query-with-p‌​aging-gives-me-an-er‌​ror

[Ivan Vilanculo]

I totally agree with you but what about when you want to use projections?

[crizzis]

@IvanVilanculo Mixing projections and specifications is not supported, see stackoverflow.com/questions/41692391/…