Home
ExamplesExamples
The following are some examples of JoSQL usage. As you go further down the page, the more complex the examples become. For all of the examples, the object used is java.io.File. To start with though, here is the basic usage from Java.
// Get a list of objects.
List myObjs = getMyObjects ();

// Create a new Query.
Query q = new Query ();

// Parse the SQL you are going to use.
q.parse (sql);

// Execute the query.
QueryResults qr = q.execute (myObjs);

// Get the query results.
List res = qr.getResults ();

// Do something with the results.
Sections
Basic Where Clauses
The following is about the simplest form you can use, just select some Files with names ending in .java.
SELECT *
FROM   java.io.File
WHERE  name LIKE '%.java'
This simple case will return all the matching files that have a filename ending with .java. Easy ;)

This time return specific information from the File object (the name and length), for the Files with names ending in .java.
SELECT name,
       length
FROM   java.io.File
WHERE  name LIKE '%.java'
Now, the results will be a List of Lists. Each List will contain 2 values, index 0 will be a String (the name of the file), index 1 will be a Number (the length of the file).
Complex Where Clause
This time return specific information from the File object (the name and length), for the Files with names ending in .java and have been modified between 10/10/2000 and 10/10/2001 and are src directories.
SELECT name,
       length
FROM   java.io.File
WHERE  name LIKE '%.java' 
AND    lastModified BETWEEN toDate ('10-10-2000') AND toDate ('10-10-2001')
AND    parent LIKE '%src%'
Again, the results will be a List of Lists. Each List will contain 2 values, index 0 will be a String (the name of the file), index 1 will be a Number (the length of the file). JoSQL uses lazy-evaluation in the WHERE clause so it is better to put clauses that are likely to limit the results most near the top. In this case the clause checking the parent path is probably best off being moved up since it will prevent non src directories from being searched. Notice here that we also make use of the function: toDate to convert the string representation of the date to a java.util.Date object.
Bind Variables
This time return specific information from the File object (the name and length). In this case for the WHERE clause we use bind variables to specify the values instead of hard-coding them in the query.
SELECT name,
       length
FROM   java.io.File
WHERE  name LIKE :fileExt 
AND    lastModified BETWEEN :fromDate AND :toDate
AND    parent LIKE :path
Notice here that for the date used in the BETWEEN clause there is no need to perform a conversion. JoSQL supports Java objects directly, conversion is only generally required when you need to convert from one format to another, for example a string representation to a date.

In this case the bind variables need to be specified in the Query object prior to executing the query, this can be performed thus:
// Set the file extension, note you need to provide the wildcard.
q.setVariable ("fileExt",
               "%.java");

SimpleDateFormat sdf = new SimpleDateFormat ("dd-MM-yyyy");

// Set the from date, we use a java.util.Date object here.
q.setVariable ("fromDate",
               sdf.parse ("10-10-2000"));

// Set the from date, we use a java.util.Date object here.
q.setVariable ("toDate",
               sdf.parse ("10-10-2001"));

// Set the from date, we use a java.util.Date object here.
q.setVariable ("path",
               "%src%");

// Now execute the query.
QueryResults res = q.execute (myFiles);
It should be noted here that those bind variables will remain in the Query object until they are either cleared. This means that you can set them once and then re-execute the query.
Functions
This time return specific information from the File object (the name and length), but process the values with functions first, also use functions in the WHERE clause. 
SELECT upper (fileExtension (name)),
       formatNumber (length),
       formatDateTime (lastModified),
       formatDateTime (parentFile.lastModified)
       formatTimeDuration (lastModified - parentFile.lastModified)
FROM   java.io.File
WHERE  lower (name) LIKE lower (:fileExt) 
AND    toDate (lastModified) BETWEEN :fromDate AND :toDate
AND    upper (parent) LIKE upper (:path)
Starting to get a little more complex here ;) But still pretty simple, note the use of functions in both the SELECT and WHERE clauses. Functions can basically be used everywhere. You can use any function in the SELECT clause but be aware of performance issues since JoSQL DOES NOT perform caching of function return values (to minimize performance implications use the EXECUTE ON clauses). It should be noted that the use of the lower and upper functions here can be re-worked to use the $LIKE operator instead which will perform a case-insensitive comparison instead.
Execute On Clauses
This example uses EXECUTE ON clauses. These clauses allow a number of functions to be defined that should be executed a specific number of times (usually once) at specific points in the execution of the statement and the values returned from the functions saved in save values. Once these functions have been executed the saved values are then available for use in the rest of the statement by using a @ prefix. Thus:
SELECT @maxLength,
       formatNumber (@avgLength),
       formatDate (@newest),
       @mostFiles.name
