Before you begin, you can take a look at this section, which summarizes the different query methods and shows how to index fields to speed up query based operations. If you're already familiar with the SQLQuery API, you can jump ahead to Writing the code. All code is documented inside each example as well.

See use of Query using Scripting and Query using GSIterator inside the tutorial.

For more detailed information, see SQLQuery.

We use the following Person POJO:

public class Person{

   private Integer age;
   private Integer height;
   private String name;
   .
   .
   Constructors/Setters/Getters
}

SQLQuery is the primary API when accessing the space.

Simple query

SQL Query

Using a SQL query in the code:

// Create SQLQuery that returns all the person objects that have age field equals 5 and height field less then 180
SQLQuery<Person> query = new SQLQuery<Person>(Person.class,"age = 5 and height < 180");

// Get all results that match the query
Person[] result = gigaSpace.readMultiple(query /* the query */ ,Integer.MAX_VALUE /* maximum objects to read */);

Plain, Non-Parameterized SQL Query using rlike

Regular expression pattern matching should be used with extreme care. Matching regular expressions in read operations is done in a serial manner and cannot take advantage of the space indexing mechanisms. Therefore, with large result sets, it can take a considerable amount of time to process and consume a lot of CPU resources.
Using regular expression pattern matching for notify templates is less dangerous since matching the objects is done once at a time, when the operation that the template was registered for (read, write, update, take, etc.) is invoked. In this case, the client application is not directly affected by this as the template matching is done in the background and does not affect client performance directly.

Using regular expressions for pattern matching on POJO properties in the query is done using the rlike option, as part of the SQL query:

// Create SQLQuery with 'rlike' that return all the objects that have a name field that starts with a or c
SQLQuery<Person> query = new SQLQuery<Person>(Person.class,"name rlike '(a|c).*'");

// Get all results that match the query
MyObject[] result = gigaSpace.readMultiple(query /* the query */ ,Integer.MAX_VALUE /* maximum objects to read */);

Parameterized query using JDBC like API

You can use a single SQLQuery instance multiple times by binding different values to it every time. The query should include the ? placeholder instead of the actual value. When executing the query, the condition that includes the ? placeholder is replaced with corresponding field values taken from the value defined in the setParameters method, or in the constructor.

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

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) in one query. If you perform these together, only the values you define in the dynamic query using parameters is taken into account.

Set the parameters using the setParameters() method:

SQLQuery query<Person> = new SQLQuery<Person>(Person.class,"age > ? or height > ? and name=?");
query.setParameters(22,178,"USA");

Or set each parameter seperetly using the setParameter() method:

query.setParameter(1,22);
query.setParameter(2,178);
query.setParameter(3,"USA");

Or pass the values in the constructor:

SQLQuery<Person> query = new SQLQuery<Person>(Person.class,"age > ? or height > ? and name=?",22,178,"USA");

Parameterized query using a template object

You can create one SQLQuery object and assign different queries and templates to it. The values in the template are used to construct the query. The query should include the ? placeholder instead of the actual value. When executing the query, the condition that includes the ? placeholder is replaced with corresponding field values taken from the relevant template field.

When performing dynamic queries using templates, you can not use the same field several times as part of the SQL statement. To do this, use the setParameters() method in the previous tab (Parameterized query using JDBC like API).

Set the the Person template attributes:

Person template = new Person();
template.setAge(22);
template.setHeight(178);
template.setName("USA");

Set the query template using the setTemplate method:

SQLQuery<Person> query;
query.setTemplate(template);
query.setQuery("age > ? and height < ? and name=?");

Or pass the template and clause in the constructor:

SQLQuery<Person> query = new SQLQuery<Person>(template,"age > ? and salary < ? and userName=?");

Query in event containers

This polling container (defined in a pu.xml configuration file) registers for notifications on every Person object that is written to the space and matches the query template. Upon registration, the simpleListener is invoked:

Namespace:

<os-events:notify-container id="eventContainer" giga-space="gigaSpace">
    <os-core:sql-query where="age>22 and height>178 and name=&apos;USA&apos;" class="mypackage.Person"/>
    <os-events:listener>
        <os-events:annotation-adapter>
            <os-events:delegate ref="simpleListener"/>
        </os-events:annotation-adapter>
    </os-events:listener>
</os-events:notify-container>

Plain XML:

<bean id="eventContainer" class="org.openspaces.events.notify.SimpleNotifyEventListenerContainer">

    <property name="gigaSpace" ref="gigaSpace" />

    <property name="template">
        <bean class="com.j_spaces.core.client.SQLQuery">
            <constructor index="0" value="mypackage.Person" />
            <constructor index="0" value="age>22 and height>178 and name=&apos;USA&apos;" />
        </bean>
    </property>
    
    <property name="eventListener">
    	<bean class="org.openspaces.events.adapter.AnnotationEventListenerAdapter">
    	    <property name="delegate" ref="simpleListener" />
    	</bean>
    </property>
</bean>

Index to speed up operations

Index to speed up operations

Querying indexed fields speeds up read and take operations.
To index a field use the field level decoration @SpaceProperty:

import com.gigaspaces.annotation.pojo.SpaceProperty;
import com.gigaspaces.annotation.pojo.SpaceProperty.IndexType;

public class Person{
   .
   .
   private String name;
   .
   .
   @SpaceProperty(index=IndexType.BASIC)
   public String getName() {
		return name;
	}
}

Field Level Decoration @SpaceProperty

Element XML Space Mapping File Element Name Type Description Default Value
index index Enum of IndexType Defines if this field data is indexed. Querying indexed fields speeds up read and take operations. Possible values of NONE and BASIC. NONE
nullValue null-value String Specifies that a value be treated as null.
(Usage: @SpaceProperty(nullValue="-1") whereas -1 functions as a null value)
 
GigaSpaces.com - Legal Notice - 3rd Party Licenses - Site Map - API Docs - Forum - Downloads - Blog - White Papers - Contact Tech Writing - Gen. by Atlassian Confluence