5 Replies Latest reply on Oct 8, 2009 11:00 AM by Nikos Paraskevopoulos

    framework:entity-query : Query is invoked multiple times

    first last Newbie

      Hi all,


      I'm trying to avoid multiple queries into the DB, my design is obviously wrong somehow, though I don't know how should this be elegantly acompilshed.


      I have a Client entity and a Sale entity, where each Sale has a Client.


      from Sale.java :


      private Client client;
         
      @NotNull
      @ManyToOne
      @JoinColumn(name="client_id")
      public Client getClient() {
              return client;
      }
      public void setClient(Client client) {
              this.client = client;
      }
      



      The Client entity has no awareness of any Sales (it's a one-way relation thing).


      I have this in my components.xml :


      <framework:entity-query name="allClientsQuery"
                              ejbql="select c from Client c"
                              order="c.name asc" />
      <factory name="allClients" value="#{allClientsQuery.resultList}" scope="conversation"/>



      And in the Sales.xhtml page I have a search box where you can select a Client as a search criteria:


      <h:selectOneMenu value="#{saleList.clientId}">
         <s:selectItems value="#{allClients}" var="c" itemValue="#{c.id}" label="#{c.name}" />
      </h:selectOneMenu>



      As well as a table that shows the current search results, one column on which is the Client of the Sale's name:




      <rich:dataTable
              id="saleTable"
              onRowMouseOver="this.style.backgroundColor='#F8F8F8'"
              onRowMouseOut="this.style.backgroundColor='#{a4jSkin.tableBackgroundColor}'"
              value="#{saleList.resultList}"
              var="_sale"
              sortMode="single"
              rows="10"
              rendered="#{not empty saleList.resultList}">
      
              ...
      
              <rich:column sortBy="#{_sale.client.name}"
                              width="150px" >
                      <f:facet name="header">
                              <h:outputText value="#{messages['client.client']}" />
                      </f:facet>
                      <h:outputText id="saleClient" value="#{_sale.client.name}" />
              </rich:column>
      
              ...
      
      </rich:dataTable>



      The saleList component is a simple EntityQuery<Sale> (the MyList class is a common base class for lists, doesn't add much) :




      @Name("saleList")
      @Scope(ScopeType.CONVERSATION)
      public class SaleList extends MyList<Sale>
      {
                      
              private static final String EJBQL = "select s from Sale s";
      
              ... RESTRICTIONS and search criteria stuff ...
      }




      AND - When the page is displayed the allClientsQuery (select c from Client c asc ...)
      is ran as well as a


      "select
              client0_.id as id0_0_,
              ...
              client0_.name as name0_0_,
              ...
          from
              Client client0_ 
          where
              client0_.id=?"



      For each one of the Sales.


      How can I make it so that a single query into the Clients table answers all the page's needs ??


      Thanks!

        • 1. Re: framework:entity-query : Query is invoked multiple times
          blabno Master

          Is this used during long-running conversation ? By default conversation scope equals event (request) scope.

          • 2. Re: framework:entity-query : Query is invoked multiple times
            Nikos Paraskevopoulos Novice

            Hello,


            It is probably a JPA problem. The query run for each item in the Sales list is not allClientsQuery, but the query to get the relation Sale->Client (the where client0_.id=?, this is NOT a part of allClientsQuery).


            You see, in the table that iterates Sales you access the name of the client. JPA probably detects that the client for that specific Sale is not loaded and loads it. The good old N+1 queries problem, I believe.


            Try eager loading the client, either permanently by annotating Sale.java or just for the query. Check JPA and Hibernate docs for query hints. I believe query hints can be set from components.xml too, but I am not sure about that.

            • 3. Re: framework:entity-query : Query is invoked multiple times
              blabno Master

              Strange, for @ManyToOne has FetchType.EAGER by default. Any way to achieve what Nikos says in query you do this :


              "select s from Sale s FETCH JOIN s.client"

              • 4. Re: framework:entity-query : Query is invoked multiple times
                first last Newbie

                Thanks Bernard and Nikos, it totally worked :)
                I change my EJBQL to be


                "select s from Sale s left join fetch s.client"



                And now the DB is inquired once for all Sales records.


                (my wish for a single query to supply the all clients data as well was faulty logic - the data of this query is simply different than the data of the sales query).




                If I have your attention maybe you could help me out on how to compose the following ejbql query:


                a Sale has a List of Items:




                @Entity
                @Name("sale")
                public class Sale extends MyEntity {
                     private List<Item> items = new ArrayList<Item>();
                
                     ...
                
                     @OneToMany(cascade =
                                        {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE},
                               mappedBy="sale")
                     @org.hibernate.annotations.Cascade(value=
                                        {org.hibernate.annotations.CascadeType.DELETE_ORPHAN})
                     public List<Item> getItems() {
                          return items;
                     }
                     
                     ...
                }



                An Item is nothing more than a bridge between a Sale and a Product - it references one of each and specifies the amount purchased:




                @Entity
                @Name("item")
                public class Item extends MyEntity implements MyProxy<Item> {
                
                     Product product;
                     Sale sale;     
                     Integer amount;
                
                        @NotNull
                     public Product getProduct() {
                          return product;
                     }
                
                     @NotNull
                     @ManyToOne
                     public Sale getSale() {
                          return sale;
                     }
                
                        ...
                }




                SO: Given some product X, What would be the query string to


                     Get all Sales that have an Item in them whose Product is X


                ??


                I can't seem to find a good EjbQL learning resource,


                any help or leads would be great.
                Thanks.

                • 5. Re: framework:entity-query : Query is invoked multiple times
                  Nikos Paraskevopoulos Novice

                  Something like the following could do it:


                  SELECT DISTINCT s
                  FROM Sale s, IN(s.items) i
                  WHERE i.product = :prod
                  



                  Parameter is the entire Product object. It might be more appropriate to do:


                  WHERE i.product.id = :product_id
                  



                  Check out the JPA specs in the chapter for query language, there are some examples for these cases.