Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 2.1.8

Name and version of the database you are using: Oracle 9i

Hi,

Our projcet is now completely using Hibernate for all the DB data fetch. But now we got a few new requerements to generate reports which involves a lot of data.

For these reports we have a few SQL queries which invloves joining of 2 or 3 tables and complex where conditions. We may need to add sql hints also for the performance tuning. As we are using hibernate for all other operations, here also we are planning to use Native SQL query feature of Hibernate. I did a basic Native SQLs using hibernate. But I'm not able to go ahead using joining tables and fetching collection of data. Any help will be appreciated. Thanks a lot.

Any code sample using native SQLs (joining tables) will be very helpful.

Sooraj

I think you won't get enough information about joins here. There are several types of joins and things about joins you should know. Try to use google in order to find some resources.

For some reasons you should prefer the ANSI SQL syntax to the "old" style (which uses the join condition in the WHERE clause), but pay attention that your database has to support this standard form of writing joins (e.g. Oracle does not support ANSI SQL joins prior to 9i).

The "old" join style (in this case an inner join) looks like this:


Here is the same example with an ANSI SQL join:


Best regards
Sven

Thanks for the reply.

I think my query was a bit misleading.

I know native sql joins very well.. but dont how to implement those joins in hibernate.

I've two table

EMP
----

EMP_ID
EMP_NAME

DEPT
--------
DEPT_ID
EMP_ID

and my sql query in oracle sql is

Select Emp.*, DEPT.* from EMP, DEPT where EMP.EMP_ID=DEPT.EMP_ID.

How to implement this in hibernate?

Assuming your classes are called Emp and Dept, it could be -

List list = session.createSQLQuery("select {e.*}, {d.*} from EMP {e}, DEPT {d} where {e}.EMP_ID={d}.EMP_ID")
.addEntity("e", Emp.class)
.addEntity("d", Dept.class)
.list();

see
http://www.hibernate.org/hib_docs/v3/re ... -scrolling
for explanations.

Wow!! thats really cool.

That link is really helpful.

So if we use multiple table joins in query, is it possible to set where conditions dynamically??

I saw createcriteria.. but will that work with multiple tables?

If you mean...put in parameters, then yes, that is ...

List list = session.createSQLQuery("select {e.*}, {d.*} from EMP {e}, DEPT {d} where {e}.EMP_ID={d}.EMP_ID" and {e}.EMP_NAME = :name)
.addEntity("e", Emp.class)
.addEntity("d", Dept.class)
.addParameter("name","aname")
.list();

thanks a lot..
this what I was looking for.