Jofti

User Guide

3.2  SQL Format Queries

The general strucutre of an SQL format query is:

SELECT ClassName [FROM namespace] where PropertyName [OPERATOR] Value [ORDER BY Identifier [ASC|DESC]]

The OPERATORs supported in the WHERE clause of the SQL format queries are: >/p>

• =
• != (<>)
• >=
• >
• <
• <=
• IS
• NOT
• LIKE
• IN

Identifiers follow the JavaBean convention of className.propertyName.propertyName to allow for nested property specification in the WHERE and ORDER BY clauses.

The optional namespace FROM clause is for use with name spaced caches such as JBossCache and is detailed in later sections.

3.2.1  Basic SQL Queries

The basic form of the query is:

// select ClassName where PropertyName [=,!=,>=,<=] Value

Map results = index.query(
	new SQLQuery("select com.foo.bah.TestClass where privateField = 20"));
	

This query retrieves entries of TestClass where an Integer privateField is equal to 20.

Unlike SQL there is no FROM clause in the query. This is because for non-name spaced indexes there is no 'Table' equivalent to select from, instead the return object are always full objects and the keys they are under. It is not currently possible in Jofti to return field sets from objects (otherwise we would have adopted an initial syntax to the Hibernate or EJB3 Query Language).

This form of query we already have in the convenience classes so lets expand the query to do something the Match classes cannot.

// select ClassName where PropertyName [=,!=,>=,<=] Value
// and PropertyName2 [=,!=,>=,<=] Value

Map results = index.query(
    new SQLQuery("select com.foo.bah.TestClass where privateField = 20 
	and privateField2 = 'some string'"));

In this code we can see that we are querying for TestClass where both property names match the specified values.
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 ClassName where PropertyName [=,!=,>=,<=] Value
// and PropertyName2 [=,!=,>=,<=] Value

Map results = index.query(
	new SQLQuery("select com.foo.bah.TestClass where privateField = 
	20 and privateField2 = test"));
	

However, for clarity and to save errors later it is strongly recommended that you do use the 'xxx' format.

 

3.2.2  Nested Clauses In SQL Queries

Jofti uses the standard grouping mechanisms of parenthesis delineated grouping to work out how to group clauses in queries.

For example:

// select ClassName where (PropertyName [=,!=,>=,<=] Value
// and PropertyName2 [=,!=,>=,<=] Value) or PropertyName3 =
// [=,!=,>=,<=] Value

Map results = index.query(
	new SQLQuery("select com.foo.bah.TestClass where (privateField 
	= 20 and privateField2 = test") or privateField3 = 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.2.3  Nested Objects in SQL 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");

The class-definitions file has the following structure:

<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 SQLQuery("select com.test.NestedTestClass
    where contained.containedField =30"));

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 
   SQLQuery("select com.test.NestedTestClass.contained where 
       containedField =30"));

Or:

Map results = index.query(new SQLQuery("select com.test.Contained where 
   containedField =30"));

In both cases Jofti will not be able to match the class and property referred to with its configuration.

 

3.2.4  Using Multiple Classes In SQL Queries

In order to support the use of multiple classes in queries Jofti has adopted the SQL alias keyword 'AS'.
The keyword is used in the following manner:

// select ClassName AS alias1, ClassName AS alias2 where
// alias1.PropertyName [=,!=,>=,<=] Value
// and alias2.PropertyName2 [=,!=,>=,<=] Value

Map results = index.query(
    new SQLQuery("select 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 must use the AS keyword to specify an alias and use this alias 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.

Even for queries where the properties do not conflict the AS keyword is mandatory if you want to use more than one class.

 

3.2.5  Name Spaced Caches and SQL 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. Jofti uses the FROM keyword to give an analogous mechanism to SQL's FROM keyword. Although, it is not really the same functionally, it is similar enough in concept (e.g. select some stuff from a nameSpace where these clauses apply) to work reasonably well and also fits into the SQL like language we have adopted.

So to use the name space we would write something like the following:

Map results = index.query(new SQLQuery("select java.lang.String 
    from /namespace/one where value = 'test'"));
	

Notice how we are again 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.2.6  Using the LIKE operator in SQL Queries

The standard operators [=,!=,<,<=,>,>=] in Jofti are very simple to use and act as one would expect. Jofti has another operator 'LIKE' which operates very similar to the SQL LIKE keyword.

The difference to the standard SQL 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 SQLQuery
    (" select java.lang.String where value LIKE 'tester%'"));
            

However, you cannot do this

Map results = index.query(new SQLQuery
    (" select java.lang.String where value LIKE 'te%er'"));
            

 

3.2.7  Escape Sequences in SQL 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 SQLQuery("select com.three  where privateField =' \\' '");
	

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 \, 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 SQLQuery("select com.three  where privateField ='\\\\'");
	

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 dot 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 SQLQuery("select com.three where privateField 
	='\\nds\\asd\\test'");
	

so we need only two \\ when we are dealing with slash separated characters (in order to change the \n into a carriage return inside the String we could simple add another \ before the n - \\\n - to get a literal line break in the String).

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 SQLQuery("select com.three  where privateField 
	='some String\\\\\\'');
	

Four to produce the end slash and two for the quote, in order to make sure that the resulting \ and ' were not part of an escape sequence.

3.2.8  Sorting of SQL 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 SQLQuery("SELECT com.foo.bah.TestClass where privateField > 20 
    ORDER BY privateField ASC"));

//descending sort
Map results = index.query( 		
    new SQLQuery("SELECT com.foo.bah.TestClass where privateField > 20 
    ORDER BY 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 SQLQuery("SELECT java.lang.String as t where t.VALUE > 20 
    ORDER BY t.VALUE ASC"));

As in traditional 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 SQLQuery("SELECT com.foo.bah.TestClass where privateField > 20 
    ORDER BY privateField ASC,privateField2 DESC"));

//descending sort on property1 followed by ascending sort
// on property2
Map results = index.query( 		
    new SQLQuery("SELECT 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).

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 on an attribute that does not implement Comparable.