|Basic Querying / Building a Query String|
Each FROM clause expression must resolve to a collection of objects. The collection is then available for iteration in the query expressions that follow in the WHERE clause.
SELECT DISTINCT * FROM /exampleRegion p WHERE p.status = 'active'
The entry value collection is iterated by the WHERE clause, comparing the status field to the string 'active'. When a match is found, the value object is added to the return set.
SELECT DISTINCT * FROM /exampleRegion, positions.values positions WHERE positions.qty > 1000.00
You must implement the equals and hashCode methods in your custom objects if you are doing ORDER BY and DISTINCT queries on the objects. The methods must conform to the properties and behavior documented in the online Java API documentation for java.lang.Object. Inconsistent query results may occur if these methods are absent.
int ID int otherValueLet's put two CustomObjects (we'll call them CustomObjectA and CustomObjectB) into the cache:
If you have implemented the equals method to simply match on the ID field (ID == ID), queries will produce unpredictable results.
SELECT * FROM /CustomObjects c WHERE c.ID > 1 AND c.ID < 3 AND c.otherValue > 0 AND c.otherValue < 3returns two objects, however the objects will be two of either CustomObjectA or CustomObjectB.
SELECT * FROM /CustomObjects c WHERE c.ID > 1 AND c.ID < 3 AND c.otherValue > 1 AND c.otherValue < 3returns either 0 results or 2 results of CustomObjectB, depending on which entry is evaluated last.
To avoid unpredictable querying behavior, implement detailed versions of the equals and hashCode methods.
If you are comparing a non-primitive field of the object in the WHERE clause, use the equals method instead of the = operator. For example instead of nonPrimitiveObj = objToBeCompared use nonPrimitiveObj.equals(objToBeCompared).
Objects must implement serializable if you will be querying partitioned regions or if you are performing client-server querying.
If you are using PDX serialization, you can access the values of individual fields without having to deserialize the entire object. This is accomplished by using PdxInstance, which is a wrapper around the serialized stream. The PdxInstance provides a helper method that takes field-name and returns the value without deserializing the object. While evaluating the query, the query engine will access field values by calling the getField method thus avoiding deserialization.
// Cache configuration setting PDX read behavior <cache> <pdx read-serialized="true"> ... </pdx> </cache>
You can access any object or object attribute that is available in the current scope of a query. In querying, an object's attribute is any identifier that can be mapped to a public field or method in the object. In the FROM specification, any object that is in scope is valid. Therefore, at the beginning of a query, all locally cached regions and their attributes are in scope.
SELECT DISTINCT * FROM /exampleRegion p WHERE p.position1.secId = '1' SELECT DISTINCT * FROM /exampleRegion p WHERE p.position1.SecId = '1' SELECT DISTINCT * FROM /exampleRegion p WHERE p.position1.getSecId() = '1'
The query engine tries to evaluate the value using the public field value. If a public field value is not found, it makes a get call using field name (note that the first character is uppercase.)
If collections in the FROM clause are not related to each other, the WHERE clause can be used to join them.
SELECT * FROM /exampleRegion portfolio1, /exampleRegion2 portfolio2 WHERE portfolio1.status = portfolio2.status
To create indexes for region joins you create single-region indexes for both sides of the join condition. These are used during query execution for the join condition. Partitioned regions do not support region joins. For more information on indexes, see Working with Indexes.Examples:
|Query Description||Query String|
|Query two regions. Return the ID and status for portfolios that have the same status.||
SELECT portfolio1.ID, portfolio2.status FROM /exampleRegion portfolio1, /exampleRegion2 portfolio2 WHERE portfolio1.status = portfolio2.status
SELECT * FROM /exampleRegion portfolio1, portfolio1.positions.values positions1, /exampleRegion2 portfolio2, portfolio2.positions.values positions2 WHERE positions1.secId = positions1.secId
SELECT * FROM /exampleRegion portfolio1, portfolio1.positions.values positions1, /exampleRegion2 portfolio2, portfolio2.positions.values positions2 WHERE portfolio1.ID = 1 AND positions1.secId = positions1.secID
GemFire offers limited support for the LIKE predicate. LIKE can be used to mean 'equals to'. If you terminate the string with a wildcard ('%'), it behaves like 'starts with'. You can also place a wildcard (either '%' or '_') at any other position in the comparison string. You can escape the wildcard characters to represent the characters themselves.
You can also use the LIKE predicate when an index is present.
|Query Description||Query STring|
|Query the region. Return all objects where status equals 'active'.||
SELECT * FROM /exampleRegion p WHERE p.status LIKE 'active'
|Query the region using a wild card for comparison. Returns all objects where status begins with 'activ'.||
SELECT * FROM /exampleRegion p WHERE p.status LIKE 'activ%'
SELECT entry.value FROM /exampleRegion.entries entry WHERE entry.value.toUpperCase LIKE '%BAR%'or
SELECT * FROM /exampleRegion WHERE foo.toLowerCase LIKE '%bar%'
SELECT DISTINCT * FROM /exampleRegion p WHERE p.positions.size >= 2 - maps to positions.size()
Methods declared to return void evaluate to null when invoked through the query processor.
You cannot invoke a static method. See Enum Objects for more information.
Methods without parameters
If the attribute name maps to a public method that takes no parameters, just include the method name in the query string as an attribute. For example, emps.isEmpty is equivalent to emps.isEmpty().
SELECT DISTINCT * FROM /exampleRegion p WHERE p.positions.isEmpty
Methods with parameters
To invoke methods with parameters, include the method name in the query string as an attribute and provide method arguments between parentheses.
SELECT DISTINCT * FROM /exampleRegion p WHERE p.name.startsWith('Bo')
For overloaded methods, the query processor decides which method to call by matching the runtime argument types with the parameter types required by the method. If only one method's signature matches the parameters provided, it is invoked. The query processor uses runtime types to match method signatures.
If more than one method can be invoked, the query processor chooses the method whose parameter types are the most specific for the given arguments. For example, if an overloaded method includes versions with the same number of arguments, but one takes a Person type as an argument and the other takes an Employee type, derived from Person, Employee is the more specific object type. If the argument passed to the method is compatible with both types, the query processor uses the method with the Employee parameter type.
The query processor uses the runtime types of the parameters and the receiver to determine the proper method to invoke. Because runtime types are used, an argument with a null value has no typing information, and so can be matched with any object type parameter. When a null argument is used, if the query processor cannot determine the proper method to invoke based on the non-null arguments, it throws an AmbiguousNameException.
To write a query based on the value of an Enum object field, you must use the toString method of the enum object or use a query bind parameter.
//INVALID QUERY select distinct * from /QueryRegion0 where aDay = Day.Wednesday
The reason it is invalid is that the call to Day.Wednesday involves a static class and method invocation which is not supported.
Enum types can be queried by using toString method of the enum object or by using bind parameter. When you query using the toString method, you must already know the constraint value that you wish to query. In the following first example, the known value is 'active'.
|Query Description||Query String|
|Query enum type using the toString method.||
// eStatus is an enum with values 'active' and 'inactive' select * from /exampleRegion p where p.eStatus.toString() = 'active'
|Query enum type using a bind parameter. The value of the desired Enum field ( Day.Wednesday) is passed as an execution parameter.||
select distinct * from /QueryRegion0 where aDay = $1
The IN expression is a boolean indicating if one expression is present inside a collection of expressions of compatible type. The determination is based on the expressions' equals semantics.
If e1 and e2 are expressions, e2 is a collection, and e1 is an object or a literal whose type is a subtype or the same type as the elements of e2, then e1 IN e2 is an expression of type boolean.
For example, 2 IN SET(1, 2, 3) is TRUE.
SELECT name, address FROM /company WHERE id IN (SELECT id FROM /portfolios WHERE status = 'active')
The interior SELECT statement returns a collection of ids for all /portfolios entries whose status is active. The exterior SELECT iterates over /company, comparing each entry’s id with this collection. For each entry, if the IN expression returns TRUE, the associated name and address are added to the outer SELECT’s collection.
Comparing Set Values
The following is an example of a set value type comparison where sp is of type Set:
SELECT * FROM /exampleRegion WHERE sp = set('20','21','22')In this case, if sp only contains '20' and '21', then the query will evaluate to false. The query compares the two sets and looks for the presence of all elements in both sets.
SELECT * FROM /exampleRegion WHERE sp.containsAll(set('20','21','22))where sp is of type List.
SELECT * FROM /exampleRegion WHERE sp IN SET (set('20','21','22'),set('10',11','12'))where a set value is searched in collection of set values.
One problem is that you cannot create indexes on Set or List types (collection types) that are not comparable. To workaround this, you can create an index on a custom collection type that implements Comparable.
The comparison behavior of Double.NaN and Float.NaN within GemFire queries follow the semantics of the JDK methods Float.compareTo and Double.compareTo.
|If p.value is NaN, the following query:||Evaluates to:||Appears in the result set?|
|SELECT * FROM /positions p WHERE p.value = 0||false||no|
|SELECT * FROM /positions p WHERE p.value > 0||true||yes|
|SELECT * FROM /positions p WHERE p.value >= 0||true||yes|
|SELECT * FROM /positions p WHERE p.value < 0||false||no|
|SELECT * FROM /positions p WHERE p.value <= 0||false||no|
|When p.value and p.value1 are both NaN, the following query:||Evaluates to:||Appears in the result set:|
|SELECT * FROM /positions p WHERE p.value = p.value1||true||yes|
If you combine values when defining the following query in your code, when the query is executed the value itself is considered UNDEFINED when parsed and will not be returned in the result set.
String query = "SELECT * FROM /positions p WHERE p.value =" + Float.NaN
Executing this query, the value itself is considered UNDEFINED when parsed and will not be returned in the result set.
To retrieve NaN values without having another field already stored as NaN, you can define the following query in your code:
String query = "SELECT * FROM /positions p WHERE p.value > " + Float.MAX_VALUE;