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:

• 1keydata - SQL Syntax
• Devguru - SELECT Statement Syntax Diagram

Supported Spaces Methods

GigaSpaces SQLQuery supports the following operations:

• read()
• take ()
• readIfExists() – acts like regular read
• takeIfExists() – acts like regular take
• take()
• readMultiple()
• takeMultiple () with timeout greater than NO_WAIT
• count()
• clear()
• EventSession/snapshot/GSIterator – only simple SQL queries (that can be translated to one regular template) are supported - for example:

  (field1 < value1) AND (field2 > value2) AND (field3 == values3)...

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.
• GROUP BY - performs DISTINCT on the POJO properties
• 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.

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
• GROUP BY
• ORDER BY

Enum data types are not supported when using SQL queries.

Performing Regular Expression Queries as Part of SQL Queries

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

Querying the space using the Java Regular Expression Query syntax is done using the rlike option:

// 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
Object[] 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.

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

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

In such a case you should:

• Use a load-balancing policy*** other than hash based, for example, round-robin. In this case, the blocking read/take operation is performed against different partition each time.
• Use Custom Queries*** and delegate the blocking take/read operation to the task.

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:

• java.text.SimpleDateFormat
• The Java Tutorials - About Patterns

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.

Using JDK 1.5

Set the parameters using the new 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);

Using JDK 1.4

Set the parameters using the new setParameters method.

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

Or pass the values in the constructor:

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

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