Summary: Querying the space using SqlQuery
OverviewThe SqlQuery class is used to query the space using SQL-like syntax. This query can be passed as a template to the Read, Take, ReadMultiple, TakeMultiple, Count and Clear operations, as well as a template when registering for notification. UsageAn SqlQuery is composed from the type of entry to query, an expression in a SQL syntax and optionally one or more parameters which provide values for the expression. Example 1Read all entries of type Person whose Age is greater than or equal to 21. Person[] persons = proxy.ReadMultiple<Person>( new SqlQuery<Person>("Age >= 21")); Note that the expression is equivalent to the WHERE part of a query. The FROM part is derived from the generic argument of the SqlQuery class, and the SELECT part is not needed since the result is a fully-formed object. Example 2Take up to 100 entries of type Person whose Age is greater than or equal to 21, AND whose FirstName is John. SqlQuery<Person> query = new SqlQuery<Person>( "Age >= ? AND FirstName = ?"); query.SetParameter(1, 21); query.SetParameter(2, "John"); Person[] persons = proxy.TakeMultiple<Person>(query, 100); This time instead of specifying the values directly in the expression we've used question marks to denote expression variables and parameters to specify the values for those variables. Supported SQL FeaturesGigaSpaces SqlQuery supports the following:
IndexingIt is highly recommended to use indexes on relevant properties to increase performance. For more information see Indexing. 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<MyClass>(new SQLQuery<MyClass>( "Num > 500"), timeout); RoutingWhen running on a partitioned space, it is important to understand how routing is determined for SQL queries. Routing is how the partitioned space determines on which partition a given data element is stored. 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>( "Num = 1"); // Execute query on all partitions - // no way to tell which partitions hold matching results: SQLQuery<MyClass> query2 = new SQLQuery<MyClass>( "Num > 1"); // Execute query on all partitions - // no way to tell which partitions hold matching results: SQLQuery<MyClass> query3 = new SQLQuery<MyClass>( "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). Starting 8.0.1, this can be done via the Routing property: SQLQuery<MyClass> query = new SQLQuery<MyClass>("Num > 3"); query.Routing = 1; MyClass[] result = space.ReadMultiple<MyClass>(query); LimitationsEnumsSince Enums are stored in the space as their underlying primitive type, they must be explicitly cast to that primitive type to be used with SqlQuery. 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
Unsupported SQL FeaturesGigaSpaces SqlQuery does not support the following:
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 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 |