34/68

# 24 Using Coherence Query Language

This chapter describes how to use Coherence Query Language (CohQL) to interact with Coherence caches. CohQL is a new light-weight syntax (in the tradition of SQL) that is used to perform cache operations on a Coherence cluster. The language can be used either programmatically or from a command-line tool.

The following sections are included in this chapter:

- Understanding Coherence Query Language Syntax

- Using the CohQL Command-Line Tool

- Building Filters in Java Programs

- Additional Coherence Query Language Examples

Note:

- Although the CohQL syntax may appear similar to SQL, it is important to remember that the syntax is not SQL and is actually more contextually related to the Java Persistence Query Language (JPQL) standard.

- CQL (Continuous Query Language) is a query language related to Complex Event Processing (CEP) and should not be confused with CohQL.

## Understanding Coherence Query Language Syntax

The following sections describe the functionality provided by CohQL. Each section describes a particular statement, its syntax, and an example. You can find more query examples in "Additional Coherence Query Language Examples".

Note:

The current release of CohQL does not support subqueries.

The following topics are included in this section:

- Query Syntax Basics

- Retrieving Data

- Managing the Cache Lifecycle

- Working with Cache Data

- Working with Indexes

- Issuing Multiple Query Statements

For reference, Table 24-1 lists the Coherence query statements, clauses, and expressions in alphabetical order.

Table 24-1 Coherence Query Language Statements

For this statement, clause, or expression... | See this section
--- | ---
BACKUP CACHE | "Writing a Serialized Representation of a Cache to a File"
bind variables | "Using Bind Variables"
CREATE CACHE | "Creating a Cache"
CREATE INDEX | "Creating an Index on the Cache"
DELETE | "Deleting Entries in the Cache"
DROP CACHE | "Removing a Cache from the Cluster"
DROP INDEX | "Removing an Index from the Cache"
ENSURE CACHE | "Creating a Cache"
ENSURE INDEX | "Creating an Index on the Cache"
GROUP BY | "Aggregating Query Results"
INSERT | "Inserting Entries in the Cache"
key() pseudo-function | "Using Key and Value Pseudo-Functions"
path-expressions | "Using Path-Expressions"
RESTORE CACHE | "Loading Cache Contents from a File"
SELECT | "Retrieving Data from the Cache"
SOURCE | "Processing Query Statements in Batch Mode"
UPDATE | "Changing Existing Values"
value() pseudo-function | "Using Key and Value Pseudo-Functions"
WHERE | "Filtering Entries in a Result Set"

### Query Syntax Basics

This section describes some of the building blocks of the syntax, such as path expressions, bind variables, and pseudo-functions.

#### 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()

#### Using Bind Variables

For programmatic uses, the API passes strings to a simple set of query functions. Use bind variables if you want to pass the value of variables and do not want to engage 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 will be "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.

#### 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".

#### Using Aliases

Although not needed semantically, CohQL supports aliases in order 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.

#### Using Quotes with Literal Arguments

Generally, you do not need 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 will throw an error if it encounters an unquoted literal argument or filename that contains an offending character.

### Retrieving Data

The following sections describe the `SELECT` statement and the `WHERE` clause. These entities are the basic building blocks of most cache queries.

#### Retrieving Data from the Cache

The `SELECT` statement is the basic building block of a query: it indicates that you want to retrieve 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"

#### 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.

Note:

CohQL does not have access to type information. This means that if a getter returns a numeric type different than the type of the literal, you may get a `false` where you would have expected a `true` on the comparison operators. The work around is to specify the type of the literal with `l`, for `long`, `d` for `double`, or `s` for `short`. The defaults are `Integer` for literals without a period (`.`) and `Float` for literals with a period (`.`).

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 "`%`" wild-cards.

The `IN` operator can be used to 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 be used to 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.

Note:

Coherence provides a programmatic API that allows you to create standalone Coherence filters based on the `WHERE` clause conditional-expression syntax. See "Building Filters in Java Programs".

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

### Managing the Cache Lifecycle

The following sections describe how to create and remove caches. They also describe how to backup and restore cache contents.

