Hibernate version 3.05:

Oracle version 9.2:

I am quite new to Hibernate and have stumbled right into a special situation.

I need to use native SQL to execute a query. The objects to be returned are instances of a subclass (say DomesticCat), based on the DB tables DBCAT and DBDOMCAT, joined on the column CatId.

I can use HQL without problems, all the necessary mappings are in place for that, but I need to use native SQL in order to specify an SQL hint.

My query would look somthing like this:

session.createSQLQuery(
"select {cat.*}, {domcat.*} from DBDOMCAT domcat
INNER JOIN DBCAT cat ON cat.catid=domcat.catid
WHERE cat.age > 5 and domcat.name like 'A%' ").
addEntity("domcat", DomesticCat.class).
list();

I need to join the tables in order to fully populate the DomesticCat object and also to specify search criteria on both tables. However Hibernate does not know the alias 'cat'. Is it at all possible to do what I am trying to do? If so, I hint or a link will be much appreciated.

- Thanks