Hello,
I have a rather quick question on the topic of "Can Hibernate do this?"
I want to use Hibernate mainly for database independence for all projects with some ORM for some of our newer projects. The only problem I come across is not being able to do "left joins" without creating associations (we receive the "Path expected for join!" error).
The problem with not having associations is we have some pretty complex SQL queries in some of our applications. For example:

select a.PRTNR_TB_PRTNR_ID,
a.APPLICATION_ID_TB_APPLICATION_ID,
a.TRANSACTION_TYPE_TB_TRANSACTION_TYPE_ID,
a.PYR_ID,
SUM(a.NUMBER),
b.TRANSACTION_RATE,
b.PASS_THRU_RATE,
case when c.PRTNR_TB_PRTNR_ID > 0 then 'DIRECT' else 'INDIRECT' end
from DAILY_TRANSACTION_TB a
left join PRTNR_PYR_RATE_TB b
with a.PRTNR_TB_PRTNR_ID = b.PRTNR_TB_PRTNR_ID AND
a.APPLICATION_ID_TB_APPLICATION_ID=b.APPLICATION_ID_TB_APPLICATION_ID AND
a.PYR_ID=b.PYR_ID AND
b.RANGE_MIN <= ? AND (b.RANGE_MAX >= ? OR b.RANGE_MAX=-1) AND
b.ACTIVE=TRUE
left join PRTNR_DIRECT_PYR_LIST_TB c
with a.PRTNR_TB_PRTNR_ID = c.PRTNR_TB_PRTNR_ID and
a.PYR_ID=c.PYR_ID
WHERE DATE >= ? AND DATE <= ? AND a.PRTNR_TB_PRTNR_ID=? AND TRANSACTION_TYPE_TB_TRANSACTION_TYPE_ID <1000
GROUP BY
a.PRTNR_TB_PRTNR_ID,a.APPLICATION_ID_TB_APPLICATION_ID,a.TRANSACTION_TYPE_TB_TRANSACTION_TYPE_ID,a.PYR_ID

Basically to map the tables in this database (containing 40 + tables) we would have to map every table to every other table and different SQL queries are joining the tables on different critiera. For example some queries join the DAILY_TRANSACTION_TB table to the PRTNR_DIRECT_PYR_LIST_TB on the PRTNR_TB_PRTNR_ID columns but others will join by the PYR_ID and the one in this example joins on both.

We can not use inner joins (also called theta-style joins) because they do not return the same result set as the left joins.

Is there a way to do complex SQL queries like this one without associations being defined in the table configuration file. Looking through this forum and on the internet I do not see that it is possible, if it is not does anyone know of a product that can.

The primary purpose is to make all of our java applications database independent even if the table/column names are between the different databases (MySQL, DB2, Oracle).

Any help or advice would be greatly appreciated

Jon

As far as I know, Hibernate does not allow joins not specified in the mapping files (they call them "theta-joins") unless they are inner joins.

Depending on your design, you could overcome this limitation by creating a view that does the joins you want within the database, and then map a Hibernate entity to that. This mapping would be read-only, of course, but you could have normal entity mappings aside from it.