4 Replies Latest reply on Jun 24, 2010 3:18 AM by Anitha Raj

    Trouble with relating 2 entities

    Tanya Ruttenberg Expert
      I have 2 related entities:

      OfficeView.java:

           private int officeId;
           private String state;
           private String type;
           private List<Devices> devices;

      ...skip getters and setters...

           @OneToMany(mappedBy="officeView")
           public List<Devices> getDevices() {
                return devices;
           }
           public void setDevices(List<Devices> devices) {
                this.devices = devices;     
           }


      and Devices.java

           private Integer devId;
           private String devName;
           private String devSerialNum;
           private String devPlatform;
           private OfficeView officeView;

      ...skip getters and setters...

          @ManyToOne
           @JoinColumn(name="office_id")
           public OfficeView getOfficeView() {
                return officeView;
           }
           public void setOfficeView(OfficeView officeView) {
                this.officeView = officeView;
           }
           
      Forgetting about the Devices entity for a moment, the following works great. When
      the form input is entered in basicsearch.xhtml, I get the result I want, namely
      a list of OfficeView's.

      basicsearch.xhtml:

           <h:form>
                <h:outputText value="State" />
                <h:inputText value="#{officeViewList.officeView.state}" />
                <h:outputText value="Office type" />
                <h:inputText value="#{officeViewList.officeView.type}" />
           </h:form>
           
           
      and here is OfficeViewList.java:

           @Name("officeViewList")
           public class OfficeViewList extends EntityQuery<OfficeView> {
           
                private static final String EJBQL = "select officeView from OfficeView officeView";
           
                private static final String[] RESTRICTIONS = {
                          "lower(officeView.state)=lower(#{officeViewList.officeView.state})",
                          "lower(officeView.type) like concat('%',lower(#{officeViewList.officeView.type}),'%')",};
           
                private OfficeView officeView = new OfficeView();
           
                public OfficeViewList() {
                     setEjbql(EJBQL);
                     setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
                     setMaxResults(25);
                }
                
                public OfficeView getOfficeView() {
                     return officeView;
                }
                     
           }

      But now I want to also use devices.devName as additional search criteria and I can't figure out how to do it.

      I add this to the basicsearch.xhtml:

                <h:outputText value="Hostname" />
                <h:inputText value="#{devicesList.devices.devName}" />
                
      And I change the RESTRICTIONS in officeViewList.java like this:

           private static final String[] RESTRICTIONS = {
                     "lower(officeView.state)=lower(#{officeViewList.officeView.state})",
                     "lower(officeView.type) like concat('%',lower(#{officeViewList.officeView.type}),'%')",
                     "lower(devices.devName) like concat('%',lower(#{devicesList.devices.devName}),'%')",};

      And it doesn't work. I get this error:

      Caused by: java.lang.IllegalArgumentException:
      org.hibernate.QueryException: illegal
      attempt to dereference collection [{synthetic-alias}{non-qualified-property-ref}devices]
      with element property reference [devName] [select officeView from dne.nmt.ond.OfficeView
      officeView where lower(devices.devName) like concat('%',lower(:el18),'%')]
         
      I've played around with this endlessly, but clearly I don't understand this well enough to figure it out.

      How do I do this?

      TDR

      PS I purposely only included information I thought would be helpful.  Let me know if I need more detail