1 Reply Latest reply on Apr 6, 2006 4:37 PM by tsar_bomba

    EJBQL - not pulling records correctly

      I've got a simple parent/child relationship (one-to-many) between two objects; ProductCategory->Product

      Here's the two domain object classes:

      @Entity
      @Table(name="tbl_product_category")
      public class ProductCategory implements Serializable
      {
       private long categoryId;
       private String name;
       private String description;
       private List<Product> products;
      
       public ProductCategory()
       {
       }
      
       @Id @GeneratedValue(strategy=GenerationType.AUTO)
       public long getCategoryId()
       {
       return this.categoryId;
       }
      
       public void setCategoryId(long categoryId)
       {
       this.categoryId = categoryId;
       }
      
       @NotNull @Length(max=500)
       public String getDescription()
       {
       return this.description;
       }
      
       public void setDescription(String description)
       {
       this.description = description;
       }
      
       @NotNull @Length(max=50)
       public String getName()
       {
       return this.name;
       }
      
       public void setName(String name)
       {
       this.name = name;
       }
      
       @OneToMany(mappedBy="productCategory", fetch=FetchType.EAGER)
       public List<Product> getProducts()
       {
       return this.products;
       }
      
       public void setProducts(List<Product> products)
       {
       this.products = products;
       }
      }
      
      @Entity
      @Table(name="tbl_product")
      public class Product implements Serializable
      {
       private long productId;
       private ProductCategory productCategory;
       private String productCode;
       private String shortName;
       private String longName;
       private String summary;
       private String description;
       private float unitPrice;
       private float weight;
       private float height;
       private float width;
       private float length;
       private float insuredValue;
       private Date nextAvailableDate;
       private String thumbnail;
       private String photo;
       private boolean isAddOn;
       private List<Configuration> configurations;
      
       public Product()
       {
       }
      
       @Length(max=1000)
       public String getDescription()
       {
       return this.description;
       }
      
       public void setDescription(String description)
       {
       this.description = description;
       }
      
       public float getHeight()
       {
       return this.height;
       }
      
       public void setHeight(float height)
       {
       this.height = height;
       }
      
       public float getInsuredValue()
       {
       return this.insuredValue;
       }
      
       public void setInsuredValue(float insuredValue)
       {
       this.insuredValue = insuredValue;
       }
      
       public float getLength()
       {
       return this.length;
       }
      
       public void setLength(float length)
       {
       this.length = length;
       }
      
       @Length(max=50) @NotNull
       public String getPhoto()
       {
       return this.photo;
       }
      
       public void setPhoto(String photo)
       {
       this.photo = photo;
       }
      
       @Length(min=5,max=20) @NotNull
       public String getProductCode()
       {
       return this.productCode;
       }
      
       public void setProductCode(String productCode)
       {
       this.productCode = productCode;
       }
      
       @Id @GeneratedValue(strategy=GenerationType.AUTO)
       public long getProductId()
       {
       return this.productId;
       }
      
       public void setProductId(long productId)
       {
       this.productId = productId;
       }
      
       @Length(max=50) @NotNull
       public String getThumbnail()
       {
       return this.thumbnail;
       }
      
       public void setThumbnail(String thumbnail)
       {
       this.thumbnail = thumbnail;
       }
      
       public float getUnitPrice()
       {
       return this.unitPrice;
       }
      
       public void setUnitPrice(float unitPrice)
       {
       this.unitPrice = unitPrice;
       }
      
       public float getWeight()
       {
       return this.weight;
       }
      
       public void setWeight(float weight)
       {
       this.weight = weight;
       }
      
       public float getWidth()
       {
       return this.width;
       }
      
       public void setWidth(float width)
       {
       this.width = width;
       }
      
       @Length(max=1000)
       public String getSummary()
       {
       return this.summary;
       }
      
       public void setSummary(String summary)
       {
       this.summary = summary;
       }
      
       @OneToMany(mappedBy="product", fetch=FetchType.EAGER)
       public List<Configuration> getConfigurations()
       {
       return this.configurations;
       }
      
       public void setConfigurations(List<Configuration> configurations)
       {
       this.configurations = configurations;
       }
      
       @ManyToOne
       public ProductCategory getProductCategory()
       {
       return this.productCategory;
       }
      
       public void setProductCategory(ProductCategory productCategory)
       {
       this.productCategory = productCategory;
       }
      
       @Length(max=100) @NotNull
       public String getLongName()
       {
       return this.longName;
       }
      
       public void setLongName(String longName)
       {
       this.longName = longName;
       }
      
       @Length(max=50) @NotNull
       public String getShortName()
       {
       return this.shortName;
       }
      
       public void setShortName(String shortName)
       {
       this.shortName = shortName;
       }
      
       @NotNull
       public Date getNextAvailableDate()
       {
       return this.nextAvailableDate;
       }
      
       public void setNextAvailableDate(Date nextAvailableDate)
       {
       this.nextAvailableDate = nextAvailableDate;
       }
      
       public boolean isAddOn()
       {
       return this.isAddOn;
       }
      
       public void setAddOn(boolean isAddOn)
       {
       this.isAddOn = isAddOn;
       }
      }
      


      When I query like this, from the category down:

       public List<ProductCategory> getAllCategories()
       {
       Query q = this.em.createQuery("select pc from ProductCategory pc where pc.products.addOn = false");
       return q.getResultList();
       }
      


      It returns only the category that = true, not false...the exact opposite of what I want! If I switch this to true, I get the same results.

      If I query at the Product level, like so:

       public List<Product> getNonAddOns()
       {
       Query q = this.em.createQuery("select p from Product p where p.addOn = false");
       return q.getResultList();
       }
      


      ...I get exactly what I expect...

      What I want is to get all categories & products but I want the products excluded where the isAddOn field = false (MSSQL bit field).

      What am I doing wrong?

      Thanks!