getting data from 2 tables using 2 DTOs

[sai kinnera]

Hi,
Can anybody please explain me how data can be obtained using Hibernate3 if I have 2 tables mapped to 2 separate DTOs. I am already making queries to these two tables separately. But now I have a query that joins these 2 tables.

Thanks in advance,
Sai

[Ghulam Rashid]

Probably, use of Collection object will help you.
Suppose, you have two DTO ProductDTO and OrderDTO. Then in DAO, after database query, populate the data for these two DTO and store them in Collection Object and return one Collection object(may be ArrayList/HashSet) which have both DTOs to the caller and caller will extract the data from collection->DTO.

I hope this will help.

[Paul Sturrock]

Have a look at the Hibernate documentation regarding associations and how they can be used in HQL. This should give you an idea.

[sai kinnera]

Hi guys,
May be I was not clear on my question. If I have a query like ' from OneDTO as one, TwoDTO as two where one.id=two.key(+) i.e.,an outer join with + sign, is not working in HIbernate3. It used to work in Hibernate 2.1. It says unexpected token '+'. How do I replace this (+).
Thanks,
Sai

[Ghulam Rashid]

Your outer(+) join is more specific to vendor like Oracle. Other database does not support + for join. I am not sure abt ver 2 or 3.

Probably, you need to write your database neutral query as -

"from OneDTO as one left outer join fetch one.TwoDTO as two WHERE ine.id = 1"
Hope you have the proper relationship between one and two dto.

For detail look into Hibernate Reference document for the ANSI Join Syntax.

The supported join types are borrowed from ANSI SQL
� inner join
� left outer join
� right outer join
� full join (not usually useful)

[sai kinnera]

Hi Rashid,

I tried the following way by avoiding + sign: ' from OneDTO as one left outer join TwoDTO as two where one.id=two.key'. But it gave the Exception as 'Path expected for join!'. I found that my two tables do not have exact PK-FK relationship. Is there a way out.
Thanks,
Sai

[Ghulam Rashid]

I found that my two tables do not have exact PK-FK relationship. Is there a way out.

Even if you dont have PK-Fk R-Ship it will work.

one.id=two.key

Are you using composite PK? Then you need to provide comp pk property.

[sai kinnera]

Hi Rashid,
I am not using any composite id, but one.id is not a PK in one table and two.key is not a PK in two table. I am trying to fetch name of a person which is in the second table against loginId which is there in both tables.

Thanks,
Sai