Program guide > Access data with client applications > Retrive entities and objects (Query API)



Reference for eXtreme Scale queries

WebSphere eXtreme Scale has its own language by which the user can query data.


ObjectGrid query FROM clause

The FROM clause specifies the collections of objects to which to apply the query. Each collection is identified either by an abstract schema name and an identification variable, called a range variable, or by a collection member declaration that identifies either a single or multi-valued relationship and an identification variable.

Conceptually, the semantics of the query is to first form a temporary collection of tuples, referred to as R. Tuples are composed of elements from the collections that are identified in the FROM clause. Each tuple contains one element from each of the collections in the FROM clause. All possible combinations are formed subject to the constraints that are imposed by the collection member declarations. If any schema name identifies a collection for which there are no records in the persistent store, then the temporary collection R is empty.


Examples using FROM

The DeptBean object contains records 10, 20 and 30. The EmpBean object contains records 1, 2 and 3 that are related to department 10 and records 4 and 5 that are related to department 20. Department 30 has no related employees.

FROM DeptBean d, EmpBean e

This clause forms a temporary collection R that contains 15 tuples.

FROM DeptBean d, DeptBean d1

This clause forms a temporary collection R that contains 9 tuples.

FROM DeptBean d, IN (d.emps) AS e

This clause forms a temporary collection R that contains 5 tuples. Department 30 is not in the R temporary collection because it contains no employees. Department 10 is contained in the R temporary collection three times and department 20 is contained in R twice.

Instead of using IN(d.emps) as e, you can use a JOIN predicate:

FROM DeptBean d JOIN d.emps as e

After forming the temporary collection, the search conditions of the WHERE clause are applied to the R temporary collection, yielding a new temporary collection R1. The ORDER BY and SELECT clauses are applied to R1 to yield the final result set.

An identification variable is a variable that is declared in the FROM clause using the IN operator or the optional AS operator.

FROM DeptBean AS d, IN (d.emps) AS e

is equivalent to:

FROM DeptBean d, IN (d.emps) e

An identification variable that is declared to be an abstract schema name is called a range variable. In the previous query, "d" is a range variable. An identification variable that is declared to be a multi-valued path expression is called a collection member declaration. The "d" and "e" values in the previous example are collection member declarations.

An example of using a single-valued path expression in the FROM clause follows:

FROM EmpBean e, IN(e.dept.mgr) as m


ObjectGrid query SELECT clause

The syntax of the SELECT clause is illustrated in the following example:

SELECT { ALL | DISTINCT } [ selection , ]* selection

selection  ::= {single_valued_path_expression |
                identification_variable |
                OBJECT ( identification_variable) |
           aggregate_functions } [[ AS ] id ]

The SELECT clause consists of one or more of the following elements: a single identification variable that is defined in the FROM clause, a single-valued path expression that evaluates to object references or values, and an aggregate function. Use the DISTINCT keyword to eliminate duplicate references.

A scalar-subselect is a subselect that returns a single value.


Examples using SELECT

Find all employees that earn more than the John employee:

SELECT OBJECT(e) FROM EmpBean ej, EmpBean eWHERE ej.name = 'John' and e.salary > ej.salary

Find all departments that have one or more employees who earn less than 20000:

SELECT DISTINCT e.dept FROM EmpBean e where e.salary < 20000

A query can have a path expression that evaluates to an arbitrary value:

SELECT e.dept.name FROM EmpBean e where e.salary < 20000

The previous query returns a collection of name values for the departments that have employees who earn less than 20000.

A query can return an aggregate value:

SELECT avg(e.salary) FROM EmpBean e

A query that retrieves the names and object references for underpaid employees follows:

SELECT e.name as name , object(e) as emp from EmpBean e where e.salary < 50000


ObjectGrid query WHERE clause

The WHERE clause contains search conditions that are composed of the elements presented below. When a search condition evaluates to TRUE, the tuple is added to the result set.


ObjectGrid query literals

A string literal is enclosed in single quotes. A single quotation mark that occurs within a string literal is represented by two single quotes, for example: 'Tom''s'.

A numeric literal can be any of the following values:

Boolean literals are TRUE and FALSE.

Temporal literals follow JDBC escape syntax based on the type of attribute:

Enum literals are expressed using Java enum literal syntax using the fully qualified enum class name.


ObjectGrid query input parameters

You can specify input parameters by either using an ordinal position or by using a variable name. Writing queries that use input parameters is strongly encouraged, because using input parameters increases performance by allowing the ObjectGrid to catch the query plan between running actions.

An input parameter can be any of the following types: Byte, Short, Integer, Long, Float, Double, BigDecimal, BigInteger, String, Boolean, Char, java.util.Date, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.util.Calendar, a Java SE 5 enum, an Entity or POJO Object, or a binary data string in the form of Java byte[].

