Package ch.tocco.nice2.persist.core.api.query

package ch.tocco.nice2.persist.core.api.query
This package contains the classes and interfaces needed for querying entities.

The Query Language

In order to be able to query entities based on arbitrary search criteria independently from the storage backend, the persistence layer defines its own query language.

To build queries in code please refer to the interface QueryBuilder

There are three basic forms of queries: 

  *  Query for all:

         find MyEntity

     Note that named queries are currently not implemented and probably never will be.
     We found that [functions](#functions) are just fine.

  *  Find by criteria:

         find MyEntity where someField == 23 or someRelation.anotherField == 42
The two first forms should be self-explanatory, so the following describes the last form only.

Query Syntax

Keywords

The following words are keywords: find, where, and, or, not, null, true, false, exists, secure, insecure.

There are some more keywords which are reserved for possible future extensions: keys, in, exists.

Identifiers

Any word that follows the usual Java identifier constraints and isn't a keyword is an identifier. If an identifier matches a keyword (e.g. a field called "exists") or doesn't match the identifier constraints may be enclosed in single quotes. For example, 
     find MyEntity where where == "Some Location" or my field > 42
would result in syntax errors: The second <<<where>>> is not allowed here, neither is field after my. To get around this problem, you may write: 
     find MyEntity where 'where' == "Some Location" or 'my field' > 24
Quoted identifiers may contain any characters you like. Also, all Java escape sequences are supported.

A special form of identifiers are paths, which are used to follow relations: 

     find Person where city.country.isoCode == "CH"
Find all persons living in a city located in Switzerland. The quoted form of this would be: 
     find 'Person' where 'city'.'country'.'isoCode' == "CH"

Literals

You've already seen two kinds of literals: Integer literals and string literals. Like in quoted identifiers, all Java escape sequences are supported within string literals. Integer literals also follow the Java conventions, i.e. <<<0xa>>> is the hex number A, 03 is the octal number 3.

Floating point literals are also supported and follow the same rules as in Java.

Other supported literals are the boolean literals true and false, and the literal for null: null.

Finally, there are special literals for non-builtin types. These take the form 

     type:'value'

 Some examples:

 decimal:"12345"
 : A big decimal.

 date:"2009-01-23"
 : A date.

 time:"12:30"
 : A time.

 datetime:"2009-01-23 12:30"
 : Date and time.
Time values may also contain seconds (13:30:15) or milliseconds (12:30:15.250).

Parameters

Parameters have the form :myParameter. The values can be injected into a query using the (compiled) query's Query.setParameter(String, Object) setParameter()} method.

The where Clause

The where clause specifies the actual search criteria and conjunctions. The conjunctions are the usual ones, or, and and not, their precedence as listed, i.e. 
     A and B or not C and D

 can also be written as

     (A and B) or ((not C) and D)
The actual criteria are boolean expressions. The following operators are available: ==, !=, >, >=, <, <= and ~=. ~= is similar to the LIKE operator in SQL and supported for string fields only.

The supported wildcard characters are the asterisk * (for SQL %, zero to any characters) and the questionmark ? (exactly one character, most probably depends on the character set in use (db table or connection) for what the boundaries of "one character" are).

Do not use the % and _ wildcards in the QL; they work at the moment because they are transparently passed on to sql, but may be subject to escaping in the future.

As of now it is not possible to escape the wildcard characters. If really required then it can be implemented.

The non-existent limit Clause

In SQL there is various non-standard offset and limit syntax. In nice2 the limit parameters must be specified when executing the query: 
     Query.execute(int limit)

The order by Clause

In SQL the order by clause is part of the command. In nice2 it can either be done as in sql, or the ordering can be added to an ordering object. Example: 

 Query query = userEntity.getContext().compileQuery("find Interface_language");
 Ordering ordering = query.getOrdering();
 ordering.append("sorting", Ordering.Direction.ASCENDING);
Assume that if the query contains an order clause, and then later on ordering is appended using query.getOrdering().append(), that the code just appends, and clear() also clears the one from the query string.

exists() and count() subqueries

Using exists() you can check for the existence of a specific related entity. The syntax is as follows: 
     find MyEntity where exists(myRelation.anotherRelation [where ...])
Any paths in the optional where clause will be relative to the target entity.

Functions

A function may be part of a boolean expression but it must contributed to the backend of choice first. If contributed, the function is recognized by the parser and generation of the target language is delegated to the function implementation.

A function is used as follows: 

     functionname ( [param1 [, paramN]] )
The function arguments may be a mix of literals, identifiers or parameters.

A simple example that assumes a contributed function of name DISTANCE: 

     find Cities where DISTANCE(lat, lng, :lat2, :lng2) < :dist