Discussions

EJB programming & troubleshooting: EJB QL RETURNING ONLY THE FIRST RECORD OF THE TABLE

  1. Hi: I have following EJB QL: SELECT OBJECT(p) FROM Product p WHERE p.homeCategory = ?1 which for some weird reason is returning only the first record in the table Product. In the table product there is a foreign key called categoryId that points to the primary key of the field CategoryId of the table Category. There is three records in the table Product that contains a foregn key of CategoryId=1, THEREFORE THE ABOVE SQL should return three records from the Product table but the result only returns the first record of the Product table no matter what the value is for the first record of the CategoryId field for the Product Table. Here is the client jsp code that calls the ejb: package com; import java.util.Collection; import javax.ejb.CreateException; import javax.ejb.FinderException; public interface CategoryHomeLocal extends javax.ejb.EJBLocalHome { public CategoryLocal create(Integer id) throws CreateException; public CategoryLocal findByPrimaryKey(Integer id) throws FinderException; public Collection findCategory() throws FinderException; public Collection selectProduct(CategoryLocal categoryLocal) throws FinderException; } Here is the ejb-jar.xml: <!--?xml version="1.0"?--> CategoryEJB com.CategoryHomeLocal com.CategoryLocal com.CategoryBean Container java.lang.Integer False 2.x Category categoryId categoryName picture pictureWidth pictureHeight labelOn labelOff button categoryId findCategory SELECT OBJECT(c) FROM Category c ejbSelectProduct com.CategoryLocal SELECT OBJECT(p) FROM Product p WHERE p.homeCategory = ?1 ProductEJB com.ProductHomeLocal com.ProductLocal com.ProductBean Container java.lang.Integer False 2.x Product productId brandName productDescription purchasePrice categoryId productId <!-- <query> <query-method> <method-name>findByProduct</method-name> <method-params> <method-param>com.titan.address.CategoryLocal</method-param> </method-params> </query-method> <ejb-ql> SELECT OBJECT(p) FROM Product p WHERE p.homeCategory = ?1 </ejb-ql> </query> --> Product-HomeCategory Product-has-a-Category one ProductEJB homeCategory java.util.Collection Category-belongs-to-Product many CategoryEJB Employees Employees CategoryEJB * CategoryEJB * ProductEJB * Required Here is the weblogic-cmp-rdbms.xml: CategoryEJB ShoeStore category categoryId categoryID categoryName categoryName picture Picture pictureWidth PictureWidth pictureHeight PictureHeight labelOn LabelOn labelOff LabelOff button Button SQL_SERVER ProductEJB ShoeStore PRODUCT productId productId categoryId categoryId brandName brandName productDescription productDescription purchasePrice purchasePrice <!-- Automatically generate the value of ID in the database on inserts using sequence table --> SQL_SERVER Product-HomeCategory Category-belongs-to-Product categoryId productId Here is the weblogic-ejb-jar.xml: <!--?xml version="1.0"?--> CategoryEJB 100 WebLogic_CMP_RDBMS 6.0 META-INF/weblogic-cmp-rdbms-jar.xml CategoryHomeLocal ProductEJB 100 WebLogic_CMP_RDBMS 6.0 META-INF/weblogic-cmp-rdbms-jar.xml ProductHomeLocal <!-- Map the ejbbook user to the Employees role --> Employees ejbbook Here is the ProductBean.java: package com; import javax.naming.InitialContext; import javax.ejb.EntityContext; import javax.ejb.CreateException; import javax.ejb.FinderException; import javax.naming.NamingException; import java.util.Date; import java.util.Collection; import java.util.Iterator; import java.util.Vector; import java.lang.Integer; public abstract class ProductBean implements javax.ejb.EntityBean { public Integer ejbCreate(Integer id) { this.setProductId(id); return null; } public void ejbPostCreate(Integer id) { } //public abstract Integer getCategoryId(); // public abstract void setCategoryId(Integer categoryId); public abstract Integer getProductId(); public abstract void setProductId(Integer productId); public abstract Integer getCategoryId(); public abstract void setCategoryId(Integer categoryId); public abstract String getBrandName(); public abstract void setBrandName(String brandName); public abstract String getProductDescription(); public abstract void setProductDescription(String productDescription); public abstract Double getPurchasePrice(); public abstract void setPurchasePrice(Double purchasePrice); public abstract Collection getHomeCategory(); public abstract void setHomeCategory(Collection categoryLocal); public void setEntityContext(EntityContext ec) { System.out.println("ProductBean setEntityContext"); } public void unsetEntityContext() { System.out.println("ProductBean unsetEntityContext"); } public void ejbLoad() { System.out.println("ProductBean ejbLoad"); } public void ejbStore() { System.out.println("ProductBean ejbStore"); } public void ejbActivate() { System.out.println("ProductBean ejbActivate"); } public void ejbPassivate() { System.out.println("ProductBean ejbPassivate"); } public void ejbRemove() { System.out.println("ProductBean ejbRemove"); } } Here is the ProductLocal.java: package com; import javax.ejb.CreateException; import javax.ejb.FinderException; import javax.naming.NamingException; import java.util.Date; import java.util.Vector; import java.util.Collection; public interface ProductLocal extends javax.ejb.EJBLocalObject { public Integer getProductId(); public void setProductId(Integer productId); public String getBrandName(); public void setBrandName(String brandName); public String getProductDescription(); public void setProductDescription(String productDescription); public Double getPurchasePrice(); public void setPurchasePrice(Double purchasePrice); public Collection getHomeCategory(); public void setHomeCategory(Collection categoryLocal); } ProductHomeLocal.java: package com; import java.util.Collection; import javax.ejb.CreateException; import javax.ejb.FinderException; public interface ProductHomeLocal extends javax.ejb.EJBLocalHome { public ProductLocal create(Integer id) throws CreateException; public ProductLocal findByPrimaryKey(Integer id) throws FinderException; } Here is the CategoryBean.java: package com; import javax.naming.InitialContext; import javax.ejb.EntityContext; import javax.ejb.CreateException; import javax.naming.NamingException; import java.util.Date; import java.util.Collection; import java.util.Iterator; import java.util.Vector; import javax.ejb.FinderException; public abstract class CategoryBean implements javax.ejb.EntityBean { public Integer ejbCreate(Integer id) { this.setCategoryId(id); return null; } public void ejbPostCreate(Integer id) { } // abstract accessor methods // public abstract javax.ejb.EntityBean ejbSelectProduct(CategoryLocal categoryLocal) // throws FinderException; public abstract Collection ejbSelectProduct(CategoryLocal categoryLocal) throws FinderException; public Collection ejbHomeSelectProduct(CategoryLocal categoryLocal) throws FinderException { return (Collection)(this.ejbSelectProduct(categoryLocal)); } public abstract Integer getCategoryId(); public abstract void setCategoryId(Integer categoryId); public abstract String getCategoryName(); public abstract void setCategoryName(String categoryName); public abstract String getPicture(); public abstract void setPicture(String picture); public abstract String getPictureWidth(); public abstract void setPictureWidth(String pictureWidth); public abstract String getPictureHeight(); public abstract void setPictureHeight(String pictureHeight); public abstract String getLabelOn(); public abstract void setLabelOn(String labelOn); public abstract String getLabelOff(); public abstract void setLabelOff(String labelOff); public abstract String getButton(); public abstract void setButton(String button); public void setEntityContext(EntityContext ec) { System.out.println("setEntityContext"); } public void unsetEntityContext() { System.out.println("unsetEntityContext"); } public void ejbLoad() { System.out.println("ejbLoad"); } public void ejbStore() { System.out.println("ejbStore"); } public void ejbActivate() { System.out.println("ejbActivate"); } public void ejbPassivate() { System.out.println("ejbPassivate"); } public void ejbRemove() { System.out.println("ejbRemove"); } } Here is the CategoryLocal.java: package com; import javax.ejb.CreateException; import javax.naming.NamingException; import java.util.Date; import java.util.Vector; import java.util.Collection; public interface CategoryLocal extends javax.ejb.EJBLocalObject { public Integer getCategoryId(); public void setCategoryId(Integer categoryId); public String getCategoryName(); public void setCategoryName(String categoryName); public String getPicture(); public void setPicture(String picture); public String getPictureWidth(); public void setPictureWidth(String pictureWidth); public String getPictureHeight(); public void setPictureHeight(String pictureHeight); public String getLabelOn(); public void setLabelOn(String labelOn); public String getLabelOff(); public void setLabelOff(String labelOff); public String getButton(); public void setButton(String button); } Here is the CategoryHomeLocal.java: package com; import java.util.Collection; import javax.ejb.CreateException; import javax.ejb.FinderException; public interface CategoryHomeLocal extends javax.ejb.EJBLocalHome { public CategoryLocal create(Integer id) throws CreateException; public CategoryLocal findByPrimaryKey(Integer id) throws FinderException; public Collection findCategory() throws FinderException; public Collection selectProduct(CategoryLocal categoryLocal) throws FinderException; } Any hint would be greatly appreciated. Yours, Frustrated.
  2. Here are the table structure and data: Product Table: ProductId BrandName CategoryId 1 Hush Puppies 2 2 Minnetonka 3 3 Anne klein 1 4 DKNY Mollie 1 5 Bass Kiltie 2 I passed a categoryId of 1 for the above EJB-QL but for some reason, it only returns the first record of the Product Table. It doesn't seem to matter what the value of the CategoryId. That seems strange. Is my EJB/QL correct? Here is the client jsp code that calls the ejb: