Summary: The SQLQuery class is used to query the space using the SQL like syntax.
Constructing SQLQuery object is relatively expensive operation. You should not construct it with every space query operation. You should construct it once and reuse it using the dynamic query options: SQLQuery.setParameters and SQLQuery.setParameter

Overview

The SQLQuery class is used to query the space using the SQL syntax. The query statement should only include the WHERE statement part.

If the WHERE clause is null or an empty string – the template argument is used as is – i.e., it is considered as a regular call to the relevant space's method.

If the WHERE statement argument exists (it is not null or an empty string), and does not include a question mark ("?") – only the template class name is used and the returned result array is based on the WHERE clause statement.
When using ? as part of the WHERE statement condition, the corresponding value in the template field is used.

When using clustered space with the load-balancing hash-based policy and the broadcast option is disabled – the WHERE statement must include the first indexed field with an appropriate value to be used to resolve the target space to be queried.

For the full documentation of the class's methods and constructors, see Javadoc .

For more details on the SQL syntax, refer to:

SQLQuery is supported for regular concrete Java classes, derived classes and abstract classes. SQLQuery is not supported with Interface classes.

Supported Spaces Methods

The following operations support GigaSpaces SQLQuery:

  • count()
  • clear()
  • read()
  • take()
  • readIfExists() – acts like regular read
  • takeIfExists() – acts like regular take
  • readMultiple()
  • takeMultiple()
  • EventSession/snapshot/GSIterator – only Simple Queries are supported.

SQLQuery Supported Features

GigaSpaces SQLQuery supports the following:

  • AND/OR operators to join two or more conditions.
  • All basic logical operations to create conditions: =, <>, <,>, >=, <=, like, NOT like, is null, is NOT null, IN.
  • ORDER BY (ASC | DESC) for multiple POJO properties. Supported only by readMultiple. ORDER BY supports also nested object fields.
  • GROUP BY - performs DISTINCT on the POJO properties. Supported only by readMultiple. GROUP BY supports also nested object fields.
  • sysdate - current system date and time.
  • rownum - limits the number of rows to select.
  • Sub-query - only for IN operations.
  • "." used to indicate a double data type.
    Examples:
    // IN query
    SQLQuery<MyClass> query = new SQLQuery<MyClass> (MyClass.class , "num IN (1,2,3)"); 
    MyClass result[] = gigapace.readMultiple(query, Integer.MAX_VALUE);
    
    // range query
    SQLQuery<MyClass> query = new SQLQuery<MyClass> (MyClass.class , "num >=1 AND num <=100"); 
    MyClass result[] = gigapace.readMultiple(query, Integer.MAX_VALUE);
    
    // Order by query
    SQLQuery<MyClass> query = new SQLQuery<MyClass> (MyClass.class , "num > 1 ORDER BY str");
    MyClass result[] = gigapace.readMultiple(query, Integer.MAX_VALUE);
When using the IN clause with large amount of elements (above 100), make sure you increase the com.gs.transport_protocol.lrmi.max-threads system property value for the JVM running the space to have a larger number. It should be larger than the maximum amount of elements your IN clause may have.

SQLQuery Unsupported Features

GigaSpaces SQLQuery does not support the following:

  • Aggregate functions: COUNT, MAX, MIN, SUM, AVG. These are supported with the JDBC API.
  • Multiple tables select - This is supported with the JDBC API.
  • DISTINCT - This is supported with the JDBC API.
  • The SQL statements: HAVING, VIEW, TRIGGERS, EXISTS, BETWEEN, NOT, CREATE USER, GRANT, REVOKE, SET PASSWORD, CONNECT USER, ON.
  • Constraints: NOT NULL, IDENTITY, UNIQUE, PRIMARY KEY, Foreign Key/REFERENCES, NO ACTION, CASCADE, SET NULL, SET DEFAULT, CHECK.
  • Set operations: Union, Minus, Union All.
  • Advanced Aggregate Functions: STDEV, STDEVP, VAR, VARP, FIRST, LAST.
  • Mathematical expressions.
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • INNER JOIN

Simple Queries - Supported and Non-Supported Operators

A simple SQL query is a query that can be translated into a single space query, meaning each of its fields can appear in one range or regular expression.

The simple query format is as follows:

field1 > 5 and field1 < 6 and field2 like '%aaa%'

The following operators are supported when using simple queries:

  • <
  • >
  • AND
  • LIKE

The following operators are not supported when using simple queries:

  • NOT LIKE
  • OR
  • IN
  • GROUP BY
  • ORDER BY
Enum data types are not supported when using SQL queries.

Performing Regular Expression Queries with SQL Queries

