I wish to execute a query containing an outer join of two not-really-related tables. By "not-really-related" I mean that there is a many-to-many relationship between two tables in which there may or may not be a coincidental match between two columns. (A user may match 0-to-many Speed Dial names to a Telephone Number and may make any number calls to numbers that happen to be referenced to 0 or more speed dial entries). As such, my inclination is to avoid expressing any relationship between the two tables in my Hibernate mapping files - no such relationship is maintained in the database.

I looked into writing such a query in HQL. In HQL, according to "Java Persistence with Hibernate", all the join styles except "Theta-style joins" (pp 652-653) seem to imply mapping relationships. However, p.653 gives me the bad news that "it's currently not possible in HQL or JPA QL to outer join two tables that don't have a mapped association -- theta style joins are inner joins". So if this is correct, HQL is a non-starter for my purposes.

I next looked into a SQL query. I created such a query that performs the outer join I need and it works. I am pretty sure I could implement this as a projection and mess around by hand with the result sets, but I would rather have the result set objects be mapped to POJOs by Hibernate if possible.

How can I map them, though? Mapping as a class demands that I define an ID and there really isn't one. These objects will simply be presented to the user and go away. They will never be persisted. I don't see an ID generator that fits such an oddball use case. The best I have been able to come up with so far is to grab the primary key for the main class in the outer join even though I don't actually need it and call that the id.

I come up with the following:


But when I call this query (against a MySQL 5.0 database), I get the following error.

"Column 'CallStatID24_0_' not found"

As I stated, the sql query works standalone. Why is Hibernate looking for "CallStatID24_0_" rather than just "CallStatID" and how do I get around this? OR, is there a better strategy for doing this that I've completely overlooked here?

I have found a way to get beyond this. It's not perfectly ideal but it's close.

That way is to abandon any attempt to map the <return> elements in the query:



and use the Transformations API instead:


This would be perfectly acceptable except that the CallHistoryRecord members include a couple of special types that I wish to map to (enums, a special telephone number class). I've sort of hacked this in the POJO itself. Are there any code samples of writing your own transformer to handle this properly?