Summary: Query nested properties, maps and collections using SQL queries
OverviewThe SQL Query page shows how to express complex queries on flat space entries (i.e entries which are composed of scalar types like integers and strings), but in reality space entries are often composed of more complex types.
An application would likely need to execute queries which involve such properties (e.g. a person which lives in city X, or is part of some group Y, or has a specific home phone number). This page explains how GigaSpaces SQLQuery can be used to express such queries. Matching PathsMatching a nested property is done by specifying a Path which describes how to obtain its value. For example, suppose we have a Person class and an Address class, and Person has an address property of type Address: @SpaceClass public class Person { private Address address; // Getter and setter methods are omitted for brevity. } public class Address implements Serializable { private String city; // Getter and setter methods are omitted for brevity. } The following example queries for a Person with an address whose city equals "New York": ... = new SQLQuery<Person>(Person.class, "address.city = 'New York'");
The number of levels in the path is unlimited. ... = new SQLQuery<Person>(Person.class, "address.street.name = 'Main'"); Naturally, any other feature of the SQL syntax can be used with paths: // Using parameters instead of fixed criteria expressions: ... = new SQLQuery<Person>(Person.class, "address.city = ?", "New York"); // Using other SQL comparison operands: ... = new SQLQuery<Person>(Person.class, "address.street.houseNum >= 10"); // Using SQL composition operands to express compound predicates: ... = new SQLQuery<Person>(Person.class, "address.city = 'New York' AND address.street.houseNum >= 10"); Paths can also be specified in ORDER BY and GROUP BY clauses: // Query for Persons in 'Main' street, sort results by city: ... = new SQLQuery<Person>(Person.class, "address.street.name = 'Main' ORDER BY address.city"); // Query for Persons in 'Main' street, group results by city: ... = new SQLQuery<Person>(Person.class, "address.street.name = 'Main' GROUP BY address.city"); IndexingIndexing plays an important role in a system's architecture - a query without indexes can slow down the system significantly. Paths can be indexed to boost performance, similar to properties. @SpaceClass public class Person { private Address address; @SpaceIndex(path = "city") public Address getAddress() { return address; } // Setter method is omitted for brevity. }
Remarks
Nested MapsThe path syntax is tailored to detect Map objects and provide access to their content: when a property's key is appended to the path, it is implicitly resolved to the property's value (at runtime). For example, suppose the Person class contains a phoneNumbers property which is a Map, encapsulating various phone numbers (e.g. home, work, mobile): @SpaceClass public class Person { private Map<String, String> phoneNumbers; // Getter and setter methods are omitted for brevity. } The following example queries for a Person whose phoneNumbers property contains the key-value pair "home" - "555-1234": ... = new SQLQuery<Person>(Person.class, "phoneNumbers.home = '555-1234'");
IndexingPaths containing map keys can be indexed to boost performance, similar to 'regular' paths. @SpaceClass public class Person { private Address address; @SpaceIndex(path = "home") public Map<String, String> getPhoneNumbers() { return phoneNumbers; } // Setter method is omitted for brevity. }
Nested CollectionsThe GigaSpaces SQL syntax supports a special operand [*], which is sometimes referred to as the 'contains' operand. This operand is used in conjunction with collection properties to indicate that each collection item should be evaluated, and if at least one such item matches, the owner entry is considered as matched.
Suppose we have a type called Dealer with a property called cars (which is a list of strings). ... = new SQLQuery<Dealer>(Dealer.class, "cars[*] = 'Honda'"); Now suppose that cars is not a collection of Strings but of a user-defined class called Car, which has a string property called company. ... = new SQLQuery<Dealer>(Dealer.class, "cars[*].company = 'Honda'"); Matching collections within collections recursively is supported as well. ... = new SQLQuery<Dealer>(Dealer.class, "cars[*].tags[*] = 'Convertible'"); Multiple Conditions On Collection ItemsThe scope of the [*] operand is bounded to the predicate it participates in. When using it more than once, each occurrence is evaluated separately. ... = new SQLQuery<Dealer>(Dealer.class, "cars[*].company = 'Honda' AND cars[*].company = 'Subaru'"); You can use parentheses to specify multiple conditions on the same collection item. ... = new SQLQuery<Dealer>(Dealer.class, "cars[*](company = 'Honda' AND color = 'Red')");
IndexingCollection properties can be indexed to boost performance, similar to 'regular' paths, except that each item in the collection is indexed. @SpaceIndex(path = "[*].company") public List<Car> getCars() { return this.cars; }
|
![]() |
GigaSpaces.com - Legal Notice - 3rd Party Licenses - Site Map - API Docs - Forum - Downloads - Blog - White Papers - Contact Tech Writing - Gen. by Atlassian Confluence |