An input parameter must not have a NULL value.

To search for the occurrence of a NULL value, use the NULL predicate.

Positional Parameters

Positional input parameters are defined by using question mark followed by a positive number:

?[positive integer].

Positional input parameters are numbered starting at 1 and correspond to the arguments of the query; therefore, a query must not contain an input parameter that exceeds the number of input arguments.

Example: SELECT e FROM Employee e WHERE e.city = ?1 and e.salary >= ?2

Named Parameters

Named input parameters are defined using a variable name in the format: :[parameter name].

Example: SELECT e FROM Employee e WHERE e.city = :city and e.salary >= :salary


ObjectGrid query BETWEEN predicate

The BETWEEN predicate determines whether a given value lies between two other given values.

expression [NOT] BETWEEN expression-2 AND expression-3

Example 1

e.salary BETWEEN 50000 AND 60000

is equivalent to:

e.salary >= 50000 AND e.salary <= 60000

Example 2

e.name NOT BETWEEN 'A' AND 'B'

is equivalent to:

e.name < 'A' OR e.name > 'B'


ObjectGrid query IN predicate

The IN predicate compares a value to a set of values. Use the IN predicate in one of two forms:

expression [NOT] IN ( subselect )expression [NOT] IN ( value1, value2, .... )

The ValueN value can either be a literal value or an input parameter. The expression cannot evaluate to a reference type.

Example 1

e.salary IN ( 10000, 15000 )

is equivalent to

( e.salary = 10000 OR e.salary = 15000 )

Example 2

e.salary IN ( select e1.salary from EmpBean e1 where e1.dept.deptno = 10)

is equivalent to

e.salary = ANY ( select e1.salary from EmpBean e1 where e1.dept.deptno = 10)

Example 3

e.salary NOT IN ( select e1.salary from EmpBean e1 where e1.dept.deptno = 10)

is equivalent to

e.salary <> ALL ( select e1.salary from EmpBean e1 where e1.dept.deptno = 10)


ObjectGrid query LIKE predicate

The LIKE predicate searches a string value for a certain pattern.

string-expression [NOT] LIKE pattern [ ESCAPE escape-character ]

The pattern value is a string literal or parameter marker of type string in which the underscore ( _ ) stands for any single character and percent ( % ) stands for any sequence of characters, including an empty sequence. Any other character stands for itself. The escape character can be used to search for character _ and %. The escape character can be specified as a string literal or as an input parameter.

If the string-expression is null, then the result is unknown.

If both string-expression and pattern are empty, then the result is true.

Example

'' LIKE '' is true
'' LIKE '%' is true
e.name LIKE '12%3' is true for '123' '12993' and false for '1234'
e.name LIKE 's_me' is true for 'some' and 'same', false for 'soome'
e.name LIKE '/_foo' escape '/' is true for '_foo', false for 'afoo'
e.name LIKE '//_foo' escape '/' is true for '/afoo' and for '/bfoo'
e.name LIKE '///_foo' escape '/' is true for '/_foo' but false for '/afoo'


ObjectGrid query NULL predicate

The NULL predicate tests for null values.

{single-valued-path-expression | input_parameter} IS [NOT] NULL

Example

e.name IS NULL
e.dept.name IS NOT NULL
e.dept IS NOT NULL


ObjectGrid query EMPTY collection predicate

Use the EMPTY collection predicate to test for an empty collection.

To test if a multi-valued relationship is empty, use the following syntax:

collection-valued-path-expression IS [NOT] EMPTY

Example

Empty collection predicate To find all the departments that have no employees:

SELECT OBJECT(d) FROM DeptBean d WHERE d.emps IS EMPTY


ObjectGrid query MEMBER OF predicate

The following expression tests whether the object reference that is specified by the single valued path expression or input parameter is a member of the designated collection. If the collection valued path expression designates an empty collection, then the value of the MEMBER OF expression is FALSE.

{ single-valued-path-expression | input_parameter } [ NOT ] MEMBER [ OF ] collection-valued-path-expression

Example

Find employees that are not members of a given department number:

SELECT OBJECT(e) FROM EmpBean e , DeptBean d 
WHERE e NOT MEMBER OF d.emps AND d.deptno = ?1

Find employees whose manager is a member of a given department number:

SELECT OBJECT(e) FROM EmpBean e, DeptBean d 
WHERE e.dept.mgr MEMBER  OF d.emps  and d.deptno=?1


ObjectGrid query EXISTS predicate

The EXISTS predicate tests for the presence or absence of a condition that specified by a subselect.

EXISTS ( subselect )

The result of EXISTS is true if the subselect returns at least one value, otherwise the result is false.

To negate an EXISTS predicate, precede the predicate with the NOT logical operator.

