SQL Syntax and Examples

This section provides a detailed specification for the SELECT clause. The general form of SELECT statement is:

SELECT [ * | KEY | VALUE | 
       [ (attribute 1, attribute 2, ... , attribute N) | aggregatorFunction(attribute) ] ] 
     [ FROM cache] 
     [ WHERE condition ] 
     [ GROUP BY attribute] 
     [ ORDER BY {attribute}] 
     [ LIMIT { count }]

The SELECT clause defines the Attributes to be selected.

You can also select the cache key and value denoted by the keywords key and value. A SELECT clause can have Attributes, keys, and values, in any order. To return all searchable attributes, use the wildcard character *.

The name of the cache to be queried is specified using the FROM clause.

// get all attributes for a person named Dave 
select * from Person where name = 'Dave' 
//get only the key 
select key from Person where name = 'Dave'  
//get only the value 
select value from Person where name = 'Dave'            
// get key, value and all attributes for a person named Dave 
select *, key, value from Person where name = 'Dave' 
// get only the age for a person named Dave 
select age from Person where name = 'Dave'           
// get both age and zip 
select age, zip from Person where name = 'Dave'

By default, the QueryManager locates the cache name attached to the FROM clause from all CacheManagers specified when the QueryManager was built.

... from [ Cache | CacheManager.Cache ] ...

If there are multiple CacheManagers, two or more might have a cache with the same name. For example, suppose that in addition to the ehcache-users.xml configuration (presented in Using BigMemory SQL), there is another configuration file called ehcache-address.xml that provides detailed address information.

<ehcache name="Detailed-Address"> 
  <!--another cache with the name "Address" is present in ehcache-users.xml--> 
  <cache name="Address"> 
    <searchable> 
      <searchAttribute name="name" type="String" expression="value.getName()"/>         
      <searchAttribute name="street" type="String" expression="value.getStreet()"/> 
      <searchAttribute name="apartment" type="String" expression="value.getApartment()"/> 
      <searchAttribute name="city" type="String" expression="value.getCity()"/> 
      <searchAttribute name="zip" type="int" expression="value.getZip()"/> 
    </searchable> 
  </cache> 
</ehcache>

Because more than one cache has the name Address, we prefix the CacheManager name, that is, Users.Address or Detailed-Address.Address:

// get cache managers 
CacheManager usersCacheManager = new CacheManager("ehcache-users.xml"); 
CacheManager addressCacheManager = new CacheManager("ehcache-address.xml"); 
// needed only once 
QueryManager qm = QueryManagerBuilder.newQueryManagerBuilder() 
.addAllCahcesCurrentlyIn(Users,Detailed-Address) 
.build(); 
// ** Default use case 
// Since the Person cache is unique, we don't need to supply the cache manager 
Query nameQuery1 = qm.createQuery("select name from Person where age > 21"); 
// ** Explicit scoping use case #1 
// search Address cache defined in ehcache-users.xml where the CacheManager name is "Users" 
Query nameQuery2 = qm.createQuery("select name from Users.Address where zip = 94115"); 
// ** Explicit scoping use case #2 
// search Address cache defined in ehcache-address.xml where the CacheManager name 
// is "Detailed-Address" 
Query nameQuery3 = qm.createQuery("select name from Detailed-Address.Address 
                   where zip = 94115"); 
// get the results 
Results allNamesInUserRecordsOver21 = nameQuery1.end().execute(); 
Results allNamesInUserRecordsIn94115 = nameQuery2.end().execute(); 
Results allNamesInAddressBookIn94115 = nameQuery3.end().execute(); 
  // iterate over the results, etc.

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));

To be searchable using BigMemory SQL, the data type of an Attribute must be one of the following:

• boolean
• byte
• char
• float
• short
• long
• int
• double
• date
• sqldate
• String

The value of an Attribute in the WHERE clause must follow these data type rules:

• Except for integer and string types, an explicit cast must indicate the data type.
• Values for string, boolean, date, and sqldate must be surrounded by single quotes.

To search for a specific enum type, cast the value with the enum class name. The class name must be fully qualified and must be in the same format as the return value of String.class.getName().

select * from Person where age = (enum some.company.package.Foo)'Bar'

BigMemory SQL can parse two date types:

• the java.util.Date cast by the date keyword
• its subclass, java.sql.Date, which is cast using the sqldate keyword

BigMemory SQL can parse the following common formats of date and time strings:

 'yyyy-MM-ddTHH:mm:ss.SSS z' 
 'yyyy-MM-ddTHH:mm:ss.SSS' 
 'yyyy-MM-ddTHH:mm:ss z' 
 'yyyy-MM-ddTHH:mm:ss' 
 'yyyy-MM-ddz' 
 'yyyy-MM-dd'

Formats which are supported by ISO 8601 but not by BigMemory SQL are:

• DD omission (YYYY-MM)
• Week Date formats (YYYY-Www-D)
• Ordinal dates (YYYY-DDD)
• Durations (e.g., P3Y6M4DT12H30M5S)
• Time intervals (e.g., 2007-03-01T13:00:00Z/2008-05-11T15:30:00Z)

The GROUP BY clause provides the option to group results according to specified attributes. Adding a GROUP BY clause to the query both groups the results and allows aggregate functions to be performed on the grouped attributes. The general syntax of the group by clause is:

group by <attribute 1>, <attribute 2>, . . . <attribute n>

The following apply to both the Ehcache Search API's Group By clause and the GROUP BY clause in BigMemory SQL:

• Any attribute specified with the GROUP BY clause should also be included in the target list of the SELECT clause.
• Special KEY or VALUE attributes cannot be used in a GROUP BY clause. This means that KEY, VALUE, or * cannot be used in a query that has a GROUP BY clause.
• Adding a GROUP BY clause to a query changes the semantics of any aggregators passed in, so that they apply only within each group.
• If at least one aggregation function is specified in a query, the grouped attributes are not required to be included in the result set, However, grouped attributes are typically requested to make result processing easier.

To order the Query results, add an ORDER BY clause.

The general form of the ORDER BY clause is:

order by <attribute 1> <asc[ending] | desc[ending]>, 
         <attribute 2> <asc[ending] | desc[ending]>, . . . 
         <attribute n> <asc[ending] | desc[ending]>

The default ordering direction is ascending.

If ORDER BY is used with GROUP BY, the ordering attributes are limited to those listed in the GROUP BY clause.

select age from Person where ((age > 100 and zip = 20144) or time > 10) 
       group by age order by age ascending 
select age, zip from Person where ((age > 100 and zip = 20144) or time > 10) 
       group by age, zip order by age asc, zip desc

The LIMIT clause can be used to restrict the number of results returned by the search query. Ordinarily, the LIMIT clause is used with an ORDER BY clause to get the top results.

select * from Person where age < 80 limit 100

The LIMIT clause takes a single integer argument to restrict the result set with this general form:

limit <integer>

If you have a backslash in the string you want to search for (e.g., "path\name"), escape the backslash:

select * from Person where firstName = 'path\\name'