Add Order By to entities created by seam-gen?

[crhoffman]

I used seam-gen to reverse engineer some tables in MySQL.  I now want the generated entities to start with a default sort order.  What is the best way to do this?  My records come out in random order.  I tried adding an ORDER BY clause in an EJBQL statement (see below):
--------

@Name("tmpFindPropAttemptsList")
public class TmpFindPropAttemptsList extends EntityQuery<TmpFindPropAttempts> {

//      private static final String EJBQL = "select tmpFindPropAttempts from TmpFindPropAttempts tmpFindPropAttempts";

        private static final String EJBQL = "select tmpFindPropAttempts from TmpFindPropAttempts tmpFindPropAttempts order by accessionId, propNo";
        
        private static final String[] RESTRICTIONS = {
                        "lower(tmpFindPropAttempts.accessionId) like concat(lower(#{tmpFindPropAttemptsList.tmpFindPropAttempts.accessionId}),'%')",
                        "lower(tmpFindPropAttempts.fullsciname) like concat(lower(#{tmpFindPropAttemptsList.tmpFindPropAttempts.fullsciname}),'%')",
                        "lower(tmpFindPropAttempts.proptype) like concat(lower(#{tmpFindPropAttemptsList.tmpFindPropAttempts.proptype}),'%')",
                        "lower(tmpFindPropAttempts.result) like concat(lower(#{tmpFindPropAttemptsList.tmpFindPropAttempts.result}),'%')",};

        private TmpFindPropAttempts tmpFindPropAttempts = new TmpFindPropAttempts();

        public TmpFindPropAttemptsList() {
                setEjbql(EJBQL);
                setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
                setMaxResults(25);
        }

        public TmpFindPropAttempts getTmpFindPropAttempts() {
                return tmpFindPropAttempts;
        }
}

---------
Or is there some place in the entity declaration where this should happen:
---------

/**
 * TmpFindPropAttempts generated by hbm2java
 */
@Entity
@Table(name = "tmp_find_prop_attempts", catalog = "test")
public class TmpFindPropAttempts implements java.io.Serializable {

        private int propNo;
        private TmpFindPropSciname tmpFindPropSciname;
        private short rowid;
        private String accessionId;
        private String fullsciname;
        private String proptype;
        private Date propdate;
        private String result;
        private Set<TmpFindPropDetail> tmpFindPropDetails = new HashSet<TmpFindPropDetail>(
                        0);

        public TmpFindPropAttempts() {
        }

... and so on ...

Thanks in advance for any advice!

[lvdberg]

The code should give an error when you compile because you've defined the EJBQL static twice, or is it just an example. ?

Ordering can be done also by overriding the getOrder in the quety component method where you return the order instead of using the constructor which will  not work.

Another option is just defining an query component in components.xml. You're not doing anything complex, so I think that will be thebest solution for you.

Succes, Just drop another question if you need some more help,

Leo

[andygibson.contact.andygibson.net]

Order by is done using the setOrder(String fieldName) method. Adding it to the EJBQL will just break the statement since it will have the order by in the middle of the statement.

Cheers,

Andy Gibson

PS : Shameless plug : My article on codeless pagination and ordering in seam

[crhoffman]

Thanks to both Leo and Andy for replying!  While I was waiting for replies to my post, I found some more examples and changed my code to that below.  The app is still not performing the Order By which makes me wonder if this is getting ignored. I've seen the annotation @override in a few examples. Do I need that? 

I'm also trying to understand Leo's comment here to see if I should be working elsewhere.

Ordering can be done also by overriding the getOrder in the query component method where you return the order instead of using the constructor which will not work.

Thanks for help with this newbie question!

@Name("tmpFindPropAttemptsList")
public class TmpFindPropAttemptsList extends EntityQuery<TmpFindPropAttempts> {

     private static final String EJBQL = "select tmpFindPropAttempts from TmpFindPropAttempts tmpFindPropAttempts";

//     private static final String EJBQL = "select tmpFindPropAttempts from TmpFindPropAttempts tmpFindPropAttempts order by tmpFindPropAttempts.accessionId, tmpFindPropAttempts.propNo";
     private static final String ORDER = "tmpFindPropAttempts.accessionId, tmpFindPropAttempts.propNo";
     private static final String[] RESTRICTIONS = {
               "lower(tmpFindPropAttempts.accessionId) like concat(lower(#{tmpFindPropAttemptsList.tmpFindPropAttempts.accessionId}),'%')",
               "lower(tmpFindPropAttempts.fullsciname) like concat(lower(#{tmpFindPropAttemptsList.tmpFindPropAttempts.fullsciname}),'%')",
               "lower(tmpFindPropAttempts.proptype) like concat(lower(#{tmpFindPropAttemptsList.tmpFindPropAttempts.proptype}),'%')",
               "lower(tmpFindPropAttempts.result) like concat(lower(#{tmpFindPropAttemptsList.tmpFindPropAttempts.result}),'%')",};

