CODEWITHSUNDEEP
sqlspring-bootspring-data-jpa
Vijay Gangatharan
Vijay Gangatharan

Reputation: 298

@Query annotation to filter the data

I am facing issue in this below statement

`SELECT b
FROM com.development.searchbooks.dto.BooksEntity b
WHERE lower(b.title) LIKE ?1
    OR lower(b.author_name) LIKE ?1
    OR lower(b.publication) LIKE ?1
ORDER BY title ASC LIMIT 10 OFFSET ?2`

unexpected token: LIMIT near line 1, column 173

This is the query i am using to filter based on keyword and order by ascending and doing pagination. i am getting error right after adding "LIMIT 10 OFFSET ?2" this POC.

Kindly help on this.

Upvotes: 0

Views: 200

Answers (2)

Nakul Goyal
Nakul Goyal

Reputation: 649

LIMIT keyword is not considered by jpa/jpql. So for using LIMIT with @Query, we cam make native query flag true.

( using - nativeQuery=true )

Below is the example

@Query("SELECT s FROM Table s ORDER BY s.id DESC LIMIT 1", nativeQuery=true)

Upvotes: 1

Vijay Gangatharan
Vijay Gangatharan

Reputation: 298

Finalized Answer for my Question,

@Query(value = "SELECT * FROM books WHERE lower(title) LIKE %?1% OR lower(author_name) LIKE %?1% OR lower(publication) LIKE %?1% ORDER BY title ASC LIMIT 10 OFFSET ?2", nativeQuery = true)

Upvotes: 0

Related Questions