#### Creating a Cache

Before you begin sending queries, you must first either connect to an existing cache or create a new cache. To do this, you can use either the `CREATE` `CACHE` or `ENSURE` `CACHE` statement. This statement will first attempt to connect to a cache with the specified `cache-name`. If the cache is not found in the cluster, Coherence attempts to create a new 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).

Note:

Cache names and service names must be enclosed in quotes (either double-quotes `("` `"`) or single-quotes (`'` `'`)) in a statement.

The syntax is:

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

Example:

- Create a cache named `dept`.

      create cache "dept"

#### 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"

Note:

The backup (and subsequent restore) functionality is designed for use in a development and testing environment and should not be used on a production data set as it makes no provisions to ensure data consistency. It is not supported as a production backup, snapshot, or checkpointing utility.

In particular:

- The backup is slow since it only operates on a single node in the cluster.

- The backup is not atomic. That is, it will miss changes to elements which occur during the backup and will result in a dirty read of the data.

- The backup stops if an error occurs and results in an incomplete backup. In such scenarios, an `IOException` is thrown that describes the error.

Example:

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

      backup cache "dept" to file "textfile"

#### 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"

#### 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 will receive 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"

### Working with Cache Data

The following sections describe how to work with data in the cache, such as inserting and deleting cache data and filtering result sets.

#### 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 possesses 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

#### 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

#### 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 will be sent the message `getKey()`, if possible. Otherwise, the `value` object will be 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"

#### 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.

Be Careful:

If the `WHERE` clause is not present, then all entities in the given cache are removed.

Example:

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

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

### Working with Indexes

The following sections describe how to create and remove indexes on cache data. Indexes are a powerful tool that allows Coherence's built-in optimizer to more quickly and efficiently analyze queries and return results.

#### 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-separated list that uses path expressions to create `ValueExtractors`. If more than one element exists, then a `MultiExtractor` is used. If you want 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

#### 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

### Issuing Multiple Query Statements

The following section describes how to more efficiently issue multiple query statements to the cache.

#### 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 semi-colon (`;`) 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

## Using the CohQL Command-Line Tool

The CohQL command-line tool provides a non-programmatic way to interact with caches by allowing statements to be issued from the command line. The tool can be run using the `com.tangosol.coherence.dslquery.QueryPlus` class or, for convenience, a startup script is available to run the tool and is located in the `COHERENCE_HOME``/bin/` directory. The script is available for both Windows (`query.cmd`) and Unix (`query.sh`).

The script starts a cluster node in console mode; that is, storage is not enabled on the node. This is the suggested setting for production environments and assumes that the node will join a cluster that contains storage-enabled cache servers. However, a storage-enabled node can be created for testing by changing the `storage_enabled` setting in the script to `true`.

Note:

As configured, the startup script uses the default operational configuration file (`tangosol-coherence.xml`) and the default cache configuration file (`coherence-cache-config.xml`) that are located in the `coherence.jar` when creating/joining a cluster and configuring caches. For more information on configuring Coherence, see Chapter 3, "Understanding Configuration."

The script also provides the option for setting the `COHERENCE_HOME` environment variable as well as the `JLINE_HOME` environment variable. If `COHERENCE_HOME` is not already set on the computer, set it in the script to the location where Coherence was installed. The `JLINE_HOME` environment variable should be set to the location of the JLine JAR, which is used for enhanced command-line editing capabilities, such as having the up and down arrows move through the command history. However, JLine is not required to use CohQL.

Out-of-Box, the script expects the `jline-0.9.94.jar` library to be located in the `COHERENCE_HOME``/bin/` directory. If it is not found, a message displays indicating that the JAR was not loaded. Copy the JAR to the `COHERENCE_HOME``/bin/` directory, or modify the `JLINE_HOME` variable to point to the location of the JAR.The JLine JAR can be downloaded from the following location.

`http://jline.sourceforge.net/`

### Starting the Command-line Tool

The following procedure demonstrates how to start the CohQL command-line tool using the startup script and assumes that the `storage_enabled` setting in the script is set to `false` (the default):

