framework:entity-query : Query is invoked multiple times

[kipod]

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!

[blabno]

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

[niox.nikospara.yahoo.com]

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.

[blabno]

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"

[kipod]

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.

[niox.nikospara.yahoo.com]

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.