How to pass limit and off set values dynamically in native query

Question

I'm using native query to fetch the pagination results from PostgreSQL and I used this query and I'm getting the below exception:

SELECT a.*
  FROM table1 a LEFT OUTER JOIN table2 b ON a.clmn1 = b.clmn1
  WHERE (a.clmn3 = ?3 OR a.clmn4 ISNULL)
  ORDER BY a.clmn1 DESC offset = ?1 limit = ?2

Query:

@Query(nativeQuery = true, value="select a.* from table1 a left outer join table2 b ON a.clmn1 = b.clmn1 where (a.clmn3= ?3 OR a.clmn4 isnull) order by a.clmn1 desc offset = ?1 limit = ?2")
public List<Result> getResults(int offset, int limit, int value);

Exception:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "="

Please suggest.

Restructured query above to point out the lack of space between "is" and "null"... `OR a.clmn4 ISNULL` — Mr. Polywhirl, Feb 21 '19 at 14:16
Maybe duplicate ? https://stackoverflow.com/questions/25008472/pagination-in-spring-data-jpa-limit-and-offset — Sorin Penteleiciuc, Feb 22 '19 at 09:47

score 0 · Answer 1 · answered Feb 21 '19 at 14:15

There is a syntax error in your query. Remove the = and prefer named parameters like:

@Query(nativeQuery = true, value = "SELECT a.* FROM table1 a "
    + " LEFT OUTER JOIN table2 b"
    + " ON a.clmn1 = b.clmn1 "
    + " WHERE (a.clmn3= ?3 OR a.clmn4 IS NULL) "
    + " ORDER BY a.clmn1 DESC OFFSET :offset LIMIT :limit ")
public List<Result> getResults(@Param("offset")int offset, 
                               @Param("limit")int limit, int value);

Disclaimer: did not test how the param injection works but the syntax should be like this

score 0 · Answer 2 · answered Feb 21 '19 at 14:17

0

I Believe the error is

offset = ?1 limit = ?2

https://www.postgresql.org/docs/8.0/queries-limit.html

No '=' is required on offset and limit

answered Feb 21 '19 at 14:17

ziJon

1
1
1

How to pass limit and off set values dynamically in native query

2 Answers2