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:

Supported JavaSpaces Methods

  • 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)...
    

Supported and Non-Supported SQL Options

The Following SQL options are supported with SQLQuery:

  • GROUP BY – performs DISTINCT on the POJO properties
  • Order By (ASC | DESC)
  • IN

The Following SQL options are not supported with SQLQuery:

  • HAVING

For more details, refer to the JDBC Supported Features section.

Supported and Non-Supported Operators with Simple Queries

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,null,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 extends MyAbstractPojo{

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

SQLQuery supports abstract classes.e.g.

SQLQuery query = new SQLQuery(MyAbstractPojo.class, "");

Using SQLQuery with read

MyPojo result = (MyPojo) space.read(query, null, JavaSpace.NO_WAIT);

The read() method returns the first matching object.

Using SQLQuery with take

MyPojo result = (MyPojo) space.take(query, null, JavaSpace.NO_WAIT);

The take() method returns the first matching object.

Using SQLQuery with readMultiple

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

Using SQLQuery with takeMultiple

MyPojo[] result = (MyPojo[])space.takeMultiple(query, null, 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,null, 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:

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

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