• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

EJB QL RETURNING ONLY THE FIRST RECORD OF THE TABLE

 
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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"?>

<!DOCTYPE ejb-jar PUBLIC
"-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN"
"http://java.sun.com/dtd/ejb-jar_2_0.dtd">

<ejb-jar>
<enterprise-beans>
<entity>
<ejb-name>CategoryEJB</ejb-name>
<local-home>com.CategoryHomeLocal</local-home>
<local>com.CategoryLocal</local>
<ejb-class>com.CategoryBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
<reentrant>False</reentrant>
<cmp-version>2.x</cmp-version>
<abstract-schema-name>Category</abstract-schema-name>
<cmp-field><field-name>categoryId</field-name></cmp-field>
<cmp-field><field-name>categoryName</field-name></cmp-field>
<cmp-field><field-name>picture</field-name></cmp-field>
<cmp-field><field-name>pictureWidth</field-name></cmp-field>
<cmp-field><field-name>pictureHeight</field-name></cmp-field>
<cmp-field><field-name>labelOn</field-name></cmp-field>
<cmp-field><field-name>labelOff</field-name></cmp-field>
<cmp-field><field-name>button</field-name></cmp-field>

<primkey-field>categoryId</primkey-field>
<security-identity><use-caller-identity/></security-identity>
<query>
<query-method>
<method-name>findCategory</method-name>
<method-params></method-params>
</query-method>
<ejb-ql>
SELECT OBJECT(c) FROM Category c
</ejb-ql>
</query>
<query>
<query-method>
<method-name>ejbSelectProduct</method-name>
<method-params>
<method-param>com.CategoryLocal</method-param>

</method-params>
</query-method>
<ejb-ql>
SELECT OBJECT(p) FROM Product p
WHERE p.homeCategory = ?1
</ejb-ql>
</query>
</entity>
<entity>
<ejb-name>ProductEJB</ejb-name>
<local-home>com.ProductHomeLocal</local-home>
<local>com.ProductLocal</local>
<ejb-class>com.ProductBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
<reentrant>False</reentrant>
<cmp-version>2.x</cmp-version>
<abstract-schema-name>Product</abstract-schema-name>
<cmp-field><field-name>productId</field-name></cmp-field>
<cmp-field><field-name>brandName</field-name></cmp-field>
<cmp-field><field-name>productDescription</field-name></cmp-field>
<cmp-field><field-name>purchasePrice</field-name></cmp-field>
<cmp-field><field-name>categoryId</field-name></cmp-field>
<primkey-field>productId</primkey-field>
<security-identity><use-caller-identity/></security-identity>
<!-- <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>
-->
</entity>
</enterprise-beans>
<relationships>
<ejb-relation>

<ejb-relation-name>Product-HomeCategory</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>
Product-has-a-Category
</ejb-relationship-role-name>
<multiplicity>one</multiplicity>
<relationship-role-source>
<ejb-name>ProductEJB</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>homeCategory</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>
Category-belongs-to-Product
</ejb-relationship-role-name>
<multiplicity>many</multiplicity>
<cascade-delete/>
<relationship-role-source>
<ejb-name>CategoryEJB</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>
</relationships>
<assembly-descriptor>
<security-role>
<role-name>Employees</role-name>
</security-role>
<method-permission>
<role-name>Employees</role-name>
<method>
<ejb-name>CategoryEJB</ejb-name>
<method-name>*</method-name>
</method>
</method-permission>
<container-transaction>
<method>
<ejb-name>CategoryEJB</ejb-name>
<method-name>*</method-name>
</method>
<method>
<ejb-name>ProductEJB</ejb-name>
<method-name>*</method-name>
</method>
<trans-attribute>Required</trans-attribute>
</container-transaction>
</assembly-descriptor>

</ejb-jar>

Here is the weblogic-cmp-rdbms.xml:
<!DOCTYPE weblogic-rdbms-jar PUBLIC
'-//BEA Systems, Inc.//DTD WebLogic 8.1.0 EJB RDBMS Persistence//EN'
'http://www.bea.com/servers/wls810/dtd/weblogic-rdbms20-persistence-810.dtd'>
<weblogic-rdbms-jar>

<weblogic-rdbms-bean>
<ejb-name>CategoryEJB</ejb-name>
<data-source-name>ShoeStore</data-source-name>
<table-map>
<table-name>category</table-name>
<field-map>
<cmp-field>categoryId</cmp-field>
<dbms-column>categoryID</dbms-column>
</field-map>
<field-map>
<cmp-field>categoryName</cmp-field>
<dbms-column>categoryName</dbms-column>
</field-map>
<field-map>
<cmp-field>picture</cmp-field>
<dbms-column>Picture</dbms-column>
</field-map>
<field-map>
<cmp-field>pictureWidth</cmp-field>
<dbms-column>PictureWidth</dbms-column>
</field-map>
<field-map>
<cmp-field>pictureHeight</cmp-field>
<dbms-column>PictureHeight</dbms-column>
</field-map>
<field-map>
<cmp-field>labelOn</cmp-field>
<dbms-column>LabelOn</dbms-column>
</field-map>
<field-map>
<cmp-field>labelOff</cmp-field>
<dbms-column>LabelOff</dbms-column>
</field-map>
<field-map>
<cmp-field>button</cmp-field>
<dbms-column>Button</dbms-column>
</field-map>
</table-map>
<automatic-key-generation>
<generator-type>SQL_SERVER</generator-type>

