subquery with limit does not work anymore in hibernate 3

Axel Wilhelm Berle · **Posted:** Mon Jun 20, 2005 3:04 pm

The following query worked fine with Hibernate 2.1. It has a subquery that uses limit. It does not work anymore in Hibernate 3. The error message is:

Quote:

org.hibernate.hql.ast.QuerySyntaxError: expecting CLOSE, found 'limit'

The code is:

Code:

StringBuffer sql = new StringBuffer("select count(*) from TrainingsLog as log where log.richtig = true and log.id in ( select log2.id from TrainingsLog as log2 where log2.user = :userId and log2.loId = :loId and log2.loTyp = :loTyp order by log2.datumAnlage desc limit :limit  ) ");
Session session = HibernateUtil.getSession();
Query query = session.createQuery(sql.toString())
   .setLong("userId", user.getId().longValue())
   .setLong("loId", loId.longValue())
   .setInteger("loTyp", typ)
   .setInteger("limit", anzahlLetztenAntworten);
Integer count = (Integer)query.uniqueResult();
return count.intValue();                

I would appreciate any pointers,
thanks,

Axel

gavin · **Posted:** Mon Jun 20, 2005 3:13 pm

Where in the Hibernate documentation (or even in ANSI SQL), does it mention any "limit" keyword?

sven · **Posted:** Mon Jun 20, 2005 4:49 pm

The keyword LIMIT is neither implemented in Hibernate, nor an ANSI SQL standard.
Though you can limit your query using "setMaxResults()" and "setFirstResult()".

Code:

Query query = Session.createQuery(sql.toString());
query.setFirstResult(1);
query.setMaxResults(50);