Join query

[jmacneedshelp]

I have the following query:

"select o from Orders o join o.orderitems as item where item.vendor=:vendor and o.status In('Vendors Assigned')"

I'm using the resulting list in an dataTable to list the Orders and OrderItems; However I only want the OrderItems for which the given Vendor is assigned.

After some reading I discovered 'fetch' so I'm using it like this:

"select o from Orders o join fetch o.orderitems as item where item.vendor=:vendor and o.status In('Vendors Assigned')"

The pages loads fine the first time, but when I reload the page I get the following exception:

Caused by: org.hibernate.TransientObjectException: object references an unsaved transient instance - save the transient instance before flushing: com.xeosuite.xeoadmin.entity.Vendor
     at org.hibernate.engine.ForeignKeys.getEntityIdentifierIfNotUnsaved(ForeignKeys.java:219)
     at org.hibernate.type.EntityType.getIdentifier(EntityType.java:407)
     at org.hibernate.type.ManyToOneType.nullSafeSet(ManyToOneType.java:87)
     at org.hibernate.param.NamedParameterSpecification.bind(NamedParameterSpecification.java:38)
     at org.hibernate.loader.hql.QueryLoader.bindParameterValues(QueryLoader.java:491)
     at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1563)
     at org.hibernate.loader.Loader.doQuery(Loader.java:673)
     at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
     at org.hibernate.loader.Loader.doList(Loader.java:2220)
     at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
     at org.hibernate.loader.Loader.list(Loader.java:2099)
     at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
     at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
     at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
     at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
     at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
     at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:65)

Here's the component from which I'm executng the query:

@Stateful
@Name("VendorOrder")
@Scope(ScopeType.SESSION)
public class VendorOrderAction implements VendorOrder {
  
    @In private EntityManager entityManager;
    
    @In @Out
    private Vendor vendor;
    
    public Vendor getVendor() {
         return vendor;
    }
    
    public void setVendor(Vendor vendor) {
         this.vendor = vendor;
    }
    
    public List<Orders> getOrderList() { 
         return entityManager.createQuery(
              "select o from Orders o join fetch o.orderitems as item where item.vendor=:vendor and o.status In('Vendors Assigned')"
         ).setParameter("vendor", vendor).getResultList();
    }

Does anyone have any idea why I get this exception on second request to the page, but no the first?

Thanks.

[jmacneedshelp]

OK, this is odd.

This works as it should

return entityManager.createQuery(
              "select o from Orders o join fetch o.orderitems as item where item.vendor.vendorid=:vendor and o.status In('Vendors Assigned')"
         ).setParameter("vendor", vendor.getVendorid()).getResultList();

Whereas this throws the error explained above:

return entityManager.createQuery(
              "select o from Orders o join fetch o.orderitems as item where item.vendor=:vendor and o.status In('Vendors Assigned')"
         ).setParameter("vendor", vendor).getResultList();

Can anyone explain this.

Thanks.

[asookazian]

1) this question belongs on a JPA or possibly Hibernate forum

2) you need to post your entity classes

3) why are you doing o.status In('Vendors Assigned')?  The IN keyword is used for a list of items, not just a single varchar(x).  Anyways, that's just a side note.

4) The global fetch plan can be eager or lazy.  Usually it defaults to lazy and you can override it as you are by specifying join fetch in your JPQL query.  That causes the persistence provider to essentially submit a query to the db that will pre-populate the OrderItems collection rather than dynamically when the JSF page is rendered and the object graph is navigated.

using ANSI SQL:

select o
from Orders o
inner join o.orderitems oi
inner join oi.vendors v
where o.status in ('Vendors Assigned')

or using theta joins

select o
from Orders o,
OrderItems oi,
Vendors v
where o.orderid = oi.order.orderid
and orderitems.vendor.vendorid = v.vendorid
and o.status in ('Vendors Assigned')

warning: I have not tested the above JPQL queries.