1. Start a cache server cluster node or ensure that an existing cache server cluster node is started.

   To start a cache server cluster node, open a command prompt or shell and execute the cache server startup script that is located in the `/bin` directory: `cache-server.cmd` on the Windows platform or `cache-server.sh` for UNIX platforms. The cache server starts and output is emitted that provides information about this cluster member.

2. Open a command prompt or shell and execute the CohQL command-line startup script that is located in the `/bin` directory: `query.cmd` on the Windows platform or `query.sh` for UNIX platforms. Information about the Java environment displays. The command-line tool prompt (`CohQL>`) is returned.

   Note:

   When joining an existing cache server node, modify the startup script to use the same cluster settings as the existing cache server node, including the same cache configuration.

3. Enter `help` at the prompt to view the complete command-line help. Enter `commands` to list the help without detailed descriptions.

   Typically, your first statement will be to create a new cache or connect to an existing cache. See "Creating a Cache".

   "A Command-Line Example" illustrates a series of query statements that exercise the command-line tool.

### Using Command-Line Tool Arguments

The CohQL command-line tool includes a set of arguments that are read and executed before the `CohQL>` prompt returns. This is useful when using the script as part of a larger script– for example, as part of a build process or to pipe input/output. Enter `help` at the `CohQL>` prompt to view help for the arguments within the command-line tool.

Table 24-2 Coherence Query Language Command-Line Tool Arguments

Argument | Description
--- | ---
`-t` | enable trace mode to print debug information.
`-c` | Exit the command-line tool after processing the command-line arguments. This argument should not be used when redirecting from standard input; in which case, the tool exits as soon as the command line arguments are finished being processed and the redirected input is never read.
`-s` | Run the command-line tool in silent mode to remove extraneous verbiage. This allows the command line tool to be used in pipes or filters by redirecting standard input (`<myInput`) and standard output (`>myOuput`).
`-e` | Run the command-line tool in extended language mode. This mode allows object literals in update and insert commands. See the command-line help for complete usage information.
`-l` `statement` | Execute the given statement. Statements must be enclosed in single or double quotes. Any number of `-l` arguments can be used.
`-f` `filename` | Process the statements in the given file. The statements in the file must be separated by a semi-colon (;). The file is an operating system-dependent path and must be enclosed in single or double quotes. Any number of `-f` arguments can be used.

Examples

Return all entries in the `contact` cache and print the entries to the standard out then exit the command-line tool.

    query.sh -c -l "select * from contact"

Return all entries in the `dist-example` cache and print the entries (suppressing extra verbiage) to the file named `myOutput` then exit the command-line tool.

    query.cmd -s -c -l "select * from 'dist-example'" >myOutput

Process all the segments in the file named `myStatements` then exit the command-line tool.

    query.sh -c -f myStatements

Read the commands from the `myInput` file and print the output (suppressing extra verbiage) to the file named `myOutput`.

    query.sh -s <myInput >myOutput

### A Command-Line Example

Example 24-1 illustrates a simple example that exercises the command-line tool on Windows. This example is intended to test statements against a local cache, so the `storage_enabled` setting in the startup script is set to `true`. The example illustrates creating and dropping a cache, storing and retrieving entries, and restoring the cache from a backup file. It also highlights the use of the `key()` and `value()` pseudo-functions.

When you start `query.cmd` at the command prompt, information about the Java environment, the Coherence version and edition, and Coherence cache server is displayed. You then receive a prompt (`CohQL>`) where you can enter your query statements.

Annotations that describe the commands and their output have been added to the example in bold-italic font. Here is an example:

    < This is an annotation. >

