Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams 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?

Could you please post SearchResult and Entity class? This way your question will be more like SSCCE and chance of getting an answer will be increased. – bhdrkn Aug 27, 2015 at 16:50 An stacktrace will also be very useful. BTW try replacing col_0_0_ for a.col_0_0_ in all the Query – Alfonso Presa Aug 27, 2015 at 18:15 Did you try to replace positional params with named params? I also had a problem with multiple positional params with same index in query but when I changed them to named there was no error. – NikolaB Aug 28, 2015 at 10:36 @NikolaB 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? – user3861812 Sep 5, 2015 at 19:27

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
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
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
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);
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.