Trouble with relating 2 entities
daxxy Apr 27, 2009 10:15 PMI 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
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