</automatic-key-generation>
</weblogic-rdbms-bean>

<weblogic-rdbms-bean>
<ejb-name>ProductEJB</ejb-name>
<data-source-name>ShoeStore</data-source-name>
<table-map>
<table-name>PRODUCT</table-name>
<field-map>
<cmp-field>productId</cmp-field>
<dbms-column>productId</dbms-column>
</field-map>
<field-map>
<cmp-field>categoryId</cmp-field>
<dbms-column>categoryId</dbms-column>
</field-map>
<field-map>
<cmp-field>brandName</cmp-field>
<dbms-column>brandName</dbms-column>
</field-map>
<field-map>
<cmp-field>productDescription</cmp-field>
<dbms-column>productDescription</dbms-column>
</field-map>
<field-map>
<cmp-field>purchasePrice</cmp-field>
<dbms-column>purchasePrice</dbms-column>
</field-map>

</table-map>

<!-- Automatically generate the value of ID in the database on inserts using sequence table -->
<automatic-key-generation>
<generator-type>SQL_SERVER</generator-type>

</automatic-key-generation>

</weblogic-rdbms-bean>

<weblogic-rdbms-relation>
<relation-name>Product-HomeCategory</relation-name>
<weblogic-relationship-role>
<relationship-role-name>Category-belongs-to-Product</relationship-role-name>
<relationship-role-map>
<column-map>
<foreign-key-column>categoryId</foreign-key-column>
<key-column>productId</key-column>
</column-map>
</relationship-role-map>
</weblogic-relationship-role>
</weblogic-rdbms-relation>
</weblogic-rdbms-jar>

Here is the weblogic-ejb-jar.xml:
<?xml version="1.0"?>

<!DOCTYPE weblogic-ejb-jar PUBLIC "-//BEA Systems, Inc.//DTD WebLogic 8.1.0 EJB//EN" "http://www.bea.com/servers/wls810/dtd/weblogic-ejb-jar.dtd">

<weblogic-ejb-jar>

<weblogic-enterprise-bean>

<ejb-name>CategoryEJB</ejb-name>

<entity-descriptor>

<entity-cache>
<max-beans-in-cache>100</max-beans-in-cache>
</entity-cache>

<persistence>
<persistence-use>
<type-identifier>WebLogic_CMP_RDBMS</type-identifier>
<type-version>6.0</type-version>
<type-storage>META-INF/weblogic-cmp-rdbms-jar.xml</type-storage>
</persistence-use>
</persistence>

</entity-descriptor>

<local-jndi-name>CategoryHomeLocal</local-jndi-name>

</weblogic-enterprise-bean>

<weblogic-enterprise-bean>

<ejb-name>ProductEJB</ejb-name>

<entity-descriptor>

<entity-cache>
<max-beans-in-cache>100</max-beans-in-cache>
</entity-cache>

<persistence>
<persistence-use>
<type-identifier>WebLogic_CMP_RDBMS</type-identifier>
<type-version>6.0</type-version>
<type-storage>META-INF/weblogic-cmp-rdbms-jar.xml</type-storage>
</persistence-use>
</persistence>

</entity-descriptor>

<local-jndi-name>ProductHomeLocal</local-jndi-name>

</weblogic-enterprise-bean>
<!-- Map the ejbbook user to the Employees role -->
<security-role-assignment>
<role-name>Employees</role-name>
<principal-name>ejbbook</principal-name>
</security-role-assignment>

</weblogic-ejb-jar>

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.
 
John Smith
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here are the table structure and data:
Product Table:

ProductIdBrandNameCategoryId
1Hush Puppies2
2Minnetonka 3
3Anne klein1
4DKNY Mollie1
5Bass 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:



<%
Context jndiContext = getInitialContext();
Object objCategory = jndiContext.lookup("CategoryHomeLocal");
CategoryHomeLocal categoryHome = (CategoryHomeLocal)objCategory;
Integer categoryId = new Integer(categoryIdString);
CategoryLocal category = categoryHome.findByPrimaryKey(categoryId);
Collection products = categoryHome.selectProduct(category);
Iterator iterator = products.iterator();
%>
reply
    Bookmark Topic Watch Topic
  • New Topic