0 Replies Latest reply on Jul 14, 2004 9:08 AM by chris chris

    findAll and Collection result optimization

    chris chris Newbie

      I'm using JBoss 3.2.4 (EJB CMP 2.0) and MySQL 4.0.20d. I have to display a list of records from a database using a JSP, in a user friendly way. SQL requests are performed using a CMP entity.

      An easy solution would be a findAll (CMP) using an EJB QL query like :
      <ejb-ql>SELECT OBJECT(a) FROM App a</ejb-ql>.
      The result is a Collection with all the selected records from the database.
      This way might lead to an OutOfMemoyException because of the number of records in the database.

      I'd like to get a Collection from a "SELECT *" like query without loading all the stored elements from the database (a kind of cursor I would be able to move according to the number of records to fetch. A fetch by page in a way, using EJB CMP).

      It is possible to fetch data from MySQL using the LIMIT clause.

      'SELECT ... FROM ... LIMIT start, size', where start is the position of the first database record (eg 0) and size is the size of the page (eg 20).

      To bad for me I can't use it from a CMP entity because CMP 2.0 can't manage it (Error compiling EJB-QL statement) :(:(

      Any idea to solve this problem without reimplementing my CMP into BMP(which would be quite a work).