Summary: The SQLQuery class is used to query the space using
SQL-like syntax.
OverviewThe SQLQuery class is used to query the space using SQL-like syntax. The query statement includes only the WHERE statement part - the selection aspect of a SQL statement is embedded in other parameters for a SQL query.
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)");
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). Starting 8.0.1, 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:
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); // Sub-query example: query = new SQLQuery<Student>(Student.class, "grade >= (SELECT AVG(grade) FROM Student)"); You may also use SQLQuery with queries on nested objects when registering for notifications using // import net.jini.core.event.UnknownEventException and // import java.rmi.RemoteException RemoteEventListener listener = new RemoteEventListener() { public void notify(RemoteEvent theEvent) throws UnknownEventException, RemoteException { System.out.println("got notification "); }; }; DataEventSession des = EventSessionFactory.getFactory( gigaSpace.getSpace()).newDataEventSession(); des.addListener(new SQLQuery<Person>(Person.class, "info.name='John'"), listener); Querying Custom Nested PropertiesCustom nested properties refers to nested properties which are of a user created class type.
Here is an example of a space class (Department) containing custom nested properties (Employee): public class Employee implements Comparable<Employee> { private Integer id; private String name; public Employee(Integer id, String name) { this.id = id; this.name = name; } public Integer getId() { return id; } public String getName() { return name; } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((id == null) ? 0 : id.hashCode()); return result; } @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; Employee other = (Employee) obj; if (id == null) { if (other.id != null) return false; } else if (!id.equals(other.id)) return false; return true; } @Override public int compareTo(Employee other) { return this.name.compareToIgnoreCase(other.name); } } @SpaceClass public class Department { private Integer id; private String name; private Employee manager; private List<Employee> employees; public Department() { } public Department(Integer id, String name, Employee manager, List<Employee> employees) { this.id = id; this.name = name; this.manager = manager; this.employees = employees; } @SpaceId public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Employee getManager() { return manager; } public void setManager(Employee manager) { this.manager = manager; } public List<Employee> getEmployees() { return employees; } public void setEmployees(List<Employee> employees) { this.employees = employees; } } Query Examples: SQLQuery<Department> query = new SQLQuery<Department>(Department.class, "manager = ?"); Employee manager = new Employee(10, "John Doe"); query.setParameter(1, manager); gigaSpace.read(query); SQLQuery<Department> query = new SQLQuery<Department>(Department.class, "employees[*] = ?"); Employee employee = new Employee(1, "Jane Doe"); query.setParameter(1, employee); gigaSpace.read(query); Nested Object Query IndexesAn index can be defined on a nested object field to improve performance. This is highly recommended. It can be defined on a nested property of a space class and will be used implicitly by the queries.
LimitationsCurrent limitations of nested object queries with XAP 8.0:
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 Map<String, MyData> getInfo() { return info; } public void setInfo(Map<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 objects - since the keys in the map are not part of the schema, you can use the map to add or remove data from a space object without changing its structure. Nested CollectionA SQLQuery allows you to query on properties of type java.util.Collection. Starting with XAP 8.0.1 arrays of objects are supported as well. In the following example, we will read a Company object which contains at least one Employee named "John Doe" in its employees collection.
The Company class
@SpaceClass public class Company { // Collection of Pojos private List<Employee> employees; // Collection of Strings private List<String> employeesNames; public void setEmployees(List<Employee> employees) { this.employees = employees; } public List<Employee> getEmployees() { return this.employees; } public void setEmployeesNames(List<String> employeesNames) { this.employeesNames = employeesNames; } public List<String> getEmployeesNames() { return this.employeesNames; } // ... } We write a Company object to space and then read it: // Write List<Employee> employees = new ArrayList<Employee>(); employees.add(new Employee("John Doe")); Company company = new Company("GigaSpaces"); company.setEmployees(employees); List<String> employeesNames = new LinkedList<String>(); employeesNames.add("John Doe"); employeesNames.add("Jane Woo"); company.setEmployeesNames(employeesNames); gigaspace.write(company); // Read using SQLQuery SQLQuery<Company> query1 = new SQLQuery<Company>(Company.class, "employees[*].name = 'John Doe'"); Company result1 = gigaspace.read(query1); SQLQuery<Company> query2 = new SQLQuery<Company>(Company.class, "employeesNames[*] = 'Jane Woo'"); Company result2 = gigaspace.read(query2); Matching a Collection property supports any number of nesting levels, e.g.: SQLQuery<Company> query = new SQLQuery<Company>(Company.class,
"employees[*].children[*].name = 'Junior Doe');
This will return all Company objects which has an Employee which has a child named "Junior Doe". Enum PropertiesYou can use an Enum field when querying the space with SQLQuery by specifying the Enum's string value or by setting it as a parameter. In the following example we will query a Vehicle object with a VehicleType Enum field.
The Vehicle class
public class Vehicle { public enum VehicleType { CAR, BIKE, TRUCK }; // ... private VehicleType type; public VehicleType getType() { return this.type; } public void setType(VehicleType type) { this.type = type; } // ... } Example of a query using a parameter: SQLQuery<Vehicle> query = new SQLQuery<Vehicle>(Vehicle.class, "type = ?"); query.setParameter(1, VehicleType.CAR); Vehicle result = gigaspaces.read(query); Here's an example of a query that uses the Enum string value: SQLQuery<Vehicle> query = new SQLQuery<Vehicle>(Vehicle.class, "type = 'TRUCK'"); Vehicle result = gigaspaces.read(query);
Date PropertiesWhen you have Date values that you want to query it is recommended to index them with extended indexing which enables to efficiently execute relative (i.e., greater-than or less-than) queries. Here's an example:
The space class
public class MyClass { java.util.Date date ; @SpaceIndex(type=SpaceIndexType.EXTENDED) public java.util.Date getDate() { return date; } public void setDate(java.util.Date date) { this.date=date; } }
The application code
GigaSpace gigaspace = new GigaSpaceConfigurer( new UrlSpaceConfigurer("/./mySpace")).gigaSpace(); Date startDate = ...; Date endDate = ...; SQLQuery<MyClass> query = new SQLQuery<MyClass>(MyClass.class, "date > ? and date < ?"); query.setParameter(1, startDate); query.setParameter(2, endDate); MyClass ret[]= gigaspace.readMultiple(query); Date, Time and DateTime Type FormatGigaSpaces supports different date, time, and datetime formats for the JDBC API and SQLQuery API. The different date, time, and datetime format support is implemented by defining the space date, datetime, and time format as part of deployment configuration file (pu.xml) or when using the relevant API to construct a space instance. The following space properties should be used: space-config.QueryProcessor.date_format space-config.QueryProcessor.datetime_format space-config.QueryProcessor.time_format These should have valid Java format pattern as defined in the official Java language 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 configures 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 SQLQuery code would then look as follows:: GigaSpace space; SQLQuery<TestClass> query = new SQLQuery<TestClass>( TestClass.class.getName() ,"dateField < '2020-03-03 10:10:10' "); Object ret[] = space.readMultiple(query);
In any case, the most recommended way in such cases is to use parameterized queries which avoid the parsing of strings to dates altogether. 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.
Optimization TipsRe-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') 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 |