Is it possible to join db-function output in JPA criteria API?

user_5951066 0
Viewed 107

Let's assume my JPA criteria API produces something like the following SQL:

SELECT * FROM a WHERE myCustomDbFunction(a.col) > "abc" order by myCustomDbFunction(a.col) asc;

The function calls are created using criteriaBuilder.function, returning an Expression<String> which I then can use in my where and order by clauses. The query works fine, but the db function is called twice (or even more often in more complex queries), which is very bad for performance.

In SQL I could simply do this:

SELECT * FROM a, myCustomDbFunction(a.col) as myValue WHERE myValue > "abc" order by myValue asc;

or this: (both have the same performance according to explain:

SELECT * FROM a CROSS JOIN myCustomDbFunction(a.col) myValue WHERE myValue > "abc" order by myValue asc;

However, in Criteria API I did not find a way to do this as it seems that I can only use the entity attributes for joins.

Is there any way to avoid calling the function multiple times using JPA criteria API? Or is it only possible using custom SQL?

hibernate-criteria jpa hibernate
1 Answers

What makes you think that listing the function multiple times will cause it to be executed multiple times? SQL is declarative, so engines can and will evaluate a "stable" function only once. See here for how PostgreSQL defines the concept of stable functions: https://www.postgresql.org/docs/10/xfunc-volatility.html

Not sure what kind of issue you are trying to solve here, but putting functions in a cross join is AFAIK not a standard feature and thus also not supported by Hibernate or JPA.

user_4124460
Related