JoSQL User Manual - Select Clause

Select Clause

The SELECT clause allows you to return values from the objects that match the WHERE clause. In general you can either return the objects that matched or values (gained from accessors) from the objects that matched. Functions, save values, bind variables and expressions can be also returned in the SELECT columns. Columns can be assigned an "alias" value, aliases are only useful in special circumstances, the AS keyword can be used to indicate the alias but it is optional. Multi-word aliases can be quoted (single or double quotes). For example, to return objects use the * value:

SELECT *
FROM   java.io.File

To return individual values from each of the objects:

SELECT name,
       length,
       lastModified,
       parent,
       parentFile.lastModified
FROM   java.io.File

Each of the values selected above are accessors. The full Java object model is available when using the accessors which is why it is possible to return the last modified time of the parent directory in this case.

The values gained in the SELECT clause are held in the org.josql.QueryResults object and can be accessed via the: getResults() method. When selecting objects (using *) the List returned from getResults () will contain the objects themselves. When selecting columns the List will contain other Lists, one per "row".

It is also possible to return distinct values using the DISTINCT keyword, thus:

SELECT DISTINCT name
FROM   java.io.File
WHERE  name LIKE '%.java'

This will return the unique set of .java filenames from the those passed in. Note: the use of DISTINCT when returning objects via * is unnecessary, unless your input List of objects contains duplicates.

A full example is given below:

SELECT name Name,
       upper (name) 'Upper Name',
       replace (parent, "", '/') + "/" + name AS "Full Pathname",
       formatDateTime (lastModified) AS "Last Modified",
       formatNumber (length) "File Length",
       :_currobj FileObject,
       @avgLength
FROM   java.io.File
EXECUTE ON RESULTS avg (length) avgLength

Special Handling for Collections and Maps

There are often times when you are dealing with an object that contains a collection or map within it. For example:

public class MyObject
{

   public Collection collection = new ArrayList ();
   public Map map = new HashMap ();

   public Collection getCollection () { return this.collection; }

   public Map getMap () { return this.map; }     

}

Given this object, it is sometimes desirable to get the objects held within the collection or map and have them added to the query results, rather than the objects that match the WHERE clause. In other words this type of statement is needed:

SELECT collection
FROM   MyObject

However, all that would happen in this case is that the Collection object would be added to the results instead of the objects within the collection. As of 0.9 it is now possible to get JoSQL to add the objects within the collection or map via the special syntax, either: [*] for a collection, or: [*, key|value] for a map. Thus:

SELECT [*] collection
FROM   MyObject

And to add the map keys:

SELECT [*,key] map
FROM   MyObject

And map values:

SELECT [*,value] map
FROM   MyObject

Iteration order (and hence order of addition) is provided by the Map iterator. There are limitations here however, to maintain the purpose of JoSQL (and the true purpose of Java collections, i.e. that of type homogeneity) you can only add objects from collections/maps OR select the objects (via *) OR select columns, they cannot be mixed since there is then a good chance of type incompatibility. Thus the following is invalid:

SELECT [*,value] map,
       hashCode
FROM   MyObject