Is it possible to write & call user-defined function in JPQL?
Add a comment
|
2 Answers
It's not supported by JPA specification itself, however, some JPA implementations may provide such an extension.
For example, in Hibernate you can subclass a Dialect and define customs SQL functions by calling registerFunction(). Many dialect-specific functions are already defined this way.
Comments
Is it possible to write & call user-defined function in JPQL?
The short answer - No.
The long answer is that native functions cannot be referenced in JPQL queries, as JPQL as a very well defined grammar. For instance, the SELECT clause of a JPQL query is defined in the JPA specification using BNF notation as:
select_clause ::= SELECT [DISTINCT] select_item {, select_item}*
select_item ::= select_expression [ [AS] result_variable]
select_expression ::= single_valued_path_expression | scalar_expression | aggregate_expression | identification_variable | OBJECT(identification_variable) | constructor_expression
constructor_expression ::= NEW constructor_name ( constructor_item {, constructor_item}* )
constructor_item ::= single_valued_path_expression | scalar_expression | aggregate_expression | identification_variable
aggregate_expression ::= { AVG | MAX | MIN | SUM } ([DISTINCT] state_field_path_expression) | COUNT ([DISTINCT] identification_variable | state_field_path_expression | single_valued_object_path_expression)
Other statements are defined in a similar manner. One can notice that the only allowed functions are AVG, MAX, MIN, SUM and COUNT which must occur in context of an aggregate expression. There is no scope for user defined functions in the JPQL grammar, and hence, one must use native SQL queries to invoke user-defined functions present in the database.
