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:


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

      ...skip getters and setters...

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

           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.


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

           public class OfficeViewList extends EntityQuery<OfficeView> {
                private static final String EJBQL = "select officeView from OfficeView officeView";
                private static final String[] RESTRICTIONS = {
                          "lower(officeView.type) like concat('%',lower(#{officeViewList.officeView.type}),'%')",};
                private OfficeView officeView = new OfficeView();
                public OfficeViewList() {
                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.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?


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