How can I create SELECT FROM SELECT query with JPA and Hibernate?


#1

Hello. I need create query

SELECT *
FROM (SELECT ...) alias
WHERE alias.SomeProperty = someValue AND alias.OtherProperty = otherValue

But QueryOver.WithSubquery creates query SELECT * FROM table WHERE someProperty = (SELECT …)

Help me please


#2

Neither JPQL or Criteria supports derived tables. Just use the native SQL which allows you to benefit from any database-specific feature or syntax.


#3

Just recently Jan-Willem Gmeilig Meyling was introduced on the Hibernate blog who wrote about exactly that use case and how Blaze-Persistence’s support for CTEs can help you with this:
http://in.relation.to/2018/08/07/meet-jan-willem-gmelig-meyling/

See here for examples: https://persistence.blazebit.com/documentation/1.3/core/manual/en_US/#ctes