Summary: The SQLQuery class is used to query the space using the
SQL like syntax.
OverviewThe 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.
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.
. Supported JavaSpaces Methods
Supported and Non-Supported SQL OptionsThe Following SQL options are supported with SQLQuery:
Supported and Non-Supported Operators with Simple QueriesA 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:
Performing Regular Expression Queries as Part of SQL QueriesYou 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.
. ExamplesPOJO ClassWe 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 query = new SQLQuery(MyAbstractPojo.class, "");
Using SQLQuery with readMyPojo result = (MyPojo) space.read(query, null, JavaSpace.NO_WAIT);
Using SQLQuery with takeMyPojo result = (MyPojo) space.take(query, null, JavaSpace.NO_WAIT);
Using SQLQuery with readMultipleMyPojo[] result = (MyPojo[])space.readMultiple(query, null, Integer.MAX_VALUE); Using SQLQuery with takeMultipleMyPojo[] result = (MyPojo[])space.takeMultiple(query, null, Integer.MAX_VALUE); Using SQLQuery with EventSession
Using SQLQuery with Blocking Operations through SnapshotBlocking 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);
In such a case you should:
Date, Time and DateTime Type FormatGigaSpaces supports different date, time and datetime formats for the JDBC API and SQLQuery API. 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. 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 QueryYou can perform dynamic queries using parameters, or using templates.
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 OptimizationWhen 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 |