Hibernate inner join mapping - string where id.

Question

Hi, I'd like to map the following sql in NHibernate. Will I need to make a separate entity object i.e RoomTypeVO mapped to tb_tags to do this? Any help much appreciated.

SELECT dbo.tb_rooms.id, dbo.tb_rooms.name, dbo.tb_tags.name AS 'roomType' FROM dbo.tb_rooms INNER JOIN dbo.tb_tags ON (dbo.tb_rooms.typeID = dbo.tb_tags.id)

<id name="id" column="id">

  <generator class="native" />

</id>

<property name="name" />

Answer 1

If you to a straight sql query you do not have to. If you want to use HQL you will have to work with an entity.

But, you can always do sql queries directly.

If you have a mapped entity then you could probably just do something like this:

FROM RoomType

Answer 2

When you refer to 'FROM', are you thinking of something like this?

<property name="totalPrice"
formula="( SELECT SUM (li.quantity*p.price) FROM LineItem li, Product p
            WHERE li.productId = p.productId
            AND li.customerId = customerId
            AND li.orderNumber = orderNumber )"/>