4 Replies Latest reply on Dec 9, 2009 11:36 AM by Joshua D

    Passing where clause parameters to query list interface

    Joshua D Novice

      I have the following panels in a particular page.


      Panel-1: User
      Panel-2: User Address (e.g. rendered using userAddressList.resultList)
      Panel-3: User Phones (e.g. rendered using userPhoneList.resultList)


      How do I pass the userId to the userAddressList, userPhoneList where clause parameter dynamically from the view page.

        • 1. Re: Passing where clause parameters to query list interface
          Emir Calabuch Newbie

          Since you're probably rendering your user via a component, you just need to inject the user's id as a restriction. Suppose you can access your user id with: you can just add a restriction to your query like this:


          user_id = #{user.id}
          



          Note that EL expressions in restrictions have a quirk: if the expression evaluates to NULL, the restriction will not be applied (in this case, if your user id is null, the query will extract ALL addresses and phone numbers in the DB). If this is not what you want, you can use a workaround like this:


          user_id = #{not empty user.id ? user.id : 0}
          



          In this case, if the user id is null, the restriction will resolve to: user_id = 0, which will return an empty dataset (no users match id 0).

          • 2. Re: Passing where clause parameters to query list interface
            Joshua D Novice
            Thanks for the reply. The above scenario talks about injecting a single parameter (user.id) to Panel-2, Panel-3.

            But I would like to pass multiple parameters to the userAddressList apart from the #{user.id} (e.g. address type, ...). I would like to segregate the view to print all work | home addresses.

            Would it be possible to call a method on the userAdddressList bean from the view html and pass these as parameters. What am I missing here? I would appreciate any pointers to resolve this issue.
            • 3. Re: Passing where clause parameters to query list interface
              Emir Calabuch Newbie

              You can have as many restrictions as you want in your entity queries, you're not limited to just one. If you want the query result to depend on several search parameters, I usually create a separate bean containing the query parameters and use it in the query.


              Suppose you need to search by user id, address type and phone type (the latter two being enums), so I create a bean with to contain the search parameters (getters and setters omitted for brevity):


              @Name("userSearch")
              public class UserSearch {
                  private Long userId;
                  private AddressType addressType;
                  private PhoneType phoneType;
              }
              



              In your page, you can wire these search parameters to the part of the view that takes care of displaying the search parameters (an input field for the user id, a combo for address and phone types):


              <h:inputText value="#{userSearch.userId}" />
              <h:selectOneMenu value="#{userSearch.addressType}">
                  ...
              </h:selectOneMenu>
              <h:selectOneMenu value="#{userSearch.phoneType}">
                  ...
              </h:selectOneMenu>
              



              Now you can use these in an entity query (in this case these are queries in components.xml, but this equally applies to subclasses of EntityQuery):


              <framework:entity-query name="addressQuery"
                      ejbql="from Address a">
                  <framework:restrictions>
                      <value>a.userId = #{userSearch.userId}</value>
                      <value>a.addressType = #{userSearch.addressType}</value>
                  </framework:restrictions>
              </framework:entity-query>
              
              <framework:entity-query name="phoneQuery"
                      ejbql="from Address a">
                  <framework:restrictions>
                      <value>a.userId = #{userSearch.userId}</value>
                      <value>a.phoneType = #{userSearch.phoneType}</value>
                  </framework:restrictions>
              </framework:entity-query>
              



              And then use the entity query result in your view:


              <rich:dataTable var="_addr" value="#{addressQuery.resultList}">
                  ....
              </rich:dataTable>
              
              <rich:dataTable var="_phone" value="#{phoneQuery.resultList}">
                  ....
              </rich:dataTable>
              



              If you need other search parameters, just add them to the search bean and use them in the queries as needed.

              • 4. Re: Passing where clause parameters to query list interface
                Joshua D Novice

                Thanks and appreciate the detailed response