SQLQuery in JavaSpaces

  Search Here
Searching GigaSpaces XAP/EDG 6.5 Documentation

                                               

This page is specific to:
GigaSpaces 6.5

If you're interested in another version, click it below:
• GigaSpaces 5.x
• GigaSpaces 6.0

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 will be used as is – i.e., it will be considered as a regular call to the relevant JavaSpaces method.

If the WHERE statement argument exists (it is not null and not an empty string) and does not include a question mark ("?") – only the template class name will be used and the returned result array will be based on the WHERE clause statement.
When using ? as part of the WHERE statement condition, the corresponding value in the template field will be 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()
  • NotifyDelgator/EventSession/snapshot – 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 Entry fields
  • 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, and each of its fields can appear once.

The simple query format is as follows:

field1 = 5 and field2 < 6 and field3 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.
  • Abstract classes cannot be used as templates (in pu.xml when used with a notify/polling container or when used as part of the code).

Performing Regular Expression Queries as Part of SQL Queries

You can query the space using the Java Regular Expression Query syntax. SQLQuery and Regular Expression Queries performance is now identical.

SQLQuery is the primary API when accessing the space – you don't need to use regular templates for simple matching, and SQLQuery for complex queries.

Querying the space using the Java Regular Expression Query syntax is done using the rlike option, as part of the SQLQuery itself: 

// Create SQLQuery with 'rlike' that return all the objects that have a name that starts with a or c
SQLQuery rquery = new SQLQuery(MyEntry.class.getName(),"name rlike '(a|c).*'");

// Get all results that match the query
Entry[] 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

Entry Class

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

import java.sql.Date;
import java.sql.Time;
import net.jini.core.entry.Entry;
public class MyEntry implements Entry{

 
       public MyEntry(){}

 
        public String m_string = null;
           public Long m_long = null;
           public Time m_time = null;
           public Date m_date = null;
           public Integer m_integer = null;
           public Double m_double = null;
           public Float m_float = null;

           public static String[] __getSpaceIndexedFields()
           {

                  String[] indexedFields =
                   { "m_string" ,"m_long" , "m_time" ,"m_date" , "m_integer" , "m_double" ,"m_float"};

                  return indexedFields;
           }
}

Using SQLQuery with readMultiple

SQLQuery query = new SQLQuery(new MyEntry(),"m_integer>500";);
Entry result[] = space.readMultiple(query, null,Integer.MAX_VALUE);

Using SQLQuery with takeMultiple

SQLQuery query4 = new SQLQuery(new MyEntry(),"m_integer<500";);
Entry result[] = space.takeMultiple(query4, null,Integer.MAX_VALUE);

Using SQLQuery with read

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

The read() method returns the first matching Entry.

Using SQLQuery with take

MyEntry result4 = (MyEntry) space.take(query, null,JavaSpace.NO_WAIT);

The take() method returns the first matching Entry.

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(Order.class.getName() , "price > 500");
Entry snap = space.snapshot(query);
ExternalEntry resultEE = (ExternalEntry) space.take(snap,null, 100000);
if (resultEE != null) {
	Order result = (Order) resultEE.getObject(space);
}

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 the space schema or configuration file. See the example below:

<space-config>
       <QueryProcessor> 
             <date_format>yyyy-MM-dd</date_format>
             <datetime_format>yyyy-MM-dd hh:mm:ss</datetime_format>
             <time_format>hh:mm:ss</time_format>
       </QueryProcessor> 
</space-config>

The <date_format>, <datetime_format> and <time_format> should have any 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:

String querystrDate = "m_date>'2000-06-04'";
SQLQuery queryDate = new SQLQuery(new MyEntry(),querystrDate);
Entry result[] = space.readMultiple(queryDate, null,Integer.MAX_VALUE);

Example

The following example starts an embedded space using a schema called testdate, where each space uses different date, time and datetime formats, and performs queries using the SQLQuery and the readMultiple operation using date, time and datetime values with the relevant formats.

An example for using a space schema that has the following settings:

<space-config>
<QueryProcessor> 
                 <date_format>${com.gs.date_format}</date_format>
                <datetime_format>${com.gs.datetime_format}</datetime_format>
                <time_format>${com.gs.time_format}</time_format>
</QueryProcessor> 
</space-config>

