Forums Register Login

help regarding EJB-QL

+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
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
pie. tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 657 times.
Similar Threads
Out of Memory when testing a BMP Bean
ques about EJB-QL
composite entity pattern
EJB QL RETURNING ONLY THE FIRST RECORD OF THE TABLE
Displaying images dynamically from database
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 19:09:54.