Skip Headers
Oracle® Coherence Developer's Guide
Release 3.6.1

Part Number E15723-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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:

Note:

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:

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

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.

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