The <date_format>, <datetime_format> and <time_format> are configured in run time where each space using a different date, time and datetime format. The configuration is done by setting the
com.gs.date_format, com.gs.datetime_format, and com.gs.time_format system properties to have the different values.

The example below should return:

  • 5 objects for the Date type query.
  • 5 objects for the DateTime (Timestamp) type query.
  • 4 objects for the Time type query
package com.examples.querydate;
import java.rmi.RemoteException;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.GregorianCalendar;
import java.util.Properties;
import com.j_spaces.core.IJSpace;
import com.j_spaces.core.admin.IRemoteJSpaceAdmin;
import com.j_spaces.core.admin.SpaceConfig;
import com.j_spaces.core.client.*;
import net.jini.core.entry.Entry;
import net.jini.core.entry.UnusableEntryException;
import net.jini.core.lease.Lease;
import net.jini.core.transaction.TransactionException; 

public class Main {

static IJSpace space;
static String year_value = "2000";
static String month_value = "06";
static String day_value = "04";
static String hour_value = "08";
static String min_value = "12";
static String second_value = "05";
static final String dateFormats[] = { "yyyy-MM-dd", "yyyy%MM%dd",

"MM#dd#yyyy", "dd*MM*yyyy" };
static final String dateTimeFormats[] = { "yyyy-MM-dd hh:mm:ss",

"yyyy%MM%dd hh:mm:ss", "MM-dd-yyyy hh:mm:ss", "dd-MM-yyyy hh#mm#ss" };
static final String timeFormats[] = { "mm:hh:ss", "ss:mm:hh", "hh:ss:mm",

"hh#mm#ss" };
static final String dateValues[] = {

year_value + "-" + month_value + "-" + day_value,
year_value + "%" + month_value + "%" + day_value,
month_value + "#" + day_value + "#" + year_value,
day_value + "*" + month_value + "*" + year_value };
static final String dateTimeValues[] = {

year_value + "-" + month_value + "-" + day_value + " " + hour_value
+ ":" + min_value + ":" + second_value,
year_value + "%" + month_value + "%" + day_value + " " + hour_value
+ ":" + min_value + ":" + second_value,
month_value + "-" + day_value + "-" + year_value + " "+hour_value+":"+min_value+":"+second_value,
day_value + "-" + month_value + "-" + year_value + " " + hour_value
+ "#" + min_value + "#" + second_value };
static final String timeValues[] = {

min_value + ":" + hour_value + ":" + second_value,
second_value + ":" + min_value + ":" + hour_value,
hour_value + ":" + second_value + ":" + min_value,
hour_value + "#" + min_value + "#" + second_value };
public static void main(String[] args) throws Exception {

 
System.out.println("Welcome to GigaSpaces Date Query example!");
Main main = new Main();
for (int i = 0; i < dateFormats.length; i++) {

main.go(i);
}

}
public void go(int count) throws Exception {

try {

Properties props = new Properties();
System.setProperty("com.gs.date_format", dateFormats[count]);
System.setProperty("com.gs.datetime_format", dateTimeFormats[count]);
System.setProperty("com.gs.time_format", timeFormats[count]);
String url = "/./space" + count + "?schema=datetest";
space = (IJSpace) SpaceFinder.find(url);
IRemoteJSpaceAdmin spaceadmin = (IRemoteJSpaceAdmin) space
.getAdmin();
SpaceConfig conf = spaceadmin.getConfig();
System.out.println("TimeFormat:" + conf.getQpTimeFormat());
System.out.println("DateTimeFormat:" + conf.getQpDateTimeFormat());
System.out.println("DateFormat:" + conf.getQpDateFormat());
if (space == null) {

System.out.println("Space not found: " + url);
System.exit(-1);
}

System.out.println("clean space");
// space.clean();
long maxObjects = 10;
System.out
.println("Writing " + maxObjects + " entries to space...");
System.out.println();
for (int i = 0; i < maxObjects; i++) {

MyEntry msg = new MyEntry();
msg.m_date = new Date(100, 05, i);
msg.m_timestamp = new Timestamp(100, 05, i, 8, 12, i, 0);
msg.m_long = new Long(i);
msg.m_integer = new Integer(i);
msg.m_double = new Double(i);
msg.m_string = "aaaaab";
msg.m_time = new Time(8, 12, i);
space.write(msg, null, Lease.FOREVER); 

}
System.out.println();
System.out.println("Writing " + maxObjects
+ "entries to space...Done!");
System.out.println("Hit Enter to Query data");
// --------------------------------------------------------------------------
space.snapshot(new MyEntry());
// //////////////////
String query1strTestDate = "m_date>'" + dateValues[count] + "'";
String query1strTestDateTime = "m_timestamp>'"
+ dateTimeValues[count] + "'";
String query1strTestTime = "m_time>'" + timeValues[count] + "'";
SQLQuery queryDateTest1 = new SQLQuery(new MyEntry(),
query1strTestDate);
SQLQuery queryDateTest2 = new SQLQuery(new MyEntry(),
query1strTestDateTime);
SQLQuery queryDateTest3 = new SQLQuery(new MyEntry(),
query1strTestTime);
runQuery(queryDateTest1, query1strTestDate);
runQuery(queryDateTest2, query1strTestDateTime);
runQuery(queryDateTest3, query1strTestTime);
} catch (Exception e) {

e.printStackTrace();
}

}
void runQuery(SQLQuery query, String querystr) throws RemoteException,
TransactionException, UnusableEntryException {

long startTime = System.currentTimeMillis();
System.out.println("Query:" + querystr);
Entry resultDateTest[] = space.readMultiple(query, null,
Integer.MAX_VALUE);
long endTime = System.currentTimeMillis();
System.out.println("resultDateTest - found:" + resultDateTest.length
+ " objects");
System.out.println("Query Time:" + (endTime - startTime));

}

}

