* Purpose: Define an object-level representation of a database query where clause.
** Description: An expression is a tree-like structure that defines the selection * criteria for a query against objects in the database. The expression has the advantage * over SQL by being at the object-level, i.e. the object model attributes and relationships * can be used to be query on instead of the database field names. * Because it is an object, not a string the expression has the advantage that is can be * easily manipulated through code to easily build complex selection criterias.
** Responsibilities: *
Example: *
* EclipseLink: employee.get("date").addDate("year", 2)
* Java: NA
* SQL: DATEADD(date, 2, year)
* Example: *
* EclipseLink: employee.get("date").addDate("year", 2)
* Java: NA
* SQL: DATEADD(date, 2, year)
* attributeName
* holding true for criteria.
* * For every expression with an anyOf, its negation has either an allOf or a * noneOf. The following two examples will illustrate as the second is the * negation of the first: *
* AnyOf Example: Employees with a non '613' area code phone number. *
* ReadAllQuery query = new ReadAllQuery(Employee.class);
* ExpressionBuilder employee = new ExpressionBuilder();
* Expression exp = employee.anyOf("phoneNumbers").get("areaCode").notEqual("613");
* * AllOf Example: Employees with all '613' area code phone numbers. *
* ExpressionBuilder employee = new ExpressionBuilder();
* ExpressionBuilder phones = new ExpressionBuilder();
* Expression exp = employee.allOf("phoneNumbers", phones.get("areaCode").equal("613"));
* SQL:
* SELECT ... EMPLOYEE t0 WHERE NOT EXISTS (SELECT ... PHONE t1 WHERE
* (t0.EMP_ID = t1.EMP_ID) AND NOT (t1.AREACODE = '613'))
* * allOf is the universal counterpart to the existential anyOf. To have the * condition evaluated for each instance it must be put inside of a subquery, * which can be expressed as not exists (any of attributeName some condition). * (All x such that y = !Exist x such that !y). *
Likewise the syntax employee.allOf("phoneNumbers").get("areaCode").equal("613") * is not supported for the
equalmust go inside a subQuery. *
* This method saves you from writing the sub query yourself. The above is * equivalent to the following expression: *
* ExpressionBuilder employee = new ExpressionBuilder();
* ExpressionBuilder phone = new ExpressionBuilder();
* ReportQuery subQuery = new ReportQuery(Phone.class, phone);
* subQuery.retreivePrimaryKeys();
* subQuery.setSelectionCriteria(phone.equal(employee.anyOf("phoneNumbers").and(
* phone.get("areaCode").notEqual("613")));
* Expression exp = employee.notExists(subQuery);
* * Note if employee has no phone numbers allOf ~ noneOf. * @param criteria must have its own builder, as it will become the * separate selection criteria of a subQuery. * @return a notExists subQuery expression */ public Expression allOf(String attributeName, Expression criteria) { ReportQuery subQuery = new ReportQuery(); subQuery.setShouldRetrieveFirstPrimaryKey(true); Expression builder = criteria.getBuilder(); criteria = builder.equal(anyOf(attributeName)).and(criteria.not()); subQuery.setSelectionCriteria(criteria); return notExists(subQuery); } /** * PUBLIC: * Return an expression that is the boolean logical combination of both expressions. * This is equivalent to the SQL "AND" operator and the Java {@literal "&&"} operator. *
Example: *
* EclipseLink: employee.get("firstName").equal("Bob").and(employee.get("lastName").equal("Smith"))
* Java: (employee.getFirstName().equals("Bob")) {@literal &&} (employee.getLastName().equals("Smith"))
* SQL: F_NAME = 'Bob' AND L_NAME = 'Smith'
* Example: *
*| Format | *Equivalent | *
|---|---|
| EclipseLink | *
*
* ReadAllQuery query = new ReadAllQuery(Employee.class);
* |
*
| Java | *No direct equivalent | *
| SQL | *SELECT DISTINCT ... WHERE (t2.MGR_ID (+) = t1.ID) AND (t2.F_NAME = 'Bob') | *
Example: *
*| Format | *Equivalent | *
|---|---|
| EclipseLink | *
*
* ReadAllQuery query = new ReadAllQuery(Employee.class);
* |
*
| Java | *No direct equivalent | *
| SQL | *SELECT DISTINCT ... WHERE (t2.MGR_ID (+) = t1.ID) AND (t2.F_NAME = 'Bob') | *
Example: *
*| Format | *Equivalent | *
|---|---|
| EclipseLink | *
*
* ReadAllQuery query = new ReadAllQuery(Employee.class);
* |
*
| Java | *No direct equivalent | *
| SQL | *SELECT DISTINCT ... WHERE (t2.MGR_ID (+) = t1.ID) AND (t2.F_NAME = 'Bob') | *
Example: *
*| Format | *Equivalent | *
|---|---|
| EclipseLink | *
*
* ReadAllQuery query = new ReadAllQuery(Employee.class);
* |
*
| Java | *No direct equivalent | *
| SQL | *SELECT DISTINCT ... WHERE (t2.MGR_ID (+) = t1.ID) AND (t2.F_NAME = 'Bob') | *
Example: *
* EclipseLink: employee.get("project").treat(LargeProject.class).get("budget").equal(1000)
* Java: ((LargeProject)employee.getProjects().get(0)).getBudget() == 1000
* SQL: LPROJ.PROJ_ID (+)= PROJ.PROJ_ID AND L_PROJ.BUDGET = 1000 AND PROJ.TYPE = "L"
*
* readAllQuery.addOrderBy(expBuilder.get("address").get("city").ascending())
*
* readAllQuery.addOrderBy(expBuilder.get("address").get("city").ascending().nullsFirst())
*
* readAllQuery.addOrderBy(expBuilder.get("address").get("city").ascending().nullsLast())
* * Example: *
*
* EclipseLink: employee.asOf(new AsOfClause(pastTime))
* Java: None
* SQL (Flashback): SELECT ... FROM EMPLOYEE AS OF TIMESTAMP (pastTime) t0 ...
* SQL (Generic): .. WHERE (t1.START {@literal <=} pastTime) AND ((t1.END IS NULL) OR t1.END {@literal >} pastTime)
*
* * Set an as of clause at the expression level to still query for current objects * while expressing selection criteria like: *
* Simultaneously querying on two versions of the same object (one past * one present) lets you express these advanced selection criteria. *
* Example: Querying on past attributes using parallel expressions. *
*
* // Finds all employees who lived in Ottawa as of a past time.
* ExpressionBuilder employee = new ExpressionBuilder();
* ExpressionBuilder pastEmployee = new ExpressionBuilder(Employee.class);
* pastEmployee.asOf(pastTime);
* Expression pastAddress = pastEmployee.get("address"); // by default address will also be as of past time.
* Expression selectionCriteria = pastAddress.get("city").equal("Ottawa").and(
* employee.equal(pastEmployee));
* * The advantage of the parallel expression is that you can still read current * objects, the as of clause will affect only the where clause / selection criteria. *
* You may be tempted to rewrite the above as employee.get("address").asOf(pastTime). * That is allowed but see below for the finer points involved in this. *
* Example: Querying on object changes using parallel expressions. *
*
* // Finds all employees who recently received a raise. Note that current
* // objects are returned, so can be cached normally.
* ExpressionBuilder employee = new ExpressionBuilder();
* Expression pastEmployee = new ExpressionBuilder(Employee.class);
* pastEmployee.asOf(yesterday);
* Expression parallelJoin = employee.equal(pastEmployee);
* Expression selectionCriteria = parallelJoin.and(
* employee.get("salary").greaterThan(pastEmployee.get("salary")));
* * Example: Querying on object changes using custom query keys *
* // First define the custom query key and add it to your descriptor.
* ExpressionBuilder builder = new ExpressionBuilder(Employee.class);
* Expression joinCriteria = builder.getField("EMPLOYEE.EMP_ID").equal(builder.getParameter("EMPLOYEE.EMP_ID"));
* OneToOneQueryKey selfReferential = new OneToOneQueryKey();
* selfReferential.setName("this");
* selfReferential.setJoinCriteria(joinCriteria);
* selfReferential.setReferenceClass(Employee.class);
* getSession().getDescriptor(Employee.class).addQueryKey(selfReferential);
*
* // Now build query as before.
* Expression employee = new ExpessionBuilder();
* Expression pastEmployee = employee.get("this").asOf(yesterday);
* Expression selectionCriteria = employee.get("salary").greaterThan(pastEmployee.get("salary"));
* * Note in general that any parallel expression can be rewritten using a custom query key. * EclipseLink will even automatically interpret x.get("this") for you so you do * not need to define the above query key first. *
* Full Reference: *
* If an object is mapped to multiple tables, then each table will be as of * the same time. Two objects mapped to the same table can not have different * as of times. Conversely only expressions which have associated tables can have an as * of clause. *
* If an as of clause is not explicitly set an expression will use the clause * of its base expression, and so on recursively until one is found or an * ExpressionBuilder is reached. Some usage scenarios follow: *
* Watch out for x.asOf(oneTime).get("y").asOf(anotherTime). *
this
* @since OracleAS EclipseLink 10g (10.0.3)
* @see org.eclipse.persistence.history.AsOfClause
* @see #hasAsOfClause
* @see org.eclipse.persistence.sessions.Session#acquireHistoricalSession(org.eclipse.persistence.history.AsOfClause)
* @see org.eclipse.persistence.queries.ObjectLevelReadQuery#setAsOfClause(org.eclipse.persistence.history.AsOfClause)
*/
public Expression asOf(AsOfClause pastTime) {
throw new UnsupportedOperationException("anyOfAllowingNone");
}
/**
* INTERNAL:
* Alias a particular table within this node
*/
protected void assignAlias(String name, DatabaseTable tableOrExpression) {
// By default, do nothing.
}
/**
* INTERNAL:
* Assign aliases to any tables which I own. Start with t(initialValue),
* and return the new value of the counter , i.e. if initialValue is one
* and I have tables ADDRESS and EMPLOYEE I will assign them t1 and t2 respectively, and return 3.
*/
public int assignTableAliasesStartingAt(int initialValue) {
if (hasBeenAliased()) {
return initialValue;
}
int counter = initialValue;
List* This is equivalent to the SQL "BETWEEN AND" operator and Java {@literal ">=", "<=;"} operators. *
Example: *
* EclipseLink: employee.get("age").between(19,50)
* Java: (employee.getAge() {@literal >=} 19) {@literal &&} (employee.getAge() {@literal <=} 50)
* SQL: AGE BETWEEN 19 AND 50
*
*/
public Expression between(Object leftValue, Object rightValue) {
ExpressionOperator anOperator = getOperator(ExpressionOperator.Between);
List args = new ArrayList(2);
args.add(leftValue);
args.add(rightValue);
return anOperator.expressionForArguments(this, args);
}
public Expression between(Expression leftExpression, Expression rightExpression) {
return between((Object)leftExpression, (Object)rightExpression);
}
/**
* PUBLIC:
* Function, between two shorts
*/
public Expression between(short leftValue, short rightValue) {
return between(Short.valueOf(leftValue), Short.valueOf(rightValue));
}
/**
* PUBLIC:
* Function Convert values returned by the query to values
* given in the caseItems Map. The equivalent of
* the Oracle CASE function
* Example: *
* Map caseTable = new HashMap();
* caseTable.put("Robert", "Bob");
* caseTable.put("Susan", "Sue");
*
* EclipseLink: employee.get("name").caseStatement(caseTable, "No-Nickname")
* Java: NA
* SQL: CASE name WHEN "Robert" THEN "Bob"
* WHEN "Susan" THEN "Sue"
* ELSE "No-Nickname"
*
* SQL: CASE name WHEN "Robert" THEN "Bob"
* WHEN "Susan" THEN "Sue"
* ELSE "No-Nickname"
* Example: *
* Map caseTable = new HashMap();
* caseTable.put(employee.get("name").equals("Robert"), "Bob");
* caseTable.put(employee.get("name").equals("Susan"), "Sue");
*
* EclipseLink: expressionBuilder.caseConditionStatement(caseTable, "No-Nickname")
* Java: NA
* SQL: CASE WHEN name = "Robert" THEN "Bob"
* WHEN name = "Susan" THEN "Sue"
* ELSE "No-Nickname"
*
* SQL: CASE WHEN name = "Robert" THEN "Bob"
* WHEN name = "Susan" THEN "Sue"
* ELSE "No-Nickname"
* Example: *
* EclipseLink: builder.get("name").nullIf( "Bobby")
* Java: NA
* SQL: NULLIF(name, "Bobby")
* Example: *
* List list = new ArrayList(3);
* list.add(builder.get("firstName"));
* list.add(builder.get("lastName"));
* list.add(builder.get("nickname"));
*
* EclipseLink: expressionBuilder.coalesce(caseTable)
* Java: NA
* SQL: COALESCE(firstname, lastname, nickname)
* Example: *
* EclipseLink: project.get("description").containsAllKeyWords("EclipseLink rdbms java")
* Example: *
* EclipseLink: project.get("description").containsAllKeyWords("EclipseLink rdbms java")
* Example: *
* EclipseLink: employee.get("firstName").containsSubstring("Bob")
* Java: employee.getFirstName().indexOf("Bob") != -1
* SQL: F_NAME LIKE '%BOB%'
* Example: *
* EclipseLink: employee.get("firstName").containsSubstring("Bob")
* Java: employee.getFirstName().indexOf("Bob") != -1
* SQL: F_NAME LIKE '%BOB%'
* Example: *
* EclipseLink: employee.get("firstName").containsSubstringIgnoringCase("Bob")
* Java: employee.getFirstName().toUpperCase().indexOf("BOB") != -1
* SQL: UPPER(F_NAME) LIKE '%BOB%'
* Example: *
* EclipseLink: employee.get("firstName").containsSubstringIgnoringCase("Bob")
* Java: employee.getFirstName().toUpperCase().indexOf("BOB") != -1
* SQL: UPPER(F_NAME) LIKE '%BOB%'
* Example: *
* EclipseLink: employee.get("date").dateDifference("year", new Date(System.currentTimeMillis()))
* Java: NA
* SQL: DATEADD(date, 2, GETDATE)
* Example: *
* EclipseLink: employee.get("date").dateDifference("year", new Date(System.currentTimeMillis()))
* Java: NA
* SQL: DATEADD(date, 2, GETDATE)
* Example: *
* EclipseLink: employee.get("date").dateName("year")
* Java: new String(date.getYear())
* SQL: DATENAME(date, year)
* Example: *
* EclipseLink: employee.get("date").datePart("year")
* Java: date.getYear()
* SQL: DATEPART(date, year)
* Example: *
* Map decodeTable = new HashMap();
* decodeTable.put("Robert", "Bob");
* decodeTable.put("Susan", "Sue");
*
* EclipseLink: employee.get("name").Decode(decodeTable, "No-Nickname")
* Java: NA
* SQL: DECODE(name, "Robert", "Bob", "Susan", "Sue", "No-Nickname")
* Example: *
* readAllQuery.addOrderBy(expBuilder.get("address").get("city").descending())
* Example: *
* EclipseLink: employee.get("name").difference("Frank")
* SQL: DIFFERENCE(name, 'Frank')
* Example: *
* EclipseLink: employee.get("firstName").equal("Bob")
* Java: employee.getFirstName().equals("Bob")
* SQL: F_NAME = 'Bob'
* Since OracleAS EclipseLink 10g (9.0.4) if this is an ExpressionBuilder and theValue
* is not used elsewhere, both will be translated to the same table. This can
* generate SQL with one less join for most exists subqueries.
*
Example: *
* EclipseLink: employee.get("manager").equal(employee)
* Java: employee.getManager().equals(employee)
* SQL (optimized): EMP_ID = MANAGER_ID
* SQL (unoptimized): t0.MANAGER_ID = t1.EMP_ID AND t0.EMP_ID = t1.EMP_ID
* Example: *
* EclipseLink: employee.get("firstName").equalsIgnoreCase("Bob")
* Java: employee.getFirstName().equalsIgnoreCase("Bob")
* SQL: UPPER(F_NAME) = 'BOB'
* Example: *
* EclipseLink: employee.get("firstName").equalsIgnoreCase("Bob")
* Java: employee.getFirstName().equalsIgnoreCase("Bob")
* SQL: UPPER(F_NAME) = 'BOB'
* Example: *
* ExpressionBuilder builder = new ExpressionBuilder();
* ReportQuery subQuery = new ReportQuery(Employee.class, new ExpressionBuilder());
* subQuery.setSelectionCriteria(subQuery.getExpressionBuilder().get("name").equal(builder.get("name")));
* builder.exists(subQuery);
* Example: *
* builder.get("address").get("city").equal("Ottawa");
* Example: *
* builder.get("address", false).get("city").equal("Ottawa");
* Example: *
* builder.getAllowingNull("address").get("city").equal("Ottawa");
* null if no clause set, AsOfClause.NO_CLAUSE if
* clause explicitly set to null.
* @see #asOf(org.eclipse.persistence.history.AsOfClause)
* @see #hasAsOfClause()
*/
public AsOfClause getAsOfClause() {
return null;
}
/**
* INTERNAL:
* For Flashback: If this expression is not already as of some timestamp
* gets the clause from the base expression. Allows a clause to be set
* only on the builder and then propogated during normalize.
*/
public AsOfClause getAsOfClauseRecursively() {
return null;
}
/**
* INTERNAL:
* Return the expression builder which is the ultimate base of this expression, or
* null if there isn't one (shouldn't happen if we start from a root)
*/
public abstract ExpressionBuilder getBuilder();
/**
* INTERNAL:
* If there are any fields associated with this expression, return them
*/
public DatabaseField getClonedField() {
return null;
}
/**
* ADVANCED:
* Return an expression representing a field in a data-level query.
* This is used internally in EclipseLink, or to construct queries involving
* fields and/or tables that are not mapped.
* Example: *
* builder.getField("ADDR_ID").greaterThan(100);
* builder.getTable("PROJ_EMP").getField("TYPE").equal("S");
* Example: *
* builder.getField(aField).greaterThan(100);
* Example: *
* Expression address = employee.getAllowingNull("address");
* employee.join(address, address.get("city").equal("Ottawa"));
* query.addNonFetchJoin(address);
* Example: *
* Expression address = employee.getAllowingNull("address");
* employee.leftJoin(address, address.get("city").equal("Ottawa"));
* query.addNonFetchJoin(address);
* Example: *
* builder.get("name").getFunction(MyFunctions.FOO_BAR).greaterThan(100);
* Example: *
* List arguments = new ArrayList();
* arguments.add("blee");
* builder.get("name").getFunction(MyFunctions.FOO_BAR, arguments).greaterThan(100);
* Example: *
* List arguments = new ArrayList();
* arguments.add("blee");
* builder.get("name").operator("FOO_BAR", arguments).greaterThan(100);
* Example: *
* List arguments = new ArrayList();
* arguments.add("blee");
* builder.get("name").getFunction(MyFunctions.FOO_BAR, arguments).greaterThan(100);
* Example: *
* builder.get("firstName").getFunction("UPPER");
* Example: *
* builder.getClassForInheritance().equal(SmallProject.class);
* builder.anyOf("projects").getClassForInheritance().equal(builder.getParameter("projectClass"));
* Example: *
* builder.getTable("PROJ_EMP").getField("TYPE").equal("S");
* Example: *
* builder.getTable(linkTable).getField("TYPE").equal("S");
* Example: *
* builder.getAlias(builder.subQuery(reportQuery)).get("type").equal("S");
* this is to be queried as of a past time.
* @return false from asOf(null); hasAsOfClause().
* @see #getAsOfClause
*/
public boolean hasAsOfClause() {
return false;
}
/**
* INTERNAL:
* Answers if the database tables associated with this expression have been
* aliased. This insures the same tables are not aliased twice.
*/
public boolean hasBeenAliased() {
return false;
}
/**
* PUBLIC:
* Function, returns binary array value for the hex string.
*/
public Expression hexToRaw() {
ExpressionOperator anOperator = getOperator(ExpressionOperator.HexToRaw);
return anOperator.expressionFor(this);
}
/**
* PUBLIC:
* Function return a specific value if item returned from the
* query is null. Equivalent of the oracle NVL function
* Example: *
* EclipseLink: employee.get("name").ifNull("no-name")
* Java: NA
* SQL: NVL(name, 'no-name')
* Example: *
* EclipseLink: employee.get("age").in(ages)
* Java: ages.contains(employee.getAge())
* SQL: AGE IN (55, 18, 30)
* Example: *
* ReportQuery query = new ReportQuery();
* query.setReferenceClass(Employee.class);
* ExpressionBuilder builder = query.getExpressionBuilder();
* Expression firstNameJohn = builder.get("firstName").equal("John");
* Expression anyOfProjects = builder.anyOf("projects");
* Expression exp = firstNameJohn.and(anyOfProjects.index().between(2, 4));
* query.setSelectionCriteria(exp);
* query.addAttribute("projects", anyOfProjects);
*
* SELECT DISTINCT t0.PROJ_ID, t0.PROJ_TYPE, t0.DESCRIP, t0.PROJ_NAME, t0.LEADER_ID, t0.VERSION, t1.PROJ_ID, t1.BUDGET, t1.MILESTONE
* FROM OL_PROJ_EMP t4, OL_SALARY t3, OL_EMPLOYEE t2, OL_LPROJECT t1, OL_PROJECT t0
* WHERE ((((t2.F_NAME = 'John') AND (t4.PROJ_ORDER BETWEEN 2 AND 4)) AND (t3.OWNER_EMP_ID = t2.EMP_ID)) AND
* (((t4.EMP_ID = t2.EMP_ID) AND (t0.PROJ_ID = t4.PROJ_ID)) AND (t1.PROJ_ID (+) = t0.PROJ_ID)))
* attributeName
* being empty.
* Equivalent to size(attributeName).equal(0)
* Example: *
* EclipseLink: employee.isEmpty("phoneNumbers")
* Java: employee.getPhoneNumbers().size() == 0
* SQL: SELECT ... FROM EMP t0 WHERE (
* (SELECT COUNT(*) FROM PHONE t1 WHERE (t0.EMP_ID = t1.EMP_ID)) = 0)
* Example: *
* EclipseLink: employee.get("firstName").like("B%")
* Java: NA
* SQL: F_NAME LIKE 'B%'
* Example: *
* EclipseLink: employee.get("firstName").like("B\_SMITH", "\")
* Java: NA
* SQL: F_NAME LIKE 'B\_SMITH ESCAPE '\''
* Example: *
* EclipseLink: employee.get("firstName").like("B%")
* Java: NA
* SQL: F_NAME LIKE 'B%'
* Example: *
* EclipseLink: employee.get("firstName").regexp("^B.*")
* Java: Pattern.compile("^B.*").matcher(employee.getFirstName()).matches()
* SQL: F_NAME REGEXP '^B.*'
* Example: *
* EclipseLink: employee.get("firstName").regexp("^B.*")
* Java: Pattern.compile("^B.*").matcher(employee.getFirstName()).matches()
* SQL: F_NAME REGEXP '^B.*'
* Example: *
* EclipseLink: employee.get("firstName").like("B\_SMITH", "\")
* Java: NA
* SQL: F_NAME LIKE 'B\_SMITH ESCAPE '\''
* Example: *
* EclipseLink: employee.get("firstName").likeIgnoreCase("%Bob%")
* Java: none
* SQL: UPPER(F_NAME) LIKE 'BOB'
* str in this
* Example: *
* EclipseLink: employee.get("firstName").locate("ob")
* Java: employee.getFirstName().indexOf("ob") + 1
* SQL: LOCATE('ob', t0.F_NAME)
*
* Note that while in String.locate(str) -1 is returned if not found, and the
* index starting at 0 if found, in SQL it is 0 if not found, and the index
* starting at 1 if found.
*/
public Expression locate(Object str) {
ExpressionOperator anOperator = getOperator(ExpressionOperator.Locate);
List args = new ArrayList(1);
args.add(str);
return anOperator.expressionForArguments(this, args);
}
/**
* PUBLIC:
* Function, returns the position of str in this,
* starting the search at fromIndex.
*
Example: *
* EclipseLink: employee.get("firstName").locate("ob", 1)
* Java: employee.getFirstName().indexOf("ob", 1) + 1
* SQL: LOCATE('ob', t0.F_NAME, 1)
*
* Note that while in String.locate(str) -1 is returned if not found, and the
* index starting at 0 if found, in SQL it is 0 if not found, and the index
* starting at 1 if found.
*/
public Expression locate(String str, int fromIndex) {
return locate(str, Integer.valueOf(fromIndex));
}
/**
* PUBLIC:
* Function, returns the position of str in this,
* starting the search at fromIndex.
*
Example: *
* EclipseLink: employee.get("firstName").locate("ob", 1)
* Java: employee.getFirstName().indexOf("ob", 1) + 1
* SQL: LOCATE('ob', t0.F_NAME, 1)
* * Note that while in String.locate(str) -1 is returned if not found, and the * index starting at 0 if found, in SQL it is 0 if not found, and the index * starting at 1 if found. */ public Expression locate(Object str, Object fromIndex) { ExpressionOperator anOperator = getOperator(ExpressionOperator.Locate2); List args = new ArrayList(2); args.add(str); args.add(fromIndex); return anOperator.expressionForArguments(this, args); } /** * PUBLIC: * This represents the aggregate function Maximum. Can be used only within Report Queries. */ public Expression maximum() { return getFunction(ExpressionOperator.Maximum); } /** * PUBLIC: * This represents the aggregate function Minimum. Can be used only within Report Queries. */ public Expression minimum() { return getFunction(ExpressionOperator.Minimum); } /** * PUBLIC: * Function, returns the decimal number of months between the two dates. */ public Expression monthsBetween(Object otherDate) { ExpressionOperator anOperator = getOperator(ExpressionOperator.MonthsBetween); return anOperator.expressionFor(this, otherDate); } /** * PUBLIC: * Return a Map.Entry containing the key and the value from a mapping that maps to a java.util.Map * This expression can only be used as a return value in a ReportQuery and cannot be used as part of * the WHERE clause in any query * * EclipseLink: eb.get("mapAttribute").mapEntry() * @return */ public Expression mapEntry(){ MapEntryExpression expression = new MapEntryExpression(this); expression.returnMapEntry(); return expression; } /** * PUBLIC: * Return the key from a mapping that maps to a java.util.Map * This expression can be used either in as a return value in a ReportQuery or in the WHERE clause in a query * * EclipseLink: eb.get("mapAttribute").mapKey() * @return */ public Expression mapKey(){ return new MapEntryExpression(this); } /** * PUBLIC: * funcation return a date converted to a new timezone. Equivalent of the Oracle NEW_TIME function *
Example: *
* EclipseLink: employee.get("date").newTime("EST", "PST")
* Java: NA
* SQL: NEW_TIME(date, 'EST', 'PST')
* attributeName
* holding true for criteria.
* * For every expression with an anyOf, its negation has either an allOf or a * noneOf. The following two examples will illustrate as the second is the * negation of the first: *
* AnyOf Example: Employees with a '613' area code phone number. *
* ReadAllQuery query = new ReadAllQuery(Employee.class);
* ExpressionBuilder employee = new ExpressionBuilder();
* Expression exp = employee.anyOf("phoneNumbers").get("areaCode").equal("613");
* * NoneOf Example: Employees with no '613' area code phone numbers. *
* ExpressionBuilder employee = new ExpressionBuilder();
* ExpressionBuilder phones = new ExpressionBuilder();
* Expression exp = employee.noneOf("phoneNumbers", phones.get("areaCode").equal("613"));
* SQL:
* SELECT ... EMPLOYEE t0 WHERE NOT EXISTS (SELECT ... PHONE t1 WHERE
* (t0.EMP_ID = t1.EMP_ID) AND (t1.AREACODE = '613'))
* * noneOf is the universal counterpart to the existential anyOf. To have the * condition evaluated for each instance it must be put inside of a subquery, * which can be expressed as not exists (any of attributeName some condition). * (All x such that !y = !Exist x such that y). *
Likewise the syntax employee.noneOf("phoneNumbers").get("areaCode").equal("613")
* is not supported for the equal must go inside a subQuery.
*
* This method saves you from writing the sub query yourself. The above is * equivalent to the following expression: *
* ExpressionBuilder employee = new ExpressionBuilder();
* ExpressionBuilder phone = new ExpressionBuilder();
* ReportQuery subQuery = new ReportQuery(Phone.class, phone);
* subQuery.retreivePrimaryKeys();
* subQuery.setSelectionCriteria(phone.equal(employee.anyOf("phoneNumbers").and(
* phone.get("areaCode").equal("613")));
* Expression exp = employee.notExists(subQuery);
* Example: *
* EclipseLink: employee.get("age").equal(24).not()
* Java: (! (employee.getAge() == 24))
* SQL: NOT (AGE = 24)
* attributeName
* not being empty.
* Equivalent to size(attributeName).greaterThan(0)
* Example: *
* EclipseLink: employee.notEmpty("phoneNumbers")
* Java: employee.getPhoneNumbers().size() {@literal >} 0
* SQL: SELECT ... FROM EMP t0 WHERE (
* (SELECT COUNT(*) FROM PHONE t1 WHERE (t0.EMP_ID = t1.EMP_ID)) {@literal >} 0)
* Example: *
* ExpressionBuilder builder = new ExpressionBuilder();
* ReportQuery subQuery = new ReportQuery(Employee.class, new ExpressionBuilder());
* subQuery.setSelectionCriteria(subQuery.getExpressionBuilder().get("name").equal(builder.get("name")));
* builder.notExists(subQuery);
* Example: *
* EclipseLink: employee.get("age").in(ages)
* Java: ages.contains(employee.getAge())
* SQL: AGE IN (55, 18, 30)
* Example: *
* EclipseLink: employee.get("age").notNull()
* Java: employee.getAge() != null
* SQL: AGE IS NOT NULL
* Example: *
* EclipseLink: employee.get("firstName").equal("Bob").OR(employee.get("lastName").equal("Smith"))
* Java: (employee.getFirstName().equals("Bob")) || (employee.getLastName().equals("Smith"))
* SQL: F_NAME = 'Bob' OR L_NAME = 'Smith'
* Example: *
* EclipseLink: employee.get("name").replicate(2)
* Java: NA
* SQL: REPLICATE(name, 2)
* Example: *
* EclipseLink: employee.get("name").replicate(2)
* Java: NA
* SQL: REPLICATE(name, 2)
* Example: *
* EclipseLink: employee.get("name").reverse()
* Java: NA
* SQL: REVERSE(name)
* Example: *
* EclipseLink: employee.get("name").right(2)
* Java: NA
* SQL: RIGHT(name, 2)
* Example: *
* EclipseLink: employee.get("name").right(2)
* Java: NA
* SQL: RIGHT(name, 2)
* attributeName.
* Example: *
* EclipseLink: employee.size("phoneNumbers")
* Java: employee.getPhoneNumbers().size()
* SQL: SELECT ... FROM EMP t0 WHERE ...
* (SELECT COUNT(*) FROM PHONE t1 WHERE (t0.EMP_ID = t1.EMP_ID))
* Example: *
* EclipseLink: employee.size(Class returnType)
* Java: employee.getPhoneNumbers().size()
* SQL: SELECT ... FROM EMP t0 WHERE ...
* (SELECT COUNT(*) FROM PHONE t1 WHERE (t0.EMP_ID = t1.EMP_ID))
* Example: *
* ExpressionBuilder builder = new ExpressionBuilder();
* ReportQuery subQuery = new ReportQuery(Employee.class, new ExpressionBuilder());
* subQuery.addMaximum("salary");
* builder.get("salary").equal(builder.subQuery(subQuery));
* Example: *
* EclipseLink: employee.get("salary").toChar().equal("100000")
* Java: employee.getSalary().toString().equals("100000")
* SQL: TO_CHAR(SALARY) = '100000'
* Example: *
* EclipseLink: employee.get("startDate").toChar("day").equal("monday")
* Java: employee.getStartDate().getDay().equals("monday")
* SQL: TO_CHAR(START_DATE, 'day') = 'monday'
* Example: *
* EclipseLink: employee.get("firstName").toLowerCase().equal("bob")
* Java: employee.getFirstName().toLowerCase().equals("bob")
* SQL: LOWER(F_NAME) = 'bob'
* Example: *
* EclipseLink: employee.get("firstName").toUpperCase().equal("BOB")
* Java: employee.getFirstName().toUpperCase().equals("BOB")
* SQL: UPPER(F_NAME) = 'BOB'
* Example: *
* EclipseLink: employee.get("date").truncDate(year)
* Java: NA
* SQL: TRUNC(date, year)
* Example: *
* reportQuery.addItem("a constant", builder.value("a constant"));
* Example: *
* reportQuery.addItem("a constant", builder.value("a constant"));
* Example: *
* reportQuery.addItem("a constant", builder.value("a constant"));
* Example: *
* reportQuery.addItem("a constant", builder.value("a constant"));
* Example: *
* reportQuery.addItem("a constant", builder.value("a constant"));
* Example: *
* reportQuery.addItem("a constant", builder.value("a constant"));
* Example: *
* reportQuery.addItem("a constant", builder.value("a constant"));
* Example: *
* reportQuery.addItem("a constant", builder.value("a constant"));
* Example: *
* reportQuery.addItem("a constant", builder.value("a constant"));
* Example: *
* reportQuery.addItem("currentTime", builder.literal("SYSDATE"));
* Example: *
* EclipseLink: employee.get("age").in(ages)
* Java: ages.contains(employee.getAge())
* SQL: AGE IN (55, 18, 30)
* Example: *
* EclipseLink: employee.get("age").in(ages)
* Java: ages.contains(employee.getAge())
* SQL: AGE IN (55, 18, 30)
* Example: *
* EclipseLink: employee.get("age").in(ages)
* Java: ages.contains(employee.getAge())
* SQL: AGE IN (55, 18, 30)
* Example: *
* EclipseLink: employee.get("age").in(ages)
* Java: ages.contains(employee.getAge())
* SQL: AGE IN (55, 18, 30)
* Example: *
* EclipseLink: employee.get("age").in(ages)
* Java: ages.contains(employee.getAge())
* SQL: AGE IN (55, 18, 30)
* Example: *
* EclipseLink: employee.get("age").in(ages)
* Java: ages.contains(employee.getAge())
* SQL: AGE IN (55, 18, 30)
*