2 Replies Latest reply on Nov 2, 2009 3:58 PM by Val Sw

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

    Val Sw Expert
      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>
        • 1. Re: Seam EntityQuery ejbql error not able to read column when using customized query.
          Shervin Asgari Master

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




          • 2. Re: Seam EntityQuery ejbql error not able to read column when using customized query.
            Val Sw Expert
            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);
              }
            }
            }