Can I use hibernate query language for entities not mapped to a table?

Question

Following is the mySQL query that I am using to retrieve HolidayPackages for a given Hotel:

SELECT 
    pkg.idHolidayPackage, pkg.name
FROM
    holidaypackage pkg
        INNER JOIN
    holidaypackagehotel hph ON pkg.idHolidayPackage = hph.idHolidayPackage
        INNER JOIN
    hotelroom hr ON hr.idHotelRoom = hph.idHotelRoom
WHERE
    hr.idHotel = 1;

I have POJOs with mapping for:

HolidayPackage
Hotel
HotelRoom

I don't have a POJO for HolidayPackageHotel.

Is there any way to use Criteria API or HQL to execute the sql query without creating a POJO for HolidayPackageHotel?

For the curios, DB relations:

score 3 · Accepted Answer · edited May 23 '17 at 12:34

3

No. You can not use the un-mapped entities inside the HQL.

If you want to generate the List of beans from the query you can use the ResultSet transformers, which can convert the query results(object arrays) to beans. By doing this you will save the overhead of creating and filling the POJO beans.

Read here for an example.

edited May 23 '17 at 12:34

Community

1
1

answered Mar 20 '12 at 09:02

ManuPK

11,623
10
57
76

I used the following NamedNativeQuery: @NamedNativeQuery( name = "getHolidayPackageForHotel", query = "SELECT * FROM wah_schema.holidaypackage pkg INNER JOIN wah_schema.holidaypackagehotel hph ON pkg.idHolidayPackage = hph.idHolidayPackage INNER JOIN wah_schema.hotelroom hr ON hr.idHotelRoom = hph.idHotelRoom WHERE hr.idHotel = :idHotel", resultClass= HolidayPackage.class) This seemed to work for me, since the return type: HolidayPackage.class is a POJO entity I have defined. Do you think this approach has any drawbacks ? – brainydexter Mar 20 '12 at 09:05
In any query, **SELECT * from** is not a preferred approach. Add the column names in the select clause if possible. the rest is fine. – ManuPK Mar 20 '12 at 09:41
I get a **Column 'IDHOTELROOM' not found** if I use this: SELECT DISTINCT pkg.idHolidayPackage, pkg.name, pkg.itineraryHeader FROM wah_schema.holidaypackage pkg INNER JOIN wah_schema.holidaypackagehotel hph ON pkg.idHolidayPackage = hph.idHolidayPackage INNER JOIN wah_schema.hotelroom hr ON hr.idHotelRoom = hph.idHotelRoom WHERE hr.idHotel = ? – brainydexter Mar 20 '12 at 10:08
If you are running in to problems with **NamedNativeQuery** You can try the example given [here](http://stackoverflow.com/a/3937519/507864). – ManuPK Mar 20 '12 at 10:14

score -3 · Answer 2 · edited May 29 '13 at 07:27

yes, You can use the un-mapped entities inside the HQL.Below is the example what i did in one of my project.

List list = session.createQuery("select p, a from UserAccount p, Channels a " +  "where p.uid = a.uid").list();  

Iterator iter = list.iterator();  
while (iter.hasNext())  
{  
  Object[] objArray = (Object[]) iter.next();  
  UserAccount p = (UserAccount) objArray[0];  
  Channels a = (Channels) objArray[1];  
  System.out.println(p.getUsername());  
  System.out.println(a.getTitle());  
}

Can I use hibernate query language for entities not mapped to a table?

2 Answers2

Linked