Community
Participate
Working Groups
Bug 300512 - Add FUNCTION support to extended JPQL - allows to call database functions by specifying the function's name in FUNC. For instance, the following jpql: SELECT a.id FROM A a WHERE FUNC('TO_NUMBER', a.str) = 1 Will produce the same sql no matter which database platform is used: SELECT ID FROM A_TABLE WHERE TO_NUMBER(STR) = 1 Note that this sql will work on Oracle, but not on MySQL. That means this jpql is platform dependent. The current bug is about allowing specifying functions in platform independent way in jpql and getting platform-dependent functions generated in the sql. The previous example would generate the sql appropriate to the database platform in use: Oracle: SELECT ID FROM A_TABLE WHERE TO_NUMBER(STR) = 1 MySQL: SELECT ID FROM A_TABLE WHERE CONVERT(STR, SIGNED) = 1 DB2: SELECT ID FROM A_TABLE WHERE DECIMAL(STR) = 1 Firebird: SELECT ID FROM A_TABLE WHERE CAST(STR AS NUMERIC) = 1 etc. There should be an expression operator defined in Eclipselink corresponding to the function to support that (ExpressionOperator.toNumber in example). The question is how to distinguish between these two approaches to function in jpql extension (whether to use function name without platform-specific translation, as described in Bug 300512; or alternatively to translate for each database platform). One suggestion is to use the same FUNC key word in jpql, but prefix the functions to be translated with "EL." or "Eclipselink": SELECT a.id FROM A a WHERE FUNC('EL.TO_NUMBER', a.str) = 1 Another suggestion is to add an new key word to jpql (OPER was suggested because EclipseLink operator is required): SELECT a.id FROM A a WHERE OPER('TO_NUMBER', a.str) = 1 The latter approach though would put this jpql extension out of sync with Criteria API (Criteria has Function only).
Correction to examples: the passed name should be exactly the same as ExpressionOperator's name: SELECT a.id FROM A a WHERE FUNC('EL.ToNumber', a.str) = 1 or SELECT a.id FROM A a WHERE OPER('ToNumber', a.str) = 1
Yet another suggestion was to use ExpressionOperator name with FUNC and without a prefix: SELECT a.id FROM A a WHERE FUNC('ToNumber', a.str) = 1 hoping that it always will be different from any database function name. If there would be a database function with the same name then specify it all upper cae (or all lower case): SELECT a.id FROM A a WHERE FUNC('TONUMBER', a.str) = 1
I do believe the OPER approach should be considered since we need a solution that will work both with JPQL as well a Criteria's function(...) method. The names you pass to identify the use of a named expression operator should be the same in both usages.
Adding support for OPERATOR on Hermes, select OPERATOR('Mod', e.id, 10) from Employee e Gives JPQL access to 80 EclipseLink defined platform independent functions, as well as support for users to add there own platform independent or dependent functions. This also allows users to call functions with non standard syntax such as CAST.
Created attachment 211909 [details] patch adds OPERATOR, SQL, FUNCTION, COLUMN to hermes
SVN commit trunk: Bug#305187 JPQL enhancements Also bugs # 315087, 350843, 372894, https://bugs.eclipse.org/bugs/show_bug.cgi?id=305187 http://wiki.eclipse.org/EclipseLink/DesignDocs/312146 Adds several enhancements to JPQL support on the Hermes parser. Adds support for FUNCTION, OPERATOR, SQL, COLUMN. Code Review: Pascal (pending) Changes: - AbstractReadAllQueryVisitor, only define ASC ordering when set - Added support for OperatorExpression, SQLExpression, ColumnExpression to EclipseLink 2.4 grammar and parser. - Added support for FunctionExpression to JPA 2.1 grammar and parser. - Added operator() and sql() Expression operations. - Added map of ExpressionOperator names. - Fix to Function/RelationExpression to check for join table and use correct foreign key order when optimizing 1-1 joins. - Changed logging category for “ejb_or_metadata” to be just “metadata”, “jpa_metamodel” to be just “metamodel”. - Added tests for new JPQL operators, and Criteria API. - AbstractGrammarValidator, added generic missing right/left parenthesis warnings. - CriteriaBuilderImpl, add support for fromExpression(), toExpression() API. - Added JpaCriteriaBuilder interface to expose fromExpression(), toExpression() API.
Fixed in 2.4
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
