I have written a complex JPA 2 Criteria API query (my provider is EclipseLink), where I find myself re-using the same subquery over and over again. Unless the DB (Oracle) does something clever, I think that the subquery will be executed each time it is found in the query. I am looking for a way to execute the subquery only once.
We have field-level access, which means that a user has visibility to a DB Column if certain conditions are met. In the example below, the user has the following access:
- COLUMN_1 is visible if the result belongs to category 1
- COLUMN_2 is visible if the result belongs to category 2
- COLUMN_3 is visible if the result belongs to category 1 or category 2
This is a pseudo-query:
SELECT T.PK
FROM MY_TABLE T
WHERE
(
T.COLUMN_1 = 'A'
AND
T.PK IN (SELECT PKs of category 1)
)
AND
(
T.COLUMN_2 = 'B'
AND
T.PK IN (SELECT PKs of category 2)
)
AND
(
T.COLUMN_3 = 'C'
AND
(
T.PK IN (SELECT PKs of category 1)
OR
T.PK IN (SELECT PKs of category 2)
)
)
If I would write it by hand in SQL, I would write it by OUTER JOINing the two queries, like this:
SELECT T.PK
FROM MY_TABLE T
LEFT OUTER JOIN (SELECT PKs of category 1) IS_CAT_1 ON T.PK = IS_CAT_1.PK
LEFT OUTER JOIN (SELECT PKs of category 2) IS_CAT_2 ON T.PK = IS_CAT_2.PK
WHERE
(
T.COLUMN_1 = 'A'
AND
IS_CAT_1.RESULT = true
)
AND
(
T.COLUMN_2 = 'B'
AND
IS_CAT_2.RESULT = true
)
AND
(
T.COLUMN_3 = 'C'
AND
(
IS_CAT_1.RESULT = true
OR
IS_CAT_2.RESULT = true
)
)
Can I join a query as a table with the Criteria API? Creating a View would by my very last choice (the DB is not maintained by me).
Note: I have seen that EclipseLink provides such vendor-specific support in JPQL (link), but I haven't seen this available for Criteria.