FROM   java.io.File
EXECUTE ON ALL max (:_allobjs, length) maxLength,
               avg (:_allobjs, length) avgLength,
               max (:_allobjs, lastModified) newest,
               maxObject (:_allobjs, parentFile.listFiles.length) mostFiles
Each of the functions listed in the EXECUTE ON ALL clause is executed once during execution for all the File objects. As can be seen, the save values can then be manipulated via functions as well and can in fact then accessors associated with them as with the maxObject function which in this case will return the directory that has the most files in it.

Now one further note here, because JoSQL does not have the "normal" concept (used by RDBMSs) of grouping it means that in this case that there will be as many results as there are input objects. However this is generally not desirable, so to prevent this, the LIMIT clause should be used thus:
LIMIT   1, 1
Which in this case means, only return a single row, starting at row 1.
Order By
This example provides sorting of a statement, notice that functions, expressions and save values can be used as sort criteria.
SELECT *
FROM   java.io.File
ORDER BY upper (name) DESC, length > @avgLength
EXECUTE ON ALL avg (:_allobjs, length) avgLength
It is also possible to sort on a column in the SELECT clause (Note: in which case, one or more columns must be selected).
SELECT name,
       length
FROM   java.io.File
ORDER BY 1 DESC, 2 DESC
EXECUTE ON ALL avg (:_allobjs, length) avgLength
Of course when sorting on columns indices you can't use a function (for what should be pretty obvious reasons! If the reason isn't obvious then consider the following ORDER BY upper (2), in this case the parser must take the 2 as a numeric literal rather than a column index).
Grouping
One significant different between JoSQL's use of SQL and standard (ANSI) SQL used by RDBMSs is in respect to grouping of data. Since JoSQL has the full power of the Java collections framework available it allows the grouping data AND the items that correspond to that grouping (key) available as well. In practice this is effectively like placing items into a Map with the GROUP BY clause providing a multi-column "key" and the SELECT clause providing a List of objects that map to that key. For example to group on the parent directory, i.e. return all files in each directory:
SELECT *
FROM   java.io.File
GROUP BY   parent    
GROUP BY ORDER parent     
ORDER BY name DESC, length DESC
It should be noted here that the GROUP BY columns can be sorted as well by using the GROUP BY ORDER clause. The ORDER BY clause is then used to sort the results of each List.

To use in Java code:
// Now execute the query.
QueryResults res = q.execute (myFiles);

// Get the group by results.
Map grpBys = res.getGroupByResults ();

Iterator iter = grpBys.keySet ().iterator ();

while (iter.hasNext ())
{

   // Here we will have a List of size 1, index 0 holding 
   // the parent file path.
   List key = (List) iter.next ();

   // The List here will contain "java.io.File" objects.
   List res = (List) grpBys.get (key);

   // Do something with this data.

}
Putting it all together
So, let's solve a real (well potentially real ;) problem.

Let's get ALL Java files from a particular set of files, group them by directory, only return those greater than 10K in size, order the files by file size, then name, order the directories by the last time they were modified. Also, let's work out some overall statistics about the files in each directory, the average file size, the file that was modified last, the smallest file and for each directory how many Java files are in the directory. Here goes!
SELECT *
FROM   java.io.File
WHERE  name LIKE :fileType
AND    length > (:kbsize * 1024) 
GROUP BY parent    
GROUP BY ORDER lastModified     
ORDER BY length, name 
EXECUTE ON GROUP_BY_RESULTS avg (length) avgLength, 
                            minObject (:_allobjs, lastModified) lastModified,
                            minObject (:_allobjs, length) smallest,
                            accessor (:_allobjs, "size") count
Just so you know, JoSQL does have lots more functionality than this but hopefully this has provided a good enough "taster" to get you started! If you have any further questions please don't hesitate to External site, opens in new window contact me for assistance.