4 Replies Latest reply on Feb 26, 2009 7:00 PM by Robert Grampp

    EJBQL with lookup table

    Robert Grampp Newbie

      Hi 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???

        • 1. Re: EJBQL with lookup table
          Marcio Endo Newbie

          This should work:


          private static final String EJBQL = "select p from Printer p join p.manufacturer m";
          
          private static final String[] RESTRICTIONS = {
               "lower(m.name) like concat(lower(#{printerList.manufacturer.name}),'%')",
               "lower(p.modelname) like concat(lower(#{printerList.printer.modelname}),'%')",
          };
          

          • 2. Re: EJBQL with lookup table
            Robert Grampp Newbie

            Hi Marcio,
            thanks for dealing with my problem!


            Sorry, doesn't work this way.
            After adding a class member manufacturer it runs without exception,
            but the SQL statement doesn't include a where clause part for querying manufacturer.name.
            On the other hand, if I enter a search string for modelname, a where clause part is added:


                select
                    printer0_.id as id2_,
                    printer0_.manufacturer_id_fk as manufact6_2_,
                    printer0_.modelname as modelname2_
                from
                    public.printer printer0_,
                    public.manufacturer manufactur1_ 
                where
                    printer0_.manufacturer_id_fk=manufactur1_.id 
                    and (
                        lower(printer0_.modelname) like ('%'||lower(?)||'%')
                    ) 



            If I only search for a manufacturer name, the SQL ends before the and you see above.
            So how do I have to specify the manufacturer name from the linked table?
            How do specify it in the xhtml?
            Now I have


            <h:inputText id="manufacturer" value="#{printerList.manufacturer.name}"/>
            



            As I'm understand correctly, the param in .page.xml is irrelevant, it's only for bookmarkable URLs (?).


            So guys, could please anyone explain what's going on under the hood? How does the parameter from the search form get into the query in PrinterList.java??
            Meanwhile I only understand Bahnhof - as the German's say.


            Regards,
            Robert

            • 3. Re: EJBQL with lookup table
              Marcio Endo Newbie

              After adding a class member manufacturer it runs without exception,
              but the SQL statement doesn't include a where clause part for querying manufacturer.name.


              On of the reasons for this you already mentioned:



              <h:inputText id="manufacturer" value="#{printerList.manufacturer.name}"/>




              Another could be this:



              As I'm understand correctly, the param in .page.xml is irrelevant, it's only for bookmarkable URLs (?).


              Actually the param in .page.xml ARE relevant. It is from there that the <s:link> can automatically append parameters to a URL.


              Try and edit you .page.xml and see if anything changes.

              • 4. Re: EJBQL with lookup table
                Robert Grampp Newbie

                Yesss, after having in .page.xml a line


                <param name="nameItAnyHow" value="#{printerList.manufacturer.name}"/>



                it works. It's strange that the param name isn't reflected (i. e.) used anywhere in the xhtml or the list bean.



                Actually the param in .page.xml ARE relevant. It is from there that the  can automatically append parameters to a URL.

                I saw, the search button was rendered as a


                <input type="submit">


                , so I thought it would go over http POST method and parameters in .page.xml wouldn't be used/recognized (I didn't watch the address bar).
                But I learned, page params are used to get the submitted values into the list bean.
                So, how would I accomplish this without a pages xml file (a 'normal' http submit)??


                Regards, Robert