26 Using Coherence Query Language

26.1.1.1 Using Path-Expressions

One of the main building blocks of CohQL are path-expressions. Path expressions are used to navigate through a graph of object instances. An identifier in a path expression is used to represent a property in the Java Bean sense. It is backed by a ReflectionExtractor that is created by prepending a get and capitalizing the first letter. Elements are separated by the "dot" (.) character, that represents object traversal. For example the following path expression is used to navigate an object structure:

a.b.c

It reflectively invokes these methods:

getA().getB().getC()

26.1.1.2 Using Bind Variables

For programmatic uses, the API passes strings to a simple set of query functions. Use bind variables to pass the value of variables without engaging in string concatenation. There are two different formats for bind variables.

• the question mark (?)—Enter a question mark, immediately followed by a number to signify a positional place holder that indexes a collection of objects that are "supplied" before the query is run. The syntax for this form is: ?n where n can be any number. Positional bind variables can be used by the QueryHelper class in the construction of filters. For example:

  QueryHelper.createFilter("number = ?1" , new Object[]{new Integer(42)};
  

• the colon (:)—Enter a colon, immediately followed by the identifier to be used as a named place holder for the object to be supplied as a key value pair. The syntax for this is :identifier where identifier is an alpha-numeric combination, starting with an alphabetic character. Named bind variables can be used by the QueryHelper class in the construction of filters. For example:

  HashMap env = new HashMap();
  env.put("iNum",new Integer(42));
  QueryHelper.createFilter("number = :iNum" , env};
  

See "Building Filters in Java Programs" for more information on the QueryHelper class and constructing filters programmatically.

26.1.1.3 Using Key and Value Pseudo-Functions

CohQL provides a key() pseudo-function because many users store objects with a key property. The key() represents the cache's key. The query syntax also provides a value() pseudo-function. The value() is implicit in chains that do not start with key(). The key() and value() pseudo-functions are typically used in WHERE clauses, where they test against the key or value of the cache entry. For examples of using key() and value(), see "Key and Value Pseudo-Function Examples" and "A Command-Line Example".

26.1.1.4 Using Aliases

Although not needed semantically, CohQL supports aliases to make code artifacts as portable as possible to JPQL. CohQL supports aliases attached to the cache name and at the head of dotted path expressions in the SELECT, UPDATE, and DELETE commands. CohQL also allows the cache alias as a substitute for the value() pseudo function and as an argument to the key() pseudo function.

26.1.1.5 Using Quotes with Literal Arguments

Generally, you do not have to enclose literal arguments (such as cache-name or service-name) in quotes. Quotes (either single or double) would be required only if the argument contains an operator (such as -, +, ., <, >, =, and so on) or whitespace.

Filenames should also be quoted. Filenames often contain path separators (/ or \) and dots to separate the name from the extension.

The compiler throws an error if it encounters an unquoted literal argument or filename that contains an offending character.

26.1.2.1 Retrieving Data from the Cache

The SELECT statement is the basic building block of a query: it retrieves data from the cache. The clause can take several forms, including simple and complex path expressions, key expressions, transformation functions, multiple expressions, and aggregate functions. The SELECT statement also supports the use of aliases.

The form of the SELECT statement is as follows:

SELECT (properties* aggregators* | * | alias) 
FROM "cache-name" [[AS] alias]
[WHERE conditional-expression] [GROUP [BY] properties+]

The asterisk (*) character represents the full object instead of subparts. It is not required to prefix a path with the cache-name. The FROM part of the SELECT statement targets the cache that forms the domain over which the query should draw its results. The cache-name is the name of an existing cache.

See "Simple SELECT * FROM Statements that Highlight Filters" for additional examples.

Example:

• Select all of the items from the cache dept.

  select * from "dept"
  

26.1.2.2 Filtering Entries in a Result Set

Use the WHERE clause to filter the entries returned in a result set. One of the key features of CohQL is that they can use path expressions to navigate object structure during expression evaluation. Conditional expressions can use a combination of logical operators, comparison expressions, primitive and function operators on fields, and so on.

In the literal syntax of the WHERE clause, use single quotes to enclose string literals; they can be escaped within a string by prefixing the quote with another single quote. Numeric expressions are defined according to the conventions of the Java programming language. Boolean values are represented by the literals TRUE and FALSE. Date literals are not supported.

Operator precedence within the WHERE clause is as follows:

1. Path operator (.)

2. Unary + and -

3. Multiplication ( * ) and division (/ )

4. Addition ( + ) and subtraction ( - )

5. Comparison operators: =, >, >=, <, <=, <>, [ NOT ] BETWEEN, [ NOT ] LIKE, [ NOT ] IN, IS [ NOT ] NULL, CONTAINS [ ALL | ANY ]

6. Logical operators (AND, OR, NOT)

The WHERE clause supports only arithmetic at the language level.

The BETWEEN operator can be used in conditional expressions to determine whether the result of an expression falls within an inclusive range of values. Numeric, or string expressions can be evaluated in this way. The form is: BETWEEN lower AND upper.

The LIKE operator can use the _ and % wildcards. The _ wildcard is used to match exactly one character, while the % wildcard is used to match zero or more occurrences of any characters. To escape the wildcards, precede them with an escape character that is defined using the escape keyword. The following example escapes the % wildcard using the \ escape character in order to select a key literally named k%1.

SELECT key(),value() FROM mycache WHERE key() LIKE "k\%1" escape "\"

In addition, any character may be defined as the escape character. For example:

SELECT key(),value() FROM mycache WHERE key() LIKE "k#%1" escape "#"

The IN operator can check whether a single-valued path-expression is a member of a collection. The collection is defined as an inline-list or expressed as a bind variable. The syntax of an inline-list is:

"(" literal* ")"

CONTAINS [ ALL | ANY ] are very useful operators because Coherence data models typically use de-normalized data. The CONTAINS operator can determine if a many-valued path-expression contains a given value. For example:

e.citys CONTAINS "Boston"

The ALL and ANY forms of CONTAINS take a inline-list or bind-variable with the same syntax as the IN operator.

See "Simple SELECT * FROM Statements that Highlight Filters" for additional examples.

Example:

• Select all of the items in the cache dept where the value of the deptno key equals 10.

  select * from "dept" where deptno = 10
  

26.1.3.1 Creating a Cache

Before sending queries, connect to an existing cache or create a new cache using the CREATE CACHE or ENSURE CACHE statements, respectively. This statement first attempts to connect to a cache with the specified cache-name. If the cache is not found in the cluster, Coherence attempts to create a cache with the specified name based on the current cache configuration file. This statement is especially useful on the command line. If you are using this statement in a program, you have the option of specifying service and classloader information instead of a name (classloaders cannot be accessed from the command line).

The syntax is:

[ CREATE | ENSURE ] CACHE "cache-name" 
[ SERVICE "service-name" ]

Example:

• Create a cache named dept.

  create cache "dept"
  

26.1.3.2 Writing a Serialized Representation of a Cache to a File

Use the BACKUP CACHE statement to write a serialized representation of the given cache to a file represented by the given filename. The filename is an operating system-dependent path and must be enclosed in single or double quotes. The BACKUP CACHE statement is available only in the command-line tool. The syntax is:

BACKUP CACHE "cache-name" [ TO ] [ FILE ] "filename"

Example:

• Write a serialized representation of the cache dept to the file textfile.

  backup cache "dept" to file "textfile"
  

26.1.3.3 Loading Cache Contents from a File

Use the RESTORE CACHE statement to read a serialized representation of the given cache from a file represented by the given filename. The filename is an operating system-dependent path and must be enclosed in single or double quotes. The RESTORE CACHE statement is available only in the command-line tool. The syntax is:

RESTORE CACHE "cache-name" [ FROM ] [ FILE ] "filename"

Example:

• Restore the cache dept from the file textfile.

  restore cache "dept" from file "textfile"
  

26.1.3.4 Removing a Cache from the Cluster

Use the DROP CACHE statement to remove the specified cache completely from the cluster. The cache is removed by a call to the Java destroy() method. If any cluster member holds a reference to the dropped cache and tries to perform any operations on it, then the member receives an IllegalStateException. The syntax for the Coherence query DROP CACHE statement is:

DROP CACHE  "cache-name"

Example:

• Remove the cache orders from the cluster.

  drop cache "orders"
  

26.1.4.1 Aggregating Query Results

An aggregate query is a variation on the SELECT query. Use an aggregate query when you want to group results and apply aggregate functions to obtain summary information about the results. A query is considered an aggregate query if it uses an aggregate function or has a GROUP BY clause. The most typical form of an aggregate query involves the use of one or more grouping expressions followed by aggregate functions in the SELECT clause paired with the same lead grouping expressions in a GROUP BY clause.

CohQL supports these aggregate functions: COUNT, AVG, MIN, MAX, and SUM.

See "Complex Queries that Feature Projection, Aggregation, and Grouping" for additional examples.

Example:

• Select the total amount and average price for items from the orders cache, grouped by supplier.

  select supplier,sum(amount),avg(price) from "orders" group by supplier
  

26.1.4.2 Changing Existing Values

Use the UPDATE statement to change an existing value in the cache. The syntax is:

UPDATE "cache-name" [[AS] alias]
SET update-statement {, update-statement}* 
[ WHERE conditional-expression ]

Each update-statement consists of a path expression, assignment operator (=), and an expression. The expression choices for the assignment statement are restricted. The right side of the assignment must resolve to a literal, a bind-variable, a static method, or a new Java-constructor with only literals or bind-variables. The UPDATE statement also supports the use of aliases.

See "UPDATE Examples" for additional examples.

Example:

• For employees in the employees cache whose ranking is above grade 7, update their salaries to 1000 and vacation hours to 200.

  update "employees" set salary = 1000, vacation = 200 where grade > 7
  

26.1.4.3 Inserting Entries in the Cache

Use the INSERT statement to store the given VALUE under the given KEY. If the KEY clause is not provided, then the newly created object is sent the message getKey(), if possible. Otherwise, the value object is used as the key.

Note that the INSERT statement operates on Maps of Objects. The syntax is:

INSERT INTO "cache-name"
[ KEY (literal | new java-constructor | static method) ]
VALUE (literal | new java-constructor | static method)

Example:

• Insert the key writer with the value David into the employee cache.

  insert into "employee" key "writer" value "David"
  

26.1.4.4 Deleting Entries in the Cache

Use the DELETE statement to delete specified entries in the cache. The syntax is:

DELETE FROM "cache-name" [[AS] alias] 
[WHERE conditional-expression]

The WHERE clause for the DELETE statement functions the same as it would for a SELECT statement. All conditional-expressions are available to filter the set of entities to be removed. The DELETE statement also supports the use of aliases.

Example:

• Delete the entry from the cache employee where bar.writer key is not David.

  delete from "employee" where bar.writer IS NOT "David"
  

26.1.5.1 Creating an Index on the Cache

Use the CREATE INDEX or the ENSURE INDEX statement to create indexes on an identified cache. The syntax is:

[ CREATE | ENSURE ] INDEX [ON] "cache-name" (value-extractor-list)

The value-extractor-list is a comma-delimited list that uses path expressions to create ValueExtractors. If multiple elements exist, then a MultiExtractor is used. To create a KeyExtractor, then start the path expression with a key() pseudo-function.

Natural ordering for the index is assumed.

Example:

• Create a index on the attribute lastname in the orders cache.

  create index "orders" lastname
  

26.1.5.2 Removing an Index from the Cache

The DROP INDEX statement removes the index based on the given ValueExtractor. This statement is available only for the command-line tool. The syntax is:

DROP INDEX [ON] "cache-name" (value-extractor-list)

Example:

• Remove the index on the lastname attribute in the orders cache.

  drop index "orders" lastname
  

26.1.6.1 Processing Query Statements in Batch Mode

The SOURCE statement allows for the "batch" processing of statements. The SOURCE statement opens and reads one or more query statements from a file represented by the given filename. The filename is an operating system-dependent path and must be enclosed in single or double quotes. Each query statement in the file must be separated by a semicolon (;) character. Sourcing is available only in the command-line tool, where you naturally want to load files consisting of sequences of commands. Source files may source other files. The syntax is:

SOURCE FROM [ FILE ] "filename"

SOURCE can be abbreviated with an "at" symbol (@) as in @"filename". On the command command line only, a "period" symbol '.' can be used as an abbreviation for '@' but must no contain quotes around the filename.

Example:

• Process the statements in the file command_file.

  source from file "command_file"
  

  or,

  @ "command_file"
  

  or,

  . command_file