Example 24-1 A Command-Line Query Exercise

    C:/coherence/bin/query.cmd
    ** Starting storage enabled console **
    java version "1.6.0_14"
    Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
    Java HotSpot(TM) Server VM (build 14.0-b16, mixed mode)

    2010-01-27 16:54:07.501/0.265 Oracle Coherence 3.6.0.0 Internal <Info> (thread=main, member=n/a): Loaded operational configuration from "jar:file:/C:/coherence360/coherence/lib/coherence.jar!/tangosol-coherence.xml"
    2010-01-27 16:54:07.501/0.265 Oracle Coherence 3.6.0.0 Internal <Info> (thread=main, member=n/a): Loaded operational overrides from "jar:file:/C:/coherence360/coherence/lib/coherence.jar!/tangosol-coherence-override-dev.xml"
    2010-01-27 16:54:07.501/0.265 Oracle Coherence 3.6.0.0 Internal <D5> (thread=main, member=n/a): Optional configuration override "/tangosol-coherence-override.xml" is not specified
    2010-01-27 16:54:07.517/0.281 Oracle Coherence 3.6.0.0 Internal <D5> (thread=main, member=n/a): Optional configuration override "/custom-mbeans.xml" is not specified

    Oracle Coherence Version 3.6.0.0 Internal Build 0
     Grid Edition: Development mode
    Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

    2010-01-27 16:54:09.173/1.937 Oracle Coherence GE 3.6.0.0 Internal <D5> (thread=Cluster, member=n/a): Service Cluster joined the cluster with senior service member n/a
    2010-01-27 16:54:12.423/5.187 Oracle Coherence GE 3.6.0.0 Internal <Info> (thread=Cluster, member=n/a): Created a new cluster "cluster:0xC4DB" with Member(Id=1, Timestamp=2010-01-27 16:54:08.032, Address=130.35.99.213:8088, MachineId=49877, Location=site:us.oracle.com,machine:tpfaeffl-lap7,process:4316, Role=TangosolCoherenceQueryPlus, Edition=Grid Edition, Mode=Development, CpuCount=2, SocketCount=1) UID=0x822363D500000126726BBBA0C2D51F98
    2010-01-27 16:54:12.501/5.265 Oracle Coherence GE 3.6.0.0 Internal <D5> (thread=Invocation:Management, member=1): Service Management joined the cluster with senior service member 1

       < Create a cache named "employees". >
    CohQL> create cache "employees"

    2010-01-27 16:54:26.892/19.656 Oracle Coherence GE 3.6.0.0 Internal <Info> (thread=main, member=1): Loaded cache configuration from "jar:file:/C:/coherence360/coherence/lib/coherence.jar!/coherence-cache-config.xml"
    2010-01-27 16:54:27.079/19.843 Oracle Coherence GE 3.6.0.0 Internal <D5> (thread=DistributedCache, member=1): Service DistributedCache joined the cluster with senior service member 1
    2010-01-27 16:54:27.095/19.859 Oracle Coherence GE 3.6.0.0 Internal <D5> (thread=DistributedCache, member=1): Service DistributedCache: sending Config
    Sync to all
    Result

       < Insert an entry (key-value pair) into the cache. >
    CohQL> insert into "employees" key "David" value "ID-5070"

       < Change the value of the key. >
    CohQL> update employees set value() = "ID-5080" where key() like "David"
    Result
    David, true

       < Retrieve the values in the cache. >
    CohQL> select * from "employees"
    Result
    ID-5080

       < Retrieve the value of a key that does not exist. An empty result set is returned >
    CohQL> select key(), value() from "employees" where key() is "Richard"
    Result

       < Delete an existing key in the cache. An empty result set is returned. >
    CohQL> delete from employees where key() = "David"
    Result

      < Delete the contents of the employees cache. An empty result set is returned. >
    CohQL> delete from "employees"
    Result

       < Destroy the employees cache. >
    CohQL> drop cache "employees"

       < Re-create the employees cache. >
    CohQL> create cache "employees"
    Result

       < Insert more entries into the cache. >
    CohQL> insert into "employees" key "David" value "ID-5080"

    CohQL> insert into "employees" key "Julie" value "ID-5081"

    CohQL> insert into "employees" key "Mike" value "ID-5082"

    CohQL> insert into "employees" key "Mary" value "ID-5083"

       < Retrieve the keys and value in the enployees cache. >
    CohQL> select key(), value() from "employees"
    Result
    Julie, ID-5081
    Mike, ID-5082
    Mary, ID-5083
    David, ID-5080

       < Save a serialized representation of the cache in a file. >
    CohQL> backup cache "employees" to "emp.bkup"

       < Delete a key from the cache. >
    CohQL> delete from "employees" where key() = "David"
    Result

       < Retreive the cache contents again, notice that the deleted key and value are not present. >
    CohQL> select key(), value() from "employees"
    Result
    Julie, ID-5081
    Mike, ID-5082
    Mary, ID-5083

       < Delete the contents of the cache. >
    CohQL> delete from "employees"
    Result

       < Retrieve the contents of the cache. An empty result set is returned. >
    CohQL> select * from "employees"
    Result

       < Restore the cache contents from the backup file. >
    CohQL> restore cache "employees" from file "emp.bkup"

       < Retrieve the cache contents. Note that all of the entries have been restored and returned. >
    CohQL> select key(), value() from "employees"
    Result
    Julie, ID-5081
    Mike, ID-5082
    Mary, ID-5083
    David, ID-5080

       < Destroy the employees cache. >
    CohQL> drop cache "employees"

       < Exit the command-line tool. >
    CohQL> bye

