9 Replies Latest reply on Feb 8, 2006 8:12 AM by Thomas Heute

    OneToMany sorting

    Danijel Jevtic Novice

      Hi everyone. Is it possible to tell JBoss in which order the n-Items are to be retrieved? I'm using lazy fetching of the children but would like to be able to define by which fields they are sorted:

      @OneToMany(targetEntity=com.test.AbstractChild.class, cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy="parent")
      public List getChildren() {
      return products;
      }

      I cannot find anything in the EJB3 spec, but hope that Hibernate/Jboss have their own solution. This would be very useful when working with large sets of data that I want to have sorted in a specific order before retrieving them...

      Any help would be greatly appreciated.

      Kind Regards!

        • 1. Re: OneToMany sorting
          Alexander Hartner Expert

          EJB3 provides a nice and elegant solution via the @OroderBy Annotation.

          @Entity public class Course {
          ...
          @ManyToMany
          @OrderBy("lastname ASC")
          public List<Student> getStudents() {...};
          ...
          }
          @Entity public class Student {
          ...
          @ManyToMany(mappedBy="students")
          @OrderBy // PK is assumed
          public List<Course> getCourses() {...};
          ...
          }
          


          Have a look at the EJB3 Persistence Specification (ejb-3_0-pfd-spec-persistence.pdf) on page 184 / Section 9.1.26 OrderBy Annotation.

          Hope that helps
          Alex
          ejb3workshops.com
          http://ejb3workshop.com


          • 2. Re: OneToMany sorting
            Danijel Jevtic Novice

            Thank you Alex. However, I was aware of that particual annotation already. I probably didn't state my issue clearly. So I'll try again. When using the @OrderBy annotation I have no means of specifying (at runtime), by which column to sort the children, or do I?!

            Second, lazy loading fills the children collection with all members, the very first time I use the getChildren() function. So, if I had e.g. 20000 children, all children would be read in at once, even if I only wanted to display the first 10 results.

            So. I'm looking for something like:
            @OneToMany(targetEntity=com.test.AbstractChild.class, cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy="parent")
            @OrderBy(sortBy + " " + direction)
            public Iterator getChildren(String sortBy, String direction);

            or anything similar that gets the job done. Is any of my wishes fullfillable with EJB3 or hibernate's extensions of the spec?

            • 3. Re: OneToMany sorting
              Alexander Hartner Expert

              It look to me like a perfect example for a custom query. The EJB query language has support for sorting as well as paging, loading a limited subset like top 10 etc. via

              The Query Interface

              /**
              * Set the maximum number of results to retrieve.
              * @param maxResult
              * @return the same query instance
              * @throws IllegalArgumentException if argument is negative
              */
              public Query setMaxResults(int maxResult);
              /**
              * Set the position of the first result to retrieve.
              * @param start position of the first result, numbered from 0
              * @return the same query instance
              * @throws IllegalArgumentException if argument is negative
              */
              public Query setFirstResult(int startPosition);
              


              Extracted from Enterprise JavaBeans 3.0, Proposed Final Draft - 3.5.1 Query Interface

              Hope this is what you looking for. If you like an example please let me know I will have to dig one up that is to the point then.

              Alex
              ejb3workshop.com

              • 4. Re: OneToMany sorting
                Danijel Jevtic Novice

                Alex! Thank's a lot. It goes into the right direction!
                Do you think it would be possible to create such a custom Query and let it implement the Annotation interface and include the implementation of OneToMany, so that it could be used just like the OneToMany annotation (Cascades, etc.)?!
                If you have a working example (no matter what), it would be great if you can provide it.

                Thank's a lot.

                Regards

                d.j.

                • 5. Re: OneToMany sorting
                  Alexander Hartner Expert

                  Example :

                  Step 1: Create the Query

                  Query query = manager.createQuery("select o ... ORDER BY o.id desc")
                  


                  Step 2: Set Maximum Record Set
                  query.setMaxResults
                  


                  Step 3: Set the index of the first record
                  query.setFirstResult(10)
                  


                  Step 4: Run the Query
                  List<Stuff> stuff = query.getResultList()
                  


                  I hope this helps

                  Alex
                  ejb3workshop.com[/url]

                  • 6. Re: OneToMany sorting
                    Emmanuel Bernard Master

                    Guys, that's the second time I told you so, go to the USER forum.
                    http://www.jboss.com/index.html?module=bb&op=viewforum&f=221

                    • 7. Re: OneToMany sorting
                      Pieter-Jan Malfait Newbie

                      i understand perfectly well that these question belong on the user forum. but i have arrived here a couple of times already posting without knowing that this is the design forum ..

                      i follow the trail : http://www.jboss.org -> Hot JBoss Products - JBoss EJB3 -> Forums (in the menu on the left)

                      shouldn't that lead me to the user forum instead of the design forum ?

                      just my 2ct ..

                      • 8. Re: OneToMany sorting
                        Scott Stark Master

                        Yes, I have asked that this be fixed.

                        • 9. Re: OneToMany sorting
                          Thomas Heute Master

                          Since i have the rights on this, i made the change.