HQL one-to-many with where clause

tryingtogetthis · **Joined:** Thu Jan 03, 2008 3:05 pm **Posts:** 6

Hi All,

I have the following:

Store object which has a one-to-many using a bag to Customers.

How can I get the Store object along with a specific customer ID.

Basically my method is the following

public Store getStore(int storeId, int customerId)

Of course I can just do a get

hibernate.get<Store>(storeId), but that returns all the customers, where I want to limit the customer returned to 1 (matching the customerId provided).

Thank you.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Code:

select stor, cust
from Store stor
join stor.customers cust
where cust.customerId='your cust Id'

this will return an array of objects, hopefully of row size=1, with the first object being a Store bean, and the second being a Customer bean. You will have to cast them.

tryingtogetthis · **Joined:** Thu Jan 03, 2008 3:05 pm **Posts:** 6

So by doing the above HQL you are basically overriding how the customers should be retrieved?

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

No, I am not overriding anything.
I don't quite understand your question, but if you want both Store and Customer information, Hibernate returns that in Object[] form.
Is that what you are asking?

tryingtogetthis · **Joined:** Thu Jan 03, 2008 3:05 pm **Posts:** 6

I was actually hoping to get an Store object with the right customer being the only customer in the Customers collection.

However as you mentioned I have an object[] with the 1st element being the store and the 2nd element being the customer. Its the right data but not in the format I was hoping for.

Is there anything I can do to avoid dealing with a object[].

Thanks.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

No, for the following reason:
Although you and I know that you will receive only one Customer and Only one store, Hibernate doesn't. Therefore, Hibernate needs to return things in such a format that allows it to return 1 as well as many rows, of any kind of bean.

You could change the design of your application so that the relation between a customer and a Store be necessarily one-to-one.
If so, given a Store bean, Customer would be a property of it, so that you could access the customer in the following way:

Code:

  Store store;
  ...
  Customer cust=store.getCustomer();

or viceversa

Code:

  Customer cust;
  ...
  Store stot=customer.getStore();

But, philosophically speaking, that doesn't look like very sound design, unless those customers have some sort of contract of exclusivity with a given store.

tryingtogetthis · **Joined:** Thu Jan 03, 2008 3:05 pm **Posts:** 6

Would it be a bad design to do the following

Code:

Store s = (Store) returnObject[0];
Customer c = (Customer) returnObject[1];

s.customers.add(c);

return s;

Or is that just too hacky?

Thank you

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

The casting is correct.

However, if there were already a "customers" collection (both mapped and property) hanging from "store", and that was returned by your SQL, why would you want to further "add" the same customer to the store's customer collection? It doesn't make sense.

Tryingtogetthis, asking around is a great way to get u to speed, but if you want to further discuss this, you have to provide me some reference (table diagrams, classes, mapping files) and/or what you are trying to achieve.

Regards