Jump to: navigation, search

EclipseLink/Development/JPA 2.0/case expressions

< EclipseLink‎ | Development‎ | JPA 2.0
Revision as of 08:56, 2 September 2009 by Christopher.delahunt.oracle.com (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Case style Expressions

JPA 2.0 Root | Enhancement Request

Issue Summary

JPA 2.0 introduces case style expressions to JPQL and the criteria APIs. This feature will require functionality to be added to EclipseLink.

See JPA 2.0 section 4.6.17.3 and 5.2.11 for details.

General Solution

EclipseLink Expression Support will be required that can produce the required SQL. It may be as simple as adding a new Expression Operator for each the new function under these sections.

Native Expression support

4 JPQL expressions fall under the 'CASE' feature:

  1. Simple Case

EclipseLink already had Simple case using method Expression.caseStatement(Map caseItems, String defaultItem) which generated SQL of the form:

 "CASE <baseExpression> WHEN <caseItems.keySet().toArray()[1]> THEN <caseItems.values().toArray()[1]> .. ELSE <defaultItem> END"

defaultItem was changed to an Object type, so it can take any form of expression or value not just a string. Simple Case returns one of the values in the map or the default item, using equality on the baseExpression with the map keys to decide which. This FunctionExpression is created when the caseStatement method is called to allow any number of WHEN/THEN statement pairs to be added, and as such, is not able to be configured or enabled/disabled at the DatabasePlatform level. Example usage:

       Hashtable caseTable = new Hashtable(3);
       caseTable.put("Bob", "Bobby");
       caseTable.put("Susan", "Susie");
       caseTable.put("Eldrick", "Tiger");
       Expression expression = builder.get("firstName").caseStatement(caseTable, "NoNickname").equal("Bobby");

Generated SQL:

       "CASE t1.firstName WHEN 'Bob' THEN 'Bobby' WHEN 'Susan' THEN 'Susie' WHEN 'Eldrick' THEN 'Tiger' ELSE 'NoNickname' END = 'Bobby'"

So far, this seems to have problems on Derby

  1. Case

Added Expression.caseConditionStatement(Map<Expression, Object> caseConditions, Object defaultItem) which similar to caseStatement is also a FunctionExpression created on the method call, and so cannot be disabled or overriden at the DatabasePlatform level. Unlike caseStatement, caseConditionStatement requires the keys in caseCondition to be expressions. The expressions will not be checked for compatibility with case, so constants can still be passed in if they are passed as constant expressions, but and they will be evaluated at the database. Case returns one of the values in the map or the default item, evaluating the map key expressions to decide which. Generated SQL is of the form: "CASE WHERE expression1 THEN value1 ELSE defaultItem" Example usage:

       Hashtable caseTable = new Hashtable(3);
       caseTable.put(builder.get("firstName").equal("Bob"), "Bobby");
       caseTable.put(builder.get("firstName").equal("Susan"), "Susie");
       caseTable.put(builder.get("firstName").equal("Eldrick"), "Tiger");
       Expression expression = builder.caseConditionStatement(caseTable, "No-Nickname").equal("Bobby");    

Generated SQL:

       "CASE WHEN t1.firstName='Bob' THEN 'Bobby' WHEN t1.firstName='Susan' THEN 'Susie' WHEN t1.firstName='Eldrick' THEN 'Tiger' ELSE 'NoNickname' END = 'Bobby'"
  1. Coalesce

Added Expression.coalesce(Collection expressions) which will return null if all arguments are null and the first non-null argument otherwise. Like the previous case methods, the coalesce implementation creates a FunctionExpression in the method call itself, so it cannot be overriden or defined at the databasePlatform level. Example usage:

       Vector caseTable = new Vector(3); 
       caseTable.add(builder.get("firstName")); 
       caseTable.add(builder.get("lastName")); 
       caseTable.add("NoName"); 
       Expression expression = builder.coalesce(caseTable).equal("Bob"); 

Generated SQL:

       "COALESCE( t1.firstName, t1.lastName, 'NoName') = 'Bob'"
  1. NullIf

Added Expression.nullIf(Object object) which when the expression is evaluated, returns null if the Object equals the base expression, otherwise returns the value of the base expression. This implementation can be overriden on specific databasePlatforms Example usage:

       Expression expression = builder.get("firstName").nullIf( "Bobby").equal("Bob");

Generated SQL:

       "NULLIF(t1.firstName, 'Bobby') = 'Bob'"

Work Required

  1. completed - Native work done through 252491
 - JPQL checked in through 249224