Apache Geode

Using Query Bind Parameters

Using query bind parameters in Geode queries is similar to using prepared statements in SQL where parameters can be set during query execution. This allows user to build a query once and execute it multiple times by passing the query conditions during run time.

Query objects are thread-safe.

The use of query bind parameters is now supported in Client-to-Server queries.

The query parameters are identified by a dollar sign, $, followed by a digit that represents the parameter’s position in the parameter array passed to the execute method. Counting begins at 1, so $1 references the first bound attribute, $2 the second attribute, and so on.

The Query interface provides an overloaded execute method that accepts parameters inside an Object array. See the Query.execute JavaDocs for more details.

The 0th element of the Object array is used for the first query parameter, and so on. If the parameter count or parameter types do not match the query specification, the execute method throws an exception. Specifically, if you pass in the wrong number of parameters, the method call throws a QueryParameterCountInvalidException. If a parameter object type is not compatible with what is expected, the method call throws a TypeMismatchException.

In the following example, the first parameter, the integer 2, is bound to the first element in the object array. The second parameter, active, is bound to the second element.

Sample Code

// specify the query string
String queryString = "SELECT DISTINCT * FROM /exampleRegion p WHERE p.id = $1 and p.status = $2";

QueryService queryService = cache.getQueryService();
Query query = queryService.newQuery(queryString);

// set query bind parameters
Object[] params = new Object[2];
params[0] = 2;
params[1] = "active";

// Execute the query locally. It returns the results set.
SelectResults results = (SelectResults) query.execute(params);

// use the results of the query; this example only looks at the size 
 int size = results.size();

Using Query Bind Parameters in the Path Expression

Additionally the query engine supports the use of query bind parameter in place of a region path. When you specify a bind parameter in the query’s FROM clause, the parameter’s referenced value must be bound to a collection.

Examples:

The following query can be used on any collection by passing in the collection as a query parameter value. In this query you could pass in a Region object for $1, but not the String name of a region.

SELECT DISTINCT * FROM $1 p WHERE p.status = $2

Get all the keys from the region passed as a Region object for $1 for those entries whose name attribute is equal to the value passed for $2:

SELECT e.key FROM ($1).entrySet e WHERE e.value.name=$2"