package com.examples.querydate;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import com.j_spaces.core.client.MetaDataEntry;
public class MyEntry
extends MetaDataEntry
{

    public String m_string = null;
    public Long m_long = null;
    public Time m_time = null;
    public Date m_date = null;
    public Timestamp m_timestamp = null;
    public Integer m_integer = null;
    public Double m_double = null;
    public MyEntry( )
    {

    }
    public String toString( )
    {

        return "m_string = " + m_string + " m_long =" + m_long + " m_time ="
                + m_time + " m_date =" + m_date + " m_integer=" + m_integer
                + " m_double=" + m_double;

    }

 public static String[] __getSpaceIndexedFields()
    {
    String[] indexedFields = { "m_date" , "m_time" , "m_timestamp"}; 

    return indexedFields;
    }

}

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("TestEntry","col1 > ? or col1 > ? and col2=?");
query.setParameters(2,3,5);

Or pass the values in the constructor: 

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

Using JDK 1.4:

Set the parameters using the new setParameters method. 

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

Or pass the values in the constructor: 

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

Using Templates

You can create one SQLQuery object and assign into it different queries and templates, where the values within the template are used to construct the query. 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 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 parameters method above.

See the following example:

The Entry class:

package com.examples.query;
import java.sql.Date;
import java.sql.Time;
import net.jini.core.entry.Entry;
public class MyEntry implements Entry{

 
       public MyEntry(){}


        public String m_string = null;
           public Long m_long = null;
           public Time m_time = null;
           public Date m_date = null;
           public Integer m_integer = null;
           public Double m_double = null;
           public Float m_float = null;

           public static String[] __getSpaceIndexedFields()
           {

                  String[] indexedFields = 
                  { "m_string" ,"m_long" , "m_time" ,"m_date" , "m_integer" , "m_double" ,"m_float"};

                  return indexedFields;
           }
}

The application:

package com.examples.query;

import java.sql.Date;
import java.sql.Time;
import java.util.GregorianCalendar;
import com.j_spaces.core.IJSpace;
import com.j_spaces.core.client.*;
import net.jini.core.entry.Entry;
import net.jini.core.lease.Lease; 

public class QueryTest {

 

