3

I'm migrating an application, that uses DB2 database, from Spring(IBM Websphere) to Springboot(Embedded Tomcat).

The existing application, that uses hibernate 4.1.9.Final, runs perfectly using this FETCH query :

@Query(
"SELECT ssss FROM SSSSPackageDiscountLoad ssss, SSSBasicLoad ttt WHERE ssss.schemeId = ttt.id AND ttt.code = :schemeCode AND "
+ "ssss.ruleCode = :ruleCode ORDER BY ssss.effDate DESC FETCH FIRST 1 ROWS ONLY")

The migrated springboot(version 2.1.7.RELEASE) app uses hibernate 5.3.10.Final.

Starting the application gives me this issue :

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: FETCH near line 1, column 255

Praveesh P
  • 1,399
  • 2
  • 25
  • 42

2 Answers2

0

You could use the equivalent statement:

@Query(
"SELECT ssss FROM SSSSPackageDiscountLoad ssss, SSSBasicLoad ttt WHERE ssss.schemeId = ttt.id AND ttt.code = :schemeCode AND "
+ "ssss.ruleCode = :ruleCode ORDER BY ssss.effDate DESC LIMIT 1")

However, if you want to use the query as it is, then you would need to force the dialect as suggested here.

In your case, this should work:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.DB2Dialect
smartmek
  • 51
  • 5
0

From another post: unexpected token: LIMIT

Basically your query should be native:

`@Query(
"SELECT ssss FROM SSSSPackageDiscountLoad ssss, SSSBasicLoad ttt WHERE ssss.schemeId = ttt.id AND ttt.code = :schemeCode AND "
+ "ssss.ruleCode = :ruleCode ORDER BY ssss.effDate DESC FETCH FIRST 1 ROWS ONLY", nativeQuery = true)`

Notice the nativeQuery at the end. By default, nativeQuery is false.

Tung Anh Nguyen
  • 1