JPQL dynamic query to accept multiple days

Question

How can we write the JPA or JPQL query for the below native query? Reference Link - SQL Oracle - How to make query dynamic to accept multiple days to calculate the expiration

SELECT * FROM PASS_EXP
WHERE  TRUNC(EXPIRY_DT) IN (
 SELECT TRUNC(SYSDATE) + COLUMN_VALUE
 FROM   TABLE(SYS.ODCINUMBERLIST(1, 3, 5, 7, 15))
);

Or

SELECT p.*
FROM PASS_EXP p
INNER JOIN TABLE(SYS.ODCINUMBERLIST(1, 3, 5, 7, 15)) t
ON (   p.expiry_dt >= TRUNC(SYSDATE) + t.COLUMN_VALUE
  AND p.expiry_dt <  TRUNC(SYSDATE) + t.COLUMN_VALUE + 1 );

Assume 1, 3, 5, 7, 15 is going to come as List to the query

You can't because JPQL does not have a TABLE function. But IMHO this is not an issue because you can get Entites from Native Queries — Simon Martinelli, Sep 02 '21 at 12:44

score 0 · Answer 1 · answered Sep 02 '21 at 13:06

You don't need to convert the queries to JPQL (despite this wouldn't be possible due to the lack of equivalent functions in JPQL.

BUT you can simply use the native queries to return entities. I assume that you have an entity for the table PASS_EXP.

Then you can do:

List<PassExp> entities = em.createNativeQuery("SELECT * FROM PASS_EXP " +
"WHERE  TRUNC(EXPIRY_DT) IN ( " +
" SELECT TRUNC(SYSDATE) + COLUMN_VALUE " +
" FROM   TABLE(SYS.ODCINUMBERLIST(1, 3, 5, 7, 15)) " +
")").getResultList();

JPQL dynamic query to accept multiple days

1 Answers1