Example

Return departments that have at least one employee that earns more than 1000000:

SELECT  OBJECT(d) FROM  DeptBean d 
WHERE EXISTS ( SELECT  e  FROM IN (d.emps) e WHERE  e.salary > 1000000 )

Return departments that have no employees:

SELECT OBJECT(d) FROM DeptBean d 
WHERE NOT EXISTS  ( SELECT e FROM IN (d.emps) e)

You can also rewrite the previous query like in the following example:

SELECT OBJECT(d) FROM DeptBean d WHERE SIZE(d.emps)=0


ObjectGrid query ORDER BY clause

The ORDER BY clause specifies an ordering of the objects in the result collection. An example follows:

ORDER BY [ order_element ,]* order_element order_element ::={ path-expression }[ ASC | DESC ]

The path expression must specify a single-valued field that is a primitive type of byte, short, int, long, float, double, char, or a wrapper type of Byte, Short, Integer, Long, Float, Double, BigDecimal, String, Character, java.util.Date, java.sql.Date, java.sql.Time, java.sql.Timestamp and java.util.Calendar. The ASC order element specifies that the results are displayed in ascending order, which is the default. A DESC order element specifies that the results are displayed in descending order.

Example

Return department objects. Display the department numbers in decreasing order:

SELECT OBJECT(d) FROM DeptBean d ORDER BY d.deptno DESC

Return employee objects, sorted by department number and name:

SELECT OBJECT(e) FROM EmpBean e ORDER BY e.dept.deptno ASC, e.name DESC


ObjectGrid query aggregation functions

Aggregation functions operate on a set of values to return a single scalar value. Use these functions in the select and subselect methods. The following example illustrates an aggregation:

SELECT SUM (e.salary) FROM EmpBean e WHERE e.dept.deptno =20

This aggregation computes the total salary for department 20.

The aggregation functions are: AVG, COUNT, MAX, MIN, and SUM. The syntax of an aggregation function is illustrated in the following example:

aggregation-function ( [ ALL | DISTINCT ] expression )

or:

COUNT( [ ALL | DISTINCT ] identification-variable )

The DISTINCT option eliminates duplicate values before applying the function. The ALL option is the default option, and does not eliminate duplicate values. Null values are ignored in computing the aggregate function except when you use the COUNT(identification-variable) function, which returns a count of all the elements in the set.


Define return type

The MAX and MIN functions can apply to any numeric, string or date-time data type and return the corresponding data type. The SUM and AVG functions take a numeric type as input. The AVG function returns a double type. The SUM function returns a long type if the input type is an integer type, except when the input is a Java BigInteger type, then the function returns a Java BigInteger type. The SUM function returns a double type if the input type is not an integer type, except when the input is a Java BigDecimal type, then the function returns a Java BigDecimal type. The COUNT function can take any data type except collections, and returns a long type.

When applied to an empty set, the SUM, AVG, MAX, and MIN functions can return a null value. The COUNT function returns zero (0) when it is applied to an empty set.


Use GROUP BY and HAVING clauses

The set of values that is used for the aggregate function is determined by the collection that results from the FROM and WHERE clause of the query. You can divide the set into groups and apply the aggregation function to each group.

To perform this action, use a GROUP BY clause in the query. The GROUP BY clause defines grouping members, which comprise a list of path expressions. Each path expression specifies a field that is a primitive type of byte, short, int, long, float, double, boolean, char, or a wrapper type of Byte, Short, Integer, Long, Float, Double, BigDecimal, String, Boolean, Character, java.util.Date, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.util.Calendar or a Java SE 5 enum.

The following example illustrates the use of the GROUP BY clause in a query that computes the average salary for each department:

SELECT e.dept.deptno, AVG ( e.salary) FROM EmpBean e GROUP BY e.dept.deptno

In division of a set into groups, a NULL value is considered equal to another NULL value.

Groups can be filtered using a HAVING clause that tests group properties before involving aggregate functions or grouping members. This filtering is similar to how the WHERE clause filters tuples (that is, records of the return collection values) from the FROM clause. An example of the HAVING clause follows:

SELECT e.dept.deptno, AVG ( e.salary) FROM EmpBean e
GROUP BY e.dept.deptno
HAVING COUNT(e) > 3 AND e.dept.deptno > 5

This query returns the average salary for departments that have more than three employees and the department number is greater than five.

Use a HAVING clause without a GROUP BY clause. In this case, the entire set is treated as a single group, to which the HAVING clause is applied.


Parent topic:

Retrive entities and objects (Query API)


Related concepts

Query data in multiple time zones

Insert data for different time zones

Use the ObjectQuery API

EntityManager Query API

Query performance tuning

Use objects other than keys to find partitions (PartitionableKey interface)