You can query the space using the SQL Like or Java Regular Expression Query syntax.

When using the SQL like operator you may use the following:
% - match any string of any length (including zero length)
_ - match on a single character

SQLQuery<MyPojo> likeQuery = new SQLQuery<MyPojo>(MyPojo.class.class , "data like 'A%'")
// Get all results that match the query
MyPojo[] result = space.readMultiple(likeQuery,Integer.MAX_VALUE);

Querying the space using the Java Regular Expression provides more options than SQL like operator . The Query syntax is done using the rlike operator:

// Create SQLQuery with 'rlike' that return all the objects that have a name that starts with a or c
SQLQuery rquery = new SQLQuery(MyPojo.class,"name rlike '(a|c).*'");
// Get all results that match the query
MyPojo[] result = space.readMultiple(rquery,Integer.MAX_VALUE);

All the supported methods and options above are relevant also for using rlike with SQLQuery.

For more details on Java regular expression syntax, see Javadoc.

For a more precise description of the behavior of regular expression constructs, refer to: Mastering Regular Expressions, 2nd Edition, Jeffrey E. F. Friedl, O'Reilly and Associates, 2002.

Case Insensitive Query

Implementing case insensitive queries can be done via:

  • like operator or rlike operator. Relatively slow. Not recommended when having large amount of objects.
  • Store the data in lower case and query on via lower case String value (or upper case)

SQLQuery usage Examples

POJO Class

We will use the following POJO class as part of the code examples:

import java.sql.Date;

public class MyPojo{

   public String getName(){...}   
   public Date getDate{...}   
   public Integer getInteger{...}
   ...
}
SQLQuery query = new SQLQuery(MyPojo.class, "integer>500");

Using SQLQuery with read

MyPojo result = space.read(query, 0);

The read() method returns the first matching object.

Using SQLQuery with take

MyPojo result = space.take(query, 0);

The take() method returns the first matching object.

Using SQLQuery with readMultiple

MyPojo[] result = space.readMultiple(query, Integer.MAX_VALUE);

Using SQLQuery with takeMultiple

MyPojo[] result = space.takeMultiple(query, Integer.MAX_VALUE);

Using SQLQuery with EventSession

For an example of SQLQuery with EvenSession, refer to the Session Based Messaging API section.

Using SQLQuery with Blocking Operations through Snapshot

Blocking operations (read and take with timeout>0) are supported using the snapshot call:

SQLQuery query = new SQLQuery(MyPojo.class , "integer>500");
Object snap = space.snapshot(query);
Object result = space.take(snap, 100000);

If you are using a complex query you will get the following message - java.sql.SQLException: Operation with timeout doesn't support complex SQL queries that can't be translated to a single template. In this case you might want to consider to rewrite the query in the simple format or split it into several simple queries. for more information refer to Simple Queries definition.

Blocking Take/Read Operations with null as the Routing Field are not Supported with Partitioned Space

Matching Nested Properties

Gigaspaces SQLQuery supports matching nested entry properties within the query criteria using the dot ('.') notation. For example, say you have a class named Person, which contains a property 'info' of type Info, which contains a property 'address' of type Address, which contains a property 'street' of type String. To get all persons which live in a certain street, you could use:

SQLQuery query = new SQLQuery(Person.class, "info.address.street = ?")

As you can see from the above example, you can navigate multiple levels in the object graph.

Here is an example for a space class and its embedded classes, followed by relevant queries supported:

@SpaceClass
public class Person {
    private Integer id;
    private String description;
    private Info info;
    // Getter and setter methods are removed for brevity.
}

public class Info implements Serializable {
    private String name;
    private Address address;
    private Date birthday;
    private long socialSecurity;
    private int id;
    // Getter and setter methods are removed for brevity.
}

public class Address implements Serializable {
    private int zipCode;
    private String street;
    // Getter and setter methods are removed for brevity.
}

Here are examples for supported queries:

SQLQuery<Person> query;

query = new SQLQuery<Person>(Person.class, "info.name = 'John'");
query = new SQLQuery<Person>(Person.class, "info.socialSecurity < 10000050L");
query = new SQLQuery<Person>(Person.class, "info.socialSecurity in (10000010L,10000004L,1L)");
query = new SQLQuery<Person>(Person.class, "info.birthday = '1982-04-18'");
query = new SQLQuery<Person>(Person.class, "info.address.street = 'Street0'");
query = new SQLQuery<Person>(Person.class, "info.name like '%n1%'");
query = new SQLQuery<Person>(Person.class, 
                             "info.name not like '%n1%' and description not like '%a2b%'");
