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