How to call db2 database functions with literal arguments using JPA Api criterion?

Question

How to call db2 database functions with literal arguments using JPA Api criterion?

I have a DB2 database function declared as follows:

CREATE FUNCTION MYDB.FN_ISNEWSCOVERAGE(NEWS_ID INTEGER, USERID VARCHAR(50)) RETURNS INTEGER ...

which contains some complex SQL logic and returns 1 or 0 (to indicate true / false).

I am trying to use a function to filter the results in a query built using the JPA API criteria (more Spring 3 / hibernation), as follows:

 EntityManager em = ... CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<News> cq = cb.createQuery(News.class); Root<News> news = cq.from(News.class); Predicate criteria = cb.conjunction(); ... String userid = ... criteria = cb.and( criteria, cb.equal( cb.function( "MYDB.FN_ISNEWSCOVERAGE", Long.class, news.get("id"), cb.<String>literal( userid ) ), 1 ) ); ... TypedQuery<News> tq = em.createQuery(cq); List<News> results = tq.getResultList(); ...

The problem is that when starting the resulting query, db2 throws an error:

 Caused by: com.ibm.db2.jcc.b.eo: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=null, DRIVER=3.53.95

which is described here . The problem seems to be that the generated SQL contains '?' the positional parameter for one of the arguments to the MYDB.FN_ISNEWSCOVERAGE function is the corresponding section of the generated query string:

 SELECT ... from MYDB.NEWS news0_ where 1=1 and news0_.ACTIVE=? and MYDB.FN_ISNEWSCOVERAGE(news0_.NEWS_ID, ?)=1

If I copy the generated query to the sql client and replace the positional parameters with literal values, the query runs fine.

Is there a way to avoid this error when calling a database function from JPA APIs?

Update

I realized that I could work around the problem using a subquery to replace the literal function argument with a path expression like this:

 Subquery<News> sq = cq.subquery(News.class); Root<News> sqNews = sq.correlate(news); Root<User> sqUser = sq.from(User.class); sq.select(news) .where( cb.equal(sqUser.get("id"), userid), cb.equal(cb.function("MYDB.FN_ISNEWSCOVERAGE", Long.class, sqNews.get("id"), sqUser.get("id")), 1) ); criteria = cb.and(cb.exists(sq));