query = new SQLQuery<Person>(Person.class, "info.id > 10 and info.id < 20 and info.id <> 15");
query = new SQLQuery<Person>(Person.class, "info.name = 'John1' or info.address.street = 'Street0'");
query = new SQLQuery<Person>(Person.class, 
                             "info.socialSecurity < 10000050L and info.socialSecurity >= 10000010L");

// ORDER BY example:
query = new SQLQuery<Person>(Person.class, 
                             "info.socialSecurity < 10000050L and info.socialSecurity >= " + 
                             "10000010L order by info.socialSecurity");

// GROUP BY example:
query = new SQLQuery<Person>(Person.class, 
                             "info.socialSecurity < 10000050L and info.socialSecurity >= " + 
                             "10000010L group by info.socialSecurity");

// Parameterized query example:
query = new SQLQuery<Person>(Person.class, "info.socialSecurity < ? and info.socialSecurity >= ?");
query.setParameter(1, 10000050L);
query.setParameter(2, 10000010L);

You may also use SQLQuery with queries on nested objects when registering for notifications using
the Notify Container or using the Session Based Messaging API:

RemoteEventListener listener = new RemoteEventListener() {
    public void notify(RemoteEvent theEvent) throws net.jini.core.event.UnknownEventException ,java.rmi.RemoteException {
        System.out.println("got notification ");
    };
};
DataEventSession des = EventSessionFactory.getFactory(gigaSpace.getSpace()).newDataEventSession();        
des.addListener(new SQLQuery<Person>(Person.class, "info.name='John'"), listener);

Nested Object Query Indexes

An index can be defined on a nested object field to improve performance. This is highly recommended.
It can be defined on a nested property of a space class and will be used implicitly by the queries.

For more information see Nested Properties Indexing

Since like/rlike queries does not use indexes these will need to scan the relevant objects to find matching objects. This means that the performance of such queries will depend on the amount of candidate result set GigaSpaces will have out of the initial query execution phase that is using indexed fields.

Limitations

Current limitations of nested object queries with XAP 7.1:

  • Only queries on POJO classes are supported
  • No .NET support
  • The type of the nested object must be a class - not an interface

Matching Nested Maps

SQLQuery can query map properties in a similar fashion to nested properties: If a property is of type Map, you can specify the property name followed by a dot ('.') and a key, to get the value.

In the previous example, we could rewrite class Person in the following manner:

@SpaceClass
public class Person {
    private int id;
    private String description;
    private Map<String, Object> info;

	@SpaceIndexes( {@SpaceIndex(path="socialSecurity" , type = SpaceIndexType.BASIC),
			@SpaceIndex(path="salary" , type = SpaceIndexType.EXTENDED)})
	public HashMap<String, MyData> getInfo() {
		return info;
	}
	public void setInfo(HashMap<String, Object> info) {
		this.info = info;
	}

    // Other getter and setter methods are removed for brevity.
}

For example:

// Write an entry:
Person p = new Person();
p.setId(2347863);
p.getInfo().put("address", new Address(245, "Broadway"));
p.getInfo().put("socialSecurity", 1232287642L);
p.getInfo().put("salary", 10000);

// Read:
SQLQuery<Person> query = new SQLQuery<Person>(Person.class, "info.salary < 15000 and info.salary >= 8000");
Person result = space.read(query);

Complex paths involving maps and nested queries are also supported (e.g. "info.address.street = 'Broadway'"). Indexing is supported in the same manner. This feature can be useful to create flexible schema-less space object - since the keys in the map are not part of the schema, you can use it to add/remove data from a space object without changing its structure.

Query Date Fields

When you have Date values you should use the java.sql.Timestamp as part of the space class. See example below:

The space class
public class MyClass {
	java.sql.Timestamp ts ;

	@SpaceIndex(type=SpaceIndexType.EXTENDED)
	public java.sql.Timestamp getTs() {
		return ts;
	}

	public void setTs(java.sql.Timestamp ts) {
		this.ts= ts;
	}
}
The application code
GigaSpace gigaspace = new GigaSpaceConfigurer (new UrlSpaceConfigurer("/./mySpace")).gigaSpace();
Calendar c = Calendar.getInstance();
Timestamp startDate = new Timestamp((c.get(Calendar.YEAR) - 1900) , (c.get(Calendar.MONTH) ),
  (c.get(Calendar.DAY_OF_MONTH)),0,0,0,0);
Timestamp endDate = new Timestamp((c.get(Calendar.YEAR) - 1900) , (c.get(Calendar.MONTH) ), 
  (c.get(Calendar.DAY_OF_MONTH) + 1),0,0,0,0);

