0

I try to get orders of user by query :

 @Query(value = "SELECT * FROM ORDERS WHERE USER_ID = ?1 AND CAST(CREATE_AT AS NVARCHAR(100))  LIKE ?2 OR CAST(GRAND_TOTAL AS NVARCHAR(100))  LIKE ?2 OR CAST(STATUS AS NVARCHAR(100))  LIKE ?2"  , nativeQuery = true)
Page<Order> getOrdersByUserSearch(int userID, String searchS, Pageable pageable);

But it always return empty list. i run this code in SQL server and it work (?1 =2. ?2 = '2021-06-26'). If I try to change "NOT LIKE" instead of "LIKE" It run. I dont want using query ( not native), Named query or specification method because it get more error. Any advice?.

THANH TÍNH SHR
  • 3
  • 2

1 Answers1

0

SQL like query requires % along with the value for a match. In case of ordered parameters in queries we can use:

    @Query("SELECT m FROM Movie m WHERE m.rating LIKE ?1%")
    List<Movie> searchByRatingStartsWith(String rating);

Click here for more info.

In your case the query string should be like this:

SELECT * FROM ORDERS WHERE USER_ID = ?1 AND CAST(CREATE_AT AS NVARCHAR(100))  LIKE %?2% OR CAST(GRAND_TOTAL AS NVARCHAR(100))  LIKE %?2% OR CAST(STATUS AS NVARCHAR(100))  LIKE %?2%
sas
  • 512
  • 2
  • 8
  • thanks! it worked, my sql is wrong, it should be: @Query(value = "SELECT * FROM ORDERS WHERE USER_ID = ?1 AND( CAST(ORDER_ID AS NVARCHAR(100)) LIKE %?2% OR CAST(CREATE_AT AS NVARCHAR(100)) LIKE %?2% OR CAST(GRAND_TOTAL AS NVARCHAR(100)) LIKE %?2% OR CAST(STATUS AS NVARCHAR(100)) LIKE %?2%)", nativeQuery = true) – THANH TÍNH SHR Jun 30 '21 at 06:50
  • @THANHTÍNHSHR Glad to know that my answer helped you. Please consider marking it as correct so it can help other people checking this question also! :) – sas Jun 30 '21 at 06:57