## Building Filters in Java Programs

The `FilterBuilder` API is a string-oriented way to filter a result set from within a Java program, without having to remember details of the Coherence API. To do this, the API provides a set of four overloaded `createFilter` factory methods in the `com.tangosol.util.QueryHelper` class.

The following list describes the different forms of the `createFilter` method. The passed string uses the Coherence query `WHERE` clause syntax (described in "Filtering Entries in a Result Set"), but without the literal `WHERE`. The forms that take an `Object` array or `Map` are for passing objects that are referenced by bind variables. Each form constructs a filter from the provided Coherence query string.

- `public static Filter createFilter(String s)`—where `s` is a `String` in the Coherence query representing a `Filter`.

- `public static Filter createFilter(String s, Object[] aBindings)`—where `s` is a `String` in the Coherence query representing a `Filter` and `aBindings` is an array of `Objects` to use for bind variables.

- `public static Filter createFilter(String s, Map bindings)`—where `s` is a `String` in the Coherence query representing a `Filter` and `bindings` is a `Map` of `Objects` to use for bind variables.

- `public static Filter createFilter(String s, Object[] aBindings, Map bindings)`—where `s` is a `String` in the Coherence query representing a `Filter`, `aBindings` is an array of `Objects` to use for bind variables, and `bindings` is a `Map` of `Objects` to use for bind variables.

These factory methods will throw an `FilterBuildingException` if there are any malformed, syntactically incorrect expressions, or semantic errors. Since this exception is a subclass of `RuntimeException`, catching the error is not required, but the process could terminate if you do not.

Example

The following statement uses the `createFilter(String s)` form of the method. It constructs a filter for employees who live in Massachusetts but work in another state.

    ..
    QueryHelper.createFilter("homeAddress.state = 'MA'  and workAddress.state != 'MA'")
    ...

