Seam EntityQuery ejbql error not able to read column when using customized query.

[valatharv]

Hi,

We have a itemList page for item entity generated by seam-gen. Please suggest what I am doing wrong may be ejbQl, restrictions..

-I am trying to customize the list page on the basis of event i.e. When item list page loads, it displays list of items only for user logged in with dynamic button name showing "Show All Items" : OK

-So, when page loads it gets customized ejbql from itemList.java on the basis of user.
"select distinct item from ItemHistoryRecords itemHistoryRecords where userID='"+getUsers().getUserId()+"'"

Error :
When I click on "Last Updated" column header to sort, it gives exception as
---------------------------------------------------------------------
[JDBCExceptionReporter] SQL Error: 904, SQLState: 42000
[JDBCExceptionReporter] ORA-00904: "LASTUPDATEDTIMESTAMPELEMENT": invalid identifier
[STDERR] Oct 30, 2009 11:16:51 AM com.sun.facelets.FaceletViewHandler handleRenderException
SEVERE: Error Rendering View[/ItemList.xhtml]
javax.faces.FacesException: javax.el.ELException: /ItemList.xhtml @66,60 rendered="#{empty itemList.resultList}": Error reading 'resultList' on type com.session.ItemList_$$_javassist_3
     ..
Caused by: java.sql.SQLException: ORA-00904: "LASTUPDATEDTIMESTAMPELEMENT": invalid identifier
[JDBCExceptionReporter] SQL Error: 0, SQLState: null
---------------------------------------------------------------------
Note : "LASTUPDATEDTIMESTAMPELEMENT" definitely exists in Item as I am able to query it using Hibernate query editor.
Also, when I click "Show All Items", it calls default query "select item from Item item" and clicking on "Last Updated" column header works fine.

ItemList.java
-------------
@Name("itemList")
public class ItemList extends EntityQuery {
     
     @In(create=true)
     @Out
     ItemListCriteriaBean itemListCriteriaBean;     
     
     public ItemList(){
     }
     
     public String changeMyItem(){
          if(itemListCriteriaBean.isMyItems()){
               itemListCriteriaBean.setMyItems(false);
               itemListCriteriaBean.setButtonName("Show All items");
          }else{
               itemListCriteriaBean.setMyItems(true);
               itemListCriteriaBean.setButtonName("Show My items");          
          }
          this.setEjbql(this.getEjbql());
          this.refresh();
          entityManager.flush();
          return "";
     }
     
     private static final String[] RESTRICTIONS_ITEMS = {          
               "lower(item.itemCode) like concat('%',lower(#{itemList.item.itemCode}),'%')",
               "lower(item.itemName) like concat('%',lower(#{itemList.item.itemName}),'%')",};

     private static final String[] RESTRICTIONS_HISTORY = {
          "lower(itemHistoryRecords.item.itemCode) like concat('%',lower(#{itemHistoryRecords.itemList.item.itemCode}),'%')",
          "lower(itemHistoryRecords.item.itemName) like concat('%',lower(#{itemHistoryRecords.itemList.item.itemName}),'%')",};

     private Item item = new Item();

     @Override
     public String getEjbql() {
          if(itemListCriteriaBean.isMyItems()){
               return "select distinct item from ItemHistoryRecords itemHistoryRecords where userID='"+getUsers().getUserId()+"'";
          }else{
               return "select item from Item item";
          }
     }
     
     public Item getItem() {
          return item;
     }

     @Override
     public List<String> getRestrictions() {
          if(itemListCriteriaBean.isMyItems()){
               this.refresh();
               return Arrays.asList(RESTRICTIONS_HISTORY);               
          }else{
               this.refresh();
               return Arrays.asList(RESTRICTIONS_ITEMS);
          }
     }
}

ItemList.xhtml
--------------
<h:form id="itemFilter" styleClass="edit">
     <h:commandButton action="#{itemList.changeMyItem}" value="#{itemListCriteriaBean.buttonName}" />                                      
</h:form>

<h:column>
     <f:facet name="header">
          <s:link styleClass="columnHeader"
                          value="Item Name #{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>

<h:column>
     <f:facet name="header">
          <s:link styleClass="columnHeader"
                          value="Last Updated #{itemList.order=='lastUpdatedTimestampElement asc' ? messages.down : ( itemList.order=='lastUpdatedTimestampElement desc' ? messages.up : '' )}">
               <f:param name="order" value="#{itemList.order=='lastUpdatedTimestampElement asc' ? 'lastUpdatedTimestampElement desc' : 'lastUpdatedTimestampElement asc'}"/>
          </s:link>
     </f:facet>                       
      <h:outputText value="#{item.lastUpdatedTimestampElement}">
          <s:convertDateTime type="both" dateStyle="short"/>
     </h:outputText>
</h:column>

[cash1981]

Not sure if this is the reason it fails, however you should always use parameters instead of the way you are doing it to avoid sql injections.

@In EntityManager entityManager;
List<Item> myList = (List<Item>)entityManager.createQuery("select distinct item from ItemHistoryRecords ihr where ihr.user.id=:userId").setParameter("userId", getUsers().getUserId()).getResultList();

[valatharv]

Thanks for your suggestion, problem is when page loads it gets customized getEjbql() from itemList.java which have return type as String.
And I am changing the query on the basis of condition as in changeMyItem() function...

Please suggest how I can use this list query in the code.

If I sort clicking on "ItemName" from UI it works fine, is there any issue with Restrictions..

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

private static final String[] RESTRICTIONS_ITEMS = {
   "lower(item.itemCode) like concat('%',lower(#{itemList.item.itemCode}),'%')",
   "lower(item.itemName) like concat('%',lower(#{itemList.item.itemName}),'%')",};

private static final String[] RESTRICTIONS_HISTORY = {
  "lower(itemHistoryRecords.item.itemCode) like concat('%',lower(#{itemHistoryRecords.itemList.item.itemCode}),'%')",
  "lower(itemHistoryRecords.item.itemName) like concat('%',lower(#{itemHistoryRecords.itemList.item.itemName}),'%')",};

@In(create=true)
@Out
ItemListCriteriaBean itemListCriteriaBean;
   public ItemList(){
}

public String changeMyItem(){
  if(itemListCriteriaBean.isMyItems()){
   itemListCriteriaBean.setMyItems(false);
   itemListCriteriaBean.setButtonName("Show All items");
  }else{
   itemListCriteriaBean.setMyItems(true);
   itemListCriteriaBean.setButtonName("Show My items");
  }
  this.setEjbql(this.getEjbql());
  this.refresh();
  entityManager.flush();
  return "";
}

@Override
public String getEjbql() {
  if(itemListCriteriaBean.isMyItems()){
   return "select distinct item from ItemHistoryRecords itemHistoryRecords where userID='"+getUsers().getUserId()+"'";
  }else{
   return "select item from Item item";
  }
}
   public Item getItem() {
  return item;
}

@Override
public List<String> getRestrictions() {
  if(itemListCriteriaBean.isMyItems()){
   this.refresh();
   return Arrays.asList(RESTRICTIONS_HISTORY);
  }else{
   this.refresh();
   return Arrays.asList(RESTRICTIONS_ITEMS);
  }
}
}