Summary: The SQLQuery class is used to query the space using
SQL-like syntax.
Overview
Usage ExamplesAn SQLQuery is composed from the type of entry to query and an expression in a SQL syntax. For example, suppose we have a class called MyClass with an Integer property called num and a String property called name: // Read an entry of type MyClass whose num property is greater than 500: MyClass result1 = gigaSpace.read( new SQLQuery<MyClass>(MyClass.class, "num > 500")); // Take an entry of type MyClass whose num property is less than 500: MyClass result2 = gigaSpace.take( new SQLQuery<MyClass>(MyClass.class, "num < 500")); MyClass[] results; // Read all entries of type MyClass whose num is between 1 and 100: results = gigapace.readMultiple( new SQLQuery<MyClass>(MyClass.class, "num >= 1 AND num <= 100")); // Read all entries of type MyClass who num is between 1 and 100 using BETWEEN syntax: results = gigapace.readMultiple( new SQLQuery<MyClass>(MyClass.class, "num BETWEEN 1 AND 100")); // Read all entries of type MyClass whose num is either 1, 2, or 3: results = gigapace.readMultiple( new SQLQuery<MyClass>(MyClass.class, "num IN (1,2,3)")); // Read all entries of type MyClass whose num is greater than 1, // and order the results by the name property: results = gigapace.readMultiple( new SQLQuery<MyClass>(MyClass.class, "num > 1 ORDER BY name"));
Supported Space OperationsThe following operations fully support GigaSpaces SQLQuery:
The following operations support GigaSpaces SQLQuery only with Simple Queries:
Supported SQL FeaturesGigaSpaces SQLQuery supports the following:
Parameterized QueriesIn many cases developers prefer to separate the concrete values from the SQL criteria expression. In GigaSpaces' SQLQuery this can be done by placing a '?' symbol instead of the actual value in the expression. When executing the query, the conditions that includes '?' are replaced with corresponding parameter values supplied via the setParameter/setParameters methods, or the SQLQuery constructor. For example: // Option 1 - Use the fluent setParameter(int index, Object value) method: SQLQuery<MyClass> query1 = new SQLQuery<MyClass>(MyClass.class, "num > ? or num < ? and name = ?") .setParameter(1, 2) .setParameter(2, 3) .setParameter(3, "smith"); // Option 2 - Use the setParameters(Object... parameters) method: SQLQuery<MyClass> query2 = new SQLQuery<MyClass>(MyClass.class, "num > ? or num < ? and name = ?"); query.setParameters(2, 3, "smith"); // Option 3: Use the constructor to pass the parameters: SQLQuery<MyClass> query3 = new SQLQuery<MyClass>(MyClass.class, "num > ? or num < ? and name = ?", 2, 3, "smith");
SQLQuery<MyClass> query = new SQLQuery<MyClass>(MyClass.class, "name = ? AND num IN (?,?,?)"); query.setParameters("A", 1, 2, 3); // Is equivalent to: SQLQuery<MyClass> query = new SQLQuery<MyClass>(MyClass.class, "name = 'A' AND num IN (1,2,3)");
Properties TypesNested PropertiesGigaSpaces SQL syntax contains various extensions to support matching nested properties, maps, collections and arrays. Some examples: // Query for a Person who lives in New York: ... = new SQLQuery<Person>(Person.class, "address.city = 'New York'"); // Query for a Dealer which sales a Honda: ... = new SQLQuery<Dealer>(Dealer.class, "cars[*] = 'Honda'"); For more information see Query Nested Properties. Enum PropertiesAn enum property can be matched either using the enum's instance value or its string representation. For example: public class Vehicle { public enum VehicleType { CAR, BIKE, TRUCK }; private VehicleType type; // Getters and setters are omitted for brevity } // Query for vehicles of type CAR using the enum's value: ... = new SQLQuery<Vehicle>(Vehicle.class, "type = ?", VehicleType.CAR); // Query for vehicles of type CAR using the enum's string representation: ... = new SQLQuery<Vehicle>(Vehicle.class, "type = 'CAR'");
Date PropertiesA Date property can be matched either using the Date instance value or its string representation. For example: // Query using a Date instance value: ... = new SQLQuery<MyClass>(MyClass.class, "birthday < ?", new java.util.Date(2020, 11, 20)); // Query using a Date string representation: ... = new SQLQuery<MyClass>(MyClass.class ,"birthday < '2020-12-20'"); Specifying date and time values as strings is error prone since it requires configuring the date and time format properties and adhering to the selected format. It is recommended to simply use Date instance parameters. When string representation is required, the following space properties should be used: space-config.QueryProcessor.date_format space-config.QueryProcessor.datetime_format space-config.QueryProcessor.time_format For example: <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> These space properties should be configured with a valid Java format pattern as defined in the official Java language documentation.
sysdateThe sysdate value is evaluated differently when using the JDBC API vs when using it with SQLQuery API. When used with JDBC API it is evaluated using the space clock. When used with SQLQuery API it is evaluated using the client clock. If you have a partitioned space across multiple different machines and the clock across these machines is not synchronized you might not get the desired results. If you use JDBC API you should consider setting the date value as part of the SQL within the client side (since you might write objects using the GigaSpace API). In this case , you should synchronize all the client machine time. In short - all the machines (client and server) clocks should be synchronized.
Blocking OperationsBlocking operations (i.e. read or take with timeout greater than 0) are supported with the following restrictions:
long timeout = 100000; MyClass result = space.take(new SQLQuery<MyClass>(MyClass.class , "num > 500"), timeout); RoutingWhen running on a partitioned space, it is important to understand how routing is determined for SQL queries. If the routing property is part of the criteria expression with an equality operand and without ORs, its value is used for routing. For example, suppose the routing property of MyClass is num: // Execute query on partition #1 SQLQuery<MyClass> query1 = new SQLQuery<MyClass>(MyClass.class, "num = 1"); // Execute query on all partitions - no way to tell which partitions hold matching results: SQLQuery<MyClass> query2 = new SQLQuery<MyClass>(MyClass.class, "num > 1"); // Execute query on all partitions - no way to tell which partitions hold matching results: SQLQuery<MyClass> query3 = new SQLQuery<MyClass>(MyClass.class, "num = 1 OR name='smith'"); Note that in query1 the num property is used both for routing and matching. In some scenarios we may want to execute the query on a specific partition without matching the routing property (e.g. blocking operation). This can be done via the setRouting method: SQLQuery<MyClass> query = new SQLQuery<MyClass>(MyClass.class, "num > 3"); query.setRouting(1); MyClass[] result = gigaspace.readMultiple(query); Regular ExpressionsYou can query the space using the SQL like operator or Java Regular Expression Query syntax. When using the SQL like operator you may use the following: SQLQuery<MyClass> query = new SQLQuery<MyClass>(MyClass.class, "name like 'A%'") Querying the space using the Java Regular Expression provides more options than the SQL like operator. The Query syntax is done using the rlike operator: // Match all entries of type MyClass that have a name that starts with a or c: SQLQuery<MyClass> query = new SQLQuery<MyClass>(MyClass.class, "name rlike '(a|c).*'"); All the supported methods and options above are relevant also for using rlike with SQLQuery.
Free Text SearchFree text search required almost with every application. Users placing some free text into a form and later the system allows users to search for records that includes one or more words within a free text field. A simple way to enable such fast search without using regualr expression query that my have some overhead can be done using the Collection Indexing, having an array or a collection of String values used for the query. Once the query is executed the SQL Query should use the searched words as usual. See example below: Our Space class incldues the following - note the words and the freeText fields: public class MyData { String[] words; String freeText; @SpaceIndex (path="[*]") public String[] getWords() { return words; } public void setWords(String words[]) { this.words=words; } public String getFreeText() { return freeText; } public void setFreeText(String freeText) { this.freeText = freeText; this.words = freeText.split(" "); } .... }
You may write the data into the space using the following: MyData data = new MyPOJO(...);
data.setFreeText(freetext);
gigaspace.write(data);
You can query for objects having the word hello as part of the freeText field using the following: MyData results[] = gigaspace.readMultiple(new SQLQuery<MyData>(MyData.class, words[*]='hello'));
You can also execute the following to seach for object having the within the freeText field the word hello or everyone: MyData results[] = gigaspace.readMultiple(new SQLQuery<MyData>(MyData.class, words[*]='hello' OR words[*]='everyone'));
With the above approach you avoid the overhead with regular expression queries.
Case Insensitive QueryImplementing case insensitive queries can be done via:
Optimization TipsCompound IndexWhen having an AND query or a template that use two or more fields for matching a Compound Index may boost the query execution time. The Compound Index should be defined on multiple properties for a specific space class and will be used implictly when a SQL Query or a Template will be using these properties. Re-using SQLQueryConstructing an SQLQuery instance is a relatively expensive operation. When possible, prefer using SQLQuery.setParameters and SQLQuery.setParameter to modify an existing query instead of creating a new one. However, remember that SQLQuery is not thread-safe. Minimize OR usageWhen using the OR logical operator together with AND logical operator as part of your query you can speed up the query execution by minimizing the number of OR conditions in the query. 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') Projecting Partial ResultsYou can specify that the SQLQuery should contain only partial results which means that the returned object should only be populated with the projected properties.
ConsiderationsUnsupported SQL FeaturesGigaSpaces SQLQuery does not support the following:
Simple vs. Complex QueriesMost space operations and features support any SQL query, but some support only simple queries and not complex ones. A query is considered complex if it contains one or more of the following:
The following features support only simple SQL queries
Interface ClassesSQLQuery supports concrete classes, derived classes and abstract classes. Interface classes are not supported. Reserved WordsThe following are reserved keywords in the GigaSpaces SQL syntax: 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 TIMESTAMP NUMBER Reserved Separators and Operators::= || ; . ROWTYPE ~ < <= > >= => != <> \(+\) ( ) \* / + - ? \{ \} |
![]() |
GigaSpaces.com - Legal Notice - 3rd Party Licenses - Site Map - API Docs - Forum - Downloads - Blog - White Papers - Contact Tech Writing - Gen. by Atlassian Confluence |