How to write join query in native SQL of Hibernate?

Question

List<AddHotelBean> list=new ArrayList<AddHotelBean>();
List<HotelFacilities> list1=new ArrayList<HotelFacilities>();
public String execute(){

    Configuration cfg=new Configuration();
    cfg.configure();
    SessionFactory sf=cfg.buildSessionFactory();
    Session session=    sf.openSession();

    SQLQuery q=session.createSQLQuery("select d.name,d.country,f.monday,f.tuesday from hotel.hoteldetails1 d , hotel.hotelfacilities1 f where d.hotelid=f.hotelid;");


    q.addScalar("name", StandardBasicTypes.STRING);
    q.addScalar("country", StandardBasicTypes.STRING);
    q.addScalar("monday", StandardBasicTypes.STRING);
    q.addScalar("tuesday", StandardBasicTypes.STRING);

    q.setResultTransformer(Transformers.aliasToBean(HotelFacilities.class));

    List<HotelFacilities> l=q.list();

    for(HotelFacilities a:l) {
        a.getMonday();
        a.getTuesday();
        list1.add(a);
    }

org.hibernate.QueryParameterException: could not locate named parameter [hotelid]

I'm getting this exception plz any one help me

Actually my doubt is i am using two pojo classes but my Transformers.aliasToBean is only one class how it actually take two class thing to print in my output jsp page

thanks in advance.

`

i want name,country from one table and monday,tuesday from one table to be printed on my jsp page — facebook-100000921062287, Jul 20 '14 at 13:20

bitkot · Answer 1 · 2014-07-20T13:25:00.670

0

I think you are confused with the parameters. you call setParameter() when you want to pass some value in the query.

SQLQuery q=session.createSQLQuery("select d.name from hotel.hoteldetails1 d where d.hotelid=:hotelidd");
q.setParameter("hotelid", 1);

You don't require any value in the query so you don't need to call setParamert at all. Try this

SQLQuery q=session.createSQLQuery("SELECT d.name, d.country, f.monday, f.tuesday FROM hoteldetails1 d JOIN hotelfacilities1 f ON d.hotelid=f.hotelid");
q.setResultTransformer(Transformers.aliasToBean(HotelFacilities.class));
List<HotelFacilities> l=q.list();

Edit

Remove ; from the query.

edited Jul 20 '14 at 13:25

answered Jul 20 '14 at 08:34

bitkot

4,466
2
28
39

ya sry for that it was not setParameter it was addScalar plz help me – facebook-100000921062287 Jul 20 '14 at 12:37
can you please provide the table definition? and also what result you are trying to get using the query. – bitkot Jul 20 '14 at 12:59
even though it was not comming – facebook-100000921062287 Jul 21 '14 at 16:20
even though it was not working if you dont mine can u give me any program of join query example – facebook-100000921062287 Jul 21 '14 at 16:31
make sure hoteldetails1 and hotelfacilities1 has hotelid field. – bitkot Jul 21 '14 at 17:43

score 0 · Answer 2 · answered Jul 20 '14 at 08:37

You're using the setParameter method to put values into named parameters, but you're not putting any named parameters into your query string. You use the ":" syntax to do that.

I can't figure out what your query is, so I don't know if you actually want named parameters. Guessing from your result transformer, I think you want a list of HotelFacilities objects.

Also, you're using some very awkward syntax, which I'll replace in this example.

from HotelFacilities f
join HotelDetails d
where d.name = :name
  and d.country = :country
  and f.monday = :monday
  and f.tuesday = :tuesday

This query returns all HotelFacilities where the monday property and tuesday property match the named parmeters, and where the matching HotelDetails matches the provided name adn country properties. You don't have to join on HotelID since (I'm presuming) that's already handled in your mapping.

To set those named parameters in your java code, use the correct typed method, in this case setString.

q.setString("name", detailsName);
q.setString("country", detailsCountry);
q.setString("monday", facilitiesMonday);
q.setString("tuesday", facilitiesTuesday);

even though it was not working if you dont mine can u give me any program of join query example — facebook-100000921062287, Jul 21 '14 at 16:28
You mean, like the one in the answer? And what isn't working? — Paul Hicks, Jul 22 '14 at 19:34

score 0 · Answer 3 · answered Sep 09 '14 at 06:43

//how we write join query in jdbc is same as hibernate

//local level
ArrayList<Object[]> data = new ArrayList<Object[]>();
Map request;


//method level
SQLQuery q=session.createSQLQuery("select  h.roomid as roomid, h.phone1 as     phone1,h.phone2 as phone2,r.type as type,r.nrooms as nrooms from hoteldetails1 h,roomdetails1 r where h.roomid=r.roomid ");

    System.out.println("after query");
    //q.setParameter(0,roomid);


    q.addScalar("roomid",StandardBasicTypes.INTEGER);
    q.addScalar("phone1",StandardBasicTypes.STRING);
    q.addScalar("phone2",StandardBasicTypes.STRING);
    q.addScalar("type",StandardBasicTypes.STRING);
    q.addScalar("nrooms",StandardBasicTypes.STRING);

    List<Object[]> l=q.list();

    System.out.println("b4 for loop");
    for(Object[] obj:l){



        data.add(obj);
        request.put("l",data);

    }

  return "success";


 /*
 in the jsp page how to retrive the output is given below
*/

//in jsp page code is like this
<%


List<Object[]> l =(List) request.getAttribute("l");



%>

 <table border=1>
 <tr>
 <th>roomid</th>
 <th>phone1</th>
 <th>phone2</th>
 <th>type</th>
 <th>rooms</th>

 </tr>  
 <%
  for(Object[] obj:l){


  int i=(Integer)obj[0];
  String j=(String)obj[1];
  String k=(String)obj[2];
  String r=(String)obj[3];
  String m=(String)obj[4];
  out.println(obj[0] +"   "+i);
  out.println(obj[1] +"   "+j);
  out.println(obj[2] +"   "+k);
  out.println(obj[3] +"   "+r);
  out.println(obj[4] +"   "+m); 

    %>

How to write join query in native SQL of Hibernate?

3 Answers3