SQLQuery<MyClass> query = new SQLQuery<MyClass>(MyClass.class , "ts > ? and ts< ?");
query.setParameter(1, startDate);
query.setParameter(2, endDate);
MyClass ret[]= gigaspace.readMultiple(query, Integer.MAX_VALUE);

Date, Time and DateTime Type Format

GigaSpaces supports different date, time and datetime formats for the JDBC API and SQLQuery API.
The different date, time and datetime format support is done by defining the space date, datetime, and time format as part of deployment configuration file (pu.xml) or when using the relevant API to construct a space instance.
The following properties should be used:

space-config.QueryProcessor.date_format
space-config.QueryProcessor.datetime_format
space-config.QueryProcessor.time_format

These should have valid Java format pattern as defined as part of the Java documentation:

These could be: yyyy-MM-dd, yyyy-MM-dd, hh:mm:ss, mm:hh:ss, etc.
SQLQuery with the read, readMultiple, take, takeMultiple operations, supports the "where statement" argument using the different date, time and datetime format.

Here is an example of a pu.xml that configure the space date format and time format:

<beans 
	<os-core:space id="space" url="/./space">
		<os-core:properties>
            <props>
                <prop key="space-config.QueryProcessor.date_format">yyyy-MM-dd HH:mm:ss</prop>
                <prop key="space-config.QueryProcessor.time_format">HH:mm:ss</prop>
            </props>
        </os-core:properties>
    </os-core:space>
</beans>

The Query code would use this:

GigaSpace space;
SQLQuery<TestClass> query = new SQLQuery<TestClass>(TestClass.class.getName() ,"dateField <'" + "2020-03-03 10:10:10" +"'");
Object ret[] = space.readMultiple(query ,Integer.MAX_VALUE);

Dynamic Query

You can perform dynamic queries using parameters, or using templates.

It is not recommended to perform both types of dynamic queries (using parameters, and using templates). If you perform these together, only the values you define in the dynamic query using parameters is taken into account.

Using Parameters (JDBC-Like Binding)

You can create one SQLQuery object and assign it into different queries. The query should include ? instead of the actual value. When executing the query, the condition that includes ? is replaced with corresponding field values taken from the value defined in the setParameters method, or in the constructer.

Performing dynamic queries using parameters allows you to use the same field several times as part of the SQL statement when using the template value to fill in the parameters values.

Set the parameters using the setParameters() method:

SQLQuery query = new SQLQuery(MyPojo.class, "col1 > ? or col1 > ? and col2=?");
query.setParameters(2, 3, 5);

Or pass the values in the constructor:

SQLQuery query = new SQLQuery(MyPojo.class, "col1 > ? or col1 > ? and col2=?", 2, 3, 5);

To use IN with the SQLQuery.setParameters() you should create a string with exact number of "?" and pass array to the

SQLQuery.setParameters()? See below example:  
String paramValues = "?,?,?";
SQLQuery<MyClass> query = new SQLQuery<MyClass> (MyClass.class , "str = 'A' AND (num IN ("+paramValues+"))"); 
Integer intArr[] = new Integer []{1,2,3};
query.setParameters(intArr);
MyClass ret [] = gigapace.readMultiple(query, Integer.MAX_VALUE);

Reserved Words

Reserved Keywords:

ALTER ADD AND ASC BETWEEN BY CREATE CALL DROP DEFAULT_NULL DESC  DISTINCT END FROM GROUP IN IS LIKE 
MAX MIN NOT NULL OR ORDER SELECT SUBSTR SUM SYSDATE UPPER WHERE COUNT DELETE EXCEPTION ROWNUM INDEX 
INSERT INTO SET TABLE TO_CHAR TO_NUMBER FOR_UPDATE UPDATE UNION VALUES COMMIT ROLLBACK PRIMARY_KEY 
UID USING

Reserved Separators and Operators:

:= || ; . ROWTYPE ~ < <= >  >= => != <> \(+\) ( ) \* / + - ? \{ \}

Query Optimization

When using the or logical operation together with and operations as part of your query (JDBC , JavaSpaces with SQLQuery) you can speed up the query execution by having the and conditions added to each or condition.

For example:

(A = 'X' or A = 'Y') and (B > '2000-10-1' and B < '2003-11-1')

would be executed much faster when changing it to be:

(A = 'X' and B > '2000-10-1' and B < '2003-11-1') or 
  (A = 'Y' and B > '2000-10-1' and B < '2003-11-1')

***Link required

GigaSpaces.com - Legal Notice - 3rd Party Licenses - Site Map - API Docs - Forum - Downloads - Blog - White Papers - Contact Tech Writing - Gen. by Atlassian Confluence