I have an SQL query that I would like to rewrite using Hibernate Criteria.
Consider the following simplified version:
SELECT * FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY records.created DESC, records.created DESC
) AS RowNum, * FROM (
SELECT a.id AS id
FROM EntityA a
UNION
SELECT b.id AS id
FROM EntityB b
) AS records
) AS result
I approached this by splitting up the query into different parts.
I have written two seperate criterias for each of the two nested SELECTS and merged the resulting two Lists into a SET as advised here. So that covers:
SELECT a.id AS id
FROM EntityA a
UNION
SELECT b.id AS id
FROM EntityB b
Now I would like to create the surrounding query by queryin the resulting SET.
Questions
- Can I query the resultset using Criterias?
- If this is not possible, am I approaching this wrong and should I be looking to implement one large Criteria instead of trying to merge multiple smaller ones?
I'm having a little trouble explaining so don't hesitate in asking for more info if needed.