1 Reply Latest reply on Nov 20, 2006 7:06 AM by Jan Bares

    Fetch join with collection

    Jan Bares Newbie

      Hi,

      I can fetch join lazy relation that returns single result, but for collection it doesn't work correctly.

      I have Attribute.java:

      @Entity
      public class Attribute implements Serializable {
      
       private Long id;
      
       private List<Resource> resources = new ArrayList<Resource>();
      
       public Attribute() {
       super();
       }
      
       @Id
       @GeneratedValue
       public Long getId() {
       return id;
       }
      
       public void setId(Long id) {
       this.id = id;
       }
      
      
       @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
       public List<Resource> getResources() {
       return resources;
       }
      }
      


      and Resource.java:
      @Entity
      public class Resource implements Serializable {
       private Long id;
      
       private int resourceSize = 0;
      
       public Resource() {
       super();
       }
      
       @Id
       @GeneratedValue
       public Long getId() {
       return id;
       }
      
       private void setId(Long id) {
       this.id = id;
       }
      
       public int getResourceSize() {
       return resourceSize;
       }
      
       public void setResourceSize(int resourceSize) {
       this.resourceSize = resourceSize;
       }
      }
      


      and a finder:
      public List<Attribute> findWithResource(int resourceSize) {
       return em.createQuery(
       "SELECT ta FROM Attribute ta INNER JOIN FETCH ta.resources r" +
       " WHERE r.resourceSize = :resourceSize")
       .setParameter("resourceSize", resourceSize)
       .getResultList();
      }
      


      This should return all attributes that have resource of requested resourceSize with fetched resources.

      Database content:
      Attribute1: resource1 (resourceSize = 1), resource2 (resourceSize = 1), resource3 (resourceSize = 1)
      Attribute2: resource1 (resourceSize = 1), resource2 (resourceSize = 1), resource3 (resourceSize = 1)

      The query generated by EJB3 looks correctly:
      select ...
      from Attribute attribute0_
       inner join Attribute_Resource resources1_ on attribute0_.id=resources1_.Attribute_id
       inner join Resource resource2_ on resources1_.resources_id=resource2_.id
      where resource2_.resourceSize=?
      


      When I call findWithResource(1), I expect to get two Attributes, each with the three resources loaded. Instead I get six Attributes, each with three resources loaded.

      I this bug inside my query, unsupported feature or bug in EJB3?

      Thank you for your care, Jan