unexpected token: LIMIT error in hql

Question

        List<AC_Customer> lastAC_CustomersList = session.createQuery("from AC_Customer order by customer_pk DESC LIMIT 1").list()

when i execute this i got this error can any one please tell me what is wron with my hql query.

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: LIMIT near line 1, column 65 [from com.softlogic.models.AC_Customer order by customer_pk DESC LIMIT 1]

HQL is not SQL. It doesn't have any LIMIT clause. But you can set a max result on the Query itself. — JB Nizet, Jun 05 '16 at 17:03

score 2 · Answer 1 · edited Feb 08 '17 at 13:02

2

try using the below

session.createSQLQuery("select * from AC_Customer order by customer_pk DESC LIMIT 1").list();

edited Feb 08 '17 at 13:02

Gal Dreiman

3,969
2
21
40

answered Feb 08 '17 at 12:42

Keshore Durairaj

27
5

He uses HQL, not SQL. – v.ladynev Jan 20 '22 at 14:15

score 1 · Answer 2 · answered Jan 20 '22 at 13:18

1

HQL does not know LIMIT you have to use setMaxResults in a following way:

List<AC_Customer> lastAC_CustomersList = session
    .createQuery("from AC_Customer order by customer_pk DESC")
    .setMaxResults(1)
    .getResultList()

answered Jan 20 '22 at 13:18

michal.jakubeczy

8,221
1
59
63

score 0 · Answer 3 · answered Mar 30 '20 at 04:20

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: OFFSET near line 1, column 87 [FROM om.omantel.prodsheet.models.RtcsProduct where status=1 order by TRACKING_ID DESC OFFSET 1 ROWS FETCH NEXT 5 ROWS ONLY]

    @SuppressWarnings("unchecked")
    @Override
     public List<RtcsProduct> pagination(int page_id, int total) {
    String listsql = "FROM RtcsProduct where status=1 order by TRACKING_ID DESC OFFSET "+page_id+" ROWS FETCH NEXT "+total+" ROWS ONLY";
    Session session = null;
    List<RtcsProduct> productlist = null;
    try {
    if (session == null) {
        session = sessionFactory.openSession();
        session.beginTransaction();
        productlist = session.createQuery(listsql).list();
        session.getTransaction().commit();

    }
    }catch(Exception ex) {
        ex.printStackTrace();
    }

    return productlist;
}

unexpected token: LIMIT error in hql

3 Answers3