WHERE Clause
The condition expression associated with the WHERE clause fetches only those Ehcache elements that match a particular criterion. The general syntax of the condition expression is:
((attribute [ = | > | < | >= | <= | != | ilike | like | in | between ] value)
[ AND | OR | NOT ]
(attribute [ = | > | < | >= | <= | != | ilike | like | in | between ] value)
[ AND | OR | NOT ]
(attribute [ = | > | < | >= | <= | != | ilike | like | in | between ] value)
[ AND | OR | NOT ]
. . .)
For example:
select * from Person where ((age > 21 or zip=94115) and (time > 10 or fun > 100));
Where Examples
select * from Person where age = 18
select * from Person where age != 18
select * from Person where age < 18
select * from Person where age > 18
select * from Person where age <= 18
select * from Person where age >= 18
Ilike Example
select city from Address where city ilike 'San*'
Note: ilike takes the wildcard character * for zero or more characters, and ? for a single character.
To find 'cat' and 'cow', use
select * from searchable where animal ilike 'c*'
To find 'cat' but exclude 'cow', use
select * from searchable where animal ilike 'c?t'
To find elephant and sheep but exclude weasel, use
select * from searchable where animal ilike '*e*p*'
Like Example
select city from Address where city like 'San%'
Note: like takes the wildcard character % for zero or more characters, and _ for a single character.
To find 'cat' and 'cow', use
select * from searchable where animal like 'c%'
to find 'cat' but exclude 'cow', use
select * from searchable where animal like 'c_t'
To find elephant and sheep but exclude weasel, use
select * from searchable where animal like '%e%p%'
BigMemory SQL does not support the following for like and ilike:
[charlist] — Sets and ranges of characters to match
[^charlist] or [!charlist] — Matches only a character NOT specified within the brackets
Between Example
select * from Person where age between 10 and 20
Boolean Operators
select key,value from Person where (age > 100 and zip = 20144)
select key,value from Person where ((age > 100 and zip = 20144) and time > 10)
select key,value from Person where (age > 100 or zip = 20144)
select key,value from Person where ((age > 100 or zip = 20144) or time > 10)
select key,value from Person where ((age > 100 and zip = 20144) or time > 10)
select key,value from Person where ((age > 100 or zip = 20144) and (time > 10 or fun > 100))
select key,value from Person where ((age > 100 or zip = 20144) and time > 10)