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!