Hibernate 5 issue - org.hibernate.exception.SQLGrammarException

[rafaelszp]

When I use limit clause with JPAQuery it gives me the following error:

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)
    at org.hibernate.loader.Loader.doQuery(Loader.java:919)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
    at org.hibernate.loader.Loader.doList(Loader.java:2617)
    at org.hibernate.loader.Loader.doList(Loader.java:2600)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429)
    at org.hibernate.loader.Loader.list(Loader.java:2424)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)
    ... 53 more
Caused by: java.sql.SQLException: Incorrect syntax near '@P0'.

Here is a snippet of code I used:

        List<Exame> lista;
        QExame exame = QExame.exame;
        JPAQuery<Exame> query = new JPAQuery<>(getEntityManager());
        lista = query.from(exame)
                .where(
                        exame.nomeExame.like(nome+"%").or(exame.nomeExameASO.like(nome+"%")) )
                .limit(11L)
                .fetchResults()
                .getResults();

I'm using: JBOSS EAP 7/Wildfly 10 With Hibernate 5.0.7/5.0.9 and QueryDSL JPA 4.1.2
JDBC-Driver: JTDS 1.3 for MSSQL Server

[timowest]

Could you provide the full SQL?

[rafaelszp]

This is the query generated:

08:06:36,522 INFO  [org.hibernate.hql.internal.QueryTranslatorFactoryInitiator] (default task-3) HHH000397: Using ASTQueryTranslatorFactory
08:06:36,726 INFO  [stdout] (default task-3) Hibernate: 
08:06:36,726 INFO  [stdout] (default task-3)     select
08:06:36,726 INFO  [stdout] (default task-3)         count(exame0_.cd_exame) as col_0_0_ 
08:06:36,726 INFO  [stdout] (default task-3)     from
08:06:36,726 INFO  [stdout] (default task-3)         SST.dbo.EXAME exame0_ 
08:06:36,727 INFO  [stdout] (default task-3)     where
08:06:36,727 INFO  [stdout] (default task-3)         exame0_.tx_nome_exame like ? escape '!' 
08:06:36,727 INFO  [stdout] (default task-3)         or exame0_.tx_nome_exame_aso like ? escape '!'
08:06:36,856 INFO  [stdout] (default task-3) Hibernate: 
08:06:36,857 INFO  [stdout] (default task-3)     select
08:06:36,857 INFO  [stdout] (default task-3)         TOP ?  exame0_.cd_exame as cd_exame1_1_,
08:06:36,857 INFO  [stdout] (default task-3)         exame0_.tx_nome_exame as tx_nome_2_1_,
08:06:36,857 INFO  [stdout] (default task-3)         exame0_.tx_nome_exame_aso as tx_nome_3_1_ 
08:06:36,857 INFO  [stdout] (default task-3)     from
08:06:36,857 INFO  [stdout] (default task-3)         SST.dbo.EXAME exame0_ 
08:06:36,857 INFO  [stdout] (default task-3)     where
08:06:36,857 INFO  [stdout] (default task-3)         exame0_.tx_nome_exame like ? escape '!' 
08:06:36,857 INFO  [stdout] (default task-3)         or exame0_.tx_nome_exame_aso like ? escape '!'

I think it's something with the TOP parameter, because in hibernate 4.3 it won't appear the ? character within query log, instead will show the number I put in limit() method.

[timowest]

This looks like an issue with Hibernate and not Querydsl. Check org.hibernate.dialect.SQLServerDialect on the rendering of the paging parameters.

[rafaelszp]

You're right. I changed to org.hibernate.dialect.SQLServer2012Dialect and the error stopped appearing.
Thank you.

[timowest]

Great.