1 Reply Latest reply on Dec 30, 2005 9:36 PM by Emmanuel Bernard

    EJB QL generates wrong SQL with pagination (MySQL 5.0.16 + J

    Daniel Woo Newbie

      I have a very simple query with pagination like this

      Query query = em.createQuery(ql);
      query.setFirstResult(currentPage * itemsPerPage);//start rows
      query.setMaxResults(itemsPerPage);//return rows
      query.setParameter("searchValue", searchValue);//search criteria
      return query.getResultList();
      

      it should genrate a SQL like
      select ... from table where ... limit 30, 10
      

      but it always generates wrong SQL and hence throws out an exception:
      Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that correspondsto your MySQL server version for the right syntax to use near 'limit 15 15 customer0_.ID as ID13_, customer0_.address as address13_, c
      ustomer0_' at line 1
      

      Why the "limit" keyword does not work?