@Query annotation to filter the data

Question

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.

Are you passing correct values to `?2` ? How you are calling above SQL statement. can you check which SQL its firing in database from log? — Koushik Roy, Apr 21 '21 at 05:20
check if this helps https://stackoverflow.com/a/48651391/11745755 — MetaData, Apr 21 '21 at 05:30

score 1 · Accepted Answer · edited Apr 21 '21 at 12:37

1

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)

edited Apr 21 '21 at 12:37

Wai Ha Lee

8,598
83
57
92

answered Apr 21 '21 at 06:08

Nakul Goyal

593
4
12

score 0 · Answer 2 · answered Apr 21 '21 at 06:24

0

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)

answered Apr 21 '21 at 06:24

Vijay

228
3
8

@Query annotation to filter the data

2 Answers2