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)

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-paging-gives-me-an-error](https://stackoverflow.com/questions/52687061/spring-data-jpa-method-query-with-paging-gives-me-an-error) — Valijon, Mar 24 '19 at 23:19

score 3 · Answer 1 · answered Mar 24 '19 at 22:48

3

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.

answered Mar 24 '19 at 22:48

crizzis

9,978
2
28
47

I totally agree with you but what about when you want to use projections? – Ivan Vilanculo Nov 25 '19 at 15:31
2

@IvanVilanculo Mixing projections and specifications is not supported, see https://stackoverflow.com/questions/41692391/how-to-use-projections-and-specifications-with-spring-data-jpa – crizzis Nov 25 '19 at 19:30

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

1 Answers1