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

[valatharv]

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>

[blabno]

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

[valatharv]

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";
}
}

[blabno]

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.

[valatharv]

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>

[blabno]

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.

[valatharv]

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..

[blabno]

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.

[valatharv]

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);
     }

}