This statement is equivalent to the following filter/extractor using the Coherence API:

    AndFilter(EqualsFilter(ChainedExtractor(#getHomeAddress[], #getState[]), MA),
    NotEqualsFilter(ChainedExtractor(#getWorkAddress[], #getState[]), MA)))

The `QueryHelper` class also provides a `createExtractor` method that allows you to create value extractors when building filters. The extractor is used to both extract values (for example, for sorting or filtering) from an object, and to provide an identity for that extraction. The following example demonstrates using `createExtractor` when creating an index:

    cache.addIndex(QueryHelper.createExtractor("key().lastName"),/*fOrdered*/ true,
      /*comparator*/ null);

## Additional Coherence Query Language Examples

This section provides additional examples and shows their equivalent Coherence API calls with instantiated Objects (`Filters`, `ValueExtractors`, `Aggregators`, and so on). The simple `select` `*` examples that highlight `Filters` can be used to understand the translation for `FilterBuilder` API if you focus only on the `Filter` part. The full set of examples can be used to understand the translation for the `QueryBuilder` API and the command-line tool.

The examples use an abbreviated form of the path syntax where the cache name to qualify an identifier is dropped.

The Java language form of the examples also use `ReducerAggregator` instead of `EntryProcessors` for projection. Note also that the use of `KeyExtractor` should no longer be needed given changes to `ReflectionExtractor` in Coherence 3.5.

### Simple SELECT * FROM Statements that Highlight Filters

- Select the items from the cache `orders` where `40` is greater than the value of the `price` key.

      select * from "orders" where 40 > price

- Select the items from the cache `orders` where the value of the `price` key exactly equals 100, and the value of `insurance` key is less than 10 or the value of the `shipping` key is greater than or equal to 20.

      select * from "orders" where price is 100 and insurance < 10 or shipping >= 20

- Select the items from the cache `orders` where the value of the `price` key exactly equals 100, and either the value of `insurance` key is less than 10 or the value of the `shipping` key is greater than or equal to 20.

      select * from "orders" where price is 100 and (insurance < 10 or shipping >= 20)

- Select the items from the cache `orders` where either the value of the `price` key equals `100`, or the `bar` key equals `20`.

      select * from "orders" where price = 100 or shipping = 20

- Select the items from the cache `orders` where the value of the `insurance` key is not null.

      select * from "orders" where insurance is not null

- Select the items from the cache `employees` where the `emp_id` key has a value between 1 and 1000 or the `bar.emp` key is not "`Smith`".

      select * from "employees" where emp_id between 1 and 1000 or bar.emp is not "Smith"

- Select items from the cache `orders` where the value of `item` key is similar to the value "`coat`".

      select * from "orders" where item like "coat%"

- Select items from the cache `employees` where the value of `emp_id` is in the set 5, 10, 15, or 20.

      select * from "employees" where emp_id in (5,10,15,20)

- Select items from the cache `employees` where `emp_id` contains the list `5`, `10`, `15`, and `20`.

      select * from "employees" where emp_id contains (5,10,15,20)

- Select items from the cache `employees` where `emp_id` contains the all of the items `5`, `10`, `15`, and `20`.

      select * from "employees" where emp_id contains all (5,10,15,20)

- Select items from the cache `employees` where `emp_id` contains any of the items `5`, `10`, `15`, or `20`.

      select * from "employees" where emp_id contains any (5,10,15,20)

- Select items from cache `employees` where the value of `foo` key is less than 10 and occurs in the set 10, 20.

      select * from "employees" where emp_id < 10 in (10,20)

### Complex Queries that Feature Projection, Aggregation, and Grouping

- Select the home `state` and `age` of employees in the cache `ContactInfoCache`, and group by `state` and `age`.

      select homeAddress.state, age, count() from "ContactInfoCache" group by homeAddress.state, age

- Select the spurious `frobit` key from the `orders` cache. Note, an empty result set will be returned.

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

- For the items in the `orders` cache that are greater than $1000, select the items, their prices and colors.

      select item_name,price,color from "orders" where price > 1000

- Select the total `amount` for items from the `orders` cache.

      select sum(amount) from "orders"

- Select the total `amount` for items from the `orders` cache where the `color` attribute is `red` or `green`.

      select sum(amount) from "orders" where color is "red" or color is "green"

- Select the total `amount` and average `price` for items from the `orders` cache

      select sum(amount),avg(price) from "orders"

- Select one copy of the `last` `name` and `city` from possible duplicate rows from the `employees` cache, where the `state` is California.

      select distinct lastName,city from "employees" where state = "CA"

### UPDATE Examples

- For employees in the `employees` cache whose ranking is above grade 7, increase their salaries by 10% and add 50 hours of vacation time.

      update "employees" set salary = salary*1.10, vacation = vacation + 50 where grade > 7

### Key and Value Pseudo-Function Examples

This section provides examples of how to use the `key()` and `value()` pseudo-functions. For additional examples, see "A Command-Line Example".

- Select the employees from the `ContactInfoCache` whose home address is in Massachusetts, but work out of state.

      select key().firstName, key().lastName from "ContactInfoCache" homeAddress.state is 'MA' and workAddress.state != "MA"

- Select the employees from the `ContactInfoCache` cache whose age is greater than 42.

      select key().firstName, key().lastName, age from "ContactInfoCache" where age > 42