[schanamolu]

Guys,

Can you please post sampleCode (HQL: The Hibernate Query Language)
how to respresent Oracle outer join in hibernate using following Oracle SQL

select d.company_id, d.app_id, d.acm_service_id from acm_service d, company e, application f where
d.company_id = e.company_id
and d.app_id = f.app_id (+)

Appreciated early response

Thanks,
Sreedhar

[Haris Peco]

Sreedhar,

(+) syntax for outer join is Oracle specific syntax.You can try ‘left outer join’/’right outer join’ (or ‘right outer join’) in hql.
join syntax is ansi sql syntax and it will work in newer version of Oracle as well (>= 9)

For example,

select …
from acm_service d inner join company e on (d.company_id=e.company_id)
right outer join application f on (d.app_id = f.app_id)

Regards

[schanamolu]

Hi Peco,

I wrote the code for Oracle 9i.

select d.company_id, d.app_id, d.acm_service_id
from company e, acm_service d left outer join application f on d.app_id = f.app_id
where d.company_id = e.company_id

When i execute it works fine.

Can you please provide some sample code equavalent in HQL. I know (HQL) left join fetch = LEFT OUTER JOIN (Oracle 9i)

Iam looking from Hibernate Perspective what are things need to taken care. If you have some sample examples, can you please provide ???

Thanks,
Sreedhar

[Haris Peco]

Sreedhar,

It depends how you map tables in hibernate, but syntax (and context) is same :
hibernate supports left outer join and right outer join (left join is only shortcut for left outer join).
You have made hql query closed to oracle’s sql query if you use ansi sql from oracle 9i (or higher).

If you send your hbm mapping (and create table/pk ddl), I can try make some example for you.

Regards

[schanamolu]

Hi Peco,

StringBuffer sb = new StringBuffer(100);

sb.append(“select d ” +
“from com.cvg.ap.ws.model.businessobject.Company as e, ” +
“com.cvg.ap.ws.model.businessobject.AcmService as d ” +
“left join fetch ” +
“com.cvg.ap.ws.model.businessobject.Application as f ” +
“on d.application=f.appId ” +
“where d.company = e.companyId ”
);

// sb.append(” AND a.userId = “);
//
// sb.append(“‘”);
// sb.append(users.getUserId());
// sb.append(“‘”);

Query query = session.createQuery(sb.toString());
List list = query.list();

I got the following exception when It try to execute piece of code List list = query.list();

unexpected token: on near line 1, column 183 [select d from com.cvg.ap.ws.model.businessobject.Company as e, com.cvg.ap.ws.model.businessobject.AcmService as d left join fetch com.cvg.ap.ws.model.businessobject.Application as f on d.application=f.appId where d.company = e.companyId ];

nested exception is org.hibernate.hql.ast.QuerySyntaxError: unexpected token: on near line 1, column 183 [select d from com.cvg.ap.ws.model.businessobject.Company as e, com.cvg.ap.ws.model.businessobject.AcmService as d left join fetch com.cvg.ap.ws.model.businessobject.Application as f on d.application=f.appId where d.company = e.companyId ]

Caused by: line 1:183: unexpected token: on
at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1380)
at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1053)
at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:759)
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:611)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:263)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:150)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:209)
… 42 more

Corresponding Oracle SQL Query in SQL EDITOR works fine.

select d.* from company e, acm_service d left outer join application f on d.app_id = f.app_id
where d.company_id = e.company_id

Please let me know.

Thanks,
Sreedhar

[Haris Peco]

Your query depends from your mappings, but I will try help you :

I suppose that you have
– many-to-one relation ‘application’ from AcmService to Application
– many-to-one relation ‘company’ from AcmService to Company

you can make this query :

select d
from AcmService d join d.company left outer join d.application
where d.xxx=’yyy’
…

Hibernate will make correct query if you have collection instead of many-to-one mapping as well.

I can’t make correct query without mappings (and usually table ddl)
I suggest you that you try different combination in MyEclipse HQL editor.It is easier than write samples in Java and you can see generated SQL in
‘Hibernate Dynamic Query Translator’ view (Hibernate perspective)

Regards,

[waleeed]

Can you please post sampleCode (HQL: The Hibernate Query Language)
how to respresent Oracle outer join in hibernate using following Oracle SQL.

___________________________
waleeed

[Author]

Posts