8 Replies Latest reply on Oct 20, 2009 11:34 PM by Val Sw

    Issue using order by in getEjbql()-xxxxlist.java

    Val Sw Expert
      Hi,

      I am trying to use "order by item.itemName" in "getEjbql()" - default generated by seam.

      When xhtml page loads list of items are displayed in correct order BUT when I click "ItemName" header to sort again it gives the following exception...

      Order by is added twice...
      "select item from com.Item item order by item.itemName order by itemName asc"

      Please suggest how to handle this....

      ------------------------------------------------------
      javax.faces.FacesException: javax.el.ELException: /ItemList.xhtml @41,57 rendered="#{empty itemList.resultList}": Error reading 'resultList' on type com.ItemList_$$_javassist_1
      ............................
      Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: order near line 1, column 86 [select item from com.Item item order by item.itemName order by itemName asc]
      ............................
      Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: order near line 1, column 86 [select item from com.Item item order by item.itemName order by itemName asc]
           at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
      ------------------------------------------------------
      @Name("itemList")
      public class ItemList extends EntityQuery {
           private static final String[] RESTRICTIONS = {"lower(item.itemName) like concat(lower(#{itemList.item.itemName}),'%')",};

           @Override
           public String getEjbql() {
                //return "select item from Item item";
                return "select item from Item item order by item.itemName";
           }
      }

      XHTML:
      ------
      <rich:panel>
          <f:facet name="header">Item search results</f:facet>
          <div class="results" id="itemList">
          <h:outputText value="The item search returned no results." rendered="#{empty itemList.resultList}"/>              
          <rich:dataTable id="itemList" var="item" value="#{itemList.resultList}" rendered="#{not empty itemList.resultList}">     
           <h:column>
                <f:facet name="header">
                     <s:link styleClass="columnHeader"
                                     value="itemName #{itemList.order=='itemName asc' ? messages.down : ( itemList.order=='itemName desc' ? messages.up : '' )}">
                          <f:param name="order" value="#{itemList.order=='itemName asc' ? 'itemName desc' : 'itemName asc'}"/>
                     </s:link>
                </f:facet>
                #{item.itemName}
           </h:column>
           ..
           </rich:dataTable>
        • 1. Re: Issue using order by in getEjbql()-xxxxlist.java
          blabno Master

          Do not put order by clause in ejbql. Use setOrderColumn or setOrder methods. The latter is for sorting on multiple columns.

          • 2. Re: Issue using order by in getEjbql()-xxxxlist.java
            Val Sw Expert
            Thanks for the response...
            I added validate as below and it works, is it correct? however is "setOrderColumn" not supported in "jboss-seam-2.0.2.SP1"



            @Name("itemList")
            public class ItemList extends EntityQuery {
            private static final String[] RESTRICTIONS = {"lower(item.itemName) like concat(lower(#{itemList.item.itemName}),'%')",};

            @Override
            public void validate() {
                 super.validate();
                 setOrder("item.itemName");
            }

            @Override
            public String getEjbql() {
              return "select item from Item item";
            }
            }



            • 3. Re: Issue using order by in getEjbql()-xxxxlist.java
              blabno Master

              Do not override getEjbql nor validate. Just do what you need in constructor :


              @Name("itemList")
              public class ItemList extends EntityQuery<Item> {
              
                  public ItemList() {
                      setEjbql("select item from Item item");
                      setOrderColumn("item.itemName");
                      setOrderDirection("asc");
                  }
              }



              I think that setOrderColumn should be supported in seam 2.0.

              • 4. Re: Issue using order by in getEjbql()-xxxxlist.java
                Val Sw Expert
                Thanks for your nice advice.

                a) Adding "setOrder("item.itemName")" to constructor works... great thanks...
                b) getEjbql() is overridden by default when I used seam-gen : do you see any issues.

                It will be very nice to provide valueable inputs for this issue (not sure if I should post it as new issue)..

                How to customize "Search" functionality.
                Summary :
                ItemList.xhtml have "Search" functionality (default generated by seam gen).
                When we click search it returns the result on the basis of value entered in text field, it uses RESTRICTIONS in ItemList.java

                How can I customize Search functionality so that the search returns "stripped" result (i.e. no alphanumeric characters), I have done this as validation using entity query and REGULAR EXPRESSION during "Create" of Item.

                ------------ Sample Validation done suring create using Regular expression------------ 
                Iterator<Item> iterator = getItemsList().iterator();
                while (iterator.hasNext()) {
                     Item elem = ((Item) iterator.next());
                     if(removeAlphaNumericValue(itemNameUI.trim()).equalsIgnoreCase(removeAlphaNumericValue(elem.getItemName()))) {
                          throw new ValidatorException(new FacesMessage("Item "+"'"+elem.getItemName()+"'"+" already exists."));
                     }
                }

                public List<Item> getItemsList() {          
                     List<Item> itemList = entityManager.createQuery("select item from Item item").getResultList();
                     return itemList;
                }

                private String removeAlphaNumericValue(String finalRegexValue){
                     Pattern p = Pattern.compile(REGEX,Pattern.DOTALL | Pattern.CASE_INSENSITIVE);         
                     Matcher m = p.matcher(finalRegexValue); // get a matcher object
                     finalRegexValue = m.replaceAll(REPLACE);
                     return finalRegexValue;
                }
                ------------ END Validation using Regular expression------------ 

                ITEMLIST.XHTML:
                ---------------
                    <h:form id="itemSearch" styleClass="edit">   
                        <rich:simpleTogglePanel label="Item search parameters" switchType="ajax">       
                            <s:decorate template="layout/display.xhtml">
                                <ui:define name="label">itemName</ui:define>
                                <h:inputText id="itemName" value="#{itemList.item.itemName}"/>
                            </s:decorate>       
                        </rich:simpleTogglePanel>       
                        <div class="actionButtons">
                            <h:commandButton id="search" value="Search" action="/ItemList.xhtml"/>
                        </div>       
                    </h:form>
                   
                    <rich:panel>
                        <f:facet name="header">Item search results</f:facet>
                    <div class="results" id="itemList">

                    <h:outputText value="The item search returned no results."
                               rendered="#{empty itemList.resultList}"/>
                              
                    <rich:dataTable id="itemList"
                                var="item"
                              value="#{itemList.resultList}"
                           rendered="#{not empty itemList.resultList}">
                        <h:column>
                            <f:facet name="header">
                                <s:link styleClass="columnHeader"
                                             value="itemName #{itemList.order=='itemName asc' ? messages.down : ( itemList.order=='itemName desc' ? messages.up : '' )}">
                                    <f:param name="order" value="#{itemList.order=='itemName asc' ? 'itemName desc' : 'itemName asc'}"/>
                                </s:link>
                            </f:facet>
                            #{item.itemName}
                        </h:column>
                          ...
                     </rich:dataTable>
                    </div>   
                    </rich:panel>
                   
                    <div class="tableControl">     
                        <s:link view="/ItemList.xhtml"
                            rendered="#{itemList.previousExists}"
                               value="#{messages.left}#{messages.left} First Page"
                                  id="firstPage">
                          <f:param name="firstResult" value="0"/>
                        </s:link>
                        ...       
                    </div>
                • 5. Re: Issue using order by in getEjbql()-xxxxlist.java
                  blabno Master

                  If getEjbql is overridden by seam-gen then Seam devs must have known what they were doing and personally i do not find any cons.
                  If it is possible try limiting query results via ejbql itself. Your requirement for equality of stripped names is strange, but your idea for implementing it is fine.

                  • 6. Re: Issue using order by in getEjbql()-xxxxlist.java
                    Val Sw Expert
                    I understand but it is requirement by client..

                    Example : Say there are few items (Item-A,Item-A123...), if user enters Item%^&A or ItemA and clicks search button, item resultList should return both items i.e. Item-A Item-A123...

                    If I change getEjbql()... I think it will impact other stuff like asc/desc filtering on the basis of column header not sure of other impact.
                    So, looking for a way execute query with non-alphanumeric characters and return all corresponding result..
                    • 7. Re: Issue using order by in getEjbql()-xxxxlist.java
                      blabno Master

                      This sounds more like a full text search. You could write your own bridges that would remove non-alphanumeric characters and get more search options. You may also not go into full text search, but add extra table with one-to-one relation to Item table or extra column in Item table that would contain stripped name. It is better to process name once than at every query.

                      • 8. Re: Issue using order by in getEjbql()-xxxxlist.java
                        Val Sw Expert
                        Thanks a lot for looking into it...

                        I am trying to change the "Search" button action and trying to use native sql.
                        -Created "getItemResult()" which is called on clicking Serach, and somehow will try to return resultList but stuck... not sure if this is right approach... please suggest.

                        2nd. column of this query will return non-alphanumeric chars
                        select upper(CELL_TISSUE_NAME), upper(regexp_replace(ITEM_NAME, '\\W', '')) AS itemName from ITEM where ITEM_NAME

                        ItemList.xhtml
                        --------------
                        <h:form id="itemSearch" styleClass="edit">
                             <h:inputText id="itemName" value="#{itemList.item.itemName}"/>
                             <div class="actionButtons">
                                  <!--<h:commandButton id="search" value="Search" action="/ItemList.xhtml"/>-->
                                  <h:commandButton id="search" value="Search" action="#{itemList.getItemResult}" />
                             </div>
                        </h:form>

                        ItemList.java
                        -------------
                        @Name("itemList")
                        public class ItemList extends EntityQuery {

                             private static final String[] RESTRICTIONS = {"lower(item.itemName) like concat(lower(#{itemList.item.itemName}),'%')",};
                             private Item item = new Item();
                             
                             public ItemList() {
                                setEjbql("select item from Item item");
                                setOrder("item.itemName");       
                            }
                             
                             public String getItemResult(){
                                  String sql =null;          
                                  String testFmtItem=removeAlphaNumericValue(item.getItemName());
                                  sql="select upper(regexp_replace(ITEM_NAME, '\\W', '')) AS itemName from ITEM where ITEM_NAME like '%"+testFmtItem+"%'";
                                  
                                  if(item.getItemName()!=null){
                                       Query sqlQuery = entityManager.createNativeQuery(sql);
                                       List<Item> resultList = sqlQuery.getResultList();          
                                       
                                       //SOMEHOW execute loop and return list
                                       
                                       return resultList.toString();
                                  }else{          
                                       return "";
                                  }
                             }
                             
                             private String removeAlphaNumericValue(String finalRegexValue){
                                 Pattern p = Pattern.compile(REGEX,Pattern.DOTALL | Pattern.CASE_INSENSITIVE);         
                                Matcher m = p.matcher(finalRegexValue); // get a matcher object
                                finalRegexValue = m.replaceAll(REPLACE);
                                return finalRegexValue;
                             }

                             public Item getItem() {
                                  return item;
                             }

                             @Override
                             public List<String> getRestrictions() {
                                  return Arrays.asList(RESTRICTIONS);
                             }

                        }