We help IT Professionals succeed at work.

converting an sql join query into hibernate named query

prsubject used Ask the Experts™
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.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
C# ASP.NET Developer
Top Expert 2010
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 {

Open in new window


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
and co.column2=c.column3
and c.name='London'
Adam MenkesC# ASP.NET Developer
Top Expert 2010

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 ??


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'))]]>
did you tried the query that i had posted?


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



 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