Why Multiple Where Clause not Woking in nativeQuery

 @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_namen"
              + "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_namen"
              + "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

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.

Advertisement

Answer