2 Replies Latest reply on Aug 10, 2009 7:44 PM by Arbi Sookazian

    Join query

    john mcdonald Newbie

      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.

        • 1. Re: Join query
          john mcdonald Newbie

          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.

          • 2. Re: Join query
            Arbi Sookazian Master

            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.