This week's book giveaway is in the Other Languages forum.
We're giving away four copies of Rust Web Development and have Bastian Gruber on-line!
See this thread for details.
Win a copy of Rust Web Development this week in the Other Languages forum!
  • 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:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

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
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic