Why Multiple Where Clause not Woking in nativeQuery

Question

 @Query(
      nativeQuery = true,
      value =
          "SELECT td.trip_date, td.trip_code, td.trip_distance, td.trip_travel_time, tu.status, tu.pickup_drop_time, u.id, u.user_name\n"
              + "FROM trip_details td  JOIN trip_users tu ON tu.trip_details_trip_id=td.trip_id \n"
              + "JOIN users u ON u.id=tu.trip_user_id \n"
              + "WHERE td.trip_date BETWEEN :fromDate AND :toDate \n"
              + "AND u.id =: userId")
  List<Object> getMobileForUpComingTripDetails(
      @Param("userId") Integer userId,
      @Param("fromDate") Date fromDate,
      @Param("toDate") Date toDate);

IT will gives error

Caused by: java.lang.IllegalStateException: Using named parameters for method public abstract java.util.List com.dao.interfaces.TripDetailsDao.
getMobileForUpComingTripDetails(java.lang.Integer,java.util.Date,java.util.Date) but parameter 'userId' not found in annotated query 'SELECT td.trip_date, td.trip_code, td.trip_distance, td.trip_travel_time, tu.status, tu.pickup_drop_time, u.id, u.user_name
FROM trip_details td  JOIN trip_users tu ON tu.trip_details_trip_id=td.trip_id 
JOIN users u ON u.id=tu.trip_user_id 
WHERE td.trip_date BETWEEN :fromDate AND :toDate 
AND tu.trip_user_id = : userId'!

But when we use single where clause its working

 @Query(
      nativeQuery = true,
      value =
          "SELECT td.trip_date, td.trip_code, td.trip_distance, td.trip_travel_time, tu.status, tu.pickup_drop_time, u.id, u.user_name\n"
              + "FROM trip_details td  JOIN trip_users tu ON tu.trip_details_trip_id=td.trip_id \n"
              + "JOIN users u ON u.id=tu.trip_user_id \n"
              + "WHERE td.trip_date BETWEEN :fromDate AND :toDate")
  List<Object> getMobileForUpComingTripDetails(
     // @Param("userId") Integer userId,
      @Param("fromDate") Date fromDate,
      @Param("toDate") Date toDate);

and my response is

[
   {
        "tripDate": "15/02/2022",
        "tripCode": "2sdfsdfklsfj0001",
        "userName": "jkl.com",
        "userId": 204
    },
    {
        "tripDate": "15/02/2022",
        "tripCode": "220xsdjffksf0001",
        "userName": "abc.com",
        "userId": 210
    },
]

But my problem is based on queryParm as userId we get data not all, so i added multiple where claues we get error please help me for that.

Answer 1

So AS you look at the error message it obviously said that the parameter userId was not found in the query. It's because of your typo in the query:

+ "AND u.id =: userId")

it should be like:

+ "AND u.id = :userId")

Reading the error message always helps you with debugging.