We help IT Professionals succeed at work.

converting an sql join query into hibernate named query

prsubject
prsubject used Ask the Experts™
on
I have Three tables.
Equipment , Cost, City
Data is retrived from these three tables  using the following  SQL Query

Select e.modelName, c.Name from Eqipment e,City c, Cost co where e.column2=co.column1 and co.column2=c.column3
and c.name='London'

I have written Equipment , Cost and City Entity Classes .

I am  facing  problem  in writting an Hibernate Named Query for the above SQL Query.Please Guide me.

Thanks in advance for the Help.



 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
C# ASP.NET Developer
Top Expert 2010
Commented:
try this
var q = (from e in Equipment
                     join co in Cost
                     on e.column2 equals co.column1 
                     join c in City
                     on co.column2 equals c.column3  
                     where c.name == "London"
                     select new {
                         e.modelname,
                         c.name
                     }).ToList();

Open in new window

Author

Commented:
How should i write if i want the Named Query  to return an Equipment Object as written type..  
 
 
select e
from Equipment e,
Cost co,
City c
where
e.column2=co.column1
and co.column2=c.column3
and c.name='London'
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
I am not sure I understand the question, but if you want the equipment object,
instead of
 select new {...}).ToList();

just use
select e).ToList();
Can u post ur Named Query ??
or u want to write a new one ??

Author

Commented:
Here is the named query I have written. I am passing equipid and costnum as parameters

<sql-query name="validateEquipAddition">
        <return alias="equip" class="com.main.Equipment"/>
        <![CDATA[SELECT * FROM Equipment e WHERE e.column2 = :equipID
                 AND cost_id = (SELECT cost_id FROM Cost c WHERE c.cost_nbr = :costnum
                 AND city_id = (SELECT city_id FROM city ct WHERE ct.city_code = 'LONDON'))]]>
    </sql-query>
did you tried the query that i had posted?

Author

Commented:
no not yet. I was busy with an other task. I switched to this task now only. I will do it now.

Author

Commented:

 public Session getSession()
    {
         Application.showMessage("Entered root base class....");

       
         Session session=HibernateUtil.getSession();
Application.showMessage("*************Session  object in base calss is ...."+session.toString());

}
It was not connecting .As i tried to debug. In the base class which is abstract class   I have the following method getSession. It is displaying only the first statement "Entered root base class....". The next is just not getting displayed. As I entered debugger statements in the getSession() method of HibernateUtil, no debugger statement is getting printed. I removed the static clauses and instantiated the hibernate util. The temp object is just not shown. All this is in the root class which is an abstract class.


        /*HibernateUtil temp = new HibernateUtil();*/
    //    Application.showMessage("HibernateUtil object...."+temp);   //nothing is displayed here
this is a different problem now....how ever please post the source code of HibernateUtil class
AND cost_id, AND city_id
 
is should like this
AND e.cost_id, AND e.city_id
 
and check equip is there  com.main.Equipment  bean

Author

Commented:
Thanks.