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:

Solution

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.

Comments

[brainydexter]

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 ?

[ManuPK]

In any query, SELECT * from is not a preferred approach. Add the column names in the select clause if possible. the rest is fine.

[brainydexter]

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

[ManuPK]

If you are running in to problems with NamedNativeQuery You can try the example given here.