     private TmpFindPropAttempts tmpFindPropAttempts = new TmpFindPropAttempts();

     public TmpFindPropAttemptsList() {
          setEjbql(EJBQL);
          setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
          setOrder(ORDER);
          setMaxResults(25);
     }

     public TmpFindPropAttempts getTmpFindPropAttempts() {
          return tmpFindPropAttempts;
     }
}

[andygibson.contact.andygibson.net]

What Leo is suggesting is overriding the getOrder method to return a constant. Try the above code :

public String getOrder() {
    return ORDER;
}

Cheers,

Andy Gibson

[crhoffman]

Hmm, that doesn't seem to be working either, if I've put that in the right place.  I'm sure this will turn out to be something basic, so I really do appreciate the advice!

@Name("tmpFindPropAttemptsList")
public class TmpFindPropAttemptsList extends EntityQuery<TmpFindPropAttempts> {

     private static final String EJBQL = "select tmpFindPropAttempts from TmpFindPropAttempts tmpFindPropAttempts";
     private static final String ORDER = "tmpFindPropAttempts.accessionId, tmpFindPropAttempts.propNo";
     private static final String[] RESTRICTIONS = {
               "lower(tmpFindPropAttempts.accessionId) like concat(lower(#{tmpFindPropAttemptsList.tmpFindPropAttempts.accessionId}),'%')",
               "lower(tmpFindPropAttempts.fullsciname) like concat(lower(#{tmpFindPropAttemptsList.tmpFindPropAttempts.fullsciname}),'%')",
               "lower(tmpFindPropAttempts.proptype) like concat(lower(#{tmpFindPropAttemptsList.tmpFindPropAttempts.proptype}),'%')",
               "lower(tmpFindPropAttempts.result) like concat(lower(#{tmpFindPropAttemptsList.tmpFindPropAttempts.result}),'%')",};

     private TmpFindPropAttempts tmpFindPropAttempts = new TmpFindPropAttempts();
     
     public TmpFindPropAttemptsList() {
          setEjbql(EJBQL);
          setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
          setOrder(ORDER);
          setMaxResults(25);
     }

     public String getOrder() {
         return ORDER;
     }
     
     public TmpFindPropAttempts getTmpFindPropAttempts() {
          return tmpFindPropAttempts;
     }
}

[crhoffman]

I must have something wrong upstream from this code.  On another entity using the same Order/setOrder approach, the Order By does work.  Luckily, the one that works is the one that most needs sorting!

@Name("tmpFindPropScinameList")
public class TmpFindPropScinameList extends EntityQuery<TmpFindPropSciname> {

     private static final String EJBQL = "select tmpFindPropSciname from TmpFindPropSciname tmpFindPropSciname";
     private static final String ORDER = "tmpFindPropSciname.level1, tmpFindPropSciname.level2";
     private static final String[] RESTRICTIONS = {
               "lower(tmpFindPropSciname.level1) like concat('%',lower(#{tmpFindPropScinameList.tmpFindPropSciname.level1}),'%')",
               "lower(tmpFindPropSciname.level2) like concat('%',lower(#{tmpFindPropScinameList.tmpFindPropSciname.level2}),'%')",
               "lower(tmpFindPropSciname.level3) like concat('%',lower(#{tmpFindPropScinameList.tmpFindPropSciname.level3}),'%')",
               "lower(tmpFindPropSciname.level4) like concat('%',lower(#{tmpFindPropScinameList.tmpFindPropSciname.level4}),'%')",};

     private TmpFindPropSciname tmpFindPropSciname = new TmpFindPropSciname();

     public TmpFindPropScinameList() {
          setEjbql(EJBQL);
          setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
          setOrder(ORDER);
          setMaxResults(25);
     }

     public TmpFindPropSciname getTmpFindPropSciname() {
          return tmpFindPropSciname;
     }
}

[andygibson.contact.andygibson.net]

In your JSF page, add something like :

Order = #{tmpFindPropAttemptsList.orderBy}

Just to see what is says the value is.

Cheers,

Andy Gibson