SQLQuery in JavaSpaces

Added by Limor Maayan, last edited by Aleia Hutcherson on May 26, 2008  (view change)
  GigaSpaces 5.X

Documentation Home
Quick Start Guide
Release Notes

Previous release
  Search Here
Searching GigaSpaces Platform 5.X Documentation

                                               
Summary: The SQLQuery class is used to query the space using the SQL like syntax.

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.

Enum data types are not supported when using SQL Queries.

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

Supported JavaSpaces Methods

  • readMultiple()
  • takeMultiple () with NO_WAIT timeout

Supported JavaSpaces Methods - 5.2

The following methods are supported in GigaSpaces version 5.2 and onwards.

  • read()
  • take ()
  • readIfExists() – acts like regular read
  • takeIfExists() – acts like regular take
  • take()
  • readMultiple()
  • takeMultiple () with timeout greater than NO_WAIT
  • count()
  • clear()
  • notify/snapshot – only simple SQL queries (that can be translated to one regular template) are supported: 
    (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 (since GigaSpaces 5.0):

  • NOT LIKE
  • OR
  • GROUP BY
  • ORDER BY

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 NotifyDelegator

String querystrDate = "m_date>'2000-06-04'";
SQLQuery template = new SQLQuery(new MyEntry(),querystrDate);


/** Use the SQLQuery as the template for the NotifyDelegator **/
   NotifyDelegator ntfyDelegator = new NotifyDelegator(            
                    space,                                                   
                    template,                                         
                    null,                                                   
                    theListener,                                             
                    Lease.FOREVER,                                          
                    null,                                                   
                    false,                                                  
                    NotifyModifiers.NOTIFY_ALL);
New in GigaSpace 5.2
Using SQLQuery with the NotifyDelegator is available in GigaSpaces version 5.2 and onwards.

For more details on the NotifyDelegator, refer to the JavaSpaces Notifications section.

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 may 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. 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:\GigaSpacesEE5.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')

Wiki Content Tree

Your Feedback Needed!

We need your help to improve this wiki site. If you have any suggestions or corrections, write to us at techw@gigaspaces.com. Please provide a link to the wiki page you are referring to.

Labels