       /**
        * @param args
        */
       public static void main(String[] args) {

              // TODO Auto-generated method stub
              IJSpace space = null;
              try {

                    space = (IJSpace) SpaceFinder.find("rmi://localhost/./mySpace1");
                    if (space == null) {

                           System.out.println("Space not found: " + args[0]);
                           System.exit(-1);
                    }
                    space.clean();
                    for (int i = 0; i < 1000; i++) {

                           MyEntry msg = new MyEntry();
                           msg.m_date = new Date(i , 10 , 10);
                           msg.m_long = new Long(i);
                           msg.m_integer = new Integer(i);
                           msg.m_double = new Double(i);
                           msg.m_string = "aaaaab";
                           msg.m_time = new Time(10 ,5  , (i % 60)); 

                           space.write(msg, null, Lease.FOREVER);
                    }
                    System.out.println("Writing 1000 entries to space...Done!");
                    MyEntry querytemplate = new MyEntry();
//-------------------------------------
                    querytemplate.m_integer = new Integer(10);
                    String querystr = "m_integer > ?";
                    SQLQuery query = new SQLQuery(querytemplate, querystr);
                    Entry result1[] = space.readMultiple(query, null,
                                  Integer.MAX_VALUE);
                    System.out.println("m_integer > 10 - found " + result1.length);
//                  -------------------------------------
                    querytemplate = new MyEntry();
                    querytemplate.m_integer = new Integer(20);
                    query.setQuery(querystr);
                    query.setTemplate(querytemplate);
                    Entry result2[] = space.readMultiple(query, null,
                                  Integer.MAX_VALUE);
                    System.out.println("m_integer > 20 - found " + result2.length);
//                  -------------------------------------
                    querytemplate = new MyEntry();
                    querystr = "m_double > ?";
                    querytemplate.m_double = new Double(20);
                    query.setQuery(querystr);
                    query.setTemplate(querytemplate);
                    Entry result3[] = space.readMultiple(query, null,Integer.MAX_VALUE);
                    System.out.println("m_double > 20 - found " + result3.length);
//                  -------------------------------------
                    querytemplate = new MyEntry();
                    querystr = "m_date > ?";
                    Date date  = new Date(100 , 10 , 10);
                    querytemplate.m_date = date;
                    query.setQuery(querystr);
                    query.setTemplate(querytemplate);
                    Entry result4[] = space.readMultiple(query, null,Integer.MAX_VALUE);
                    System.out.println("m_date > " +date + "  - found " + result4.length);
//                  -------------------------------------
                    querytemplate = new MyEntry();
                    querystr = "m_date < ?";
                    date  = new Date(100 , 10 , 10);
                    querytemplate.m_date = date;
                    query.setQuery(querystr);
                    query.setTemplate(querytemplate);
                    result4 = space.readMultiple(query, null,Integer.MAX_VALUE);
                    System.out.println("m_date < " +date + "  - found " + result4.length);
//                  -------------------------------------
                    querytemplate = new MyEntry();
                    querystr = "m_time > ?";
                    Time  time = new Time(10 ,5  , 30);
                    querytemplate.m_time = time;
                    query.setQuery(querystr);
                    query.setTemplate(querytemplate);
                    Entry result5[] = space.readMultiple(query, null,Integer.MAX_VALUE);
                    System.out.println("m_time > " +time + "  - found " + result5.length);
//                  -------------------------------------
                    querytemplate = new MyEntry();
                    querystr = "m_time < ?";
                    time = new Time(10 ,5  , 30);
                    querytemplate.m_time = time;
                    query.setQuery(querystr);
                    query.setTemplate(querytemplate);
                    result5 = space.readMultiple(query, null,Integer.MAX_VALUE);
                    System.out.println("m_time < " +time + "  - found " + result5.length);                  

                   

              } catch (Exception e) {

                    e.printStackTrace();
              }
       } 

}

The GigaSpaces server starts using the following command:

c:\GigaSpacesXAP6.0\bin>gsInstance "/./mySpace1?scheme=cache" "D:\Tools\eclipse\workspace\query"

When using full serialization mode you may query only java.lang.* objects. To query non java.lang.* objects (e.g. java.sql.Date, java.sql.Time) you should start the space in no serialization mode and make sure the Entry class is part of the space server classpath. In the example above, we use the cache schema that uses the no serialization mode, and provides the application's Entry class to be added into the GigaSpaces Server classpath as part of the second argument to the gsInstance utility.

Output:

Writing 1000 entries to space...Done!
m_integer > 10 - found 989
m_integer > 20 - found 979
m_double > 20 - found 979
m_date > 2000-11-10  - found 899
m_date < 2000-11-10  - found 100
m_time > 10:05:30  - found 473
m_time < 10:05:30  - found 510

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:

select uid,* from table where (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:

select uid,* from table where (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')


GigaSpaces 6.5 Documentation Contents (Current Page in Bold)

    Java

    C++

    .NET

    Middleware Capabilities

    Configuration and Management

Add GigaSpaces wiki search to your browser search engines!
(works on Firefox 2 and Internet Explorer 7)

Labels