CODEWITHSUNDEEP
spring-data-jpajpql
user3861812
user3861812

Reputation: 131

java.lang.IllegalArgumentException: Parameter with that position [1] did not exist

This is my repository:

@Repository
public interface MyRepository extends JpaRepository<Entity, Long> {

  public static final String DISTANCE = "((acos(sin(?1 * pi() / 180) * sin(a.latitude * pi() / 180) + cos(?1 * pi() / 180) * cos(a.latitude * pi() / 180) * cos((?2 - a.longitude) * pi() / 180)) * 180 / pi()) * 60 * 1.609344) as distance";

  @Query("select new package.SearchResult(" + DISTANCE + ", a.addressOwner) from Address a group by a.addressOwner, col_0_0_ having col_0_0_ < ?3 order by col_0_0_")
  public Page<SearchResult> findClosestByCoordinates(double lat, double lng, double maxDistance, Pageable pageable);

}

When I try to execute this method an exception occurs:

Parameter with that position [1] did not exist; nested exception is java.lang.IllegalArgumentException: Parameter with that position [1] did not exist']

But when I replace Page<SearchResult> with List<SearchResult> everything works just fine. Is it Spring's bug or something?

UPDATE: I guess I found what is wrong: when ALL the parameters take part in where clause, everything is ok. But if at least one of them is not used there, it fails. But I don't understand why it happens with Page and doesn't happen when using List. And what is the best way to deal with it?

Upvotes: 10

Views: 41437

Answers (4)

Nakesh
Nakesh

Reputation: 576

I faced same issue. After some try got to know that Pagination works in to phases.

  1. First query for Count based on your parameter : includes every parameter in Count query's WHERE clause, if you miss any parameter you will get below exception with missed parameter index number

Parameter with that position [1] did not exist; nested exception is

  1. Second query to retrieve data : Here there is a way you can use parameter of your choice.

Solution : use value and countQuery inside @Query parameter to use different parameter values.

For Example

@Query(value = "SELECT * FROM Shop s WHERE pincode = :pin",
        countQuery = "SELECT COUNT(1) FROM Shop s WHERE pincode = :pin AND shoptitle = :title")
Page<Shop> findShopForPincode(@Param("pin") String pin, @Param("title") String title);

Upvotes: 0

Ashwani Tiwari
Ashwani Tiwari

Reputation: 1567

You can make the query by the use of @Param("query_param_name") annotation that make query more clear and understandable.

 @Repository
 public interface MyRepository extends JpaRepository<Entity, Long> {

 public static final String DISTANCE = "((acos(sin(:lat * pi() / 180) *   sin(a.latitude * pi() / 180) + cos(:lat * pi() / 180) * cos(a.latitude * pi() /     180) * cos((:lng - a.longitude) * pi() / 180)) * 180 / pi()) * 60 * 1.609344) as   distance";

  @Query("select new package.SearchResult(" + DISTANCE + ", a.addressOwner)   from Address a group by a.addressOwner, col_0_0_ having col_0_0_ < :maxDistance order by col_0_0_")
 public Page<SearchResult> findClosestByCoordinates(@Param("lat")double lat, Param("lng")double lng, @Param("maxDistance") double maxDistance, Pageable pageable);

 }

This error come when the spring not find the where to enter the method param in query. So, use of @Param() annotation binds the query param and method param together and increase the simplycity of the query

Upvotes: 11

NerdHerd
NerdHerd

Reputation: 31

I'd got same problem with you, and finally found solution for these case.

I'm trying to solve similar problem like you as calculate lat, long things.

At first, problem is "Count Query" caused by using Pageable parameter.

I debugged things and found some codes like "when using Pageable, generate count query with WHERE clauses WITHOUT EVERY CONDITIONS".

So try like this.

@Query(value = "FROM MemberEntity mem " +
        "WHERE mem.seq != :selfSeq AND " +
        "get_distance_in_kilos_between_geo_locations(" +
        ":lat, " +
        ":lon, " +
        "mem.recentLocationLog.latitude, " +
        "mem.recentLocationLog.longitude" +
        ") > :pageId " +
        "ORDER BY get_distance_in_kilos_between_geo_locations(" +
        ":lat, " +
        ":lon, " +
        "mem.recentLocationLog.latitude, " +
        "mem.recentLocationLog.longitude" +
        ") ASC "// +
        ,
        countQuery = "SELECT COUNT(1) FROM MemberEntity mem " +
                "WHERE mem.seq != :selfSeq AND " +
                "get_distance_in_kilos_between_geo_locations(" +
                ":lat, " +
                ":lon, " +
                "mem.recentLocationLog.latitude, " +
                "mem.recentLocationLog.longitude" +
                ") > :pageId "
)
Page<MemberEntity> findByDistanceAndExcludeSelf(
        @Param("lat") double latitude,
        @Param("lon") double longitude,
        @Param("selfSeq") long selfSeq,
        @Param("pageId") double pageId,
        Pageable pageable
);

Upvotes: 3

Yang
Yang

Reputation: 11

Cause Page need to call extra count Query, but List not. This error must be caused by count Query. See here, most like same problem

Upvotes: 1

Related Questions