EJBQL with lookup table
bobgee Feb 24, 2009 12:13 PMHi there!
I have a so called lookup table 'Manufacturer' linked to my primary table 'Printer' via @ManyToOne:
public class Printer implements java.io.Serializable { private long id; private String modelname; private Manufacturer manufacturer; private DeviceClass deviceClass; private String remark; ... @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "manufacturer_id_fk", nullable = false) @NotNull public Manufacturer getManufacturer() { return this.manufacturer; } ...
public class Manufacturer implements java.io.Serializable { private int id; private String name; ... @Column(name = "name", nullable = false) @NotNull public String getName() { return this.name; }
In PrinterList.xhtml I have query input fields for selecting (i.e. filtering) manufacturer name and modelname by given partial strings (to be matched from the beginning of the string):
<s:decorate template="layout/display.xhtml"> <ui:define name="label">Manufacturer</ui:define> <h:inputText id="manufacturer" value="#{printerList.manufacturer}"/> </s:decorate> <s:decorate template="layout/display.xhtml"> <ui:define name="label">Modelname</ui:define> <h:inputText id="modelname" value="#{printerList.printer.modelname}"/> </s:decorate>
I'm lost how to specify the EJB query for selecting the manufactures name.
It worked well with filtering the Printer.modelname solely before I added manufacturer to
PrinterList.java:
@Name("printerList") public class PrinterList extends EntityQuery<Printer> { private static final String EJBQL = "select printer, manufacturer from Printer printer, Manufacturer manufacturer"; private static final String ORDER = "printer.manufacturer.name, printer.modelname"; private static final String[] RESTRICTIONS = { "lower(manufacturer.name) like concat(lower(#{printerList.manufacturer.name}),'%')", "lower(printer.modelname) like concat(lower(#{printerList.printer.modelname}),'%')", }; private Printer printer = new Printer(); private Manufacturer manufacturer = new Manufacturer(); public PrinterList() { setEjbql(EJBQL); setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS)); setOrder(ORDER); setMaxResults(25); } public Printer getPrinter() { return printer; } @Transient public Manufacturer getManufacturer() { return manufacturer; } }
Sure, PrinterList.java and PrinterList.xhtml isn't consistent at the moment.
Please help me understanding that mechanism! I'm quite expert at SQL but don't know how to get this in EJBQL and EL.
Questions e. g.:
Do I need a separate Manufacturer member here or do I access 'printerList.manufacturer'?
What is the right EL expression in the xhtml - with .name or without?
Thanks!
Robert
BTW: How can I put in here a newline without adding a blank line???