User Guide
3.1 EJB3 QL Querying
The general strucutre of an EJB3 QL format query is:
SELECT Identifier FROM ClassName [AS] Identifier WHERE Identifier.PropertyName OPERATOR Value [ORDER BY Identifier [ASC|DESC]] [NAMESPACE namespace]
The OPERATORs supported in the WHERE clause of EJB3 QL format queries are:
- =
- != (<>)
- >=
- >
- <
- <=
- IS
- NOT
- LIKE
- IN
3.1.1 Basic EJB3 QL Queries
The basic form of the query is:
// SELECT Identifier from ClassName AS Identifier WHERE Identifier.PropertyName
// [=,!=,>=,<=] Value
Map results = index.query(
new EJBQuery("SELECT c FROM com.foo.bah.TestClass AS c WHERE
c.privateField = 20"));
Note: Unlike in the SQL query language in Jofti, the FROM clause is mandatory in the EJB3 format and is where the Class to Identifier mapping is specified.
The identifier specified is then used in the SELECT and WHERE clauses to specify return attributes and query attributes. This query format, unlike the SQL format, does allow the return of field sets from Objects in the Cache, not just whole Objects. This is detailed in section 3.1.11 Retrieving Object attributes EJB3 QL QueriesThis simple form of query we already have in the convenience classes so lets expand the query to do something the Match classes cannot, namely multiple attribute searching.
// SELECT Identifier from ClassName AS Identifier WHERE Identifier.PropertyName
// [=,!=,>=,<=] Value and Identifier.PropertyName2 [=,!=,>=,<=] Value
Map results = index.query(
new EJBQuery("SELECT c FROM com.foo.bah.TestClass AS c WHERE c.privateField
= 20 and c.privateField2 ='some string' "));
It is important to note that the String value is surrounded by 'xxxx' - this is required if the value contain spaces or other non alphanumeric values. E.g. for 'test' we could just write :
// SELECT Identifier from ClassName AS Identifier WHERE Identifier.PropertyName
// [=,!=,>=,<=] Value and Identifier.PropertyName2 [=,!=,>=,<=] Value
Map results = index.query(
new EJBQuery("SELECT c FROM com.foo.bah.TestClass AS c where c.privateField
= 20 and c.privateField2 =test "));
3.1.2 Nested Clauses In EJB3 QL Queries
Jofti uses the standard grouping mechanisms of parenthesis delineated
grouping to
work out how to group clauses in queries.
For example:
// SELECT Identifier from ClassName AS Identifier
// WHERE (Identifier.PropertyName [=,!=,>=,<=] Value AND
// Identifier.PropertyName2 [=,!=,>=,<=] Value)
// OR Identifier.PropertyName2 [=,!=,>=,<=] Value
Map results = index.query(
new EJBQuery("select t FROM com.foo.bah.TestClass AS t WHERE (t.privateField
= 20 and t.privateField2 = 'test') or t.privateField = 10"));
The parenthesis tell Jofti to run the first subquery then to 'OR' the
results
with the second subquery.
Any number of (subquery) groups can be ordered after the where keyword,
however,
they must be separated by
either one of the boolean operators [and|or].
3.1.3 Nested Objects in EJB3 QL Queries
As referred to previously, Jofti also allows you to search on properties that are at an arbitrary level in an Object Graph and select the root object on that basis.
So, for objects that are properties of Objects that are themselves properties of other Objects we can query in the following manner. Assuming a configuration of something similar to below:
DefaultindexConfig config = new DefaultIndexConfig();
config.setCacheAdapter("com.jofti.cache.adapter.EhCacheListenerAdapter");
Index index = manager.addIndex(config,cache,
"class-definitions.xml");
<classes>
<class name="com.test.SimpleTestClass">
<property>privateField</property>
</class>
<class name="com.test.NestedTestClass">
<property>contained.containedField</property>
</class>
</classes>
We can query using:
Map results = index.query(new EJBQuery("SELECT n FROM com.test.NestedTestClass
AS n WHERE n.contained.containedField ='some string'"));
We can see that the query property actually uses the exact property name passed into the configuration (or in the map of properties if you had configured it without using the XML file). You cannot query the same object in this manner:
Map results = index.query(
new EJBQuery("SELECT n FROM com.test.NestedTestClass.contained AS n"+
" WHERE n.containedField ='some string'"));
Map results = index.query(new EJBQuery("SELECT n FROM com.test.Contained AS n"+
" WHERE n.containedField ='some string'"));
In both cases Jofti will not be able to match the class and property referred to with its configuration.
3.4 Using Multiple Classes In EJB3 QL Queries
In order to support the use of multiple classes in queries Jofti requires that each ClassName As Identifer group must be comma separated.
This is used in the following manner:
// SELECT Identifier1, Identifer2 FROM ClassName AS Identifier1, ClassName
// AS Identifier2 WHERE Identifier1.PropertyName [=,!=,>=,<=] Value
// or Identifier2.PropertyName2 [=,!=,>=,<=] Value
Map results = index.query(
new EJBQuery("SELECT str, int from java.lang.String AS str, java.lang.Integer AS int" +
" WHERE int.VALUE = 20 or str.VALUE = 'test' "));
This query will return both Strings and Integers in the results map.
In order to use more than one Class in the query you optionally can use the
AS
keyword to specify an identifier and use this identifier after the where clause,
otherwise
for properties that are the same for the two classes (as in the above
example) it
would not be possible to work out
which was which.
Note: the .VALUE identifier used here is a special value that is used with built-in types
that have no attributes.
3.1.5 Name Spaced Caches and EJB3 QL Queries
As detailed in the earlier "Configuration Of Name Spaced Caches" there is an extra requirement for name spaced caches over and above a normal Cache or Map structure and this is the name space.
Accordingly, the query language requires a way to tell the query engine what name space we are to look in. In absence of any equivalent in the EJB3 Specification Jofti has adopted a NAMESPACE keyword, which is appended onto the query following the where clause.
So to use the name space we would write something like the
following:
Map results = cache.query(new EJBQuery("SELECT s FROM java.lang.String s
WHERE s.value = 'val' NAMESPACE /namespace/one " ));
Notice how we are using the JBossCache example namespace structure to produce the namespace object. It is important that if you want to use the more advanced form of query through the query language, the name spaces you use in JBossCache must be representable as Strings.
Note: You cannot specify more than one namespace in the same query at present and all queries are hierarchical in nature and will search this namespace and any child name spaces.
3.1.6 Using the LIKE operator in EJB3 QL Queries
The standard operators [=,!=,<,<=,>,>=] in Jofti are very simple to use and act as one would expect. Jofti also supports the 'LIKE' operator, which functions very similarly to the EJB3 specification LIKE keyword.
The difference to the standard EJB3 QL keyword is that only String prefix searching can be performed with the like operator. It does not support substring or suffix searching. In addition, only String values can be searched in this manner.
So the following works OK:
Map results = index.query(new EJBQuery("SELECT str FROM java.lang.String AS str
where str.VALUE LIKE 'tes%'"));
However, even though the specification allows the following, it is not supported in Jofti and you cannot do this
Map results = index.query(new EJBQuery("SELECT str FROM java.lang.String AS
str where str.VALUE LIKE 'tes%er'"));
3.1.7 Escape Sequences in EJB3 QL Queries
The escape character adopted by Jofti in its query language is \. The escape character has really only two functions in Jofti, one is to escape the use a single quote ' in a query String, as that character is a delimiter for the value, and the other is to escape itself. So say for instance you wanted to search for the String " ' "
You would write something like:
Map results = index.query(
new EJBQuery("SELECT str from java.lang.String AS str
WHERE str.VALUE = ' \\' ' "));
The above String looks incorrect at first glance as we have two \\ characters. However, Java's escape character is also \. So in order to get want we want in the parser (i.e. \') we have to escape the slash in Java as well (which consumes the first \ and leaves the second for the parser. Meaning we need two slashes.
However, assume that we need to end a String value with a \, which seems straight forward however, we do not want the parser to think that we are escaping the ' at the end of the value.
So we have to write
Map results = index.query(
new EJBQuery("SELECT str from java.lang.String AS str
WHERE str.VALUE = '\\\\'"));
This time we need four slashes. Two of the slashes are escapes in Java which means that although we enter four the parser only sees two (\\). This tells the parser that the \ after the escape character is a literal value and so we don't escape the ' at the end of the value.
As this only matters when we have a String that is trying to escape a ' or have a trailing \ it means that we can write
Map results = index.query(
new EJBQuery("SELECT str from java.lang.String AS str
WHERE str.VALUE = '\\nds\\asd\\test'"));
As a final note it is worth mentioning that is we want a String that ends with a slash and a quote "some String\'" we would have write :
Map results = index.query(
new EJBQuery("SELECT str from java.lang.String AS str
WHERE str.VALUE = 'some String\\\\\\'' "));
3.1.8 Named Parameters in EJB3 QL Queries
EJB3 QL supports the idea of named parameters, where you can specify a query with a variable parameter name and then bind a value to that parameter later on.
In EJB3 this is achieved using the format ':variableName'. The Query object then has a method setParameter(String variableName, Object value) that allows setting of the bind parameter.
This has a number of advantages, among them being:
- It allows the the use of an existing object as part of the query.
- It allows the query to use an object that cannot be constructed from a String.
- It allows the query to be reused without specifying the full query (just changing the parameters).
- The query engine can use a cached version of the query and substitute the parameters for execution with needing to recompile the query.
- It is a much more type safe way of passing parameters into a query.
Note: This mechanism is not currently available in the SQL format query in Jofti.
To illustrate how this works let us consider an example.
EJBQuery query = new EJBQuery("SELECT i from java.lang.Integer AS i
WHERE i.VALUE = :param1 ");
//assume some integer values already indexed
for (int i = 0;i<10;i++){
Map results =
index.query(query.setParameter("param1",new Integer(i)));
// do stuff with results
}
We can see from the example that the query object can be reused with new parameters and that we do not have to turn the Integer into a String.
There are a few things to be aware of when using named parameters:- The named parameter in the query string starts with a ':' but the string value in the setParameter method does not (it has just the variableName part).
- The type of the object passed into the parameter must be assignable (using normal Java assignability rules) to the field type that you are using the parameter to query against. Jofti does not try to coerce the value in any other way.
- Failure to set a corresponding value in the parameters that is mentioned as a variableName declared in the query will result in an exception (they are not optional).
Use of a named parameter can be to replace any variable in the WHERE clause. We can demonstrate this using the IN operator, which takes a Collection argument.
The usual way we would write this without a named parameter would be:
Map res = cache.query(new EJBQuery("SELECT s FROM java.lang.String AS s
WHERE s.value IN ( val,val4)" ));
With named parameters we can pass any type of collection object:
List temp = new ArrayList();
temp.add("val");
temp.add("val4");
Map res = cache.query(new EJBQuery("SELECT s FROM java.lang.String AS s
WHERE s.value IN :param1" ).setParameter("param1",temp));
3.1.9 Positional Parameters in EJB3 QL Queries
Positional parameters are very similar in concept to Named Parameters, but have slightly different semantics.
The difference lies in the use of numeric identifiers for the position in the query, rather than names. Accordingly, the prefix used to identify the parameter is a '?' not a ':'.
It is worthwhile illustrating this using the same query as in the previous section.
EJBQuery query2 = new EJBQuery("SELECT i from java.lang.Integer AS i
WHERE i.VALUE = ?1 ").setParameter(1,new Integer(0));
Map results = index.query(query2);
We can see that this form of query uses the setParameter method that takes an int and an Object, not the String,Object version of the method shown in the Named Parameters section.
3.1.10 Return Types of EJB3 QL Queries
Return types using this form of query are dependent upon the Identifiers mentioned in the SELECT clause of the query. Unlike the EJB3 specification, Jofti does not return a List from its query execution. Instead, it returns a Map of results, with the key being the key of the value in the Cache and the value being the type of the SELECT identifier.# For example:
Map results = index.query( new EJBQuery("SELECT i from java.lang.Integer AS i
WHERE i.VALUE = 0 "));
In this case the Map contains a value type of Integer. We can extend this to return multiple types e.g.
Map results = index.query( new EJBQuery("SELECT s, i from java.lang.Integer AS i,
java.lang.String AS s WHERE i.VALUE = 0 OR s.VALUE='some str' "));
The Map returned, assuming we match some values, will return value types of Strings and Integers.
This can further be extended by returning attributes of Objects instead of the actual Object itself. This is detailed in the next section.
3.1.11 Retrieving Object attributes in EJB3 QL Queries
Jofti also provides the ability to retrieve attributes of the root Objects in a Cache. The specification of the attribute to return is identical in format to the method of specifying attributes in the WHERE clause detailed in chapter 4.
However, it is worthwhile addressing how the attribute specification affect the return types from a query. Essentially, the value will always be retuned as an Object[], which is of length of the number of return attributes, and contains in each slot the type of the attribute (Primitive values are converted to their wrapper types).
Taking, for example a Class (TestClass) that has two attributes <Integer>privateField, <String>privateField2 we can specify that we want just the attributes from the Class, not the whole object.
Map results = index.query(
new EJBQuery("SELECT t.privateField, t.privateField2
FROM com.foo.bah.TestClass AS t where t.privateField = 20"));
In this case the Map contains a value of an Object[2], containing [Integer,String].
Jofti also allows you to obtain collections, or arrays from the parent objects in the same manner.
So, assuming that Test class has a List attribute called privateField3 we would get:
Map results = index.query(
new EJBQuery("SELECT t.privateField3, t.privateField2
FROM com.foo.bah.TestClass AS t where t.privateField = 20"));
The results Map in this instance will have a value type of an Object[2], containing [List,String].
We can take this further by returning elements of a Collection, or Array in the same manner.
Note: Jofti's notation for Arrays and Collections is not part of the EJB3 specification and is further detailed in Section 4 of the User Guide.
Assume that the List contains a collection of com.test.Contained objects which have an attribute called name, we can write:
Map res = cache.query(
new EJBQuery("select t.[com.test.Contained] from FROM com.foo.bah.TestClass AS t
where t.[com.test.Contained].name = 'bill' " )
The return type will actually then be an Object[] which in turn contains an Object[] of com.test.Contained objects.
On first reading of this query you might expect the Object[] to have only Contained objects that match the restriction in the WHERE clause. However, this is not the case. It is very important to stress here that the returned Contained objects will actually be all the Contained objects in the List of TestClass instances that were selected by the WHERE clause.
This is because the WHERE clause operates on restricting the set of objects mentioned in the FROM clause, from which all the Contained elements in the List are extracted and inserted into the Object[]. It is more accurate to say that we are actually matching whole TestClass objects using the WHERE clause, each of which may contain multiple Contained objects in its privateField3 attribute. So we are then selecting all the elements of that list (that match the specified type) mentioned in the SELECT
We can illustrate this by the following:
List list1 = new ArrayList();
list1.add("string1");
list1.add("string2");
index.put("key1",list1);
Map res = index.query(new EJBQuery("select c.[java.lang.String]
from java.util.List c where c.[java.lang.String].VALUE = 'string1' "));
// The query matches List1, we then get all the String type values
// from the List that we have matched
// this results in an Object[] that at position 0 has an Object[2]
// that contains ["string1","string2"] -not just string1 as we are using
// the WHERE clause to restrict entries in the FROM clause
Interestingly, the SELECT format does give us the ability to filter the returned values by type. In the example above if the List contained other types (such as Integers) only the Strings would be returned.
For more detailed information on formats for arrays and different data types see the appropriate section in the Data types Chapter.
This is because in Jofti the SELECT attribute, whether a single value, a Collection, or an array is not actually acted upon by the WHERE clause. Instead, the WHERE clause acts on the root Objects in the FROM clause to restrict the return set and the attributes are then obtained from each matching Object in that set.
We can also use the attribute selection mechanism to return fields that are not indexed for that Object. This is useful,as it means we do not have to take up space in the index with attributes we do not want to search on, but we can still retrieve them if need be.
For example, assume we have only referenced privateField in the index:
<classes>
<class name="com.foo.bah.TestClass">
<property>privateField</property>
</class>
</classes>
We can still write this to return only privateField2 objects:
Map results = index.query(
new EJBQuery("SELECT t.privateField2
FROM com.foo.bah.TestClass AS t where t.privateField = 20"));
We can illustrate this further, with a slightly more complex example.
Assume we have a class called CollectionTester which has java.util.Set attribute and a java.util.List attribute. We can retrieve the Set fromt the Object which depends on a String being present in the List.
Map results = index.query(
new EJBQuery("select c.set from com.test.CollectionTester as c
where c.list.[java.lang.String] = 'value' "));
3.1.12 Specifying named queries in the config file
Using the EJB3 format also allows us to specify in advance named queries in the config file. This is done on a per-index basis so that a query specified for one index will only be accessible for that index
Note:This feature is not available for SQL format queriesFor example:
[?xml version="1.0"?]
[!DOCTYPE cache-config SYSTEM "cache-config.dtd"]
<indexes>
<!-- An instance of an index -->
<index name="test" init-method="init" destroy-method="destroy">
<!-- The type of index to use -->
<type>
com.jofti.tree.TreeIndex
</type>
<!-- The type of parser to use -->
<parser>
com.jofti.introspect.JavaBeanClassIntrospector
</parser>
<!-- The cache to use -->
<cache>
<!-- The adapter for this particular index -->
<adapter init-method="init" destroy-method="destroy">
<type>
com.jofti.cache.adapter.EhCacheAdapter
<type>
<!-- The configuration properties for
the cache implemetation -->
<init-properties>
<property name="file">
cache-configs/ehcache.xml
</property>
</init-properties>
</adapter>
</cache>
<!--
Classes to be indexed
-->
<classes>
<class name="com.test.TestClass">
<property>privateField</property>
</class>
<class name="com.test.TestClass">
<property>contained.containedField</property>
</class>
</classes>
<!--
Queries for the index
-->
<queries>
<query name="query1">
select i from java.lang.Integer as i where i.VALUE =?1
</query>
<query name="query2">
<![CDATA[
select s from java.lang.String as s where s.VALUE >=?1
]]>
</query>
</query>
</index>
</indexes>
We can see from the above example that query1 and query2 are already configured for the index. It is also worth noting that query2 uses the CDATA section as we a > sign in the query which is not allowed in by the XML parser.
We can then use these queries in the following manner:
//assume we have obtained an index already
Map results = cache.query(cache.getQuery("query1").setParameter(1,new Integer(1)));
The query objects returned from the getQuery method are all thread safe , so setting parameters on one instance does not affect any other instances of the same query.
Similarly to including the static queries in the full configuration file it is also possible to use the same approach with the non-preconfiguration approach, with a minimal specification file as detailed in 2.3 Adding Classes to Index with No Configuration.
This looks like:
<classes>
<class name="com.test.SimpleTestClass">
<property>privateField</property>
</class>
<class name="com.test.NestedTestClass">
<property>contained.containedField</property>
</class>
</classes>
<queries>
<query name="query1">
select i from java.lang.Integer as i where i.VALUE =?1
</query>
<query name="query2">
<![CDATA[
select s from java.lang.String as s where s.VALUE >=?1
]]>
</query>
</queries>
3.1.13 Sorting of EJB3QL Query Results
Sorting of the results returned from a query is achieved using the ORDER BY clause. Similarly to the identifiers used in the SELECT clause, the identifiers in the ORDER BY clause also follow the JavaBeans convention of Class.propertyName. The direction of the sort can be set using the ASC for ascending or DESC for descending. The sort direction is optional and defaults to ASC.
For example:
//ascending sort
Map results = index.query(
new EJBQuery("SELECT t
FROM com.foo.bah.TestClass AS t where t.privateField > 20
ORDER BY t.privateField ASC"));
//descending sort
Map results = index.query(
new EJBQuery("SELECT t
FROM com.foo.bah.TestClass AS t where t.privateField > 20
ORDER BY t.privateField DESC"));
To apply the sorting to top level primitive wrapper objects (such as String, Integer,Date etc) you must use the special field name VALUE. For example,
//ascending sort
Map results = index.query(
new EJBQuery("SELECT t
FROM java.lang.String AS t where t.VALUE > 20
ORDER BY t.VALUE ASC"));
As in SQL, we can expect to be able to sort using multiple columns, so we can order by property1, property2 etc. The way to achieve this is to provide a comma separated list of properties.
For example :
//ascending sort on property1 followed by descending sort
// on property2
Map results = index.query(
new EJBQuery("SELECT t
FROM com.foo.bah.TestClass AS t where t.privateField > 20
ORDER BY t.privateField ASC,t.privateField2 DESC"));
//descending sort on property1 followed by ascending sort
// on property2
Map results = index.query(
new EJBQuery("SELECT t
FROM com.foo.bah.TestClass AS t where t.privateField > 20
ORDER BY t.privateField DESC, t.privateField2 ASC"));
When returning a whole object it is possible to specify a property to sort on that is not present in the index. However, the field must be able to be compared in Java and so must be implement Comparable (String, Integer etc).
In addition to sorting objects returned from a query, it is also possible to provide sort order when returning only the attributes of an object. For example:
//ascending sort on property1 followed by descending sort
// on property2
Map results = index.query(
new EJBQuery("SELECT t.privateField,t.privateField2
FROM com.foo.bah.TestClass AS t where t.privateField > 20
ORDER BY t.privateField ASC,t.privateField2 DESC"));
We can see here that although we are returning an Object[] of attributes we can still specify a field to sort on that is present in the array.Unlike when returning a whole object, the attribute to sort on must be present in the return array. i.e the attribute must be specified in the SELECT clause.
There are also some other restrictions in using the ORDER BY clause.
- ORDER BY cannot be specified when more than one class type is specified in the SELECT clause.
- ORDER BY cannot be specified when attributes are specified in the SELECT clause from more than one class.
- ORDER BY cannot be specified on an attribute that does not implement Comparable.
3.1.14 Max results in EJB3 queries
As of 1.2-rc1 Jofti now provides a way to limit the number of results returned from an EJB type query. The functionality is provided on the IndexQuery interface as setMaxResults(int i);.
The use of this is very straight forward:
Map results = index.query(
new EJBQuery("SELECT t
FROM com.foo.bah.TestClass AS t where t.privateField > 20
ORDER BY t.privateField ASC").setMaxResults(10));
MaxResults cannot be negative, and setting 0 will cause it to be ignored. Setting a value that is larger than the actual number of results returned int he query will have no effect.
One thing to be aware of is that the maxResults are not stored in a named or created query. So this:
IndexQuery query = new EJBQuery("select s from java.lang.String s
where s.value > 'val'" ).setMaxResults(5);
cache.addQuery("maxString", query);
Map res = cache.query(cache.getQuery("maxString"));
Will not result in limiting the results when it is executed. The correct behaviour is for the maxResults to be set everytime on a stored or named query. However, reuse of the same query will obviously reuse the maxResults setting (as it is the same query instance):
IndexQuery query = new EJBQuery("select s from java.lang.String s
s.value > 'val'" ).setMaxResults(10);
//returns 10 results
Map res = cache.query(query);
assertEquals(10,res.size());
//also returns 10 results
cache.query(query);
assertEquals(10,res.size());
3.1.15 Paging results in EJB3 queries
The IndexQuery Interface now also supports the idea of paging in queries by the addition of a firstResults(int i) method. This method is 0 indexed and returns results from the nth result onwards. This can then be combined with the maxResults feature to produce a paging metaphor:
IndexQuery query = new EJBQuery("select s from java.lang.String s
s.value > 'val'" ).setMaxResults(10);
//returns first 10 results
Map res = cache.query(query);
//returns second ten
res = cache.query(query).setFirstResult(9),setMaxResults(10);
// returns third set of ten
res = cache.query(query).setFirstResult(19),setMaxResults(10);
Again the firstResult setting are not cached in created or named queries (which are thread safe to share with different result settings).
User Guide
Features
- Multi Cache support
- Transaction support
- Type aware searching
- Configurable property indexing
- Indexing/searching by interfaces
- Support for Dynamic Proxies
- Support for primitve attributes
- Support for Collections and Arrays
- String prefix searching
- Simple query language