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

Question

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?

Answer 1

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