CODEWITHSUNDEEP
javasqlspringjdbctemplateoracle12c
Dharm
Dharm

Reputation: 73

Spring JDBC Template Not Binding all parameters (Fetch Parameter value)

I was learning new feature of Oracle 12 C (Top-N Queries and Pagination). I tried few examples with offset and fetch keywords and it worked well when i use sql developer. I tried implementing the same with Spring jdbcTemplate and I see a strange behavior. Ex: Here is the code snippet:

// get first ten entities
String query = "SELECT * FROM ENTITY E order by E.ID offset ? rows fetch next ? rows only";
SqlRowSet rowSet = this.jdbcTemplate.queryForRowSet(query, 1, 10);
while(rowSet.next()){
  //do something
}

Spring throws following exception:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM ENTITY E order by E.ID offset ? rows fetch next ? rows only]; nested exception is : ERROR: syntax error at or near "$2"

I tried few things same on postgres database but no luck, finally i thought of removing parameters and it worked when i remove third parameter and set a hard coded value in query string.ex:

"SELECT * FROM ENTITY E order by E.ID offset ? rows fetch next 10 rows only"

Is it not allowed to use bind parameter in fetch clause?

Upvotes: 2

Views: 1728

Answers (1)

Nacho Soriano
Nacho Soriano

Reputation: 599

If you add () on the fetch parameter, it works fine:

String query = "SELECT * FROM ENTITY E order by E.ID offset ? rows fetch next (?) rows only";

Upvotes: 2

Related Questions