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 Spaces MethodsThe following operations support GigaSpaces SQLQuery:
SQLQuery Supported FeaturesGigaSpaces SQLQuery supports the following:
SQLQuery Unsupported FeaturesGigaSpaces SQLQuery does not support the following:
Simple Queries - Supported and Non-Supported OperatorsA 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 with SQL QueriesYou can query the space using the SQL Like or Java Regular Expression Query syntax. When using the SQL like operator you may use the following: SQLQuery<MyPojo> likeQuery = new SQLQuery<MyPojo>(MyPojo.class.class , "data like 'A%'") // Get all results that match the query MyPojo[] result = space.readMultiple(likeQuery,Integer.MAX_VALUE); Querying the space using the Java Regular Expression provides more options than SQL like operator . The Query syntax is done using the rlike operator: // 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 MyPojo[] result = space.readMultiple(rquery,Integer.MAX_VALUE); All the supported methods and options above are relevant also for using rlike with SQLQuery.
Case Insensitive QueryImplementing case insensitive queries can be done via:
SQLQuery usage ExamplesPOJO ClassWe will use the following POJO class as part of the code examples: import java.sql.Date; public class MyPojo{ public String getName(){...} public Date getDate{...} public Integer getInteger{...} ... } SQLQuery query = new SQLQuery(MyPojo.class, "integer>500"); Using SQLQuery with readMyPojo result = space.read(query, 0);
Using SQLQuery with takeMyPojo result = space.take(query, 0);
Using SQLQuery with readMultipleMyPojo[] result = space.readMultiple(query, Integer.MAX_VALUE);
Using SQLQuery with takeMultipleMyPojo[] result = space.takeMultiple(query, 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, 100000);
Matching Nested PropertiesGigaspaces SQLQuery supports matching nested entry properties within the query criteria using the dot ('.') notation. For example, say you have a class named Person, which contains a property 'info' of type Info, which contains a property 'address' of type Address, which contains a property 'street' of type String. To get all persons which live in a certain street, you could use: SQLQuery query = new SQLQuery(Person.class, "info.address.street = ?") As you can see from the above example, you can navigate multiple levels in the object graph. Here is an example for a space class and its embedded classes, followed by relevant queries supported: @SpaceClass public class Person { private Integer id; private String description; private Info info; // Getter and setter methods are removed for brevity. } public class Info implements Serializable { private String name; private Address address; private Date birthday; private long socialSecurity; private int id; // Getter and setter methods are removed for brevity. } public class Address implements Serializable { private int zipCode; private String street; // Getter and setter methods are removed for brevity. } Here are examples for supported queries: SQLQuery<Person> query; query = new SQLQuery<Person>(Person.class, "info.name = 'John'"); query = new SQLQuery<Person>(Person.class, "info.socialSecurity < 10000050L"); query = new SQLQuery<Person>(Person.class, "info.socialSecurity in (10000010L,10000004L,1L)"); query = new SQLQuery<Person>(Person.class, "info.birthday = '1982-04-18'"); query = new SQLQuery<Person>(Person.class, "info.address.street = 'Street0'"); query = new SQLQuery<Person>(Person.class, "info.name like '%n1%'"); query = new SQLQuery<Person>(Person.class, "info.name not like '%n1%' and description not like '%a2b%'"); query = new SQLQuery<Person>(Person.class, "info.id > 10 and info.id < 20 and info.id <> 15"); query = new SQLQuery<Person>(Person.class, "info.name = 'John1' or info.address.street = 'Street0'"); query = new SQLQuery<Person>(Person.class, "info.socialSecurity < 10000050L and info.socialSecurity >= 10000010L"); // ORDER BY example: query = new SQLQuery<Person>(Person.class, "info.socialSecurity < 10000050L and info.socialSecurity >= " + "10000010L order by info.socialSecurity"); // GROUP BY example: query = new SQLQuery<Person>(Person.class, "info.socialSecurity < 10000050L and info.socialSecurity >= " + "10000010L group by info.socialSecurity"); // Parameterized query example: query = new SQLQuery<Person>(Person.class, "info.socialSecurity < ? and info.socialSecurity >= ?"); query.setParameter(1, 10000050L); query.setParameter(2, 10000010L); You may also use SQLQuery with queries on nested objects when registering for notifications using RemoteEventListener listener = new RemoteEventListener() { public void notify(RemoteEvent theEvent) throws net.jini.core.event.UnknownEventException ,java.rmi.RemoteException { System.out.println("got notification "); }; }; DataEventSession des = EventSessionFactory.getFactory(gigaSpace.getSpace()).newDataEventSession(); des.addListener(new SQLQuery<Person>(Person.class, "info.name='John'"), listener); Nested Object Query IndexesAn index can be defined on a nested object field to improve performance. This is highly recommended.
LimitationsCurrent limitations of nested object queries with XAP 7.1:
Matching Nested MapsSQLQuery can query map properties in a similar fashion to nested properties: If a property is of type Map, you can specify the property name followed by a dot ('.') and a key, to get the value. In the previous example, we could rewrite class Person in the following manner: @SpaceClass public class Person { private int id; private String description; private Map<String, Object> info; @SpaceIndexes( {@SpaceIndex(path="socialSecurity" , type = SpaceIndexType.BASIC), @SpaceIndex(path="salary" , type = SpaceIndexType.EXTENDED)}) public HashMap<String, MyData> getInfo() { return info; } public void setInfo(HashMap<String, Object> info) { this.info = info; } // Other getter and setter methods are removed for brevity. } For example: // Write an entry: Person p = new Person(); p.setId(2347863); p.getInfo().put("address", new Address(245, "Broadway")); p.getInfo().put("socialSecurity", 1232287642L); p.getInfo().put("salary", 10000); // Read: SQLQuery<Person> query = new SQLQuery<Person>(Person.class, "info.salary < 15000 and info.salary >= 8000"); Person result = space.read(query); Complex paths involving maps and nested queries are also supported (e.g. "info.address.street = 'Broadway'"). Indexing is supported in the same manner. This feature can be useful to create flexible schema-less space object - since the keys in the map are not part of the schema, you can use it to add/remove data from a space object without changing its structure. Query Date FieldsWhen you have Date values you should use the java.sql.Timestamp as part of the space class. See example below:
The space class
public class MyClass { java.sql.Timestamp ts ; @SpaceIndex(type=SpaceIndexType.EXTENDED) public java.sql.Timestamp getTs() { return ts; } public void setTs(java.sql.Timestamp ts) { this.ts= ts; } }
The application code
GigaSpace gigaspace = new GigaSpaceConfigurer (new UrlSpaceConfigurer("/./mySpace")).gigaSpace(); Calendar c = Calendar.getInstance(); Timestamp startDate = new Timestamp((c.get(Calendar.YEAR) - 1900) , (c.get(Calendar.MONTH) ), (c.get(Calendar.DAY_OF_MONTH)),0,0,0,0); Timestamp endDate = new Timestamp((c.get(Calendar.YEAR) - 1900) , (c.get(Calendar.MONTH) ), (c.get(Calendar.DAY_OF_MONTH) + 1),0,0,0,0); SQLQuery<MyClass> query = new SQLQuery<MyClass>(MyClass.class , "ts > ? and ts< ?"); query.setParameter(1, startDate); query.setParameter(2, endDate); MyClass ret[]= gigaspace.readMultiple(query, Integer.MAX_VALUE); 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. Set the parameters using the 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); To use IN with the SQLQuery.setParameters() you should create a string with exact number of "?" and pass array to the SQLQuery.setParameters()? See below example: String paramValues = "?,?,?"; SQLQuery<MyClass> query = new SQLQuery<MyClass> (MyClass.class , "str = 'A' AND (num IN ("+paramValues+"))"); Integer intArr[] = new Integer []{1,2,3}; query.setParameters(intArr); MyClass ret [] = gigapace.readMultiple(query, Integer.MAX_VALUE); Reserved WordsReserved Keywords: ALTER ADD AND ASC BETWEEN BY CREATE CALL DROP DEFAULT_NULL DESC DISTINCT END FROM GROUP IN IS LIKE MAX MIN NOT NULL OR ORDER SELECT SUBSTR SUM SYSDATE UPPER WHERE COUNT DELETE EXCEPTION ROWNUM INDEX INSERT INTO SET TABLE TO_CHAR TO_NUMBER FOR_UPDATE UPDATE UNION VALUES COMMIT ROLLBACK PRIMARY_KEY UID USING Reserved Separators and Operators: := || ; . ROWTYPE ~ < <= > >= => != <> \(+\) ( ) \* / + - ? \{ \} 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 |