• 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

help regarding EJB-QL

 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi guys,
I need help in writing an EJB-QL. I have 3 tables...


Prodcat
-------
categoryid
productid

Product
-------
id
barcode
description

Store_product
-------------
id
storeid
productid
price


Prodcat * --------------- 1 Product 1 ------------------- * Store_product

The prodcat contains all product id along with their category id
The product table contains each product description
The store_product contains a storeid, productid and price. This table is used because the product can exist in multiple stores and the prices can vary for each store.

I have got a session bean (facade) which has got a lookup for the prodcat. I have created a relationship
also for all the tables.

Problem 1:-
-----------
Given a categoryid, display details about all the products.

I wrote a select method in prodcat with the following EJB-QL :-
" select object(p) from Prodcat c, IN(c.product) p where c.categoryid=?1 "

THIS WORKS FINE. I get a local home for product and then uses it's getter methods to get the details.

Problem 2:-
-----------
Given a categoryid and storeid, display details about all the products along with the prices,
belonging to that particular store..

This is where I am stuck now . Will I be able to write this in one EJB-QL. Because I can only return a localhome for
product only... hwich deosn't contain the price from the other table.

Or do I need to first get the products
and then loop thru it and do a lookup in store_products ???

I tried
"select object(p) from prodcat c IN(c.prodcat) p , IN(p.storeproduct) s where p.id=s.productid and c.catid=?1 and s.id=?2"


Your help will be much appreciated.

regds
Giju
[ October 18, 2004: Message edited by: Giju George ]
 
Ranch Hand
Posts: 1209
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Not sure if this makes sense,

select object(sp) from Store_Product sp , IN (sp.product.categories) category where sp.storeid = ?1 and category.id = ?2

I need to get to Store_Product since i want the price for a product for a given store.

- this will return a collection of Store_products.

Once I have those rows selected , I can get to the product by running through the collection and accessing the corresponding product CMR?

i mean sp.product should get me to product details
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic