unexpected token: LIMIT

Question

I am getting an error mention in ERROR section when using the mentioned dependency with NamedQuery.

Note: please suggest, is it a jar problem or JBOSS? I am running this code in Jboss 7 version with jdk1.7.

Named query used using EntityManager:

Query query = this.entityManager.createNamedQuery("Program.findAll");// it will give only one Program limited to 1
        List<Program> programs = query.getResultList();
    @Entity
    @Table(name="PROGRAM")
    @NamedQueries({@NamedQuery(name="Program.findAll", query="SELECT p FROM Program p LIMIT 2")
    })

below code is from POM.xml

<dependency>
   <groupId>org.hibernate</groupId>
   <artifactId>hibernate-core</artifactId>
   <version>4.2.7.Final</version>
</dependency>
<dependency>
   <groupId>org.hibernate</groupId>
   <artifactId>hibernate-entitymanager</artifactId>
   <version>4.2.7.Final</version>
</dependency>
<dependency>
    <groupId>javax</groupId>
    <artifactId>javaee-api</artifactId>
    <version>6.0</version>
</dependency>
<dependency>
    <groupId>javax.persistence</groupId>
    <artifactId>persistence-api</artifactId>
    <version>1.0.2</version>
</dependency>

Error:

18:27:44,447 INFO  [org.hibernate.service.jdbc.connections.internal.ConnectionProviderInitiator] (MSC service thread 1-5) HHH000130: Instantiating explicit connection provider: org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider
18:27:44,748 INFO  [org.hibernate.dialect.Dialect] (MSC service thread 1-5) HHH000400: Using dialect: org.hibernate.dialect.MySQL5InnoDBDialect
18:27:44,764 INFO  [org.hibernate.engine.transaction.internal.TransactionFactoryInitiator] (MSC service thread 1-5) HHH000268: Transaction strategy: org.hibernate.engine.transaction.internal.jta.CMTTransactionFactory
18:27:44,764 INFO  [org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory] (MSC service thread 1-5) HHH000397: Using ASTQueryTranslatorFactory
18:27:44,826 INFO  [org.hibernate.validator.util.Version] (MSC service thread 1-5) Hibernate Validator 4.2.0.Final
18:28:02,721 ERROR [org.hibernate.hql.internal.ast.ErrorCounter] (MSC service thread 1-5) line 1:50: unexpected token: LIMIT
18:28:02,727 ERROR [org.hibernate.hql.internal.ast.ErrorCounter] (MSC service thread 1-5) line 1:50: unexpected token: LIMIT: line 1:50: unexpected token: LIMIT
    at org.hibernate.hql.internal.antlr.HqlBaseParser.fromClassOrOuterQueryPath(HqlBaseParser.java:1866) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.fromRange(HqlBaseParser.java:1492) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1336) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1054) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:700) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:294) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:157) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:266) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:101) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:119) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.internal.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:974) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:485) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1737) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.EntityManagerFactoryImpl.<init>(EntityManagerFactoryImpl.java:84) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:904) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:889) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.HibernatePersistence.createContainerEntityManagerFactory(HibernatePersistence.java:73) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl.createContainerEntityManagerFactory(PersistenceUnitServiceImpl.java:162) [jboss-as-jpa-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.as.jpa.service.PersistenceUnitServiceImpl.start(PersistenceUnitServiceImpl.java:85) [jboss-as-jpa-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.msc.service.ServiceControllerImpl$StartTask.startService(ServiceControllerImpl.java:1811)
    at org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1746)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_40]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_40]
    at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_40]

Answer 1

LIMIT is not supported clause in HQL. Instead you should use:

Query query = this.entityManager.createNamedQuery("Program.findAll");
query.setMaxResults(2);
List<Program> programs = query.getResultList();

And on your entity:

@Entity
@Table(name="PROGRAM")
@NamedQueries({@NamedQuery(name="Program.findAll", query="SELECT p FROM Program p ")})