I am converting an existing project over to using Hibernate by converting existing JDBC calls into HSQL and I have run into SQL query that I cannot figure out how to do in Hibernate. I have spent 2 days trying to figure this out with no luck.

I am trying to select all current form version (i.e. max form version) with a past release date. (A form with a release date in the future means the form has not yet come into effect).

Here is an example of the data in the database:



Here is the expected result.



I am familiar with SQL but am new to Hibernate. In SQL this would look like:

SELECT T2.template_id, T2.form_type, T2.form_version, T2.release_date FROM
(
SELECT form_type, max(form_version) AS form_version FROM Pp_Template_Form WHERE release_date <= sysdate GROUP BY form_type
)
T1 INNER JOIN Pp_Template_Form T2 ON T1.form_type = T2.form_type AND T1.form_version = T2.form_version ORDER BY form_type ASC


From what I can tell, Hibernate doesn't seem to support joins on subqueries. This certainly did not work:

from ( select formType, max(formVersion) from FwTemplateForm where releaseDate <= current_date() group by formType ) t1 inner join template.self t2 on t1.formType = t2.formType and t1.formVersion = t2.formVersion order by formType asc

Produces the following exception:
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 6 [from ( select formType ...

I have figured out how to do joins... and I have figured out how to do subqueries... but I have not figured out how to do both joins and subqueries at the same time.

Is this query possible to do in Hibernate (without using native SQL)? Anyone have any suggestions on how I should do this?

The mapping I am using is: