cant using "LIKE" in native query spring boot

Question

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?.

Answer 1

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%